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

share|improve this question
    
You can put together a script for this. You will get exact statistics. –  ashuthosh Nov 19 '12 at 8:54
    
Please run SHOW VARIABLES LIKE 'innodb_file_per_table'; and tell us is it is ON or OFF. –  RolandoMySQLDBA Nov 20 '12 at 17:36
    
innodb_file_per_table is on. –  jwg2s Nov 21 '12 at 4:49
add comment

1 Answer 1

Don't OPTIMIZE.

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.

share|improve this answer
    
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 '12 at 2:11
    
Very well thought out, especially 2nd and 3rd paragraphs. +1 !!! –  RolandoMySQLDBA Dec 20 '12 at 3:56
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.