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?