AWS aurora online DDL

2 minute read


不过FastDDL根据文档只在lab mode下生效,但是lab mode默认是关闭的,根据aws文档不建议在生产环境下使用lab mode。

Aurora支持添加字段的时候,支持fast DDL,不过有如下限制:

  • Fast DDL only supports adding nullable columns, without default values, to the end of an existing table.

  • Fast DDL does not support partitioned tables.

  • Fast DDL does not support InnoDB tables that use the REDUNDANT row format.

  • If the maximum possible record size for the DDL operation is too large, fast DDL is not used. A record size is too large if it is greater than half the page size. The maximum size of a record is computed by adding the maximum sizes of all columns. For variable sized columns, according to InnoDB standards, extern bytes are not included for computation.


    The maximum record size check was added in Aurora 1.15.

innodb_online_alter_log_max_size 默认值512M,mysql5.7默认值128M

只支持add column,其他的见mysql:

所以修改字段的类型,还是会rebuild table,并且会锁表,不允许进行其他dml




In Aurora, when a user issues a DDL statement:

  1. The database updates the INFORMATION_SCHEMA system table with the new schema. In addition, the database timestamps the operation, records the old schema into a new system table (Schema Version Table), and propagates this change to read replicas.

That’s it for the synchronous part of the operation. Done.

Then, on subsequent DML operations, we check to see if the affected data page has a pending schema operation. That’s easily done by comparing the log sequence number (LSN) timestamp for the page with the LSN timestamp of schema changes. If needed, we then update the page to the new schema before applying the DML statement. This operation follows the same upgrade process for redo-undo record pages as everything else. And any I/Os are piggybacked on top of user activity.

You have to be careful about only upgrading the page on DML operations, because upgrades can cause page splits. We need to deal with upgrades on our Aurora Replicas too, and they’re not allowed to change any data. For SELECT statements, we change the memory image of the buffer being passed back to MySQL. This way, it always sees the latest schema, even though the underlying storage is a mix of old and new schema formats.


Table 14.11 Online DDL Support for Index Operations

OperationIn PlaceRebuilds TablePermits Concurrent DMLOnly Modifies Metadata
Creating or adding a secondary indexYesNoYesNo
Dropping an indexYesNoYesYes
Renaming an indexYesNoYesYes
Adding a FULLTEXT indexYes*No*NoNo
Adding a SPATIAL indexYesNoNoNo
Changing the index typeYesNoYesYes

alter table tb_order_archive ADD security_type tinyint(4) DEFAULT 1 NOT NULL, ;

Altering Tables in Amazon Aurora Using Fast DDL

Amazon Aurora Under the Hood: Fast DDL

MySQL Online DDL Operations