Hot Indexing Part I: New Feature

Posted On April 5, 2011 | By Martin Farach-Colton | 3 comments

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?

mysql> create index example_idx on example_tbl (example_field);

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.

3 thoughts

  1. Alex says:

    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!

  2. […] implementation outperforms the big enterprise implementations, though it lacks some features like hot indexing and hot column addition and […]

  3. […] 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

Your email address will not be published. Required fields are marked *