Monday, January 9, 2012

Killing a Runaway MySQL Query the Right Way

It happens. Your testing a query and you forget a join in the where clause and you know it's going to run for minutes and pull thousands of records because you hit 'Return' too soon.


Run 'show processlist;' to get the current running queries with active connections (note the active connections).
You will see your runaway query and probably some other connections with a 'Sleep' status under the 'Command' column.
Get the 'id' (ex. 12345) for your runaway.

Run 'kill query 12345;'

I used to always run 'kill 12345;' (without the 'query' specification) but when I was refreshing my memory today on these commands from the mysql docs I found that that command doesn't kill the query itself, but rather by default it only kills the CONNECTION to the query. This is really bad.

The query may still be running in the background hurting performance without any visible process/connection associated with it. If you kill the connection (which is default if neither connection/query is specified) then you have to connection by which to view or kill the actual query. No fixing that performance hit if it was noticeable without restarting the MySQL service or waiting until it completed the result.

Additionally, many web servers keep connections open and 'sleeping'. Killing the connection makes the web server start a new connection. Not really all that bad, but that will add milliseconds to the next query request on top of the still-running query explained above.

Lesson for the day: Specify the optional 'query' command when killing mysql processes.


KILL Syntax


Each connection to mysqld runs in a separate thread. You can see which threads are running with the SHOW PROCESSLIST statement and kill a thread with the KILL thread_id statement.

KILL permits an optional CONNECTION or QUERY modifier:

  • KILL CONNECTION is the same as KILL with no modifier: It terminates the connection associated with the given thread_id.

  • KILL QUERY terminates the statement that the connection is currently executing, but leaves the connection itself intact.

No comments: