From 31 minutes to 2 seconds
Hot Indexing Overview
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.