Hot Column Addition and Deletion Part I – Performance

Hot Column Addition and Deletion Part I – Performance

PREVIOUS POST
NEXT POST

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

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:

InnoDB:

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.

We’re psyched about being able to provide this feature to our users. In the next post, we’ll take a look at how TokuDB is able to achieve HCAD. Finally, we’ll present a how-to on getting the most out of HCAD.

Click here for Part II.

PREVIOUS POST
NEXT POST

Share this post

Comments (6)

  • Justin Reply

    Curious as to why one reports 0 rows and the other reports 122mil? Obviously at 3 seconds something is happening. The TokuDB engine is a captivating thing to watch evolve. Hopefully I’ll get to experimenting with it in a meaningful manner soon 🙂 Keep up the good work!

    March 30, 2011 at 10:30 pm
  • Martin Farach-Colton Reply

    @Justin,

    Thanks for your kinds words.

    The number of rows affected is an artifact of the MySQL storage engine API. In both cases, all rows are affected. Please check out our upcoming post on our HCAD implementation for more details.

    March 31, 2011 at 12:45 pm
  • Hot Indexing Part I: New Feature | Tokutek Reply

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

    April 5, 2011 at 4:11 pm
  • Hot Column Addition and Deletion Part II: How it works | Tokutek Reply

    […] the previous HCAD post, I described HCAD and showed that it can reduce the downtime of column addition (or deletion) from […]

    April 7, 2011 at 12:31 pm
  • OldSQL Tricks or NewSQL Treats | Tokutek Reply

    […] Don’t get me wrong. InnoDB is a great piece of software. In my own testing, its B-tree implementation outperforms the big enterprise implementations, though it lacks some features like hot indexing and hot column addition and deletion. […]

    April 8, 2011 at 1:27 pm
  • TokuDB 推出6.1.0 | SIDE-ALICE Reply

    […] Yes (secs to mins) [more] […]

    July 18, 2012 at 6:35 pm

Leave a Reply