This page discusses Spanner time to live (TTL) metrics. To learn more,seeAbout TTL.

Metrics

Spanner provides information about TTL activities in a systemtable that can be read with SQL queries, and as metrics accessed throughCloud Monitoring.

The system table reports TTL information per table for a database, whileCloud Monitoring reports metrics at a database level.

Use a SQL query

Spanner provides a built-in table that tracks information relatedto TTL. The table is namedSPANNER_SYS.ROW_DELETION_POLICIES and has thefollowing schema.

The deletion policy information is returned per table for your database.

You can query this data with a SQL query similar to the following:

SELECTTABLE_NAME,UNDELETABLE_ROWSFROMSPANNER_SYS.ROW_DELETION_POLICIESWHEREUNDELETABLE_ROWS >0

TheSPANNER_SYS tables are only accessible through SQL interfaces;for example:

• TheSpanner Studio page in the Google Cloud console
• Thegcloud spanner databases execute-sql command
• TheexecuteQuery API

Other single read methods that Spanner provides don't supportSPANNER_SYS.

Use Cloud Monitoring

Spanner provides the following metrics to monitor TTL activity ata database level:

• row_deletion_policy/deleted_rows is the number of rows deleted by the TTLpolicy.
• row_deletion_policy/undeletable_rows is the number of rows that match therow deletion (GoogleSQL) orTTL INTERVAL(PostgreSQL)statement, but that cannot be deleted. This is usually because the row had toomany child rows, causing the action to exceed Spanner'stransaction limit.
• row_deletion_policy/processed_watermark_age is the time between now and theread timestamp used by the last successful cycle (with or without undeletablerows).

These metrics are available throughCloud Monitoringand theGoogle Cloud console.

Monitor

You can also monitor other TTL activities.

Find last successful scan

You can find the last snapshot time at which Spanner completed ascan of the table looking for expired rows. To do so as a SQL query:

SELECTPROCESSED_WATERMARKFROMSPANNER_SYS.ROW_DELETION_POLICIESWHERETABLE_NAME=$name

Alternatively, therow_deletion_policy/process_watermark_age metric displayssimilar information, but is expressed as the difference between the currenttime and the last scan time. The metric is not broken down by table, butrepresents the oldest scan time of any TTL-enabled tables in the database.

Rows that match a TTL policy are typically deleted within 72 hours of theirexpiration date. You canset an alert onprocessed_watermark_age so that you are notified if it exceeds 72 hours.

Ifprocessed_watermark_age is older than 72 hours, it may indicate thathigher-priority tasks are preventing TTL from running. In this case, werecommend checkingCPU utilization andadding more compute capacity if required. If CPU utilization iswithin the recommended range, check for hotspotting usingKey Visualizer.

Monitor deleted rows

To monitor TTL activity on your table, graph therow_deletion_policy/deleted_rows metric. This metric displays the number ofrows deleted over time.

If no data has expired, this metric is empty.

Monitor undeletable rows

When TTL is unable to delete a row, Spanner automatically retries.If, upon retry, the TTL action cannot be processed, Spanner skipsthe row and reports it in therow_deletion_policy/undeletable_rows_countmetric.

You canset an alert on therow_deletion_policy/undeletable_rows_count to be notified of a non-zerocount.

If you find a non-zero count, you can create a query to break down thecount by table:

SELECTTABLE_NAME,UNDELETABLE_ROWS,MIN_UNDELETABLE_TIMESTAMPFROMSPANNER_SYS.ROW_DELETION_POLICIESWHEREUNDELETABLE_ROWS >0

To look up the contents of the undeletable row:

SELECT*FROM$TABLE_NAMEWHERE$EXPIRE_COL>=$MIN_UNDELETABLE_TIMESTAMP

Most commonly, a row deletion failure is due to cascading updates tointerleaved tables and indexes such that the resulting transaction size exceedsSpanner'smutation limits. You can resolve theissue by updating your schema to add separateTTL policies on interleaved tables.