Does it makes sense to run "optimize table" when the mysql database is stored on SSD?

Question

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).

Answer 1

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.

Answer 2

There is something else to consider when using OPTIMIZE TABLE

What Storage Engine are you using, MyISAM or InnoDB ???

If you are using MyISAM, you can indeed recover a significant amount of disk space if a MyISAM table exeriences a lot of INSERTs, UPDATEs,and DELETEs (IUDs). Don't forget OPTIMIZE TABLE internally calls ANALYZE TABLE to update index row statistics. Index row statistics can be thrown off a great deal in a heavy IUD environment and can impede the MySQL Query Optimizer from making good choices for the EXPLAIN plans of queries. If the MyISAM table experience very low UPDATEs and DELETEs, OPTIMIZE TABLE is unncessary. If the MyISAM table experiences no DELETEs but a lot of INSERTs and UPDATEs, OPTIMZIE TABLE very sparingly (once a year) but run ANALYZE TABLE at discreet periods (perhaps once a month).

If we are talking InnoDB, let's talk about your InnoDB settings. If you have innodb_file_per_table enabled, then OPTIMIZE TABLE is all, well, and good. However, ANALYZE TABLE has no effect on InnoDB since index statistics are calculated on-the-fly through doing dives into the BTREE indexes for cardinality approximations. Please expect InnoDB .ibd files to grow faster that its MyISAM counterparts because both data and index pages resides in the same .ibd file, whereas MyISAM data and index lives in separates files (.MYD and .MYI). Thus, InnoDB will need more frequent OPTIMIZE TABLE operations that MyISAM.

If you have innodb_file_per_table disabled, you must rearchitect the InnoDB infrastructure before you can successfully run OPTIMIZE TABLE commands against InnoDB tables.