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.

How long would a delete take for 50 million records in MySQL with the InnoDB engine?

Server Config:

  • MySQL Version 5.0.77
  • CentOS 5.4 Final
  • 64 bit 16GB RAM
  • RAID 5
  • Processor Model : Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
  • 4 cores with 16 processors

DB Hits

  • 127.59 inserts/s
  • 142.39 updates/s
  • 169.28 deletes/s
  • 967.70 reads/s

Note: innodb_buffer_pool_size set to 12G, innodb_per_table is set to NO.

CREATE TABLE `contentq_old` (
  `idcontent` bigint(120) unsigned NOT NULL AUTO_INCREMENT,
  `keyvendor` varchar(32) DEFAULT '0',
  `msisdn` varchar(16) DEFAULT NULL,
  `cli` varchar(16) DEFAULT NULL,
  `message` text,
  `udh` text,
  `priority` int(10) unsigned DEFAULT '1',
  `recorddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `callback_url` text,
  `transactionid` varchar(256) DEFAULT NULL,
  `status` tinyint(4) DEFAULT '0',
   PRIMARY KEY (`idcontent`),
   KEY `PriorityIndex` (`status`,`priority`,`keyvendor`),
   KEY `NewIndex1` (`keyvendor`,`status`),
   KEY `idx_contentq_msisdn` (`msisdn`)
   ) ENGINE=InnoDB AUTO_INCREMENT=137977141 DEFAULT CHARSET=latin1;


select count(*) from contentq_old where idcontent > 82072064 ; ==> 55905077

delete from contentq_old where idcontent > 82072064 ;

Can someone let me know how much time would it take to complete? Its almost 4 hrs and 45mins now.

Also I'd like to understand how MySQL delete architecture works. Does it delete and recreate the table for indexes to be kept in place and thus it frees space in the disk?

share|improve this question
1  
I think your question would be more interesting for future visitors if you asked something like "Is there a way to estimate how much work has already been done/how much work is left before this finishes?" (i.e. how to get "progress" information for a long-running statement) rather than "how much time for this specific query". That would be more generally useful (and more useful to you in the future). –  Mat Mar 15 '13 at 5:50
    
It's too late now, but it would have been much faster if you'd dropped all the indices before deleting, and then recreated them afterwards. That's still a lot of data to delete, though. It may also have been faster to create a new table, with no indices, and select the rows you wished to retain (perhaps enormously faster if you were deleting more than you were keeping). –  Jim Stewart Mar 15 '13 at 7:37
    
It took 10 hrs to complete. –  Mannoj Mar 15 '13 at 18:51

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.