Solution:
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.
FROM THE MYSQL DOCS: http://dev.mysql.com/doc/refman/5.1/en/kill.html
KILL
Syntax
KILL [CONNECTION | QUERY] thread_id
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
statement. thread_id
KILL
permits an optional CONNECTION
or QUERY
modifier:
KILL CONNECTION
is the same asKILL
with no modifier: It terminates the connection associated with the giventhread_id
.KILL QUERY
terminates the statement that the connection is currently executing, but leaves the connection itself intact.