{"id":5673,"date":"2012-10-18T14:41:46","date_gmt":"2012-10-18T12:41:46","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5673"},"modified":"2012-10-18T18:56:08","modified_gmt":"2012-10-18T16:56:08","slug":"experimenting-with-5-6-innodb-online-ddl-bugs-included","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/experimenting-with-5-6-innodb-online-ddl-bugs-included","title":{"rendered":"Experimenting with 5.6 InnoDB Online DDL (bugs included)"},"content":{"rendered":"

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

I’ve put this new feature to the usability test. How did it go? Not too well, I’m afraid.<\/p>\n

[Updates to this text inline], also see this followup<\/a>.<\/p>\n

sakila & DDL<\/h4>\n

sakila<\/a> 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.<\/p>\n

Sakila tables are mostly InnoDB, and rental being the largest, I do:<\/p>\n

\n
node1 (sakila) > alter table sakila.rental engine=InnoDB;<\/strong>\r\nQuery OK, 16044 rows affected (6.94<\/strong> sec)\r\nRecords: 16044\u00a0 Duplicates: 0\u00a0 Warnings: 0<\/pre>\n<\/blockquote>\n
So what can be executed during these 6.94<\/strong> seconds? In a second terminal, I try the following:<\/p>\n
Meta<\/h4>\n
\n
node1 (sakila) > show create table sakila.rental\\G\r\n*************************** 1. row ***************************\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Table: rental\r\nCreate Table: CREATE TABLE `rental` (\r\n\u00a0 `rental_id` int(11) NOT NULL AUTO_INCREMENT,\r\n\u00a0 `rental_date` datetime NOT NULL,\r\n\u00a0 `inventory_id` mediumint(8) unsigned NOT NULL,\r\n\u00a0 `customer_id` smallint(5) unsigned NOT NULL,\r\n\u00a0 `return_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n\u00a0 `staff_id` tinyint(3) unsigned NOT NULL,\r\n\u00a0 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\r\n\u00a0 PRIMARY KEY (`rental_id`),\r\n\u00a0 UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),\r\n\u00a0 KEY `idx_fk_inventory_id` (`inventory_id`),\r\n\u00a0 KEY `idx_fk_customer_id` (`customer_id`),\r\n\u00a0 KEY `idx_fk_staff_id` (`staff_id`),\r\n\u00a0 CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,\r\n\u00a0 CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,\r\n\u00a0 CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE\r\n) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8\r\n1 row in set (1.08 sec<\/strong>)<\/pre>\n<\/blockquote>\n
1.08<\/strong> seconds for SHOW CREATE TABLE<\/strong>. Consider: up till 5.5<\/strong> you can’t run SHOW CREATE TABLE<\/strong> while an ALTER<\/strong> was running on that table.<\/p>\n
Read<\/h4>\n
While ALTER TABLE runs, I execute:<\/p>\n
\n
node1 (sakila) > select min(rental_date), max(return_date) from sakila.rental;\r\n+---------------------+---------------------+\r\n| min(rental_date)\u00a0\u00a0\u00a0 | max(return_date)\u00a0\u00a0\u00a0 |\r\n+---------------------+---------------------+\r\n| 2005-05-24 22:53:30 | 2005-09-02 02:35:22 |\r\n+---------------------+---------------------+\r\n1 row in set (2.77 sec)<\/pre>\n<\/blockquote>\n
So 2.77<\/strong> 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.<\/p>\n
Read & bug<\/h4>\n
But, unfortunately, being the type of geek who likes to make trouble, I am also able to consistently fail the ALTER TABLE<\/strong>. Hang it, actually:<\/p>\n
See session #1<\/strong>:<\/p>\n
\n
node1 (sakila) > alter table sakila.rental engine=innodb; \r\n\r\n... (waiting forever)<\/pre>\n<\/blockquote>\n
And session #2<\/strong>:<\/p>\n
\n
node1 (sakila) > show processlist;\r\n+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+\r\n| Id | User\u00a0\u00a0\u00a0\u00a0 | Host\u00a0\u00a0\u00a0\u00a0\u00a0 | db\u00a0\u00a0\u00a0\u00a0 | Command | Time | State\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Info\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+\r\n|\u00a0 6 | msandbox | localhost | sakila | Query\u00a0\u00a0 |\u00a0 219<\/strong> | Waiting for table metadata lock<\/strong> | alter table sakila.rental engine=innodb<\/strong> |\r\n|\u00a0 4 | msandbox | localhost | sakila | Query\u00a0\u00a0 |\u00a0\u00a0\u00a0 0 | init\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | show processlist\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+----------+-----------+--------+---------+------+---------------------------------+-----------------------------------------+<\/pre>\n<\/blockquote>\n
Read all about it in bug report #67286<\/a> .<\/p>\n
Write: not so simple<\/h4>\n
The following UPDATE<\/strong> query hangs till the ALTER<\/strong> process is over:<\/p>\n
\n
node1 (sakila) > update sakila.rental set return_date=now() where rental_id = floor(rand()*100);\r\nQuery OK, 3 rows affected, 1 warning (6.10 sec)<\/pre>\n<\/blockquote>\n
No online DDL for writes?<\/p>\n
Was I unfair? Is “ENGINE=InnoDB” really an online DDL operation? OK, let’s try with:<\/p>\n
\n
alter table sakila.rental row_format=compact<\/strong>;<\/pre>\n<\/blockquote>\n
Which is documented as one of the supported online DDL operations. Same.<\/p>\n
The manual<\/a> says I can define the ALGORITHM<\/strong> and the LOCK<\/strong> properties for the ALTER TABLE<\/strong> operation. But is gives no example, so I try my own:<\/p>\n
\n
node1 (sakila) > alter table sakila.rental row_format=compact ALGORITHM=INPLACE LOCK=NONE<\/strong>;\r\nERROR 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<\/pre>\n<\/blockquote>\n
Ummm…. then maybe:<\/p>\n
\n
node1 (sakila) > alter table sakila.rental ALGORITHM=INPLACE LOCK=NONE<\/strong> row_format=compact;\r\nERROR 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<\/pre>\n<\/blockquote>\n
OK, how about:<\/p>\n
\n
node1 (sakila) > alter table sakila.rental ALGORITHM=INPLACE<\/strong> row_format=compact LOCK=NONE<\/strong>;\r\nERROR 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<\/pre>\n<\/blockquote>\n
Reading, rereading, re-verifying the manual<\/a> — I am typing a valid statement! What’s wrong here?<\/p>\n
Yes, I’m on 5.6.7-rc-log<\/strong>. No, I can’t find, in 5.6<\/strong> documentation and slides from MySQL connect<\/a>, any code sample that actually uses ALGORITHM<\/strong> and LOCK<\/strong> (!?)<\/p>\n
[UPDATE]<\/strong>, as Marc Alff point out, I did in fact use the wrong syntax, and was missing commas. The right syntax is:<\/p>\n
\n
node1 (sakila) > alter table sakila.rental row_format=compact, algorithm=inplace, lock=none;<\/strong>\r\nERROR 1235 (42000): This version of MySQL doesn't yet support 'alter table sakila.rental row_format=compact, algorithm=inplace, lock=none'<\/pre>\n<\/blockquote>\n
Unfortunately this still results with an error. Another attempt shows that:<\/p>\n
\n
node1 (sakila) > alter table sakila.rental row_format=compact, algorithm=inplace, lock=shared;\r\nQuery OK, 0 rows affected (11.08 sec)<\/pre>\n<\/blockquote>\n
works well. So, apparently, you can only run this type<\/em> of ALTER TABLE<\/strong> a with a SHARED<\/strong> lock. The bad news?<\/p>\n
\n
node1 (sakila) > alter table sakila.rental add index(return_date)<\/strong>, algorithm=inplace, lock=none<\/strong>;\r\nERROR 1235 (42000): This version of MySQL doesn't yet support 'alter table sakila.rental add index(return_date), algorithm=inplace, lock=none'\r\nnode1 (sakila) > alter table sakila.rental add column c char<\/strong>, algorithm=inplace, lock=none<\/strong>;\r\nERROR 1235 (42000): This version of MySQL doesn't yet support 'alter table sakila.rental add column c char, algorithm=inplace, lock=none'<\/pre>\n<\/blockquote>\n
So I’m not sure as yet what kind of DDL operations are available with LOCK=NONE<\/strong>.<\/p>\n
Conclusion<\/h4>\n
Little success with online DDL. SHARED-only is many times as good as completely blocked.<\/p>\n
My personal conclusion is (and I do take into account 5.6<\/strong> is RC at this time, not GA): not there yet!<\/em> Stick to openark-kit<\/a>, Percona-toolkit<\/a> or Facebook OSC<\/a> for some time. They all provide with online-alter-table operations via external scripts.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[14,89,34,51,50],"class_list":["post-5673","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-innodb","tag-new-features","tag-openark-kit","tag-opinions","tag-scripts"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1tv","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5673","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5673"}],"version-history":[{"count":23,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5673\/revisions"}],"predecessor-version":[{"id":5694,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5673\/revisions\/5694"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5673"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5673"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5673"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}