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