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”