9

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` 
SELECT *
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

Nanne
  • 285
  • 1
  • 3
  • 12

3 Answers3

16

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.

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
3

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.

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
0

You must first stop mysql (before that Full backup with mysqldump :) )

systemctl stop mysql

if you can't stop, close all MySQL process in htop with filter with f4 and try to stop MySQL

After stop set below line in MySQL config file

innodb_force_recovery = 1

And finally start MySQL

Pirooz Jenabi
  • 101
  • 1