Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
There is no reason to panic. Sorting 3758 rows without an index may take some time but it's not going to take the server down. –  ypercube Dec 4 '12 at 10:01
    
I understand, but the table size is of about 2 billion records and sort is going to take for ever. On top of it, I have killed the query. so I shouldn't be expecting anything else other than freeing up the memory for this query. –  Mannoj Dec 4 '12 at 10:07
1  
2 billion rows, that's different! Sorry, misunderstood the 3758 with the "rows examined" column in the EXPLAIN results. –  ypercube Dec 4 '12 at 10:12
    
do I have only restart as an option?? –  Mannoj Dec 4 '12 at 10:22
    
How are you connecting? Through command line, PHPMyAdmin, Workbench? –  ypercube Dec 4 '12 at 10:32
show 6 more comments

1 Answer

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 : http://stackoverflow.com/a/6023217/491757

Give it a Try !!!

share|improve this answer
    
Does InnoDB really store a snapshot in the rollback segment for a read only query? (Assuming no updates are taking place at the same time) –  a_horse_with_no_name Dec 4 '12 at 17:38
    
@a_horse_with_no_name Since InnoDB supports MVCC and transaction isolation, it has to keep a copy of affected rows. What is really bad here is that the query has no WHERE clause. It has to provide MVCC for 2 billions rows. Ouch !!! The only way to see this in print would be to kill the mysqld process, run service mysql start in one session and run tail -f mysqld.log in another session and watch InnoDB try to clean itself up (not a pretty sight). –  RolandoMySQLDBA Dec 4 '12 at 17:41
    
It does not need to copy the data before running the query. Only if something is updated while the statement is still running. Oracle and PostgreSQL don't implement MVCC this way, they do not copy the data just because a select statement is running (they only keep a copy of the old row around in case it is updated while other transactions are still accessing it but that happens "on demand"). InnoDB's behaviour sounds like a major design flaw. Do you have some reference for this? –  a_horse_with_no_name Dec 4 '12 at 17:45
1  
Hmm, I don't see any reference in the manual that the data is actually copied to achieve the MVCC snapshot. And I can hardly believe it actually. That would make InnoDB extremely slow and nearly unusable. I'm not disputing the fact that dastnumbers_0 is the culprit of the problem. I just can not believe that InnoDB's MVCC implementation should be that bad. –  a_horse_with_no_name Dec 4 '12 at 18:26
1  
@a_horse_with_no_name I have seen this behavior in the past with an INSERT ... SELECT (See dba.stackexchange.com/a/5662/877 as well as @DTest's answer ) I know that's different from a plain SELECT. Perhaps we should ask the OP if the SELECT is in the middle of a real transaction (BEGIN ... COMMIT block) or has autocommit disabled in the session. Then my conjecture would be more realistic. –  RolandoMySQLDBA Dec 4 '12 at 18:36
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.