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` (
  `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_blob_id`),
  KEY `FK_ADC_FETCHED_DOCUMENT_SCRAPED` (`adc_scraped_property_id`),
  KEY `FK_ADC_URL_FROM_RUN` (`adc_fetch_run_id`),
  KEY `FK_SCR_URL_LINKED_FROM` (`adc_linked_from_id`),
  KEY `index_adc_fetched_documents_on_adc_fetch_run_id` (`adc_fetch_run_id`),
  KEY `index_adc_fetched_documents_on_adc_linked_from_id` (`adc_linked_from_id`),
  KEY `index_adc_fetched_documents_on_adc_fetched_document_blob_id` (`adc_fetched_document_blob_id`),
  KEY `index_adc_fetched_documents_on_adc_scraped_property_id` (`adc_scraped_property_id`),
  KEY `index_adc_fetched_documents_on_adc_duplicate_document_id` (`adc_duplicate_document_id`),
  KEY `index_adc_fetched_documents_on_document_type_id` (`document_type_id`),
  KEY `index_adc_fetched_documents_on_checksum` (`checksum`),
  KEY `index_adc_fetched_documents_on_search_frag_id` (`search_frag_id`),
  KEY `index_adc_fetched_documents_on_adc_property_id` (`adc_property_id`),
  CONSTRAINT `adc_fetched_documents_fetch_run` FOREIGN KEY (`adc_fetch_run_id`) REFERENCES `adc_fetch_runs` (`id`),
  CONSTRAINT `adc_fetched_documents_parent` FOREIGN KEY (`adc_linked_from_id`) REFERENCES `adc_fetched_documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `adc_fetched_documents_scraped_property` FOREIGN KEY (`adc_scraped_property_id`) REFERENCES `adc_scraped_properties` (`id`)

As for my it's taken so long to perform the DELETE, I'm not too worried about for now. I assume that there are various variables that need tweaking.

Many thanks in advance for any help.

  • Please show the exact DELETE statement and the CREATE TABLE statement of the table you are running the DELETE on. Nov 6, 2012 at 18:31
  • Thanks @RolandoMySQLDBA. Updated the question with the statements
    – ChrisW
    Nov 7, 2012 at 9:00

2 Answers 2


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:


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.


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

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

  • Again, thanks for your reply @RolandoMySQLDBA, I've justed checked the size of ibdata1 and it is 7.8G - unfortunately I've no idea how big it was before I started!
    – ChrisW
    Nov 8, 2012 at 9:54
  • Also, thanks for the tips on doing large deletes - I've previously done the new table/truncation on - I really should have done it again this time!
    – ChrisW
    Nov 8, 2012 at 9:59
  • sorry to bother you again, but a further question. If I stop the mysqld from running and then restart it with '–innodb_force_recovery=6' - that should mean that the 'freeing-items' recovery cycle shouldn't start up again - correct? If so, how can I make it never come back? Could I then drop the adc_fetched_documents table and reproduce it from a backup? I have a slave copy of the DB so I'm not too worried about losing the master's copy of the table - I'd turn off replication before I did anything. Thanks for any response.
    – ChrisW
    Nov 9, 2012 at 15:27

To be honest, there isn't really an answer here, but my problem has been solved.

Basically after approx. 5 days the rollback 'freeing items' finally finished. It took approx 1.5x the length of the original DELETE statement ran for to rollback, which is a long time, but less than the 30x that was the worse-case scenario mentioned here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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