Skip to content

# Experimenting with 5.6 InnoDB Online DDL (bugs included)

MySQL InnoDB New Features openark kit Opinions scripts October 18, 2012October 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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
8
Leave a Reply

7 Comment threads
1 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
4 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Marc Alff

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.

Author
shlomi

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

Author
shlomi

@Marc,
Thanks again, please see updates within text

Guest
Simon J Mudd

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… Read more »

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

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

Guest
hickey.liu@gmail.com

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.

MySQL-5.6 online DDL特性 ← hickey

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

Log Buffer #291, A Carnival of the Vanities for DBAs

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

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.