MySQL InnoDB – Does Daily Optimization Improve Performance?

innodbMySQLoptimizationperformance

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.

Best Answer

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:

Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?
Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down

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

Jun 07, 2011 : How do I convert a database from MyISAM to InnoDB?
Apr 15, 2011 : What would be the optimal MySQL configuration for a Drupal 7 site?

Related Solutions

Mysql – Why does MySQL (InnoDB) table get faster after OPTIMIZE TABLE, but then don’t work

OPTIMIZE rebuilds the table. This (for InnoDB) squeezes out some of the fragmentation and wasted space. This is unlikely to make a noticeable difference in any query.

Also, OPTIMIZE does an ANALYZE. This has a chance of changing the statistics, thereby leading to a different (better or worse) EXPLAIN plan.

Since ANALYZE is much faster (on InnoDB) than OPTIMIZE, just do the ANALYZE.

Various non-ANALYZE actions cause an ANALYZE to be done.

ANALYZE randomly probes the BTrees, gathering stats. Sometimes the resulting stats are poor. There is effectively no way to prevent this from happening. Several partial hacks have been created over the years; 5.6.7 gets close to eliminating this problem with ANALYZE. Here's one of them: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_stats_persistent_sample_pages

Mysql – InnoDB, What would cause: “db1/tableABC contains 6 indexes inside InnoDB, which is different from the number of indexes 5 defined in MySQL”

You have 6 indexes

  PRIMARY KEY (`UID`),
  KEY `FK_miii_data_miif_mapping` (`MODEL_INTEGRATION_IMPORT_FIELD_MAPPING_UID`),
  KEY `FK_miii_data_miif` (`MODEL_INTEGRATION_IMPORT_FIELD_UID`),
  KEY `FK_MIIIData_MIIInstance` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`IMPORT_PAGE_NUMBER`),
  KEY `i_ticker` (`MODEL_INTEGRATION_IMPORT_INSTANCE_UID`,`TICKER_CODE`),
  KEY `TICKER_CODE_INDEX` (`TICKER_CODE`),

The PRIMARY KEY is in the gen_clust_index (aka Clustered Index). All secondary index entries include a corresponding PRIMARY KEY entry.

I would mysqldump that table and reload it into a test DB server

Next, I would run CHECKSUM TABLE db1.tableABC; or mysqlchk against db1.tableABC in production and the test DB.

If the checksum values match, you should be OK.

If they do not match or you are not sure, run this on the production server

ALTER TABLE db1.tableABC ENGINE=InnoDB;

This will rebuild the table and its indexes.

If that error ever materializes after this, there may be a data dictionary problem inside ibdata1. Your final solution would be to dump all databases, shutdown mysql, delete ibdata1, ib_logfile0, ib_logfile1, start mysql, reload all data.

I posted this InnoDB Cleanup Process in StackOverflow back on Oct 29, 2010