I was recently debugging a long running database query. In most cases MySQL’s Slow Query Log is a great debugging tool for that. However, in this case I found MySQL’s show processlist to be a better fit.

What show processlist does is provides a list of all MySQL processes running on the server. It’s easy to run:

show full processlist;

.. and its results are easy to discern. Once ran, you’ll see that it returns details about each running process. Full details are outlined on MySQL’s documentation page, but in most cases you’ll likely want to pay attention to the process id, user, database, command, and time.

The command field, in particular, is what makes show processlist effective for debugging slow queries, because it cites the sql command the process is hanging on, and it does so immediately. That’s a huge benefit when compared to other tools such as MySQL’s slow query log. For example, the slow query log waits until the slow query completes before it’s logged (because it needs to cite execution time), meaning you’ll have to wait until the query completes before the slow query log tells you what it is. This was a huge drawback in my case, as the query in question had multiple variants that took close to 10 minutes to complete. That results in an awfully slow (and expensive!) debug cycle.

With show processlist my debug cycle was reduced to mere seconds, resulting in happier, and faster, debugging.