EmergencyEMERGENCY? Get 24/7 Help Now!

Hot Table Optimization with MySQL

 | June 28, 2012 |  Posted In: Tokutek, TokuView


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.




Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.