SHOW PROCESSLIST
Grammar Description
SHOW PROCESSLIST is used to view the currently executing list of threads (also known as process lists) and provides information about all active connections and queries in execution on the MatrixOne Intelligence server.
SHOW PROCESSLIST is used to monitor and manage activities in databases, identify potential problems, help query performance problems, and help decision-making to optimize the database's operation:
-
Monitor database activity: By executing
SHOW PROCESSLIST, you can view the currently running queries and operations in the database in real time. This is useful for monitoring database activity and promptly detecting possible performance issues. You can learn which queries are running, how they are in status, and whether there are long-running or blocking queries, or locking, deadlocking, or resource contention, etc. -
Terminate query: By viewing the process list, you can determine the query ID to be terminated and use the
KILLcommand to terminate the specific query. This is very useful for stopping long-running queries or solving deadlock problems.
Grammar Structure
> SHOW PROCESSLIST;
The query table structure fields are explained as follows:
| Column Name | Type | Constraints | Notes |
|---|---|---|---|
| node_id | varchar | not null | Node ID, used to uniquely identify different nodes in the database cluster. In MatrixOne Intelligence, a node is a CN (Compute Node). Note: - In MatrixOne Intelligence, each node has a unique node_id. System tenants can view the node_id corresponding to the node where all executing threads are located, while system tenants can only see the node_id corresponding to the node that belongs to the threads that are executing under their tenants. |
| conn_id | uint32 | not null | Connection ID, used to identify different database connections. If you need to terminate a database connection, you can use the KILL CONNECTION conn_id; command to terminate the connection. In the database, each connection is assigned a unique conn_id to identify the connection. Note: System tenants can view all conn_ids, while non-system tenants can only see conn_ids under the tenant. ID |
| session_id | varchar | not null | session ID |
| account | varchar | not null | Tenant Note: The sessions and tenant names of all tenants can be seen under the system tenant, while non-system tenants can only see the sessions and tenant names of their own tenants. |
| user | varchar | not null | user |
| host | varchar | not null | client side host name |
| db | varchar | Database Name | |
| session_start | varchar | session start time | |
| command | varchar | not null | Command type, such as 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 types, such as DDL, DML, etc | |
| sql_source_type | varchar | SQL source types, such as external_sql, internal_sql, etc. | |
| query_start | varchar | query start time |
Example
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)