Query statistics tables

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

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.

Оцените, насколько информация на этой странице была вам полезна:

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Обновлено Декабрь 4, 2019

Query statistics tables

Usage

CPU usage grouped by query

Table schema

Tables for aggregate statistics

Table schema

Data retention

Example queries

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

Listing the queries with the highest CPU usage

Finding the total execution count in a given time period

Finding the average latency for a query

Finding the queries that scan the most data

Totaling the CPU usage across all queries

What's next

Оставить отзыв о...