Hot Table Optimization with MySQL

Table optimization is a necessary evil; tables sometimes need to be optimized to reclaim space or to improve query performance.  Unfortunately, MySQL blocks writes to a table while it is being optimized.  Because optimization time is proportional to the table size, writes can be blocked for a long time.  Fractal Tree indexes support online optimization; however, the MySQL metadata lock gets in the way of writing while optimizing.  We will describe a simple patch to MySQL that enables online optimization of TokuDB tables.

Why do tables need to be optimized?  Here are some reasons.

• Insertions with random keys can result in a tree with underutilized leaf blocks.  Many tree algorithms split nodes in half when they become full.  If these nodes are stored in fixed sized blocks, like many B-trees do, then there can be a lot of wasted space.  Table optimization of B-trees write blocks with less fragmentation.  In contrast, Fractal Tree indexes do not have this problem since we use variable sized blocks.
• As B-tree’s age, the leaf nodes that are adjacent in the key sort order are spread all over the disk.  As a result, range queries spend a lot of time waiting on disk seeks.  Table optimization builds a new tree with leaf blocks written in sort order.  In contrast, Fractal Tree indexes do not have this problem since we write very large blocks.
• TokuDB’s Fractal Tree indexes may need to be optimized to take advantage of all the latest and greatest algorithms delivered in newer versions of the software.  For example, basement nodes were recently introduced into the TokuDB product.  Table optimization converts blocks from the old format to the new format, which results in much better performance.

To keep this operation hot, we added a storage engine flag that states that the storage engine can optimize a table with concurrent reads and writes.  If this flag is set for the optimize operation, the table’s metadata lock is downgraded to a level that allows concurrent reads and writes to the table.  Since the TokuDB storage engine implements this flag, TokuDB tables can be read and written while optimize is running. A change to enable hot table optimization for TokuDB (and we assume other storage engines), can be found in this patch, which we think could be useful for both MySQL v5.5 as well as the upcoming MySQL v5.6.