{"id":5823,"date":"2012-11-20T11:49:14","date_gmt":"2012-11-20T09:49:14","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5823"},"modified":"2012-11-20T13:39:03","modified_gmt":"2012-11-20T11:39:03","slug":"state-of-inndb-online-ddl-in-mysql-5-6-8-rc","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/state-of-inndb-online-ddl-in-mysql-5-6-8-rc","title":{"rendered":"State of InnDB Online DDL in MySQL 5.6.8-RC"},"content":{"rendered":"

5.6.8-rc<\/strong> is out, and so I’m following up on InnoDB’s online DDL new feature: the ability to SELECT, INSERT, DELETE, UPDATE a table even while an ALTER TABLE is executing on same table.<\/p>\n

The brief summary<\/h4>\n

Not as advertised; many things can’t be done.<\/p>\n

The longer review<\/h4>\n

I’m using 5.6.8-rc 64bit<\/strong> binary distribution for Linux, installed via mysqlsandbox<\/a>. My hardware is irrelevant, but the fact I’m testing on my laptop assists me in that ALTER TABLE<\/strong> operations take a while, so that I’m able to easily type commands in two terminals and have the time to watch them being executed. Query cache is disabled.<\/p>\n

I’m using the sakila sample database, and in particular I’m working with the rental table. Here’s the table definition:<\/p>\n

\n
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` datetime DEFAULT NULL,\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_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_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_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE\r\n) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8<\/pre>\n<\/blockquote>\n
Highlights for the table: AUTO_INCREMENT PRIMARY KEY<\/strong>, some columns indexed, some not, and Foreign Keys in place. Pretty much a standard table. It contains 16,044<\/strong> rows. Row format is COMPACT<\/strong>.<\/p>\n
What I want to know is: which DDL commands allow for which online DML commands?<\/p>\n
So, on terminal #1 I will issue queries like:<\/p>\n
\n
node1 5.6.8-rc-log sakila> alter table sakila.rental<\/strong> ROW_FORMAT=COMPACT \/* or whatever *\/<\/strong>;\r\nQuery OK, 0 rows affected (10.57 sec)\r\nRecords: 0\u00a0 Duplicates: 0\u00a0 Warnings: 0<\/pre>\n<\/blockquote>\n
And during the above operation, I will execute the following on terminal #2:<\/p>\n
    \n
  1. select max(rental_id) from sakila.rental;<\/strong> this queries the AUTO_INCREMENT value, which is of course a PRIMARY KEY<\/li>\n
  2. select min(rental_date) from sakila.rental<\/strong>; there is an index on rental_date, and normal execution plan is to optimize table away and just use the index<\/li>\n
  3. select min(return_date) from sakila.rental<\/strong>; there is no index on return_date, and full table scan is required<\/li>\n
  4. update rental set return_date = return_date + interval 1 second where rental_id=3<\/strong>; the UPDATE uses the PRIMARY KEY<\/li>\n
  5. update rental set return_date = return_date + interval 1 second where return_date = NOW()<\/strong>; won’t actually affect anything, but requires full scan.<\/li>\n<\/ol>\n
    So here are the results:<\/p>\n
    \n
    +-------------------------------------------------------------+-------+---------------------------+---------------+---------------------+-------------------------+--------------+---------------------+\r\n| ALTER statement\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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Time\u00a0 | General comments\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | select max PK | select min by index | select min by full scan | update by PK | update by full scan |\r\n+-------------------------------------------------------------+-------+---------------------------+---------------+---------------------+-------------------------+--------------+---------------------+\r\n| ROW_FORMAT=COMPACT\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\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 10.92 |\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 | Instant<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Instant<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Instant<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| AUTO_INCREMENT=16051\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\u00a0\u00a0\u00a0\u00a0 |\u00a0 0.06 | Instant, no table rebuild | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ADD INDEX(last_update)\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\u00a0\u00a0 |\u00a0 2.37 |\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 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ADD INDEX(last_update), ALGORITHM=INPLACE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 1.83 |\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 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ADD INDEX(last_update), ALGORITHM=INPLACE, LOCK=NONE\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 0.00 | ERROR 1235 (42000): ... \u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ADD COLUMN c CHAR(1) NOT NULL\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 | 11.20 |\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 | Instant<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Instant<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Instant<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0 | blocked<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| ADD COLUMN c CHAR(1) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE |\u00a0 0.00 | ERROR 1235 (42000): .   \u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | N\/A\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------------------------------------------------------+-------+---------------------------+---------------+---------------------+-------------------------+--------------+---------------------+<\/pre>\n<\/blockquote>\n
    Rather surprising, I would say.<\/p>\n