SHOW SESSIONS

The SHOW SESSIONS statement lists details about currently active sessions, including:

  • The address of the client that opened the session
  • The node connected to
  • How long the connection has been open
  • Which queries are active in the session
  • Which query has been running longest in the session
    These details let you monitor the overall state of client connections and identify those that may need further investigation or adjustment.

Required privileges

No privileges are required to execute this statement. However, note that non-root users see only their own currently active sessions, whereas the root user sees all users' currently active sessions.

Synopsis

SHOWCLUSTERLOCALSESSIONS
  • To list the active sessions across all nodes of the cluster, use SHOW SESSIONS or SHOW CLUSTER SESSIONS.
  • To list the active sessions just on the local node, use SHOW LOCAL SESSIONS.

Response

The following fields are returned for each session:

FieldDescription
node_idThe ID of the node connected to.
session_idThe ID of the connected session.
user_nameThe username of the connected user.
client_addressThe address and port of the connected client.
application_nameThe application name specified by the client, if any. For sessions from the built-in SQL client, this will be cockroach.
active_queriesThe SQL queries currently active in the session.
last_active_queryThe most recently completed SQL query in the session.
session_startThe timestamp at which the session started.
oldest_query_startThe timestamp at which the oldest currently active SQL query in the session started.
kv_txnThe ID of the current key-value transaction for the session.

Examples

List active sessions across the cluster

  1. > SHOW CLUSTER SESSIONS;
  1. +---------+----------------------------------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
  2. | node_id |            session_id            | user_name |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                
  3. +---------+----------------------------------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
  4. |       2 | 1530fd8813ad694b0000000000000001 | 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
  5. |       2 | 1530ce8813ad694b0000000000000001 | 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
  6. |       2 | 1520ab8813ad694b0000000000000001 | 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
  7. |       3 | 1559f68813ad694b0000000000000001 | 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                                 
  8. |       3 | 1340rd8813ad694b0000000000000001 | 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
  9. |       1 | 1230ab8813ad694b0000000000000001 | 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
  10. |       1 | 15234d8813ad694b0000000000000001 | 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
  11. |       1 | 14605d8813ad694b0000000000000001 | 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
  12. |       1 | 1965lh8813ad694b0000000000000001 | 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                                 
  13. +---------+----------------------------------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
  14. (9 rows)

Alternatively, you can use SHOW SESSIONS to receive the same response.

List active sessions on the local node

  1. > SHOW LOCAL SESSIONS;
  1. +---------+----------------------------------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
  2. | node_id |            session_id            | user_name |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                |
  3. +---------+----------------------------------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
  4. |       1 | 1230ab8813ad694b0000000000000001 | 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 |
  5. |       1 | 15234d8813ad694b0000000000000001 | 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 |
  6. |       1 | 14605d8813ad694b0000000000000001 | 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 |
  7. |       1 | 1965lh8813ad694b0000000000000001 | 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                                 |
  8. +---------+----------------------------------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
  9. (4 rows)

Filter for specific sessions

You can use a SELECT statement to filter the list of currently active sessions by one or more of the response fields.

Show sessions associated with a specific user

  1. > SELECT * FROM [SHOW CLUSTER SESSIONS] WHERE user-name = 'mroach';
  1. +---------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
  2. | node_id | user_name |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                |
  3. +---------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
  4. |       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 |
  5. |       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 |
  6. |       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 |
  7. +---------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
  8. (3 rows)

Exclude sessions from the built-in SQL client

To exclude sessions from the built-in SQL client, filter for sessions that do not show cockroach as the application_name:

  1. > SELECT * FROM [SHOW CLUSTER SESSIONS]
  2.       WHERE application_name != 'cockroach';
  1. +---------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------|----------------------------------+----------------------------------+--------------------------------------+
  2. | node_id | user_name |   client_address   | application_name |               active_queries                |            last_active_query               |          session_start           |        oldest_query_start        |                kv_txn                
  3. +---------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------+--------------------------------------|
  4. |       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
  5. |       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
  6. |       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
  7. |       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                                 
  8. |       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
  9. |       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
  10. |       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
  11. |       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
  12. +---------+-----------+--------------------+------------------+---------------------------------------------+--------------------------------------------+----------------------------------+----------------------------------|--------------------------------------+
  13. (8 rows)

Identify and cancel a problematic query

If a session has been open for a long time and you are concerned that the oldest active SQL query may be problematic, you can use the SHOW QUERIES statement to further investigate the query and then, if necessary, use the CANCEL QUERY statement to cancel it.

For example, let's say you run SHOW SESSIONS and notice that the following session has been open for more than 2 hours:

  1. +---------+----------+--------------------+------------------+------------------------------------+--------------------|----------------------------------+----------------------------------+--------+
  2. | node_id | user_name |   client_address   | application_name |           active_queries           | last_active_query  |          session_start           |        oldest_query_start        | kv_txn
  3. +---------+-----------+--------------------+------------------+------------------------------------+--------------------+----------------------------------+----------------------------------|--------+
  4. |       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   
  5. +---------+-----------+--------------------+------------------+------------------------------------+--------------------|----------------------------------+----------------------------------+--------+

Since the oldest_query_start timestamp is the same as the session_start timestamp, you are concerned that the SELECT query shown in active_queries has been running for too long and may be consuming too many resources. So you use the SHOW QUERIES statement to get more information about the query, filtering based on details you already have:

  1. > SELECT * FROM [SHOW CLUSTER QUERIES]
  2.       WHERE client_address = '192.168.0.72:56194'
  3.           AND user_name = 'mroach'
  4.           AND query = 'SELECT * FROM test.kv ORDER BY k';
  1. +----------------------------------+---------+-----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
  2. |             query_id             | node_id | user_name |              start               |              query               |   client_address   | application_name | distributed |   phase   |
  3. +----------------------------------+---------+-----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+
  4. | 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 |
  5. +----------------------------------+---------+-----------+----------------------------------+----------------------------------+--------------------+------------------+-------------+-----------+

Using the start field, you confirm that the query has been running since the start of the session and decide that is too long. So to cancel the query, and stop it from consuming resources, you note the query_id and use it with the CANCEL QUERY statement:

  1. > CANCEL QUERY '14dacc1f9a781e3d0000000000000001';

Alternatively, if you know that you want to cancel the query based on the details in SHOW SESSIONS, you could execute a single CANCEL QUERY statement with a nested SELECT statement that returns the query_id:

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

See also

Was this page helpful?
YesNo