Built-in statistics tables for Spanner help you investigate issues in your database. You can query these tables to gain insight about queries, transactions, and reads. The following list summarizes each statistics table and the information it offers:
Query statistics
When investigating issues in your database, it is helpful to know which queries are expensive, run frequently or scan a lot of data.
Query statistics are aggregated statistics for queries (including DML statements and change stream queries), gathered in 1, 10, and 60 minute intervals. Statistics are collected for statements that completed successfully as well as those that failed, timed out, or were canceled by the user.
The statistics include highest CPU usage, total query execution counts, average latency, most data scanned, and additional basic query statistics. Use these statistics to help identify expensive, frequently run or data-intensive queries.
You can visualize these metrics on a time series by using Query insights dashboards. These pre-built dashboards help you view spikes in CPU utilization and identify inefficient queries.
Oldest active queries
Sometimes you want to look at the current workload on the system by examining running queries. Use the Oldest active queries tool to investigate long running queries that may be having an impact on database performance. This tool tells you what the queries are, when they started running and to which session they belong.
Change stream queries are not included in oldest active queries.
Read statistics
Read statistics can be used to investigate the most common and most resource-consuming reads on your database using the Spanner Reads API. These statistics are collected and stored in 3 different time intervals - minute, 10 minutes and an hour. For each time interval, Spanner tracks the reads that are using the most resources.
Use read statistics to find out the combined resource usage by all reads, find the most CPU consuming reads, and find out how a specific read's frequency changes over time.
Transaction statistics
Transaction statistics can be used to investigate transaction-related issues. For example, you can check for slow-running transactions that might be causing contention or identify changes in transaction shapes that are leading to performance regressions. Each row contains statistics of all transactions executed over the database during 1, 10, and 60 minute intervals.
You can visualize these metrics on a time series by using the Transaction insights dashboard. The pre-built dashboard helps you view the latencies in transactions and identify problematic transactions.
Lock statistics
Lock statistics can be used to investigate lock conflicts in your database. Used with transactions statistics, you can find transactions that are causing lock conflicts by trying to acquire locks on the same cells at the same time.
You can visualize these metrics on a time series by using the Lock insights dashboard. The pre-built dashboard helps you view the lock wait time and confirm if latencies are due to lock contentions with high lock wait time.
API methods included in each tool
In Spanner there is some overlap between transactions, reads and queries. Therefore, it might not be clear which API methods are included when compiling results for each introspection tool. The following table lists the main API methods and their relationship to each tool.
| API Methods | Transaction Modes | Query statistics | Oldest active queries | Read statistics | Transaction statistics | Lock statistics |
|---|---|---|---|---|---|---|
| Read, StreamingRead | Read-only transaction1 | No | No | Yes | No | No |
| Read-write transaction | No | No | Yes | Yes | Yes | |
| ExecuteSql, ExecuteStreamingSql | Read-only transaction1 | Yes2 | Yes2 | No | No | No |
| Read-write transaction | Yes | Yes | No | Yes | Yes | |
| ExecuteBatchDml | Read-write transactions | Yes3 | Yes4 | No | Yes | Yes |
| Commit | Read-write transactions (DML5, Mutations6) | No | No | No | Yes | Yes |
Notes:
1 Read-only transactions are not included in transaction statistics or lock statistics. Only read-write transactions are included in transaction statistics and lock statistics.
2 Queries run with the PartitionQuery API are not included in query statistics or oldest active queries.
3 A batch of DML statements appear in the query statistics as a single entry.
4 Statements within the batch will appear in oldest active queries, rather than the entire batch.
5 Uncommitted DML operations are not included in transaction statistics.
6 Empty mutations that are effectively no-op are not included in transaction statistics.
Table sizes statistics
You can use Table sizes statistics to monitor the historical sizes of the tables and indexes in your database.
Use table sizes statistics to find trends in the sizes of your tables, indexes, and change stream tables. You can also keep a track of your biggest tables and indexes.
Please note that this feature provides a historical perspective only. It is not for real-time monitoring.
Table operations statistics
You can use Table operations statistics to do the following:
- Monitor the usages of your tables and indexes in your database.
- Find trends in the usage of your tables and indexes.
- Identify changes in traffic.
Also, you can correlate the changes in your table storage with the changes in your write traffic.
Debug hotspots in splits
You can debug hotspots in your database to find splits in the database that are warm or hot, meaning that a high percentage of the load on a split is constrained by the available resources. You can view statistics for splits that had the highest split CPU usage scores over the last 6 hours, by 1-minute intervals.