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?

  • Are you sure you really need to optimize the table weekly? The excess space should level itself out over time if you leave the table alone. What kind of scale/size and what amount of reclaimable space are you dealing with? – Michael - sqlbot Oct 22 '14 at 22:04
  • 1
    @Michael-sqlbot The current table size is 50GB. When OPTIMIZE is called on a copy of the table, the size is reduced to less than 10GB. The amount of records created daily has been increasing over time. – Werner Oct 25 '14 at 7:49

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.