1 minute, 32 seconds

Do you you ever have those moments where you’re trying out a query in MySQL and realize after you executed it that it’s going to kill your database server? If you’re like me, you’re often working in a LAMP stack in which case you’ve executed the query via a web page. The first thing you do is hit “esc” to stop the page from loading. MySQL doesn’t head the “full stop” call from Apache, if issued. If you’re silly and you did this on some sort production machine, you don’t exactly want to restart Apache or even worse, restart MySQL which can often take a while.

Enter show processlist; and kill PID! Yeah, mysql has it’s on version of “ps” and “kill -9”. In our case, this is extremely handy because it saves us the headache of rebooting MySQL and taking the DB offline for a minute. Connect to your DB as root and type:

mysql> show processlist;
+------+------+-----------+------+---------+------+----------------------+--------------+
| Id   | User | Host      | db   | Command | Time | State                | Info         |
+------+------+-----------+------+---------+------+----------------------+--------------+
| 1693 | root | localhost | rei  | Query   |   75 | Copying to tmp table | SELECT     di| 
| 1695 | root | localhost | NULL | Query   |    0 | NULL                 | show processl| 
+------+------+-----------+------+---------+------+----------------------+--------------+

Indeed this one query is killing the CPU:

top - 08:57:50 up 30 days, 20:59,  2 users,  load average: 0.53, 0.14, 0.04
Tasks:  72 total,   2 running,  70 sleeping,   0 stopped,   0 zombie
Cpu(s): 20.1% us, 30.1% sy,  0.0% ni, 49.8% id,  0.0% wa,  0.0% hi,  0.0% si
Mem:   2074628k total,  2010740k used,    63888k free,   142648k buffers
Swap:   524280k total,      192k used,   524088k free,  1549692k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                              
 3119 mysql     16   0  549m 115m 5076 S 99.8  5.7   9:53.40 mysqld                                                                                                                                

All we have to do is kill it and the box is back to idle. Sweet!

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

mysql> show processlist;
+------+------+-----------+------+---------+------+-------+------------------+
| Id   | User | Host      | db   | Command | Time | State | Info             |
+------+------+-----------+------+---------+------+-------+------------------+
| 1695 | root | localhost | NULL | Query   |    0 | NULL  | show processlist | 
+------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)