Oldest active queries statistics

Oldest active queries, also known as longest running queries, is a list of queries that are active in your database, sorted by how long they've been running. Gaining insight into these queries can help identify causes of system latency and high CPU usage as they are happening.

Spanner provides a built-in table,SPANNER_SYS.OLDEST_ACTIVE_QUERIES, that lists running queries, including queries containing DML statements, sorted by start time, in ascending order. It does not include change stream queries.

If there is a large number of queries running, the results might be limited to a subset of total queries due to the memory constraints the system enforces on the collection of this data. Therefore, Spanner provides an additional table, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, that shows summary statistics for all active queries (except for change stream queries). You can retrieve information from both of these built-in tables using SQL statements.

In this document, we'll describe both tables, show some example queries that use these tables and, finally, demonstrate how to use them to help mitigate issues caused by active queries.

Availability

SPANNER_SYS data is available only through SQL interfaces; for example:

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

• The gcloud spanner databases execute-sql command

• The executeQuery API

Other single read methods that Spanner provides don't support SPANNER_SYS.

OLDEST_ACTIVE_QUERIES

SPANNER_SYS.OLDEST_ACTIVE_QUERIES returns a list of active queries sorted by the start time. If there is a large number of queries running, the results might be limited to a subset of total queries due to the memory constraints Spanner enforces on the collection of this data. To view summary statistics for all active queries, see ACTIVE_QUERIES_SUMMARY.

Table schema

Example queries

You can run the following example SQL statements using the client libraries, the Google Cloud CLI, or the Google Cloud console.

List oldest running queries

The following query returns a list of oldest running queries sorted by the start time of the query.

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

Listing the top 2 oldest running queries

A slight variation on the preceding query, this example returns the top 2 oldest running queries sorted by the start time of the query.

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

Query output

ACTIVE_QUERIES_SUMMARY

As its name suggests, the built-in table, SPANNER_SYS.ACTIVE_QUERIES_SUMMARY, shows summary statistics for all active queries. As shown in the following schema, queries are grouped by age into three buckets, or counters, - older than one second, older than 10 seconds and older than 100 seconds.

Table schema

A query can be counted in more than one of these buckets. For example, if a query has been running for 12 seconds, it will be counted in COUNT_OLDER_THAN_1S and COUNT_OLDER_THAN_10S because it satisfies both criteria.

Example queries

You can run the following example SQL statements using the client libraries, the gcloud spanner, or the Google Cloud console.

Retrieve a summary of active queries

The following query returns the summary stats about running queries.

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

Query output

Limitations

While the goal is to give you the most comprehensive insights possible, there are some circumstances under which queries are not included in the data returned in these tables.

• DML queries (UPDATE/INSERT/DELETE) are not included if they're in the Apply mutations phase.

• A query is not included if it is in the middle of restarting due to a transient error.

• Queries from overloaded or unresponsive servers are not included.

• OLDEST_ACTIVE_QUERIES can't be used in a read-write transaction. Even in a read-only transaction, it ignores the transaction timestamp and always returns current data as of its execution. In rare cases, it may return an ABORTED error with partial results; in that case, discard the partial results and attempt the query again.

Use active queries data to troubleshoot high CPU utilization

Query statistics and transaction statistics provide useful information when troubleshooting latency in a Spanner database. These tools provide information about the queries that have already completed. However, sometimes it is necessary to know what is running in the system. For example, consider the scenario when CPU utilization is quite high and you want to answer the following questions.

• How many queries are running at the moment?
• What are these queries?
• How many queries are running for a long time, that is, greater than 100 seconds?
• Which session is running the query?

With answers to the preceding questions you could decide to take the following action.

• Delete the session executing the query for an immediate resolution.
• Improve the query performance by adding an index.
• Reduce the frequency of the query if it is associated with a periodic background task.
• Identify user or component issuing the query which may not be authorized to execute the query.

In this walkthrough, we examine our active queries and determine what action, if any, to take.

Retrieve a summary of active queries

In our example scenario, we notice higher than normal CPU usage, so we decide to run the following query to return a summary of active queries.

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

The query yields the following results.

It turns out we have one query that is running for more that 100 seconds. This is unusual for our database, so we want to investigate further.

Retrieve a list of active queries

We determined in the preceding step that we have a query running for over 100 seconds.To investigate further, we run the following query to return more information about the top 5 oldest running queries.

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

In this example, we ran the query on March 28, 2024 at approximately 16:44:09 PM EDT and it returned the following results. (You might need to scroll horizontally to see the entire output.)

The oldest query (fingerprint = -2833175298673875968) is highlighted in the table. It is an expensive CROSS JOIN. We decide to take action.

Cancel an expensive query

In this example, we found a query that was running an expensive CROSS JOIN so we decide to cancel the query. The query results we received in the preceding step included a query_id. We can run the following CALL cancel_query(query_id) command for GoogleSQL and the spanner.cancel_query(query_id) command for PostgreSQL to cancel the query.

CALL cancel_query(query_id)

CALL spanner.cancel_query(query_id)

CALL cancel_query('37190103859320827')

You need to query the spanner_sys.oldest_active_queries table to verify that the query is cancelled.

This walkthrough demonstrates how to use SPANNER_SYS.OLDEST_ACTIVE_QUERIES and SPANNER_SYS.ACTIVE_QUERIES_SUMMARY to analyze our running queries and take action if necessary on any queries that are contributing to high CPU usage. Of course, it is always cheaper to avoid expensive operations and to design the right schema for your use cases. For more information on constructing SQL statements that run efficiently, see SQL best practices.

What's next

• Learn about other Introspection tools.
• Learn about other information Spanner stores for each database in the database's information schema tables.
• Learn more about SQL best practices for Spanner.