Spanner provides built-in tables that store statistics about reads. You canretrieve statistics from theseSPANNER_SYS.READ_STATS* tables using SQLstatements.

Spanner provides the read statistics in theSPANNER_SYS schema. You can use the following ways to accessSPANNER_SYS data:

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

• Thegcloud spanner databases execute-sql command.

• TheexecuteSqlor theexecuteStreamingSqlmethod.

The following single read methods that Spanner providesdon't supportSPANNER_SYS:

• Performing a strong read from a single row or multiple rows in a table.
• Performing a stale read from a single row or multiple rows in a table.
• Reading from a single row or multiple rows in a secondary index.

CPU usage grouped by read shape

The following tables track the read shapes with the highest CPU usage during aspecific time period:

• SPANNER_SYS.READ_STATS_TOP_MINUTE: Read shape statistics aggregated across 1minute intervals.
• SPANNER_SYS.READ_STATS_TOP_10MINUTE: Read shape statistics aggregated across10 minute intervals.
• SPANNER_SYS.READ_STATS_TOP_HOUR: Read shape statistics aggregated across 1hour intervals.

These tables have the following properties:

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

• Intervals are based on clock times. 1 minute intervals end on the minute, 10minute intervals end every 10 minutes starting on the hour, and 1 hour intervalsend on the hour. After each interval, Spanner collects data from allservers and then makes the data available in the SPANNER_SYS tables shortlythereafter.

  For example, at 11:59:30 AM, the most recent intervals available to SQLqueries 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

• Spanner groups the statistics by read shape. If a tag is present,FPRINT is the hash of the tag. Otherwise, it is the hash of theREAD_COLUMNS value.

• Each row contains statistics for all executions of a particular read shapethat Spanner captures statistics for during the specified interval.

• If Spanner is unable to store information about every distinct readshape run during the interval, the system prioritizes read shapes with thehighest CPU usage during the specified interval.

Table schema

Example queries

This section includes several example SQL statements that retrieve readstatistics. You can run these SQL statements using theclient libraries, thegcloud spanner, or theGoogle Cloud console.

List the basic statistics for each read shape in a given time period

The following query returns the raw data for the top read shapes in the mostrecent 1-minute time intervals.

SELECTfprint,read_columns,execution_count,avg_cpu_seconds,avg_rows,avg_bytes,avg_locking_delay_seconds,avg_client_wait_secondsFROMspanner_sys.read_stats_top_minuteORDERBYinterval_endDESCLIMIT3;

Query output

List the read shapes, ordered by highest total CPU usage

The following query returns the read shapes with the highest CPU usage in themost recent hour:

SELECTread_columns,execution_count,avg_cpu_seconds,execution_count*avg_cpu_secondsAStotal_cpuFROMspanner_sys.read_stats_top_hourWHEREinterval_end=(SELECTMAX(interval_end)FROMspanner_sys.read_stats_top_hour)ORDERBYtotal_cpuDESCLIMIT3;

Query output

Aggregate statistics

SPANNER_SYS also contains tables to store aggregate read statistics capturedby Spanner in a specific time period:

• SPANNER_SYS.READ_STATS_TOTAL_MINUTE: Aggregate statistics for all readshapes during 1 minute intervals.
• SPANNER_SYS.READ_STATS_TOTAL_10MINUTE: Aggregate statistics for all readshapes during 10 minute intervals.
• SPANNER_SYS.READ_STATS_TOTAL_HOUR: Aggregate statistics for all read shapesduring 1 hour intervals.

Aggregate statistics tables have the following properties:

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

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

  For example, at 11:59:30 AM, the most recent intervals available to SQLqueries on aggregate read statistics 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

• Each row contains statistics forall read shapes executed over thedatabase during the specified interval, aggregated together. There is only onerow per time interval.

• The statistics captured in theSPANNER_SYS.READ_STATS_TOTAL_* tables mightinclude read shapes that Spanner did not capture in theSPANNER_SYS.READ_STATS_TOP_* tables.

• Some columns in these tables are exposed as metrics in Cloud Monitoring.The exposed metrics are:

  • Rows returned count
  • Read execution count
  • Read CPU time
  • Locking delays
  • Client wait time
  • Leader refresh delay
  • Bytes returned count

  For more information, seeSpanner metrics.

Table schema

Example queries

