Decoding MySQL Processlist: Understand Columns, States, and Performance Issues

Viewing MySQL Execution Status

When MySQL performance seems degraded, start by checking the current execution status with show processlist:

+----+------+-------------------+-------+---------+------+--------+-------+
| Id | User | Host              | db    | Command | Time | State  | Info  |
+----+------+-------------------+-------+---------+------+--------+-------+
|207|root|192.168.0.2:51621|mytest|Sleep|5||NULL|
|208|root|192.168.0.2:51622|mytest|Sleep|5||NULL|
|220|root|192.168.0.2:51676|mytest|Query|84|locked|
+----+------+-------------------+-------+---------+------+--------+-------+

Column meanings

Id : Identifier of the thread; use it with kill <id> to terminate a query.

User : The account that issued the statement; non‑root users see only their own queries.

Host : IP address and port of the client, useful for tracing problematic users.

Db : Database currently selected by the thread.

Command : Type of command, e.g., Sleep, Query, Connect.

Time : Duration (in seconds) the command has been in its current state.

State : Detailed status of the executing SQL, such as Copy to tmp table, Sorting result, Sending data, etc.

Info : The actual SQL statement (truncated if long); essential for diagnosing issues.

Common State Analysis

Sleep : Indicates an idle connection; excessive sleep may mean connections are not being closed properly.

Locked : The operation is waiting for a lock; using InnoDB can reduce this occurrence.

Copy to tmp table : MySQL creates a temporary table when indexes cannot satisfy the query, causing high I/O; consider reducing joins or optimizing the query, and kill long‑running statements if needed.

Sending data : Refers to reading data from disk rather than network transmission; many such connections often mean large result sets or missing indexes.

Storing result to query cache : Frequent appearance suggests query cache fragmentation; use set profiling to analyze and flush query cache to clear, adjusting cache parameters as appropriate.