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
KILLcommand to stop that particular query. This is beneficial for controlling long-running queries or resolving deadlock situations.
Syntax
> SHOW PROCESSLIST;
The explanations for the queried table structure are as follows:
| Column Name | Data Type | Constraint | Remarks |
|---|---|---|---|
| node_id | varchar | not null | Node 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_id | uint32 | not null | Connection 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_id | varchar | not null | Session ID |
| account | varchar | not null | account 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. |
| user | varchar | not null | User |
| host | varchar | not null | Hostname of client-server |
| db | varchar | Database name | |
| session_start | varchar | Session start time | |
| command | varchar | not null | Command type, e.g., COM_QUERY |
| info | varchar | Current or previous SQL statement | |
| txn_id | varchar | not null | Current or previous transaction ID |
| statement_id | varchar | Current or previous statement ID | |
| statement_type | varchar | Current or previous statement type | |
| query_type | varchar | Query type, e.g., DDL, DML, etc. | |
| sql_source_type | varchar | SQL source type, e.g., external_sql, internal_sql, etc. | |
| query_start | varchar | Query start time |
Examples
mysql> SHOW PROCESSLIST;
+--------------------------------------+---------+--------------------------------------+---------+--------------------------------------+----------------+---------+----------------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------------------------------------+------------------+------------+-----------------+----------------------------+
| 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 |
+--------------------------------------+---------+--------------------------------------+---------+--------------------------------------+----------------+---------+----------------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------------------------------------+------------------+------------+-----------------+----------------------------+
| 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 | |
| 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 |
| 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
task_id,
task_metadata_id,
task_metadata_executor,
task_metadata_context,
task_metadata_option,
task_parent_id,
task_status,
task_runner,
task_epoch,
last_heartbeat,
result_code,
error_msg,
create_at,
end_at
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 | |
| 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
cron_task_id,
task_metadata_id,
task_metadata_executor,
task_metadata_context,
task_metadata_option,
cron_expr,
next_time,
trigger_times,
create_at,
update_at
from mo_task.sys_cron_task | | | | | internal_sql | |
+--------------------------------------+---------+--------------------------------------+---------+--------------------------------------+----------------+---------+----------------------------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+--------------------------------------+------------------+------------+-----------------+----------------------------+
4 rows in set (0.01 sec)