Table operations statistics

Spanner provides built-in tables that records the read (or query), write, and delete operations statistics for your tables (including change streams tables) and indexes. With table operations statistics you can do the following:

  • Identify tables with increased write traffic corresponding to storage increase.

  • Identify tables with unexpected read, write, and delete traffic.

  • Identify heavily-used tables.

When you query or write to a table, the corresponding operation count for the table increments by 1, regardless of the number of rows accessed.

Overall operations-per-second metrics of a database can be monitored with Operations per second, Operations per second by API method, and other related metrics in your System Insights charts.

Availability

Spanner provides the table operations statistics in the SPANNER_SYS schema. SPANNER_SYS data is available only through SQL interfaces. For example:

  • A database's Spanner Studio page in the Google Cloud console

  • The gcloud spanner databases execute-sql command

  • The executeQuery API

Other single read methods that Spanner provides don't support SPANNER_SYS.

Table operations statistics

The following tables track the read (or query), write, and delete statistics on your tables and indexes during a specific time period:

  • SPANNER_SYS.TABLE_OPERATIONS_STATS_MINUTE: Operations during 1 minute intervals
  • SPANNER_SYS.TABLE_OPERATIONS_STATS_10MINUTE: Operations during 10 minute intervals
  • SPANNER_SYS.TABLE_OPERATIONS_STATS_HOUR: Operations during 1 hour intervals

These tables have the following properties:

  • Each table contains data for non-overlapping time intervals of the length that the table name specifies.

  • Intervals are based on clock times. 1 minute intervals start on the minute, 10 minute intervals start every 10 minutes starting on the hour, and 1 hour intervals start on the hour.

    For example, at 11:59:30 AM, the most recent intervals available to SQL queries are:

    • 1 minute: 11:58:00–11:58:59 AM
    • 10 minute: 11:40:00–11:49:59 AM
    • 1 hour: 10:00:00–10:59:59 AM

Schema for all table operations statistics tables

Column name Type Description
INTERVAL_END TIMESTAMP End of time interval in which the table sizes were collected.
TABLE_NAME STRING Name of the table or the index.
READ_QUERY_COUNT INT64 Number of queries or reads reading from the table.
WRITE_COUNT INT64 Number of queries writing to the table.
DELETE_COUNT INT64 Number of queries performing deletes on the table.

If you insert data into your database using mutations, the write_count increments by 1 for each table accessed by the insert statement. In addition, a query that accesses an index, without scanning the underlying table, only increments the read_query_count on the index.

Data retention

At a minimum, Spanner keeps data for each table for the following time periods:

  • SPANNER_SYS.TABLE_OPERATIONS_STATS_MINUTE: Intervals covering the previous 6 hours.

  • SPANNER_SYS.TABLE_OPERATIONS_STATS_10MINUTE: Intervals covering the previous 4 days.

  • SPANNER_SYS.TABLE_OPERATIONS_STATS_HOUR: Intervals covering the previous 30 days.

Example queries

This section includes several example SQL statements that retrieve aggregate table operations statistics. You can run these SQL statements using the client libraries, or the gcloud spanner.

Query the tables and indexes with the most write operations for the most recent interval

    SELECT interval_end,
          table_name,
          write_count
    FROM spanner_sys.table_operations_stats_minute
    WHERE interval_end = (
          SELECT MAX(interval_end)
          FROM spanner_sys.table_operations_stats_minute)
    ORDER BY write_count DESC;

Query the tables and indexes with the most delete operations for the most recent interval

    SELECT interval_end,
          table_name,
          delete_count
    FROM spanner_sys.table_operations_stats_minute
    WHERE interval_end = (
          SELECT MAX(interval_end)
          FROM spanner_sys.table_operations_stats_minute)
    ORDER BY delete_count DESC;

Query the tables and indexes with the most read and query operations for the most recent interval

    SELECT interval_end,
          table_name,
          read_query_count
    FROM spanner_sys.table_operations_stats_minute
    WHERE interval_end = (
          SELECT MAX(interval_end)
          FROM spanner_sys.table_operations_stats_minute)
    ORDER BY read_query_count DESC;

Query the usage of a table over the last 6 hours

    SELECT interval_end,
           read_query_count,
           write_count,
           delete_count
    FROM spanner_sys.table_operations_stats_minute
    WHERE table_name = table_name
    ORDER BY interval_end DESC;

Where:

  • table_name must be an existing table or index in the database.

Query the usage of a table over the last 14 days

GoogleSQL 

SELECT interval_end,
       read_query_count,
       write_count,
       delete_count
FROM spanner_sys.table_operations_stats_hour
WHERE interval_end > TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -14 DAY)
      AND table_name = table_name
ORDER BY interval_end DESC;

Where:

  • table_name must be an existing table or index in the database.

Query the tables and indexes with no usage in the last 24 hours

GoogleSQL 

(SELECT t.table_name
 FROM  information_schema.tables AS t
 WHERE t.table_catalog = ""
   AND t.table_schema = ""
   AND t.table_type = "BASE TABLE"
 UNION ALL
 SELECT cs.change_stream_name
 FROM information_schema.change_streams cs
 WHERE cs.change_stream_catalog = ""
   AND cs.change_stream_schema = ""
 UNION ALL
 SELECT idx.index_name
 FROM information_schema.indexes idx
 WHERE idx.index_type = "INDEX"
   AND idx.table_catalog = ""
   AND idx.table_schema = "")
 EXCEPT ALL
(SELECT  DISTINCT(table_name)
 FROM spanner_sys.table_operations_stats_hour
 WHERE interval_end > TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -24 HOUR));

What's next