Re: Fragmented Tables

[Daniel Smythe]

You're going to have to optimize your tables, if you want to clear the issue.

I wouldn't even bother if it wasn't impacting performance, but if it is, you're going to have to take outages while the OPTIMIZE runs. Yes, you only have to run OPTIMIZE.

It'll likely not help with InnoDB unless you have innodb_file_per_table on.

Otherwise, a good rule of thumb would be:

Table Size is KB = Seconds of outage.
Table Size is MB = Minutes of outage.
Table Size is GB = Hours of outage.

If you had another MySQL instance with a replica of your data, you could perform the OPTIMIZE on the slave server, then fail over to the slave server, making it the new master, and then OPTIMIZING the old master which is now the slave.