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 intervalsSPANNER_SYS.QUERY_STATS_TOP_10MINUTE
: Queries during 10 minute intervalsSPANNER_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 intervalsSPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE
: Queries during 10 minute intervalsSPANNER_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 theTOP
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
andSPANNER_SYS.QUERY_STATS_TOTAL_MINUTE
: Intervals covering the previous 6 hours.SPANNER_SYS.QUERY_STATS_TOP_10MINUTE
andSPANNER_SYS.QUERY_STATS_TOTAL_10MINUTE
: Intervals covering the previous 4 days.SPANNER_SYS.QUERY_STATS_TOP_HOUR
andSPANNER_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
- Learn how to view query statistics in the Cloud Console.
- Learn about other information Cloud Spanner stores for each database in the database's information schema tables.
- Learn more about SQL best practices for Cloud Spanner.