How to find and kill misbehaving MySQL queries

Last updated on November 20, 2020 by Dan Nanni

Sometimes the complexity of a relational database system can be overwhelming. Fortunately, that complexity is an advantage, as with MySQL's tools for managing queries. In this tutorial, I will show you how to find and kill any misbehaving MySQL queries.

To view the currently-running queries, log in to the MySQL console and run the show processlist command:

mysql> show processlist;

+--------+--------+-----------------+---------+---------+-------+-------+------------------+-----------+---------------+-----------+
| Id     | User   | Host            | db      | Command | Time  | State | Info             | Rows_sent | Rows_examined | Rows_read |
+--------+--------+-----------------+---------+---------+-------+-------+------------------+-----------+---------------+-----------+
|  78233 | root   | 127.0.0.1:37527 | mysql   | Sleep   | 16474 |       | NULL             |         6 |             6 |         6 |
|  84546 | root   | 127.0.0.1:48593 | mysql   | Sleep   | 13237 |       | NULL             |         2 |             2 |         2 |
| 107083 | root   | 127.0.0.1:56451 | mysql   | Sleep   | 15488 |       | NULL             |         1 |           121 |       121 |
| 131455 | root   | 127.0.0.1:48550 | NULL    | Query   |     0 | NULL  | show processlist |         0 |             0 |         0 |
+--------+--------+-----------------+---------+---------+-------+-------+------------------+-----------+---------------+-----------+
4 rows in set (0.03 sec)

The first column you should look at is Time, which is the number of seconds the process has been "doing the thing it's doing." A process whose command is Sleep is waiting for a query to come in, so it's not consuming any resources. For any other process, however, a Time of more than a few seconds indicates a problem.

In this case, the only query running is our show processlist command. Let's see what it looks like if we have a poorly-written query running:

mysql> show processlist;

+--------+--------+-----------------+-----------+---------+-------+--------------+----------------------------------+-----------+---------------+-----------+
| Id     | User   | Host            | db        | Command | Time  | State        | Info                             | Rows_sent | Rows_examined | Rows_read |
+--------+--------+-----------------+-----------+---------+-------+--------------+----------------------------------+-----------+---------------+-----------+
|  78233 | root   | 127.0.0.1:37527 | example   | Sleep   | 18046 |              | NULL                             |         6 |             6 |         6 |
|  84546 | root   | 127.0.0.1:48593 | example   | Sleep   | 14809 |              | NULL                             |         2 |             2 |         2 |
| 107083 | root   | 127.0.0.1:56451 | example   | Sleep   | 17060 |              | NULL                             |         1 |           121 |       121 |
| 132033 | root   | 127.0.0.1:54642 | example   | Query   |    27 | Sending data | select max(subtotal) from orders |         0 |             0 |         0 |
| 133933 | root   | 127.0.0.1:48679 | NULL      | Query   |     0 | NULL         | show processlist                 |         0 |             0 |         0 |
| 134122 | root   | 127.0.0.1:49264 | example   | Sleep   |     0 |              | NULL                             |         0 |             0 |         0 |
+--------+--------+-----------------+-----------+---------+-------+--------------+----------------------------------+-----------+---------------+-----------+
6 rows in set (0.00 sec)

Ah! Now we see there is a query that's been running for almost 30 seconds. If we don't want to let it run its course, we can kill it by passing its Id to the kill command:

mysql> kill 132033;
Query OK, 0 rows affected (0.00 sec)
mysql>

(Note that MySQL will always report 0 rows affected, because we're not altering any data.)

Judicious use of the kill command can clean up a backlog of queries. Remember, however, that it's not a permanent solution - if those queries came from your application, you need to rewrite them, or you'll continue to see the same issue reappear.

See also MySQL's documentation on the different Command values: https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html

Support Xmodulo

This website is made possible by minimal ads and your gracious donation via PayPal or credit card

Please note that this article is published by Xmodulo.com under a Creative Commons Attribution-ShareAlike 3.0 Unported License. If you would like to use the whole or any part of this article, you need to cite this web page at Xmodulo.com as the original source.