Can I do anything about a very long running KILL command?

I've got a table with 103M+ records in it and takes up 70GB on the disk. I decided to DELETE (in one statement) 70% of the records.

As expected, this took an awfully long time to run. I decided to KILL that DELETE command before it finished (although it had been running for approx. 350K seconds, as shown by SHOW PROCESSLIST).

The command (as shown by SHOW PROCESSLIST) has now changed to 'Killed' with the state now being 'freeing items'. It is still running, now with a time of 450K seconds.

My question is this, is there anything that I can do to actually stop the command from running? To stop it from doing the freeing? Will restarting mysqld itself have that effect?

Is there anyway to judge how far through its freeing process it has gotten?

I ask because, it is actually using a lot of the machine's resources and is having an impact on the performance of the rest of the application.

show processlist;
| Command | Time   | State         | Info  
| Killed  | 449822 | freeing items | DELETE FROM *****

As requested, here is the create table and delete statements in question:

``
DELETE FROM adc_fetched_documents WHERE id <= 73097292

CREATE TABLE adc_fetched_documents (
id int(11) NOT NULL AUTO_INCREMENT,
adc_fetch_run_id int(20) NOT NULL,
adc_linked_from_id int(20) DEFAULT NULL,
adc_fetched_document_blob_id int(20) DEFAULT NULL,
adc_scraped_property_id int(20) DEFAULT NULL,
adc_duplicate_document_id int(20) DEFAULT NULL,
status int(11) NOT NULL,
document_type_id int(4) NOT NULL,
fetched_at datetime NOT NULL,
checksum varchar(256) DEFAULT NULL,
http_response_code int(11) DEFAULT NULL,
content_type varchar(256) DEFAULT '',
url text,
http_header text,
search_frag_id int(11) DEFAULT NULL,
adc_property_id int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY FK_ADC_DUPLICATE_OF (adc_duplicate_document_id),
KEY FK_ADC_FETCHED_DOCUMENT_BLOB (adc_fetched_document_

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 !!!

StackExchange Database Administrators Q#28272, answer score: 4