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.
Best Answer
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/