I have a 100G+ innodb table that needs to be dropped in a production environment. I am trying to do this without interruption to service.

The table is no longer being written to, and needs to be dropped to free up space. My MySQL version is 5.1.52.

What is the expected impact to performance while this table is being dropped? I was thinking of slowly shrinking it by deleting rows and then optimizing. However, optimize makes a copy of the table so it will be just as disk IO intensive, if not more.

  • 1. Backup universe. 2. Drop table and see what happens. 3. Save important result info to inter-universal repository. 4. Restore to backup 5. Check repository for data. Don't forget to do step 5 after step 1 also in case you've already restored! – ErikE Feb 11 '12 at 2:36

Deleting in batches and then optimizing is really the worst thing you can do. Each delete will be performed as a transaction and each optimize will effectively recopy what remains of the giant table.

Think of it like this. You have a 5 gig table, delete 0.5 gigs, optimize (copy 4.5 gig file), delete 0.5 gigs (copy a 4.0 gig file) etc..

And really the overhead associated with each optimize is much more than just copying that much data on a filesystem since it's tantamount to inserting all those rows into a new table and all the transactional overhead that comes along with it.

Dropping a table should be the most efficient way to do it

Edit: Here is some thorough benchmarks though


| improve this answer | |
  • That said, any idea how MySQL will treat handle other queries during the drop? Should I expect issues with connection handling, or just overall slower performance due to increased file IO? – sreimer Feb 10 '12 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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