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)
