原始网页:https://www.cockroachlabs.com/docs/stable/manage-long-running-queries.html


本小节将展示如何识别慢查询,并在必要的时候取消慢查询操作。

NOTE: ALTER语句变更schema目前无法取消,用户可以通过SHOW JOBS语句监控schema变更进程。

查看慢查询

执行SHOW QUERIES语句查看当前执行中的SQL查询,包括查询开始执行时间:

SHOW QUERIES;
+----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
|             query_id             | node_id | username |              start               |                   query                   |   client_address    | application_name | distributed |   phase   |
+----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
| 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 |
| 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 |
| 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 |
| 14db657443c9dc660000000000000001 |       1 | root     | 2017-08-16 18:00:50.686083+00:00 | SHOW CLUSTER QUERIES                      | 192.168.12.56:54108 | cockroach        | NULL        | preparing |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
+----------------------------------+---------+----------+----------------------------------+-------------------------------------------+---------------------+------------------+-------------+-----------+
(9 rows)

用户可以筛选出执行了一定时间的查询。例如,当要查找执行超过3小时的查询,可运行以下SQL:

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

取消慢查询

当通过[SHOW QUERIES](/develop/sql-statements/SHOW-QUERIES/)确定慢查询时,记录下该慢查询的query_id,并执行[CANCEL QUERY`](https://www.cockroachlabs.com/docs/stable/cancel-query.html)语句:

CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

对于成功取消的查询,CockroachDB会返回query execution canceled的错误信息给查询发起的客户端。

优化慢查询

取消慢查询后,用户可以使用EXPLAIN语句检查查询计划。查询慢的原因可能是SQL查询底层执行了全表扫描。在这些情况下,用户可以通过添加索引来提高查询的性能。

更多性能优化操作可以查看性能优化章节。