I'm attempting to optimize tables on a very large, production database server. I'm looking for statistics or benchmarks on how long it will take for certain sized databases/tables.

Is there a multiplier of some sort using database/table size to figure out how long it will take?

My total database is about 90GB.

Running SHOW VARIABLES LIKE 'innodb_file_per_table'; indicates that innodb_file_per_table is on.


1 Answer 1



Seriously, there is almost never a need to OPTIMIZE an InnoDB table. 90GB would take hours, maybe days, depending on tunables, disk speed, RAID, etc, etc.

OPTIMIZE may free up some of the free space caused by DELETEs and UPDATEs. But, normally, InnoDB takes care of itself, rarely leaving more than 50% of the disk footprint "free". Normal random inserts (in data or secondary indexes) lead to an average of 31% "free" -- that's a mathematical fact for BTrees.

If your table is PARTITIONed (and you must OPTIMIZE), do it one partition at a time, and do not use OPTIMIZE. There is a bug wherein it will do the whole table in spite of what you say. REORGANIZE PARTITION can do a single partition.

Note that non-tiny InnoDB tables normally always say 4MB-7MB of "Data_free" in SHOW TABLE STATUS. You can't get rid if that 'wasted' space.

OPTIMIZE will not rearrange the data or the secondary indexes -- the rows will continue to be in exactly the same order. (This is unlike MyISAM, which has multiple reasons for doing OPTIMIZE.)

Caution: Most ALTERs, OPTIMIZEs, etc, require enough spare disk space for a complete new copy of that table -- 90GB more.

If you would like to present more details on why you want to OPTIMIZE, I will see if there is a good reason for it. Please provide SHOW CREATE TABLE and SHOW TABLE STATUS. And maybe even a technique to avoid it without downtime.

  • My main goal is general InnoDB table maintenance, something our current server hasn't received much of. We're using New Relic to record queries and improve query design to optimize from that end too, but would also like to make sure there's not general nightly/weekly type maintenance we can do in order to prevent meltdowns/general issues.
    – jwg2s
    Nov 20, 2012 at 2:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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