Why you should not use MySql 5.6 online DDL on AWS Aurora
I was exploring few options when it comes to updating large MySQL table on Aurora, and one of them was v5.6 online DDL feature. Aurora was already running on MySQL 5.6 so I thought I would take advantage of this feature.
ALTER TABLE [table] ADD COLUMN [column], ROW_FORMAT=COMPACT, ALGORITHM=INPLACE, LOCK=NONE;
Theoretically, this should work. However there is a problem with this, using INPLACE algorithm Aurora will move the data to temporary tables. This will use the ephemeral storage of your Aurora node. The amount of space is limited to cluster class instance capacity you’re running.
What’s more, there is simply no way to tell how much space is currently available on Aurora node. This operation will likely to consume a significant amount of ephemeral storage, so it’s simply a blind shot. It may fail or not, so not recommended. The alternative is to use ALGORITHM=COPY. In this case using COPY without LOCK is not allowed, using LOCK=SHARED instead locks the whole table for the alteration time which ruins the whole idea anyway.
If you need to alter larger table, possibly the best option is to use Percona Toolkit.