MySQL SHOW PROCESSLIST

Summary: in this tutorial, you will learn how to use the SHOW PROCESSLIST command to find the currently running threads.

Sometimes, you may get the “too many connections” error returned by the MySQL Server. To find out the reasons, you can use the SHOW PROCESSLIST command.

The SHOW PROCESSLIST command returns all currently running threads. You then can terminate the idle threads with the KILL statement.

The following shows the syntax of the SHOW PROCESSLIST command:

SHOW [FULL] PROCESSLIST;
Code language: SQL (Structured Query Language) (sql)

Accounts with the PROCESS privilege can view all threads. Otherwise, they can view only threads associated with their accounts.

The following shows an example of the output of the SHOW PROCESSLIST command:

mysql>SHOW PROCESSLIST;

+----+-----------------+-----------------+---------------+---------+------+------------------------+------------------+
| Id | User            | Host            | db            | Command | Time | State                  | Info             |
+----+-----------------+-----------------+---------------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost       | NULL          | Daemon  | 2246 | Waiting on empty queue | NULL             |
| 14 | root            | localhost:50924 | NULL          | Query   |    0 | starting               | SHOW PROCESSLIST |
| 15 | car             | localhost:50933 | classicmodels | Sleep   |    2 |                        | NULL             |
+----+-----------------+-----------------+---------------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)Code language: PHP (php)

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

Id

The client process’s Id

User

The username associated with the thread.

Host

The host to which the client is connected

DB

The default database if one selected otherwise NULL

Command

The command type

Time

The number of seconds that the current thread has been in its current state.

State

The thread state which represents an action, event, or state that indicates what thread is executing.

Info

The statement is being executed, or NULL if it is not executing any statement. If you do not use the FULL keyword in the SHOW PROCESSLIST command, then only the first 100 characters of each statement are returned in the Info column.

In this tutorial, you have learned how to use the MySQL SHOW PROCESSLIST statement to find the currently running threads.

Was this tutorial helpful?