≡ Menu

How to list all processes currently running in MySQL?

The question – How to list and count the number of processes currently running in MySQL?

MySQL has several commands that can do this. MySQL has a database called INFORMATION_SCHEMA which keeps track of all the processes currently running. The table in this database called PROCESSLIST has a list of all running processes.

First, open a terminal or a shell using SSH. Then login to MySQL:

mysql -u databaseuser -p

Once the mysql shell command prompt opens, use the following commands to list a count of the number of processes currently running:

mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST;
+----------+
| COUNT(*) |
+----------+
|       21 |
+----------+
1 row in set (0.02 sec)

This shows that there are 21(!) MySQL queries currently running on my server. This is quite a lot, but I have a semi-dedicated server which can handle the load!

Now, to list all processes with the query that is running, just replace COUNT(*) with *:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST;

This will list all processes and queries in their entirety. Note that this being a SQL query against a table, you can do all sorts of nice things with it, like check for processes that have a table in the query, or check for all SELECT processes only, or list all INSERTs or list all UPDATEs etc. You can use LIKE, REGEXP and other command options to query this table. The PROCESSLIST table has columns like DB (database), USER (the username of the user who performed the query), HOST (the hostname), COMMAND (the full query) etc. See this page for a full list of columns of the process list table.

There is another command that can also do this:

SHOW FULL PROCESSLIST;

This command is equivalent to the SELECT command.

Comments on this entry are closed.