Using the SHOW PROCESSLIST statement to display connections in MySQL

Use MySQL SHOW PROCESSLIST to inspect server threads, including their connection ID, account, host, default database, command, state, and current statement. Without FULL, the Info column shows only the first 100 characters.

If the server reports “too many connections,” inspect the process list before taking action. An idle session may still have work to finish; confirm its impact before terminating a connection or query.

SHOW PROCESSLIST syntax

The following is the syntax of the MySQL SHOW PROCESSLIST command:

SHOW [FULL] PROCESSLIST;

The keyword FULL is optional, if it is specified, the full statement is displayed in the returned Info column, otherwise, only the first 100 characters of the statement are returned.

Accounts with the PROCESS privilege can view all threads. Without it, nonanonymous accounts can see only their own threads; anonymous accounts cannot see process information. See the MySQL process-list access rules.

Output

The output of the SHOW PROCESSLIST command consists of the following columns:

ID

ID of the connection.

User

Username associated with the connection.

Host

The host to which the client is connected from.

db

Default database, otherwise NULL.

Command

Command type

Time

The number of seconds the thread has been in its current state, not the age of the connection.

State

Thread state, which represents an action, event, or state of the connection.

Info

The statement executing, or NULL if it is not executing any statements. If you do not specified the FULL keyword in the SHOW PROCESSLIST command, only the first 100 characters of each statement are returned.

SHOW PROCESSLIST Examples

Run SHOW PROCESSLIST with an account that has the PROCESS privilege:

SHOW PROCESSLIST;

+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
| Id | User            | Host                | db      | Command | Time    | State                  | Info             |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
|  5 | event_scheduler | localhost           | NULL    | Daemon  | 4141488 | Waiting on empty queue | NULL             |
| 27 | db_admin        | 192.0.2.10:54690    | NULL    | Query   |       0 | starting               | SHOW PROCESSLIST |
| 28 | app_user        | 192.0.2.10:54692    | appdb   | Sleep   |     247 |                        | NULL             |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+

Here, db_admin has the PROCESS privilege, so it can view threads from other accounts. The account and address are illustrative examples.

Run the same statement with an app_user account that does not have the PROCESS privilege:

SHOW PROCESSLIST;

+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
| Id | User            | Host                | db      | Command | Time    | State                  | Info             |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+
| 28 | app_user        | 192.0.2.10:54692    | appdb   | Query   |       0 | starting               | SHOW PROCESSLIST |
+----+-----------------+---------------------+---------+---------+---------+------------------------+------------------+

Here, app_user can view only its own threads. The account and address are illustrative examples.

Query process information with Performance Schema

The INFORMATION_SCHEMA.PROCESSLIST table and the SHOW PROCESSLIST implementation that uses it are deprecated. MySQL recommends the Performance Schema process-list implementation instead. The performance_schema.processlist table can be queried directly and follows the same PROCESS visibility rules as SHOW PROCESSLIST.

SELECT `ID`, `USER`, `HOST`, `DB`, `COMMAND`, `TIME`, `STATE`, `INFO`
FROM performance_schema.processlist
ORDER BY `ID`;

Make sure Performance Schema is enabled and the account has permission to query the table. As with SHOW PROCESSLIST, the PROCESS privilege controls whether the account can see other users’ threads. See the Performance Schema processlist reference and the deprecation notice for INFORMATION_SCHEMA.PROCESSLIST.

Conclusion

Use SHOW PROCESSLIST for a quick session view. For SQL-based monitoring, query performance_schema.processlist; avoid building new tools on the deprecated INFORMATION_SCHEMA.PROCESSLIST table.