MySQL / InnoDB: Does daily optimization improve performance?

All we need is an easy explanation of the problem, so here it is.

Would performing daily optimizations of tables increase performance for MySQL and InnoDB? I have never did this before, but I have seen this module for Drupal http://drupal.org/project/db_maintenance, and it looks promising.

PS: As I learned, OPTIMIZE TABLE liberates overhead. However, I double checked the overhead column in mysql and it is all empty for my innodb tables … So, I guess this may not be necessary.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

MyISAM

Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

This definitely cleans up table fragmentation and computes fresh statistics for indexes

InnoDB

Doing OPTIMIZE TABLE mydb.mytable; performs two basic operations

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

While this eliminates fragmentation, ANAYLZE TABLE is complete useless for InnoDB. I wrote about this a long time ago:

If your data growth rate is very small (or in other words, your dataset stays basically the same size for months at a time), then defragmenting tables would just be overkill. You should probably focus on tuning InnoDB’s other aspects (See my Aug 04, 2011 post: Optimizing InnoDB default settings)

I also have posts in the Drupal StackExchange on opimizing MySQL

Method 2

For it worth :
I”ve ran optimize table on the main tables of a database who works under heavy load. The main tables have been under great load of inserts/updates/deletes.
The optimize reduced the table sizes by 90% and improved the system performance by more than twice !

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply