V1.1版本新特性: SHOW SESSIONS 语句 用于列出所有处于活跃状态的会话详细信息,具体包括:

上述信息可以用来监控客户端连接的状态,来判断是否需要进行下一步研究和调整。

所需权限

权限要求。但是,非root用户只能查看自己当前的活跃会话,而root用户则可以查看所有的会话。

概要

图片

返回

每个会话的相关字段如下:如下:

Field Description
node_id 会话连接的节点ID。
username 会话连接的用户名。
client_address 会话连接的客户端地址。
application_name 客户端指定的具体的 application name。 对于 built-in SQL client的会话,该值为 cockroach
active_queries 会话中的活跃SQL查询。
last_active_query 会话中最近完成的SQL查询。
session_start 会话开始的时间。
oldest_query_start 会话中的活跃SQL查询里,最早开始执行的开始时间。
kv_txn 当前key-value事务的ID。

例子

显示整个集群的活跃会话

> SHOW CLUSTER SESSIONS;
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
| node_id | username |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
|       2 | mroach   | 192.168.0.72:56194 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878113+00:00 | 2017-08-10 14:08:44.648985+00:00 | 81fbdd4d-394c-4784-b540-97cd73910dba |
|       2 | mroach   | 192.168.0.72:56201 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878306+00:00 | 2017-08-10 14:08:44.653135+00:00 | 5aa6f141-5cae-468f-b16a-dfe8d4fb4bea |
|       2 | mroach   | 192.168.0.72:56198 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878464+00:00 | 2017-08-10 14:08:44.643749+00:00 | d8fedb88-fc21-4720-aabe-cd43ec204d88 |
|       3 | broach   | 192.168.0.73:56199 | test_app         | SELECT k, v FROM test.kv WHERE k = $1;      | UPSERT INTO test.kv(k, v) VALUES ($1, $2); | 2017-08-10 14:08:22.878048+00:00 | 2017-08-10 14:08:44.655709+00:00 | NULL                                 |
|       3 | broach   | 192.168.0.73:56196 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878166+00:00 | 2017-08-10 14:08:44.647464+00:00 | aded7717-94e1-4ac4-9d37-8765e3418e32 |
|       1 | lroach   | 192.168.0.71:56180 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.87337+00:00  | 2017-08-10 14:08:44.64788+00:00  | f691c5dd-b29e-48ed-a1dd-6d7f71faa82e |
|       1 | lroach   | 192.168.0.71:56197 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.877932+00:00 | 2017-08-10 14:08:44.644786+00:00 | 86ae25ea-9abf-4f5e-ad96-0522178f4ce6 |
|       1 | lroach   | 192.168.0.71:56200 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878534+00:00 | 2017-08-10 14:08:44.653524+00:00 | 8ad972b6-4347-4128-9e52-8553f3491963 |
|       1 | root     | 127.0.0.1:56211    | cockroach        | SHOW CLUSTER SESSIONS;                      |                                            | 2017-08-10 14:08:27.666826+00:00 | 2017-08-10 14:08:44.653355+00:00 | NULL                                 |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
(9 rows)

显示当前节点的活跃会话

> SHOW LOCAL SESSIONS;
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
| node_id | username |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
|       1 | lroach   | 192.168.0.71:56180 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.87337+00:00  | 2017-08-10 14:08:44.64788+00:00  | f691c5dd-b29e-48ed-a1dd-6d7f71faa82e |
|       1 | lroach   | 192.168.0.71:56197 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.877932+00:00 | 2017-08-10 14:08:44.644786+00:00 | 86ae25ea-9abf-4f5e-ad96-0522178f4ce6 |
|       1 | lroach   | 192.168.0.71:56200 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878534+00:00 | 2017-08-10 14:08:44.653524+00:00 | 8ad972b6-4347-4128-9e52-8553f3491963 |
|       1 | root     | 127.0.0.1:56211    | cockroach        | SHOW CLUSTER SESSIONS;                      |                                            | 2017-08-10 14:08:27.666826+00:00 | 2017-08-10 14:08:44.653355+00:00 | NULL                                 |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
(4 rows)

过滤特定会话

SHOW SESSIONS返回的response fields字段值作为 SELECT语句的where条件,过滤出我们需要的会话。

显示一个特定用户的会话

> SELECT * FROM [SHOW CLUSTER SESSIONS] WHERE username = 'mroach';
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
| node_id | username |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
|       2 | mroach   | 192.168.0.72:56194 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878113+00:00 | 2017-08-10 14:08:44.648985+00:00 | 81fbdd4d-394c-4784-b540-97cd73910dba |
|       2 | mroach   | 192.168.0.72:56201 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878306+00:00 | 2017-08-10 14:08:44.653135+00:00 | 5aa6f141-5cae-468f-b16a-dfe8d4fb4bea |
|       2 | mroach   | 192.168.0.72:56198 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878464+00:00 | 2017-08-10 14:08:44.643749+00:00 | d8fedb88-fc21-4720-aabe-cd43ec204d88 |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
(3 rows)

排除 built-in SQL client开启的会话

通过将查看 application_name不是cockroach 的会话,就可以排除由built-in SQL client执行的会话。

> SELECT * FROM [SHOW CLUSTER SESSIONS]
      WHERE application_name != 'cockroach';
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
| node_id | username |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
|       2 | mroach   | 192.168.0.72:56194 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878113+00:00 | 2017-08-10 14:08:44.648985+00:00 | 81fbdd4d-394c-4784-b540-97cd73910dba |
|       2 | mroach   | 192.168.0.72:56201 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878306+00:00 | 2017-08-10 14:08:44.653135+00:00 | 5aa6f141-5cae-468f-b16a-dfe8d4fb4bea |
|       2 | mroach   | 192.168.0.72:56198 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878464+00:00 | 2017-08-10 14:08:44.643749+00:00 | d8fedb88-fc21-4720-aabe-cd43ec204d88 |
|       3 | broach   | 192.168.0.73:56199 | test_app         | SELECT k, v FROM test.kv WHERE k = $1;      | UPSERT INTO test.kv(k, v) VALUES ($1, $2); | 2017-08-10 14:08:22.878048+00:00 | 2017-08-10 14:08:44.655709+00:00 | NULL                                 |
|       3 | broach   | 192.168.0.73:56196 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878166+00:00 | 2017-08-10 14:08:44.647464+00:00 | aded7717-94e1-4ac4-9d37-8765e3418e32 |
|       1 | lroach   | 192.168.0.71:56180 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.87337+00:00  | 2017-08-10 14:08:44.64788+00:00  | f691c5dd-b29e-48ed-a1dd-6d7f71faa82e |
|       1 | lroach   | 192.168.0.71:56197 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.877932+00:00 | 2017-08-10 14:08:44.644786+00:00 | 86ae25ea-9abf-4f5e-ad96-0522178f4ce6 |
|       1 | lroach   | 192.168.0.71:56200 | test_app         | UPSERT INTO test.kv(k, v) VALUES ($1, $2);  | SELECT k, v FROM test.kv WHERE k = $1;     | 2017-08-10 14:08:22.878534+00:00 | 2017-08-10 14:08:44.653524+00:00 | 8ad972b6-4347-4128-9e52-8553f3491963 |
+---------+----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
(8 rows)

识别并取消有问题的查询

如果一个持续时间较长,可能是因为这个会话中最老的活跃查询有问题;可以通过 SHOW QUERIES进一步查看这个查询,如果这个查询有问题,可以通过CANCEL QUERY 语句取消它。

例如,通过SHOW SESSIONS语句的查询结果,发现有会话的存活时间超过2小时:

+---------+----------+--------------------+------------------+------------------------------------+--------------------|----------------------------------+----------------------------------+--------+
| node_id | username |   client_address   | application_name |           active_queries           | last_active_query  |          session_start           |        oldest_query_start        | kv_txn |
+---------+----------+--------------------+------------------+------------------------------------+--------------------+----------------------------------+----------------------------------|--------+
|       2 | mroach   | 192.168.0.72:56194 | test_app         | SELECT * FROM test.kv ORDER BY k;  |                    | 2017-08-10 14:08:22.878113+00:00 | 2017-08-10 14:08:22.878113+00:00 | NULL   |
+---------+----------+--------------------+------------------+------------------------------------+--------------------|----------------------------------+----------------------------------+--------+

因为oldest_query_start的时间与session_start的时间一样,那么可以认为active_queries列中的SELECT查询执行了很长的时间,可能消耗了很多的资源。所以,通过已有的信息,使用 SHOW QUERIES 语句查看这个查询更详细的信息。

> SELECT * FROM [SHOW CLUSTER QUERIES]
      WHERE client_address = '192.168.0.72:56194'
          AND username = 'mroach'
          AND query = 'SELECT * FROM test.kv ORDER BY k';
+----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
|             query_id             | node_id | username |              start               |              query               |   client_address   | application_name | distributed |   phase   |
+----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
| 14dacc1f9a781e3d0000000000000001 |       2 | mroach   | 2017-08-10 14:08:22.878113+00:00 | SELECT * FROM test.kv ORDER BY k | 192.168.0.72:56194 | test_app         | false       | executing |
+----------------------------------+---------+----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+

通过start 字段,可以看到这个查询自从会话开启就已经执行,已经执行了很长时间。所以,为了阻止这个查询消耗资源,使用 CANCEL QUERY query_id 取消这个查询。

> CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

另外,如果看到SHOW SESSIONS的显示信息,确定要取消里面的查询,可以使用一个嵌套的SELECT语句返回query_id,然后使用 CANCEL QUERY 语句取消它。

> CANCEL QUERY (SELECT query_id FROM [SHOW CLUSTER QUERIES]
      WHERE client_address = '192.168.0.72:56194'
          AND username = 'mroach'
          AND query = 'SELECT * FROM test.kv ORDER BY k');

See Also