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

share|improve this question
    
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
add comment

1 Answer 1

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

http://www.mysqlperformanceblog.com/2011/04/20/drop-table-performance/

share|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
add comment

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.