TokuDB : Hot Index Creation

Hot Index Creation

TokuDB allows you to add indexes to an existing table and still perform inserts and queries on that table while the index is being created.

The ONLINE keyword is not used. Instead, the value of the tokudb_create_index_online client session variable is examined. More information is available in TokuDB Variables.
Hot index creation is invoked using the CREATE INDEX command after setting
tokudb_create_index_online=on.

Here's an example:

SET tokudb_create_index_online=ON;
Query OK, 0 rows affected (0.00 sec)

CREATE INDEX index table (field_name);

Alternatively, using the ALTER TABLE command for creating an index will create the index offline (with the table unavailable for inserts or queries), regardless of the value of tokudb_create_index_online. The only way to hot create an index is to use the CREATE INDEX command.

Hot creating an index will be slower than creating the index offline, and progress depends how busy the mysqld server is with other tasks. Progress of the index creation can be seen by using the SHOW PROCESSLIST command (in another client). Once the index creation completes, the new index will be used in future query plans.

If more than one hot CREATE INDEX is issued for a particular table, the indexes will be created serially. An index creation that is waiting for another to complete will be shown as Locked in SHOW PROCESSLIST. We recommend that each CREATE INDEX be allowed to complete before the next one is started.

TokuDB : Hot Column Addition and Deletion

Hot Column Addition and Deletion

From 18 hours to 3 seconds!

Hot Column Addition and Deletion (HCAD) Overview

TokuDB v5.0 introduces several features that are new to the MySQL world. In this series of posts, we’re going to present some information on these features: what’s the feature, how does it work under the hood, and how do you get the most out of this feature in your MySQL setup.

Today we start with HCAD: Hot Column Addition and Deletion. Many users have had the experience of loading a bunch of data into a table and associated indexes, only to find that adding some columns or removing them would be useful.

alter table X add column Y int default 0;

or the like takes a long time — hours or more — during which time the table is write locked, meaning no insertions/deletions/updates and no queries on the new column until the alter table is done.
Mark Callaghan points out that changing the row format in InnoDB is a “significant project”, so it looked like slow alter tables were going to be a challenge for MySQL for the foreseeable future. Slow alter tables is a reason for the inability of MySQL to scale to large tables.

TokuDB v5.0 changes all that with the introduction of 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.

Here we present an example of HCAD in action. See this page for details of the experiment. Drum roll…
TokuDB:

mysql> alter table ontime add column totalTime int default 0;
Query OK, 0 rows affected (3.33 sec)

InnoDB:

mysql> alter table ontime add column totalTime int default 0;
Query OK, 122225386 rows affected (17 hours 44 min 40.85 sec)

That’s 19,000x faster! Goodbye long downtimes.

As a note, the “0 rows affected” for TokuDB means that the column addition work happens in the background. All queries on the table, however, will see the new column as soon as the alter table returns, in this case after 3.33 sec.