Re: Execute table optimization concurrently

[Rick James]

Are you talking about OPTIMIZE TABLE?

The simple answer is to get rid of the script.

Seriously, there is rarely any reason to run OPTIMIZE TABLE.

OK, I have to partially take that back. I have seen about 1 table in 1000 (literally) that could benefit from periodic OPTIMIZEing. Even then, the period was something like once a month. Furthermore the were MyISAM, not InnoDB.

I would be happy to critique _your_ situation in more detail. Please provide
* SHOW CREATE TABLE,
* A discussion of how/when the data is INSERTed (UPDATEd, etc),
* The main SELECT(s) you think will benefit from the OPTIMIZE TABLE.

I will either
* Agree that OPTIMIZE is beneficial, and suggest how often might be sufficient, or
* Explain why there will be too little benefit.

Hmmm... now that I think about my "1/1000" claim...
* One system had ~1000 tables; one needed OPTIMIZE. I did the OPTIMIZE with zero downtime. Later it became clear that PARTITIONing could obviate the need for OPTIMIZE.
* Another system had ~3000 tables; one needed OPTIMIZE.
* Among many other systems I have studied, there may have been one more OPTIMIZE.