MySQL SHOW PROCESSLIST

The 'SHOW PROCESSLIST' command in MySQL is used to display all the running thread information related to the current user account. This command is very useful when the MySQL server returns too many connections error message. We have to ensure that the user account has process privilege to view all running threads; otherwise, we will see threads associated with the current accounts only. MySQL provides a KILL statement to terminate the threads that we want to kill. If we are using a terminal, we can use the CTRL+C command for terminating the current running thread.

Syntax

The following are the syntax to display process information:

If we get a "too many connections" error message and want to determine which statements are executing, we can use the syntax as follows:

It is noted that if we have not used the FULL clause, the statement displays only the first 100 characters of each statement in the Info field of the output.

MySQL SHOW PROCESSLIST Example

Let us understand it with an example. Executing the below statement, we can see the following output:

The output of the SHOW PROCESSLIST displays the following columns:

• Id: It indicates the connection identifier or client process's id. It is the same as the value returned by the CONNECTION_ID() function within the thread.
• User: It indicates the user name associated with the thread or who issued the statement.
• Host: It indicates the hostname of the client who issued the statement. In other words, it is the hostname to which the client is connected.
• DB: It indicates the default database name. If we have not selected any database, it will return a NULL value.
• Command: It is the type of command executed by a thread on behalf of the client. If the session is in an idle state, it will be in sleep mode.
• Time: It indicates the amount of time that the thread has stayed in its current state.
• State: It is a thread state representing an action, event, or state that indicates what the thread is executing. It is noted that if a thread stays in a current state for a long time, there may be a problem that should be investigated.
• Info: It contains a statement, which is being executed by a thread. If the thread is not executing any statement, it returns a NULL value. For example, if a statement executes a procedure that contains a SELECT statement, this field displays the SELECT statement.