What is the best way to schedule automatic optimization of tables in a MySQL InnoDB database? Can I use events for example? I have recently had a big performance issue (when querying) with one of the tables which is actually the largest one in my database and frequently being updated. After I run OPTIMIZE on the table, it has solved the problem.

2 Answers 2


OPTIMIZE TABLE performs the following steps internally on a table (mydb.mytable)

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

Since there is DDL involved, there is no way around queries taking a big performance hit during the operation. Additionally, not performing any optimization would be just as bad.

What you need is to have MySQL Master/Master (aka Circular) Replication set up

You could then try this:

For Servers M1 and M2 and DB VIP pointing to M1

On M2, run the following

SET sql_log_bin = 0;
Perform OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB on all InnoDB tables
Wait for replication to catch (Seconds_Behind_Master = 0)

The SET sql_log_bin = 0 would prevent the DDL commands from replicating over the Master.

Once those steps are complete, promote the Slave to Master, and demote the Master to Slave (can done by just moving your DB VIP from M1 to M2). You could perform this maintenance every day and production will not feel any effects with the exception of the Master Promotion and Slave Demotion.

You could create the script and run it on M2 like this:

echo "SET sql_log_bin = 0;" > InnoDBCompression.sql
echo "STOP SLAVE;" >> InnoDBCompression.sql
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBCompressionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" >> InnoDBCompression.sql
echo "START SLAVE;" >> InnoDBCompression.sql
mysql -u... -p... -A < InnoDBCompression.sql

From here, just wait for Seconds_Behind_Master to be 0 on M2, then move the DBVIP from M1 to M2. Now if you know the specific names of the tables you want optimized, you could adjust the query to fetch just those tables.

Give it a Try !!!


Here is a fair warning: If you have innodb_file_per_table disbaled, every time you run OPTIMIZE TABLE or ALTER TABLE ... ENGINE=InnoDB; the ibdata1 file just grows. You would need to cleanup the InnoDB infrastructure to prevent ibdata1 from growing out of control.

  • Just to clarify, if I do this and want to apply the changes back to the previous master (M1), can I just use SET sql_log_bin=1 on M2, wait for M1 to catch up and switch the VIP back to M1? Apr 26, 2012 at 11:24
  • 1
    Once the InnoDBCompression.sql script is completed, all subsequent connections will have sql_log_bin=1 by default anyway. You must run the script as is on M1 when the DBVIP is on M2. Apr 26, 2012 at 11:37

You could use events to run a periodic optimize, but I get the feeling if you experienced such improvements you may need to look elsewhere.

An optimize on innodb is effectively just running alter table engine=innodb to rebuild it, reclaiming empty space that is the result of deleting many rows. An estimate of how much data isn't actually being used can be found by comparing the data_length and data_free numbers in show table status.

After performing the optimize the operating system now has as much of the table as it can in disk cache since it just read the whole thing. This will yield short term improvements until the less frequently accessed pages are purged from the cache.

In a perfect world you would set your innodb_buffer_pool_size large enough so it's able to hold all your innodb tables in memory. Of course this is not always possible due to budgets or sufficiently large datasets. At the same time, if this is a dedicated DB server with only innodb tables set the buffer pool to 70-80% of the system memory, leaving the rest for OS overhead and disk cache.


Along the same vein of ensuring appropriate hardware, get as fast disks as you can. PCI-E SSD based cards such as Fusion IO are the best you can do today. Can't afford that? Then get a multi spindle SAS RAID disk array.

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.