How to examine and kill MySQL client processes

A useful MySQL command for keeping track of client-server transactions is the SHOW PROCESSLIST command. And you can use the ID's it returns to kill any errant processes.

A useful MySQL command, when it comes to keeping track of client-server transactions, is SHOW PROCESSLIST. It lists all the open connections to the server, together with a thread ID, information on the requesting host, user and database, the query string, and the current state of the connection. The command itself is simple to run from the MySQL client:


You can take a look at sample output in Listing A.

Note that since this command displays currently executing queries, it is only available to users with the SUPER privilege (such as the MySQL root user). A number of different states are possible for each connection—the MySQL manual lists them, together with explanations of each.

In addition to viewing processes, users with the SUPER privilege can also kill running threads—for example, threads which have "gone zombie" and are either not responding or tying up server resources—with the KILL command. To kill a thread completely, use the KILL command followed by the thread ID returned by SHOW PROCESSLIST:

mysql> KILL 27;
Query OK, 0 rows affected (0.05 sec)

To kill the query being executed by a thread but leave the connection active (yes, MySQL even allows such fine-grained control), use the KILL QUERY command instead, followed by the appropriate thread ID.

Read more about performance statistics in MySQL

"Understanding real-time performance statistics in MySQL"

"How to use the SHOW STATUS command in MySQL"

"Understanding the performance statistics from SHOW STATUS"

"Specialized status commands in MySQL"