State of InnDB Online DDL in MySQL 5.6.9-RC (good news included)

December 18, 2012

5.6.9-RC is out, and I was curious to see how the online DDL has improved since my 5.6.8 review. I also owe James Day this review, since he came up with results inconsistent with my own.

We both agreed the dataset I was using was too small, but I got similar results even on larger scale. Then some time passed, and 5.6.9 was announced.

So for the 5.6.9 test I took one of my real tables on production. It is not extremely large: it's a ~ 300MB .ibd file, in the following format:

mysql> show create table tbl \G

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `a` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `w` smallint(11) NOT NULL DEFAULT '0',
  `icount` smallint(5) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `u_idx` (`a`,`w`,`d`) KEY_BLOCK_SIZE=8,
  KEY `d` (`d`) KEY_BLOCK_SIZE=8

Got some 2.5M rows in the table; desktop machine, 64 bit Linux, mysqlsandbox.

I have crossed several DDL statements with several DML statements. The DDL statements in this test are (ALTER TABLE...):

  • AUTO_INCREMENT=16960441
  • ADD INDEX (w)

The DML statements are:

  1. select max(id) from test.tbl; -- this queries the AUTO_INCREMENT value, which is of course a PRIMARY KEY
  2. select min(d) from test.tbl; -- there is an index on d, and normal execution plan is to optimize table away and just use the index
  3. select min(icount) from test.tbl; -- there is no index on icount, and full table scan is required
  4. update test.tbl set d = d + interval 1 second where id = 8057370; -- the UPDATE uses the PRIMARY KEY
  5. update test.tbl set d = d + interval 1 second where icount = 200; -- will affect 4 rows, but requires full scan.

The results?

ALTER TABLE... Time (sec) General comments select max PK select min by index select min by full scan update by PK update by full scan
ROW_FORMAT=COMPACT 183 online online online online online
AUTO_INCREMENT=16960441 0.24 [Instant operation] n/a n/a n/a n/a n/a
ADD INDEX (w) 21 online online online online online
DROP INDEX w 0.1 [Instant operation] n/a n/a n/a n/a n/a
ADD COLUMN c CHAR(1) NOT NULL 103 online online online online online
DROP COLUMN c 110 online online online online online


  • All operations were online: operations did not wait for ALTER to complete.
  • I executed all operations multiple times during each ALTER.
  • In addition, I executed operations from another client.
  • Some operations were fast, others sometimes took as long as 7.34 seconds to complete. This is no small matter: the time it took for each DML was indeterministic, and longer than what it would usually take it. That's perfectly understandable. Just note that some operations took exceedingly long time to complete. My understanding is that the ALTER operations happens in chunks. DML statements are allowed in between these chunks. This is the reason why on smaller tables there didn't seem to be any "online" statement: the chunks were just too large in relation to table size. And so, and this is still my own understanding, your query may get lucky or unlucky depending on the exact moment it has been issued.
  • I did not try it with FOREIGN KEYs. I previously concluded that foreign keys were a no-go for online DDL. I'm not sure if this is still the case. Another time for this test - but it must take place.


Still RC - but for the first time the online DDL seem to deliver what's promised. I'm very happy to see this.

I am yet to understand how the ALTER works via replication. With single threaded replication I would assume it's back to "wait till I'm done" on the slave, in which case the "online" term is not there yet. Even on multi-threaded replication DML on same schema would hang. I'm happy to be corrected on this by an authority.

My predicament is that oak-online-alter-table or pt-online-schema-change are here to stay for the next couple of years at least. Some operations, like partitioning, are not supported by current online InnoDB DDL. Also, these scripts allow you some control over the speed at which the ALTER process works, allowing for pre-defined sleep time in between chunks, so as to let the server - and its slaves - recover their breath.

Nonetheless, big kudos for the InnoDB team at Oracle for pulling this one out!

  • Hi Shlomi! Thanks for testing this. Speaking from the perspective of Tungsten (the other parallel replication) it is correct that the ALTER TABLE will be serialized. It's dangerous to execute DDL statements out of order automatically. Assuming this is a rare operation another approach is to execute directly on each affected server using an unlogged command so that it does not even go into the binlog.

  • Hi Robert,
    Yes, I suppose that would make sense best.

  • Rolf

    Unfortunately, PK are not an online schema change. I wonder if this will be fixed by .11? Never the less I am very impressed with all the efforts from Oracle and others!

  • Rolf

    Also, ALTER PK statements now take place in @@tmpdir. That could require a rearchitecture for many people.

Powered by Wordpress and MySQL. Theme by