patternsqlModerate
mysqlcheck to optimize innodb tables -- performance/scheduling
Viewed 0 times
tablesschedulinginnodbperformanceoptimizemysqlcheck
Problem
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
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.Solution
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.
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.
Context
StackExchange Database Administrators Q#28876, answer score: 10
Revisions (0)
No revisions yet.