Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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_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`)
) ENGINE=InnoDB AUTO_INCREMENT=107925926 DEFAULT CHARSET=latin1

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.

share|improve this question
    
Please show the exact DELETE statement and the CREATE TABLE statement of the table you are running the DELETE on. –  RolandoMySQLDBA Nov 6 '12 at 18:31
    
Thanks @RolandoMySQLDBA. Updated the question with the statements –  ChrisW Nov 7 '12 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:

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

share|improve this answer
    
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 '12 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 '12 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 '12 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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