MySQL 5.6: online DDL for busy tables

Online DDL for InnoDB is another great feature new in MySQL 5.6. I don't think it is ready to replace online schema change frameworks (from Facebook, Percona Toolkit and Openark) for workloads that require minimal downtime for busy tables - but work can be done on InnoDB to change my opinion.

InnoDB supports online DDL by logging changes to the altered table when the alter is in progress. The maximum size of the log is determined by the my.cnf variable innodb_online_alter_log_max_size. The error DB_ONLINE_LOG_TOO_BIG is raised if the log gets full.

Online DDL for InnoDB blocks all concurrent inserts/updates/deletes while the log is being applied during online add index. I don't think this is documented and it should be. This can block concurrent changes for too long. I hope the code can be changed to apply most of the logged changes before getting exclusive access. In Facebook OSC most logged changes were applied in the replay step so that the cutover step, which gets exclusive access, would be fast.

I confirmed the current code blocks while applying the log by adding a call to sleep in the function row_log_apply just after the call to row_lock_x_lock(dict_index_get_lock(index)).

I created a feature request for this.

Note - I was wrong about the duration of locking. Looks like this is very online and likely to replace many uses of the OSC frameworks. See the comments below.