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