MySQL – Workaround for Read-Only Table Locking During OPTIMIZE Command

disk-spaceinnodbMySQLmysql-5.5

We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table.

The table in question is an InnoDB table and the innodb_file_per_table option was (and still is) enabled during table creation.

As the manual notes: 

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

We cannot afford the table being locked, since new records are inserted continuously in its production environment.

Hopefully there exists some workaround for the obvious problem; a reliable way to OPTIMIZE the table while dealing with the locking problem, that have been used in a production environment.

Maybe creating a temporary table and renaming it, but without the possibility of losing new records while the copying is being performed?

Best Answer

Install percona-toolkit. Then you can use pt-online-schema-change.

I usually use it when I have to alter tables. What it does is copy the data from your current table to another table. Triggers are automatically created, that copy incoming data to the copy while copying. This only works flawlessly if you don't have triggers on the table already. After that, the copied table gets renamed to the original table (which gets dropped, unless you specify not to with parameters). Then you'll have your optimized table.

For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which rebuilds the table to update index statistics and free unused space in the clustered index.

So an 

OPTIMIZE TABLE tablename;

is actually 

ALTER TABLE tablename ENGINE = InnoDB;

Then use pt-online-schema-change like this (on command line, not in MySQL client):

pt-online-schema-change --dry-run --user=root --host=localhost  --alter "ENGINE=InnoDB" --alter-foreign-keys-method=auto D=databasename,t=tablename,p=userpassword

If this runs well, change --dry-run to --execute.

Related Question