Query statistics

Cloud Spanner provides built-in tables that keep many statistics for the queries that used the most CPU, and all queries in aggregate. The Google Cloud Console uses the data in these SPANNER_SYS.QUERY_STATS* tables to generate its Query statistics view, but you can also retrieve statistics from the tables using SQL statements.

Usage

SPANNER_SYS data is available only through SQL interfaces (for example, executeQuery and gcloud spanner databases execute-sql); other single read methods Cloud Spanner provides do not support SPANNER_SYS.

CPU usage grouped by query

The following tables track the queries with the highest CPU usage during a specific time period:

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE: Queries during 1 minute intervals
  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE: Queries during 10 minute intervals
  • SPANNER_SYS.QUERY_STATS_TOP_HOUR: Queries during 1 hour intervals

These tables have the following properties:

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

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

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

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

  • Cloud Spanner groups the statistics by the text of the SQL query. If a query uses query parameters, Cloud Spanner groups all executions of that query into one row. If the query uses string literals, Cloud Spanner only groups the statistics if the full query text is identical; when any text differs, each query appears as a separate row.

  • Each row contains statistics for all executions of a particular SQL query that Cloud Spanner captures statistics for during the specified interval.

  • If Cloud Spanner is unable to store all queries run during the interval, the system prioritizes queries with the highest CPU usage during the specified interval.

Table schema

Column name Type Description
INTERVAL_END TIMESTAMP End of the time interval that the included query executions occurred in.
TEXT STRING SQL query text, truncated to approximately 64KB.
TEXT_TRUNCATED BOOL Whether or not the query text was truncated.
TEXT_FINGERPRINT INT64 Hash of the query text.
EXECUTION_COUNT INT64 Number of times Cloud Spanner saw the query during the interval.
AVG_LATENCY_SECONDS FLOAT64 Average length of time, in seconds, for each query execution within the database. This average excludes the encoding and transmission time for the result set as well as overhead.
AVG_ROWS FLOAT64 Average number of rows that the query returned.
AVG_BYTES FLOAT64 Average number of data bytes that the query returned, excluding transmission encoding overhead.
AVG_ROWS_SCANNED FLOAT64 Average number of rows that the query scanned, excluding deleted values.
AVG_CPU_SECONDS FLOAT64 Average number of seconds of CPU time Cloud Spanner spent on all operations to execute the query.

Tables for aggregate statistics

There are also tables that track aggregate data for all the queries for which Cloud Spanner captured statistics in a specific time period:

  • SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: Queries during 1 minute intervals
  • SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: Queries during 10 minute intervals
  • SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: Queries during 1 hour intervals

These tables have the following properties:

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

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

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

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

  • Each row contains statistics for all queries executed over the database during the specified interval, aggregated together. There is only one row per time interval.

  • The statistics captured in the TOTAL tables might include queries that Cloud Spanner did not capture in the TOP tables.

Table schema

Column name Type Description
INTERVAL_END TIMESTAMP End of the time interval that the included query executions occurred in.
EXECUTION_COUNT INT64 Number of times Cloud Spanner saw the query during the interval of time.
AVG_LATENCY_SECONDS FLOAT64 Average length of time, in seconds, for each query execution within the database. This average excludes the encoding and transmission time for the result set as well as overhead.
AVG_ROWS FLOAT64 Average number of rows that the query returned.
AVG_BYTES FLOAT64 Average number of data bytes that the query returned, excluding transmission encoding overhead.
AVG_ROWS_SCANNED FLOAT64 Average number of rows that the query scanned, excluding deleted values.
AVG_CPU_SECONDS FLOAT64 Average number of seconds of CPU time Cloud Spanner spent on all operations to execute the query.

Data retention

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

  • SPANNER_SYS.QUERY_STATS_TOP_MINUTE and SPANNER_SYS.QUERY_STATS_TOTAL_MINUTE: Intervals covering the previous 6 hours.

  • SPANNER_SYS.QUERY_STATS_TOP_10MINUTE and SPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE: Intervals covering the previous 4 days.

  • SPANNER_SYS.QUERY_STATS_TOP_HOUR and SPANNER_SYS.QUERY_STATS_TOTAL_HOUR: Intervals covering the previous 30 days.

Example queries

This section includes several example SQL statements that retrieve query statistics. You can run these SQL statements using the client libraries, the gcloud command-line tool, or the Cloud Console.

Listing the basic statistics for each query in a given time period

The following query returns the raw data for the top queries in the previous minute:

SELECT text,
       interval_end,
       execution_count,
       avg_latency_seconds,
       avg_rows,
       avg_bytes,
       avg_rows_scanned,
       avg_cpu_seconds
FROM spanner_sys.query_stats_top_minute
ORDER BY interval_end;

Listing the queries with the highest CPU usage

The following query returns the queries with the highest CPU usage in the previous hour:

SELECT text,
       execution_count AS count,
       avg_latency_seconds AS latency,
       avg_cpu_seconds AS cpu,
       execution_count * avg_cpu_seconds AS total_cpu
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY total_cpu DESC;

Finding the total execution count in a given time period

The following query returns the total number of queries executed in the most recent complete 1-minute interval:

SELECT interval_end,
       execution_count
FROM spanner_sys.query_stats_total_minute
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_minute)
ORDER BY interval_end;

Finding the average latency for a query

The following query returns the average latency information for a specific query:

SELECT avg_latency_seconds
FROM spanner_sys.query_stats_top_hour
WHERE text LIKE "SELECT x FROM table WHERE x=@foo;";

Finding the queries that scan the most data

You can use the number of rows scanned by a query as a measure of the amount of data that the query scanned. The following query returns the number of rows scanned by queries executed in the previous hour:

SELECT text,
       execution_count,
       avg_rows_scanned
FROM spanner_sys.query_stats_top_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_top_hour)
ORDER BY avg_rows_scanned DESC;

Totaling the CPU usage across all queries

The following query returns the number of CPU hours used in the previous hour:

SELECT (avg_cpu_seconds * execution_count / 60 / 60)
  AS total_cpu_hours
FROM spanner_sys.query_stats_total_hour
WHERE interval_end =
  (SELECT MAX(interval_end)
   FROM spanner_sys.query_stats_total_hour);

What's next