Showing running queries in MySQL

MySQL has a statement called "show processlist" to show you the running queries on your MySQL server. This can be useful to find out what's going on if there are some big, long queries consuming a lot of CPU cycles, or if you're getting errors like "too many connections".

The syntax is simply:

show processlist;

which will output something along these lines:

+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id     | User   | Host      | db     | Command | Time | State                | Info                                                                                                 |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 708163 | root   | localhost | NULL   | Query   |    0 | NULL                 | show processlist                                                                                     |
| 708174 | test   | localhost | test   | Query   |    2 | Copying to tmp table | select dist.name, dist.filename, count(*)
from orders_header h
inner join orders_detail d on h.ord |
+--------+--------+-----------+--------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The "info" column shows the query being executedm or NULL if there's nothing currently happening. When running "show processlist" it will only show the first 100 characters of the query. To show the full query run "show full processlist" instead.

Running the above command from the MySQL command line interface with a ; delimiter can make it difficult to read the output, especially if the queries are long and span multiple lines. Using the \G delimiter instead will show the data in what is often a more readable format, although it consumes more rows in your terminal. This is espcially useful when running "show full processlist" because some of the queries displayed may be quite long.

mysql> show processlist\G

*************************** 6. row ***************************
     Id: 708163
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 7. row ***************************
     Id: 708174
   User: test
   Host: localhost
     db: test
Command: Query
   Time: 3
  State: Copying to tmp table
   Info: select dist.name, dist.filename, count(*)
from orders_header h
inner join orders_detail d on h.ord
2 rows in set (0.00 sec)

If you are running as an ordinary user who doesn't have the "process" privilege, then "show processlist" will only show the processes you yourself are currently running. If the user has the process privilege then they can see everything.