EmergencyEMERGENCY? Get 24/7 Help Now!

Hot Indexing Part I: New Feature

 | April 5, 2011 |  Posted In: Tokutek, TokuView

PREVIOUS POST
NEXT POST

From 31 minutes to 2 seconds

Hot Indexing Overview

TokuDB v5.0 introduces several features that are new to the MySQL world. Recently, we posted on HCAD: Hot Column addition and Deletion. In this post, we talk about Hot Indexing.

What happens when you try to add a new index, as follows?

In standard MySQL 5.1 InnoDB, the table example_tbl gets locked while all indexes, including the primary key, get rebuilt. In the InnoDB plugin for 5.1, as well as in previous releases of TokuDB, things are improved in that the table is only locked while the one index is built. This still however can easily cause hours of downtime.

TokuDB v5.0 introduces Hot Indexing. You can add an index to an existing table with minimal downtime. The total downtime is seconds to a few minutes, because when the index is finished being built, MySQL closes and reopens the table. This means that (unlike HCAD) the downtime is not at the time of the command, but later on. Still, it is quite minimal, as the following experiment shows. The details of the table are here.

Adding index (Year, Month, DayofWeek) took 31 minutes, 34 seconds for the InnoDB 5.1 plugin, during which the table was locked for insertions/deletions/updates.

TokuDB 5.0 took 9 minutes, 30 seconds to add the same index. At the end of this time, the table was locked for under 2 seconds (we polled the database at 1 second intervals, and it was only locked at one of these test points).

The v5.0 release is fun for me. I get to blog about great features that bring great value to people trying to run big data in MySQL. Stay tuned for the details of how we do hot indexing.

Learning More

  1. I had the privilege of sitting down for the MySQL Community Podcast with Sheeri Cabral and Sarah Novotny where we spoke about the new TokuDB 5.0 features in depth. See Episodes 39 and 40.
  2. Try TokuDB v5.0 for yourself.
PREVIOUS POST
NEXT POST

3 Comments

  • Absolutely amazing work. The only thing missing in my opinion for TokuDB to be the best MySQL engine implementation is a hot backup solution.

    Great job!

  • […] TokuDB v5.0 introduced Hot Column Addition (HCAD). You can add or delete columns from an existing table with minimal downtime — just the time for MySQL itself to close and reopen the table. The total downtime is seconds to minutes. We detailed an experiment that showed this in this blog. TokuDB v5.0 also introduced Hot Indexing. You can add an index to an existing table with minimal downtime. The total downtime is seconds to a few minutes, because when the index is finished being built, MySQL closes and reopens the table. This means that the downtime occurs not when the command is issued, but later on. Still, it is quite minimal, as we showed in this blog. […]

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.