Re: Execute table optimization concurrently

[Rick James]

5 hours --> 2 hours is about all that you can expect. This is because OPTIMIZE is nearly I/O bound. Your disks won't run any faster.

Most tables (in my experience) benefit not at all by running OPTIMIZE. In particular, if a table is not modified (since the last OPTIMIZE), then OPTIMIZE is a costly way to copy the table over and rebuild the already rebuilt INDEXes.

In other cases, the benefits include defragmentation of both the data and index(es). However, this benefit is insignificant in _most_ cases.

400-way parallelizing of OPTIMIZE (or any other I/O thingie) is likely to _severely_ fragment the files. This is because each table or index will be asking for more disk space in an interleaved fashion, thereby leading to the pieces being scattered around on disk. Break it in 2-3 chunks and you will get nearly down to 2 hours, but there will still be some fragmentation.

How often are you doing the OPTIMIZEs? I have never seen a case where it is worth doing it more than monthly. You could do an average of 13 per night; but balance them so a big table stands alone, and lots of little ones are done together.

In MyISAM, you can compute Data_free / Data_length, and if it is more than, say, 0.1 (10%), then (and only then) do the OPTIMIZE. Below that you are wasting downtime.

What happens if you miss an OPTIMIZE cycle? Does the system slow down? Does the disk fill up? Try skipping multiple cycles. See if any harm is done. I suspect not.

Find the MyISAM table with the largest ratio of Data_free / Data_length; let's discuss it.

This computation does not work for InnoDB. InnoDB benefits even less from OPTIMIZE.

What version of MySQL (or Percona or MariaDB) are you using? I mention those others because they have better tools for accessing the situation.