Analyze running queries in Cloud Spanner to help diagnose performance issues

Cloud Spanner is a fully managed, highly scalable, relational database. We recently launched the ability to monitor queries while they are running. This article discusses how Oldest Active Queries complements other introspection tools and helps users troubleshoot system performance issues while they are ongoing.

In particular, we’ll describe how to troubleshoot high CPU usage. Before we do that, let’s look at what data this tool provides.

Oldest Active Queries

There are two tables for active queries:

SPANNER_SYS.OLDEST_ACTIVE_QUERIES: This provides query details which includes start time, query text, session id, and query text fingerprint. Queries are sorted by the start time in an ascending order. The fingerprint can be used to join stats from Query Statistics.

SPANNER_SYS.ACTIVE_QUERIES_SUMMARY:This provides active query statistics which includes total count and a count for each of the following duration ranges.

• Queries older than 1s
• Queries older than 10s
• Queries older than 100s

Troubleshooting High CPU Utilization

Now, let’s see how these two new tables can help troubleshoot high CPU scenarios. In this example, we have a database ‘oaq_demo’ on an instance ‘Test Instance’. The CPU usage of ‘oaq_demo’ has recently exceeded the recommended threshold of 65% as seen in the following chart.

CPU Utilization of ‘oaq_demo’ database.

Looking at the query statistics for the database, as shown in the following screenshot, nothing out of ordinary shows up. All the queries that finished in the latest 1 minute window have low CPU usage. However, this doesn’t include statistics about queries that are still running.

Query Stats.

Let’s look at the ACTIVE_QUERIES_SUMMARY table to find out how many queries are running.

SELECT active_count, 
       oldest_start_time,
       count_older_than_1s,
       count_older_than_10s,
       count_older_than_100s
FROM spanner_sys.active_queries_summary;

Data from ACTIVE_QUERIES_SUMMARY Table.

As the data from the preceding table shows, there are 7 active queries. 2 of these queries have been running for more than 100 seconds. Let’s see what these two queries are.

SELECT start_time,
       session_id,
       text,
       text_fingerprint,
       text_truncated
FROM spanner_sys.oldest_active_queries
ORDER BY start_time ASC LIMIT 5;

Data from OLDEST_ACTIVE_QUERIES Table.

The result shows active queries sorted by the start_time in ascending order. The ‘LIMIT’ clause is used to restrict the result to 5 queries. The top 2 CROSS JOIN queries have start_time older than 5 minutes (age > 100s). Their start_time, 2020–09–02T23:19:30 (UTC) and 2020–09–02T23:20:30 (UTC), are just before the beginning of the spike in CPU usage (see the preceding CPU chart). These CROSS JOIN queries are ad-hoc queries issued by a user and not the application. The immediate solution is to cancel them by deleting the associated sessions.

gcloud spanner databases sessions \
delete  --database=oaq_demo --instance=test-instance \
AN4G3x9eH3amaKWR5M-R-NWNT6ftTacMLp0C_vgTFSGG0M8pKF51LC3iBL2zUg
gcloud spanner databases sessions \
delete  --database=oaq_demo --instance=test-instance \
AN4G3x9YyKj_f4zY96G-OTje0Z-tZLBRsJPHF-8VEIxfHDmQH_Dl030utLytCA

Let’s check the CPU usage after deleting those sessions. You may have to wait up to a minute for the chart to reflect updated CPU usage.

CPU Utilization of ‘oaq_demo’ database after mitigation.

CPU utilization has gone back to the original level.

Now, deleting sessions was a quick short-term mitigation. For a long-term solution you may need to fine tune your query or add resources. Each situation is different and may require different solutions.

In summary, Cloud Spanner provides visibility into the current performance of the system with the introduction of two new tables, OLDEST_ACTIVE_QUERIES and ACTIVE_QUERIES_SUMMARY. Together with Query, Read and Transaction Statistics, these tools can help you troubleshoot and improve the performance of your Cloud Spanner instance.

For details refer to the official guide.