Manage Long-Running Queries

This page shows you how to identify and, if necessary, cancel SQL queries that are taking longer than expected to process.

Tip:
Schema changes are treated differently than other SQL queries. You can use SHOW JOBS to monitor the progress of schema changes and CANCEL JOB to cancel schema changes that are taking longer than expected.

Identify long-running queries

Use the SHOW QUERIES statement to list details about currently active SQL queries, including each query's start timestamp:

  1. > SHOW QUERIES;
  1. +----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
  2. |             query_id             | node_id | username |              start               |                   query                   |   client_address    | application_name | distributed |   phase   |
  3. +----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
  4. | 14db657443230c3e0000000000000001 |       1 | root     | 2017-08-16 18:00:50.675151+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.56:54119 | test_app         | false       | executing |
  5. | 14db657443b68c7d0000000000000001 |       1 | root     | 2017-08-16 18:00:50.684818+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.56:54123 | test_app         | false       | executing |
  6. | 14db65744382c2340000000000000001 |       1 | root     | 2017-08-16 18:00:50.681431+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.56:54103 | test_app         | false       | executing |
  7. | 14db657443c9dc660000000000000001 |       1 | root     | 2017-08-16 18:00:50.686083+00:00 | SHOW CLUSTER QUERIES                      | 192.168.12.56:54108 | cockroach        | NULL        | preparing |
  8. | 14db657443e30a850000000000000003 |       3 | root     | 2017-08-16 18:00:50.68774+00:00  | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.58:54118 | test_app         | false       | executing |
  9. | 14db6574439f477d0000000000000003 |       3 | root     | 2017-08-16 18:00:50.6833+00:00   | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.58:54122 | test_app         | false       | executing |
  10. | 14db6574435817d20000000000000002 |       2 | root     | 2017-08-16 18:00:50.678629+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.57:54121 | test_app         | false       | executing |
  11. | 14db6574433c621f0000000000000002 |       2 | root     | 2017-08-16 18:00:50.676813+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.57:54124 | test_app         | false       | executing |
  12. | 14db6574436f71d50000000000000002 |       2 | root     | 2017-08-16 18:00:50.680165+00:00 | UPSERT INTO test.kv(k, v) VALUES ($1, $2) | 192.168.12.57:54117 | test_app         | false       | executing |
  13. +----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
  14. (9 rows)

You can also filter for queries that have been running for a certain amount of time. For example, to find queries that have been running for more than 3 hours, you would run the following:

  1. > SELECT * FROM [SHOW CLUSTER QUERIES]
  2.       WHERE start < (now() - INTERVAL '3 hours');

Cancel long-running queries

Once you've identified a long-running query via SHOW QUERIES, note the query_id and use it with the CANCEL QUERY statement:

  1. > CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

When a query is successfully cancelled, CockroachDB sends a query execution canceled error to the client that issued the query.

  • If the canceled query was a single, stand-alone statement, no further action is required by the client.
  • If the canceled query was part of a larger, multi-statement transaction, the client should then issue a ROLLBACK statement.

Improve query performance

After cancelling a long-running query, use the EXPLAIN statement to examine it. It's possible that the query was slow because it performs a full-table scan. In these cases, you can likely improve the query's performance by adding an index.

(More guidance around query performance optimization forthcoming.)

See also

Was this page helpful?
YesNo