Mysql – Does it makes sense to run “optimize table” when the thesql database is stored on SSD

MySQLoptimizationssd

The manual says about "optimize table":

"Deleted rows are maintained in a
linked list and subsequent INSERT
operations reuse old row positions.
You can use OPTIMIZE TABLE to reclaim
the unused space and to defragment the
data file."

So I'm guessing there will be some performance gain, even if the storage medium is SSD (as the linked list of deleted rows will be eliminated). OTOH (just guessing again) the performance gain will not be as significant as for an HDD, which would also benefit from faster sequential reading after running optimize.

So does it worth running optimize in this situation? Will the performance gain be significant enough to outweigh the decrease in SSD life expectancy (due to unnecessary rearranging the stored data)?. I'm talking about tables that are otherwise perfect candidate for optimize (having variable-length rows with frequent updates).

Best Answer

You often will see SSD performance, even read performance, to be limited by the amount of the I/O operations per second maximum of your drive(s). Take the Intel X-25 E for instance - top read speeds of up to 250 MB/s but also a maximum of 35,000 read operations per second which results a top transfer rate of 140 MB/s for 4 KB blocks.

If your data is known to be non-contiguous and your typical reads would incur serial I/O load (i.e. you would see large request sizes in a non-fragmented database for your typical load), performance will benefit from running OPTIMIZE TABLE.

By far the easiest method of getting to know that is simply checking it out - run an OPTIMIZE TABLE command for your most-used tables, get your typical load read/write on it and check the average request size (avgrq-sz) using iostat -x /dev/<device>. If avgrq-sz is high ( > 32 sectors), you did benefit from the OPTIMIZE. If not, there is probably no need to bother in the future as your access pattern is rather random anyway.

Related Topic