How do I terminate a running SELECT query without restarting MySQL?

Question

I have a MySQL select query that is not getting killed. Please help!

And we have load avg building up on this server.

 | 64446 | root    | localhost | dast-india | Killed  | 3758 | Sorting result | select * from dastnumbers_0 order by addeddate desc limit 25 | 

I tried killing and unlocking the tables as usual it works for selects. But now its not responding for that too. Please help.

Answer 1

Based on the comments trail on this question, I have some bad news and good news.

BAD NEWS

I believe dastnumbers_0 is an InnoDB table. You can verify what with

SELECT engine FROM information_schema.tables
WHERE table_name = 'dastnumbers_0';

You summoned all the rows with your SELECT query because of there being no WHERE clause. An attempt to sort 2 billion rows must take place.

Even worse, InnoDB attempts to keep a snapshot of the entire table in the rollback segments and undo tablespace within ibdata1 ( See picture of this in http://www.scribd.com/doc/31337494/XtraDB-InnoDB-internals-in-drawing ). This is manifested as an increased size of ibdata1. When you killed the query, InnoDB attempts clear out whatever was collected in a snapshot.

You will have to just let go ahead and clear. If you kill the mysqld process prematurely, it will no matter. On the next mysql startup, it will pickup where it left off.

I was in commentary discussion with @a_horse_with_no_name about this. In theory, snapshots should not be happening on a straightforward SELECT. The only way this could happen with a SELECT query is if the DB Connection's session has autocommit disabled or a BEGIN TRANSACTION ... COMMIT/ROLLBACK block was issued. Please let us know if this is the case.

GOOD NEWS

You could refactor your query. First look at the query:

select * from dastnumbers_0 order by addeddate desc limit 25;

It will get everything, sort it in descending order, then that the first 25 rows. That's an expensive operation. Make sure you have an index on addeddate . If you do not have an index on addeddate or you are not sure, you may need to create it with this:

ALTER TABLE dastnumbers_0 ADD INDEX (addeddate);

This will take a long time since you have 2 billion rows, but you only have to do this once.

Then, make the query get only the 25 keys for the table. Suppose dastnumbers_0 has id as its primary key. You could now have the query do something like this:

SELECT B.* FROM
(
    SELECT id FROM dastnumbers_0
    ORDER BY addeddate DESC LIMIT 25
) A LEFT JOIN dastnumbers_0 B USING (id);

This technique can work because I answered a question like this in StackOverflow and got a 200-point bounty out of 11 submitted answers : https://stackoverflow.com/a/6023217/491757

Give it a Try !!!