Using the SHOW PROCESSLIST statement to display connections in MySQL

This article describes how to use the SHOW PROCESSLIST statement display all connections for the current MySQL database server.

As a database administrator or maintainer, you may want to know how many connections are there in the current database server. Too many connections may lead to the downtime of the database server.

MySQL provides SHOW PROCESSLIST commands to return information about all connections to the current database server, including connection ID, user, host, database, time, and status.

MySQL stores all connection information of the current server in the information_schema.processlist table, and you can also query all connection information from this table.

In fact, the SHOW PROCESSLIST command is shorthand for querying the information_schema.processlist table.

Sometimes, you may receive a “too many connections” error from the MySQL server. To find out why, you can get all current connections with the SHOW PROCESSLIST command and kill idle connections with the KILL command.

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.

The users with PROCESS privileges can view all current connections, Other users can only view connections associated with their account.

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 current connection has been established.
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

Let’s run the following SHOW PROCESSLIST statement with the root user:

SHOW PROCESSLIST;

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

Here, the root user is superuser and he has PROCESS privilege, so he can view all connections.

Let’s run the following SHOW PROCESSLIST statement with the sqliz user:

SHOW PROCESSLIST;

+----+-------+---------------------+---------+---------+------+----------+------------------+
| Id | User  | Host                | db      | Command | Time | State    | Info             |
+----+-------+---------------------+---------+---------+------+----------+------------------+
| 28 | sqliz | 192.168.0.204:54692 | sqlizdb | Query   |    0 | starting | SHOW PROCESSLIST |
+----+-------+---------------------+---------+---------+------+----------+------------------+

Here, the sqliz user is an normal user and does not have PROCESS privileges, so he can only view the connections associated with him.

Query connections from the processlist table

MySQL stores all connections information of the current server in the information_schema.processlist table. So, you can use the following statement to query all connections.

SELECT * FROM information_schema.processlist;

+----+-----------------+---------------------+---------+---------+---------+------------------------+----------------------------------------------+
| ID | USER            | HOST                | DB      | COMMAND | TIME    | STATE                  | INFO                                         |
+----+-----------------+---------------------+---------+---------+---------+------------------------+----------------------------------------------+
| 27 | root            | 192.168.0.204:54690 | NULL    | Query   |       0 | executing              | SELECT * FROM information_schema.processlist |
| 28 | sqliz           | 192.168.0.204:54692 | sqlizdb | Sleep   |      18 |                        | NULL                                         |
|  5 | event_scheduler | localhost           | NULL    | Daemon  | 4142731 | Waiting on empty queue | NULL                                         |
+----+-----------------+---------------------+---------+---------+---------+------------------------+----------------------------------------------+

Conclusion

In this article, you learned two ways to get a list of connections to a MySQL database server:

  1. Using SHOW PROCESSLIST command to display all connections.
  2. Fetching all connections from the information_schema.processlist table.