Experimenting with 5.6 InnoDB Online DDL (bugs included)

MySQL 5.6 offers the groundbreaking online DDL operations for InnoDB. Most common use cases will enjoy this feature, and the need for online alter table scripts will decrease. This is a killer feature!

I’ve put this new feature to the usability test. How did it go? Not too well, I’m afraid.

[Updates to this text inline], also see this followup.

sakila & DDL

sakila is still a very useful database. I say “still” because it is not very large, and computing power is getting stronger; yet on my laptop some operations can still take many seconds to complete, which is just fine for my tests.

Sakila tables are mostly InnoDB, and rental being the largest, I do:

node1 (sakila) > alter table sakila.rental engine=InnoDB;
Query OK, 16044 rows affected (6.94 sec)
Records: 16044  Duplicates: 0  Warnings: 0

So what can be executed during these 6.94 seconds? In a second terminal, I try the following:

Meta

node1 (sakila) > show create table sakila.rental\G
*************************** 1. row ***************************
       Table: rental
Create Table: CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (1.08 sec)

1.08 seconds for SHOW CREATE TABLE. Consider: up till 5.5 you can’t run SHOW CREATE TABLE while an ALTER was running on that table.

Read

While ALTER TABLE runs, I execute:

node1 (sakila) > select min(rental_date), max(return_date) from sakila.rental;
+---------------------+---------------------+
| min(rental_date)    | max(return_date)    |
+---------------------+---------------------+
| 2005-05-24 22:53:30 | 2005-09-02 02:35:22 |
+---------------------+---------------------+
1 row in set (2.77 sec)

So 2.77 seconds for a query which uses a full table scan to return. I’m not measuring performance here; am satisfies that query did actually succeed even while table was being altered.

Read & bug

But, unfortunately, being the type of geek who likes to make trouble, I am also able to consistently fail the ALTER TABLE. Hang it, actually:

See session #1:

node1 (sakila) > alter table sakila.rental engine=innodb; 

... (waiting forever)

And session #2:

node1 (sakila) > show processlist;
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
| Id | User     | Host      | db     | Command | Time | State                           | Info                                    |
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+
|  6 | msandbox | localhost | sakila | Query   |  219 | Waiting for table metadata lock | alter table sakila.rental engine=innodb |
|  4 | msandbox | localhost | sakila | Query   |    0 | init                            | show processlist                        |
+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+

Read all about it in bug report #67286 .

Write: not so simple

The following UPDATE query hangs till the ALTER process is over:

node1 (sakila) > update sakila.rental set return_date=now() where rental_id = floor(rand()*100);
Query OK, 3 rows affected, 1 warning (6.10 sec)

No online DDL for writes?

Was I unfair? Is “ENGINE=InnoDB” really an online DDL operation? OK, let’s try with:

alter table sakila.rental row_format=compact;

Which is documented as one of the supported online DDL operations. Same.

The manual says I can define the ALGORITHM and the LOCK properties for the ALTER TABLE operation. But is gives no example, so I try my own:

node1 (sakila) > alter table sakila.rental row_format=compact ALGORITHM=INPLACE LOCK=NONE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALGORITHM=INPLACE LOCK=NONE' at line 1

Ummm…. then maybe:

node1 (sakila) > alter table sakila.rental ALGORITHM=INPLACE LOCK=NONE row_format=compact;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCK=NONE row_format=compact' at line 1

OK, how about:

node1 (sakila) > alter table sakila.rental ALGORITHM=INPLACE row_format=compact LOCK=NONE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row_format=compact LOCK=NONE' at line 1

Reading, rereading, re-verifying the manual — I am typing a valid statement! What’s wrong here?

Yes, I’m on 5.6.7-rc-log. No, I can’t find, in 5.6 documentation and slides from MySQL connect, any code sample that actually uses ALGORITHM and LOCK (!?)

[UPDATE], as Marc Alff point out, I did in fact use the wrong syntax, and was missing commas. The right syntax is:

node1 (sakila) > alter table sakila.rental row_format=compact, algorithm=inplace, lock=none;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'alter table sakila.rental row_format=compact, algorithm=inplace, lock=none'

Unfortunately this still results with an error. Another attempt shows that:

node1 (sakila) > alter table sakila.rental row_format=compact, algorithm=inplace, lock=shared;
Query OK, 0 rows affected (11.08 sec)

works well. So, apparently, you can only run this type of ALTER TABLE a with a SHARED lock. The bad news?

node1 (sakila) > alter table sakila.rental add index(return_date), algorithm=inplace, lock=none;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'alter table sakila.rental add index(return_date), algorithm=inplace, lock=none'
node1 (sakila) > alter table sakila.rental add column c char, algorithm=inplace, lock=none;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'alter table sakila.rental add column c char, algorithm=inplace, lock=none'

So I’m not sure as yet what kind of DDL operations are available with LOCK=NONE.

Conclusion

Little success with online DDL. SHARED-only is many times as good as completely blocked.

My personal conclusion is (and I do take into account 5.6 is RC at this time, not GA): not there yet! Stick to openark-kit, Percona-toolkit or Facebook OSC for some time. They all provide with online-alter-table operations via external scripts.