SHOW PROCESSLIST

Description

SHOW PROCESSLIST is used to view a list of currently executing threads (also known as a process list), providing information about all active connections and executing queries on the MatrixOne server.

SHOW PROCESSLIST is used to monitor and manage activities in the database, identify potential issues, help diagnose query performance problems, and aid in decision-making to optimize database operations:

  • Monitoring Database Activity: By executing SHOW PROCESSLIST, you can view the queries and operations currently running in the database in real time. This is useful for monitoring database activity and promptly identifying potential performance issues. You can see which queries are running, their status, and whether there are long-running or blocked queries, as well as information about locks, deadlocks, or resource contention.

  • Terminating Queries: By examining the process list, you can identify the query ID of a specific query that needs to be terminated and then use the KILL command to stop that particular query. This is beneficial for controlling long-running queries or resolving deadlock situations.

Syntax

  1. > SHOW PROCESSLIST;

The explanations for the queried table structure are as follows:

Column NameData TypeConstraintRemarks
node_idvarcharnot nullNode ID uniquely identifies different nodes in the database cluster. In MatrixOne, a node corresponds to a CN (Compute Node).
Note:
- In the single-node version of MatrixOne, there is usually only one node and all processes run on this node, so all processes have the same node_id.
- In the distributed version of MatrixOne, each node has a unique node_id. System tenants can view the node_id corresponding to the nodes where all executing threads run. In contrast, non-system tenants can only see the node_id corresponding to the nodes where threads executing for their tenant are running.
conn_iduint32not nullConnection ID used to identify different database connections. To terminate a specific database connection, use the KILL CONNECTION conn_id; command. Each connection in the database is assigned a unique conn_id for identification.
Note: System tenants can view all conn_id, while non-system tenants can only see conn_id for their tenant.
session_idvarcharnot nullSession ID
accountvarcharnot nullaccount
Note: Under the system, account, sessions, and account names can be viewed. Non-system accounts can only view sessions and account names of their accounts.
uservarcharnot nullUser
hostvarcharnot nullHostname of client-server
dbvarcharDatabase name
session_startvarcharSession start time
commandvarcharnot nullCommand type, e.g., COM_QUERY
infovarcharCurrent or previous SQL statement
txn_idvarcharnot nullCurrent or previous transaction ID
statement_idvarcharCurrent or previous statement ID
statement_typevarcharCurrent or previous statement type
query_typevarcharQuery type, e.g., DDL, DML, etc.
sql_source_typevarcharSQL source type, e.g., external_sql, internal_sql, etc.
query_startvarcharQuery start time

Examples

  1. mysql> SHOW PROCESSLIST;
  2. +--------------------------------------+---------+--------------------------------------+---------+--------------------------------------+----------------+---------+----------------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------------------------------------+------------------+------------+-----------------+----------------------------+
  3. | node_id                              | conn_id | session_id                           | account | user                                 | host           | db      | session_start              | command   | info                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | txn_id                           | statement_id                         | statement_type   | query_type | sql_source_type | query_start                |
  4. +--------------------------------------+---------+--------------------------------------+---------+--------------------------------------+----------------+---------+----------------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------------------------------------+------------------+------------+-----------------+----------------------------+
  5. | dd1dccb4-4d3c-41f8-b482-5251dc7a41bf |       1 | 97f85f80-2a5c-11ee-ae41-5ad2460dea4f | sys     | mo_logger                            | 127.0.0.1:6001 |         | 2023-07-24 19:59:27.005755 | COM_QUERY | COMMIT                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |                                  |                                      |                  |            | internal_sql    |                            |
  6. | dd1dccb4-4d3c-41f8-b482-5251dc7a41bf |       8 | a056b7c6-2a5c-11ee-ae42-5ad2460dea4f | sys     | root                                 | 127.0.0.1:6001 | aab     | 2023-07-24 19:59:41.045851 | COM_QUERY | SHOW PROCESSLIST                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | c207acc30a19432f8d3cbac387c6e520 | 421aadaa-2a68-11ee-ae5b-5ad2460dea4f | Show Processlist | Other      | external_sql    | 2023-07-24 21:22:56.907266 |
  7. | dd1dccb4-4d3c-41f8-b482-5251dc7a41bf |      24 | 0915f91a-2a63-11ee-ae4d-5ad2460dea4f | sys     | 3bf028e0-aa43-4917-b82f-ed533c0f401e | 127.0.0.1:6001 | mo_task | 2023-07-24 20:45:33.762679 | COM_QUERY | select
  8.                                                 task_id,
  9.                                                         task_metadata_id,
  10.                                                         task_metadata_executor,
  11.                                                         task_metadata_context,
  12.                                                         task_metadata_option,
  13.                                                         task_parent_id,
  14.                                                         task_status,
  15.                                                         task_runner,
  16.                                                         task_epoch,
  17.                                                         last_heartbeat,
  18.                                                         result_code,
  19.                                                         error_msg,
  20.                                                         create_at,
  21.                                                         end_at
  22.                                                 from mo_task.sys_async_task where task_id>17 AND task_runner='dd1dccb4-4d3c-41f8-b482-5251dc7a41bf' AND task_status=1 order by task_id limit 3 |                                  |                                      |                  |            | internal_sql    |                            |
  23. | dd1dccb4-4d3c-41f8-b482-5251dc7a41bf |      36 | d8aa4060-2a67-11ee-ae59-5ad2460dea4f | sys     | 3bf028e0-aa43-4917-b82f-ed533c0f401e | 127.0.0.1:6001 | mo_task | 2023-07-24 21:20:00.009746 | COM_QUERY | select
  24.                                                 cron_task_id,
  25.                                                 task_metadata_id,
  26.                                                 task_metadata_executor,
  27.                                                 task_metadata_context,
  28.                                                 task_metadata_option,
  29.                                                 cron_expr,
  30.                                                 next_time,
  31.                                                 trigger_times,
  32.                                                 create_at,
  33.                                                 update_at
  34.                                                 from mo_task.sys_cron_task                                                                                                                                                                        |                                  |                                      |                  |            | internal_sql    |                            |
  35. +--------------------------------------+---------+--------------------------------------+---------+--------------------------------------+----------------+---------+----------------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------------------------------------+------------------+------------+-----------------+----------------------------+
  36. 4 rows in set (0.01 sec)