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:

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

Column name Type Description
START_TIME TIMESTAMP Start time of the query.
TEXT_FINGERPRINT INT64 Fingerprint is a hash of the operations involved in the transaction.
TEXT STRING The query statement text.
TEXT_TRUNCATED BOOL True if the query text in the TEXT field is truncated; Otherwise, false.
SESSION_ID STRING The ID of the session that is executing the query. This is used for observability.
QUERY_ID. STRING The ID for the query. You use this ID with CALL cancel_query(query_id) to cancel the query.

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;
start_time text_fingerprint text text_truncated session_id
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; False ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z -9206690983832919848 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000; False ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ
2020-07-18T07:54:08.631744Z -105437553161169030 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE a.SingerId > 7 LIMIT 1000000; False ACjbPvanq3MesDNT98t64KdKAz3TlDZoCC-zgW-FJn91cJHuczQ_cOFN_Hdflw
2020-07-18T07:54:08.720011Z -9206690983832919848 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000; False ACjbPvYIE2QHkhnmMXuAGpB4inK7yMnQjmYgQ9FoygKNaB5KCXu7Sf7f9aghYw
2020-07-18T07:54:08.731006Z 6561582859583559006 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE a.SingerId > 10 LIMIT 1000000; False ACjbPvYNZ06N2YyvwV0YMlSRBNDtXBqZEK-iAAyPFnFcTmshPvzWkhr034ud7w

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
start_time text_fingerprint text text_truncated session_id
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; False ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw
2020-07-18T07:54:08.622081Z -9206690983832919848 SELECT a.SingerId, a.AlbumId, a.TrackId, a.SongName, s.FirstName, s.LastName FROM Songs as a JOIN Singers as s ON s.SingerId = a.SingerId WHERE STARTS_WITH(s.FirstName, 'FirstName') LIMIT 1000000; False ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ

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

Column name Type Description
ACTIVE_COUNT INT64 The total number of queries that are running.
OLDEST_START_TIME TIMESTAMP An upper bound on the start time of the oldest running query.
COUNT_OLDER_THAN_1S INT64 The number of queries older than 1 second.
COUNT_OLDER_THAN_10S INT64 The number of queries older than 10 seconds.
COUNT_OLDER_THAN_100S INT64 The number of queries older than 100 seconds.

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
active_count oldest_start_time count_older_than_1s count_older_than_10s count_older_than_100s
22 2020-07-18T07:52:28.225877Z 21 21 1

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.

active_count oldest_start_time count_older_than_1s count_older_than_10s count_older_than_100s
22 2020-07-18T07:52:28.225877Z 21 21 1

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.)

start_time text_fingerprint text text_truncated session_id query_id
2024-03-28 16:44:09.356939+00:00 -2833175298673875968 select * from spanner_sys.oldest_active_queries false ACjbPvYsucrtcffHrRK6aObeIjZf12tSUwOsim-g1WC3IhqF4epzICCQR3GCHw 37190103859320827
2020-07-18T07:52:28.225877Z -3426560921851907385 SELECT a.SingerId, a.AlbumId, a.TrackId, b.SingerId as b_id, b.AlbumId as b_albumid, b.TrackId as b_trackId FROM Songs as a CROSS JOIN Songs as b; false ACjbPvaF3yKiNfxXFod2LPoFaXjKR759Bw1o34206vv0t7eOrD3wxZhu8U6ohQ 48946620525959556

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.

GoogleSQL 

CALL cancel_query(query_id)

PostgreSQL 

CALL spanner.cancel_query(query_id)

For example, in the following, the CALL statement cancels a query with the ID 37190103859320827:

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