Experimenting with 5.6 InnoDB Online DDL (bugs included)

October 18, 2012

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.

tags: , , , ,
posted in MySQL by shlomi

« | »

Follow comments via the RSS Feed | Leave a comment | Trackback URL

8 Comments to "Experimenting with 5.6 InnoDB Online DDL (bugs included)"

  1. Marc Alff wrote:

    The MySQL 5.6 manual, in:
    http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

    states that the syntax is:

    ALTER [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

    That is, the list of "[alter_specification]" is separated by commas.

    See comments in bug#65466.

  2. shlomi wrote:

    Marc, Savior of the day! I'm too accustomed to SQL syntax.
    Will review and update text.

  3. shlomi wrote:

    @Marc,
    Thanks again, please see updates within text

  4. Simon J Mudd wrote:

    For many of these commands there are too few real life, more complex examples provided.
    I spent a long time trying to figure out why a table I was adding a new column to couldn't be partitioned at the same time. No it can't and unless you really stare (or a nice soul puts you out of your misery and confirms you can't do this) you just don't see this type of thing.

    So I think that complex commands like ALTER TABLE should have a good long list of typical use cases (perhaps with comments) on how to achieve different actions which are possible.

    Oh and yes, we are _all_ waiting for pt-online-schema equivalent behaviour inside the database and when as a DBA you're bound to not let replication drift over a few seconds, you won't be sweating when the next 30 GB table has to be altered..

  5. InnoDB DDL: kudos to quick responders on bugs.mysql.com | code.openark.org wrote:

    [...] Experimenting with 5.6 InnoDB Online DDL (bugs included) [...]

  6. hickey.liu@gmail.com wrote:

    I tested on my environment, "algorithm=inplace, lock=none" works for adding a column:

    root@xiyu 08:28:06>ALTER TABLE sbtest1 ADD COLUMN book char(60) NOT NULL DEFAULT '', algorithm=inplace, lock=none;

    Oracle should release more notes on the advantages for the new added "algorithm" and "lock" options for DDL, puzzled me still.

  7. MySQL-5.6 online DDL特性 ← hickey wrote:

    [...] 2. DDL中新加的lock/algorithm选项还没研究明白性能到底如何提升,在哪些情况下如何使用。 [...]

  8. Log Buffer #291, A Carnival of the Vanities for DBAs wrote:

    [...] 5.6 offers the groundbreaking online DDL operations for InnoDB. Shlomi Noach [...]

Leave Your Comment

 

 
Powered by Wordpress and MySQL. Theme by openark.org