I copied a big table's structure with (it is an InnoDB table btw)

CREATE TABLE tempTbl LIKE realTbl 

Then I changed an index, and filled it up so I could run some test. Filling it was done using:

INSERT INTO  `tmpTbl` 
FROM `realTbl`

This took too long, so I wanted to stop this test.1

I killed the process while it was in a "Sending data" state: it is now "killed", and still in the state "Sending data".

I know some killed processes need to revert changes and so could take (equally?) long to kill compared to how long they were running, but I can't imagine why this would be the case now: The whole table needs to be emptied.

I'm curious as to what is happening that would take stopping/killing a simple query like this very long. To give you some numbers: the insert was running for an hour or 3, the kill is closer to 5 7 now. It almost looks like it runs a DELETE for every INSERT it did, and the delete takes longer then the insert did? Would that even be logical?

(And if anyone knows how to kick my test-server back into shape that would be nice too, as it's eating some resources, but that's not really important at this moment :) )

1) I don't know yet why (it's a big table, 10M rows, but it should take that long?), but that's another thing / not part of this question :). It might be that my test could have been smarter or quicker, but that is also not the question now :D

  • 1
    What are the chances of me looking for the answer to the very same question! :o – Graham Feb 18 '13 at 14:24

The reason the kill takes so long is most likely due to the rollbacks issued by the innodb transaction. From InnoDB performance tips:

Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup.

Edit: The innodb force recovery methods might be of use to you (glad you did this on a test environment)

You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

And next time, insert a small subset of data each time. 10 million rows shouldn't take that long to insert, but there could be multiple reasons it does. Without knowing your environment I can't offer advice on that.

| improve this answer | |
  • In short: I am fscked... Am I glad this is a test-environment... – Nanne Sep 12 '11 at 14:46
  • @nanne added a bit about how to force recovery. Glad it's on a test environment! – Derek Downey Sep 12 '11 at 14:50
  • I just found that quote too, i'll try it. @insertion: you are right: it didn't even cross my mind it could take this long, so never considered any alternatives :) – Nanne Sep 12 '11 at 14:53
  • @Nanne off top of my head, I'd say your innodb_buffer_pool_size is too low. See the 'Disk I/O' section of the performance tip document for other options. – Derek Downey Sep 12 '11 at 14:59
  • I'll check that, thanks. Anyway, the kill-and-force-recovery seems to have worked, alhtough I couldn't stop the process. In the end I just did a kill -9 to be done with it. – Nanne Sep 12 '11 at 15:08

I can see a messy operation in this. The tmpTbl is InnoDB. Loading new data into it will produce some MVCC activity. This will pile changes in the redo logs (housed in ib_logfile0 and ib_logfile1, perhaps some will be in ibdata1 as well).

Once you kill the INSERT, all the changes (for each row, a new record in place of no record) to the InnoDB table must be rolled back.

You could probably kill mysqld and start mysqld again only to encounter some of this during the crash recovery phase of mysql startup.

| improve this answer | |
  • I think I get it, but what do you mean by that last sentence? That if I kill the thing, the recovery-phase might start the 'rollback' all over again? That wouldn't be good. I'm now just letting it run, but its getting towards 7 hours of run-time now, which is getting ridiculous-ish. Would you advice in favor or against killing the mysqld? – Nanne Sep 12 '11 at 14:44
  • Yes, you got the point. AS to the last sentence, @DTest explains it a little more. – RolandoMySQLDBA Sep 12 '11 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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