Mysql – Internal reason for killing process taking up long time in thesql

innodbMySQLprocess

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.¹

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 😀

Best Answer

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.

Related Solutions

Mysql – Why is DROP DATABASE taking so long? (MySQL)

Rather than killing the process, it would be safer if you did it within MySQL:

$ mysqladmin processlist -u root -p
Enter password: 
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id  | User | Host      | db                | Command | Time | State | Info             |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example           | Sleep   | 297  |       |                  |
| 407 | root | localhost |                   | Query   | 0    |       | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+

The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:

$ mysqladmin kill 174

Run the processlist command above again to confirm that it was killed.

If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.

You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.

Generally speaking you can use either mysql or mysqladmin. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).

Mysql – do anything about a very long running KILL command

First of all, thank you posting the DELETE query and the table layout

With regard to your question, there is nothing you can really do because a rollback is being done via the UNDO tablespace inside ibdata1 (it should have grown immensely). If you kill the mysqld process and restart it will just pickup where it left off as part of the crash recovery cycle.

Here is how you can handle large and small deletes in the future:

LARGE DELETEs

Instead of deleting 70% of the table and create lots of rollback info, try copying the tables you want to keep.

CREATE TABLE adc_fetched_documents_new LIKE adc_fetched_documents;
INSERT INTO adc_fetched_documents_new
    SELECT * FROM adc_fetched_documents
    WHERE id > 73097292
;
ALTER TABLE adc_fetched_documents RENAME adc_fetched_documents_zap;
ALTER TABLE adc_fetched_documents_new RENAME adc_fetched_documents;
TRUNCATE TABLE adc_fetched_documents_zap;

Whenever, you delete 70% or more of the table, do it like this.

SMALL DELETEs

If you are deleting smaller chunks, perhaps you can do it with a DELETE JOIN:

CREATE TABLE deletion_ids (id INT NOT NULL PRIMARY KEY (id)) ENGINE=InnoDB;
INSERT INTO deletion_ids
    SELECT id FROM adc_fetched_documents
    WHERE id <= 73097292
;
DELETE B.* FROM deletion_ids A INNER JOIN adc_fetched_documents B USING (id);

Give it a Try !!!