This section includes several example SQL statements that retrieve aggregate readstatistics. You can run these SQL statements using theclient libraries, thegcloud spanner, or theGoogle Cloud console.

Find the total CPU usage across all read shapes

The following query returns the number of CPU hours consumed by read shapes inthe most recent hour:

SELECT(avg_cpu_seconds*execution_count/60/60)AStotal_cpu_hoursFROMspanner_sys.read_stats_total_hourWHEREinterval_end=(SELECTMAX(interval_end)FROMspanner_sys.read_stats_total_hour);

Query output

Find the total execution count in a given time period

The following query returns the total number of read shapes executed in the mostrecent complete 1-minute interval:

SELECTinterval_end,execution_countFROMspanner_sys.read_stats_total_minuteWHEREinterval_end=(SELECTMAX(interval_end)FROMspanner_sys.read_stats_total_minute);

Query output

Data retention

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

• SPANNER_SYS.READ_STATS_TOP_MINUTE andSPANNER_SYS.READ_STATS_TOTAL_MINUTE: Intervals covering the previous 6hours.

• SPANNER_SYS.READ_STATS_TOP_10MINUTE andSPANNER_SYS.READ_STATS_TOTAL_10MINUTE: Intervals covering the previous 4days.

• SPANNER_SYS.READ_STATS_TOP_HOUR andSPANNER_SYS.READ_STATS_TOTAL_HOUR: Intervals covering the previous 30 days.

SELECTinterval_end,ROUND(avg_cpu_seconds,4)asavg_cpu_seconds,execution_count,avg_locking_delay_secondsFROMSPANNER_SYS.READ_STATS_TOTAL_10MINUTEWHEREinterval_end>="2020-05-28T16:30:00"ANDinterval_end<="2020-05-28T19:30:00"ORDERBYinterval_end;

SELECTread_columns,ROUND(avg_cpu_seconds,4)asavg_cpu_seconds,execution_count,avg_rowsFROMSPANNER_SYS.READ_STATS_TOP_10MINUTEWHEREinterval_end="2020-05-28T19:20:00"ORDERBYavg_cpu_secondsDESCLIMIT3;

The following data as an example of the result we get back from our query,returning information about the top three read shapes ranked byavg_cpu_seconds. Note the use ofROUND in our query to restrictthe output ofavg_cpu_seconds to 4 decimal places.

¹_exists is an internal field that is used to check whether acertain row exists or not.

One reason for high CPU usage might be that you start to execute a few readshapes more frequently (execution_count). Perhaps the average number of rowsthat the read returned has increased (avg_rows). If none of those propertiesof the read shape reveal anything interesting, you can examine other propertiessuch asavg_locking_delay_seconds,avg_client_wait_seconds oravg_bytes.

Apply best practices to reduce high CPU usage

When you have gone through the preceding steps, consider whether supplying anyof these best practices will help your situation.

• The number of times Spanner executed read shapes during the intervalis a good example of a metric that needs a baseline to tell you if a measurementis reasonable or a sign of a problem. Having established a baseline for themetric, you'll be able to detect and investigate the cause of any unexpecteddeviations from normal behavior.

• If CPU Usage is relatively constant most of the time, but suddenly shows aspike that can be correlated with a similar sudden spike in user requests orapplication behavior, it might be an indication that everything is working asexpected.

• Try the following query to find the top read shapes ranked by the number oftimes Spanner executed for each read shape:

  SELECTinterval_end,read_columns,execution_countFROMSPANNER_SYS.READ_STATS_TOP_MINUTEORDERBYexecution_countDESCLIMIT10;

• If you are looking for the lowest possible read latencies, especially whenusing multi-region instance configurations, usestale reads instead of strongreads to reduce or remove theAVG_LEADER_REFRESH_DELAY_SECONDScomponent of read latency.

  Note: Stale reads don't provide any latency benefits in regionalconfigurations, so you should almost always use strong reads when yourinstance does not have a multi-region configuration.

• If you are only doing reads, and you can express your read using asingleread method, you should use that single read method. Singlereads don't lock, unlike read-write transactions, therefore you should useread-only transactions over more expensive read-write transactions when you arenot writing data.

What's next

• Learn about otherIntrospection tools.
• Learn about other information Spanner stores for each database in thedatabase'sinformation schema tables.
• Learn more aboutSQL best practices forSpanner.
• Learn more aboutInvestigating high CPUutilization.