A glimpse of InnoDB Online DDL

InnoDB Online DDL characteristics

It was introduced in MySQL version 5.6 and is based on the Fast Index Creation feature of version 5.5. When the data table is changed, the operation is allowed to proceed in place without copying the data, or concurrent DML statements continue to access. This brings about the improvement of database availability and performance.

With regard to the features such as local operation and concurrent access provided by Online DDL, take a look at 16 common scenarios in actual work.

Online DDL uses errors that may be encountered

1. When Online DDL runs internally, it may need to acquire exclusive locks at the beginning and end, and it will time out due to long things that are running.

two。 During the change of the data table, the log generated by the DML statement will be temporarily stored in the memory specified by the parameter innodb_online_alter_log_max_size. It may be because the space is too small to report the wrong DB_ONLINE_LOG_TOO_BIG. It is recommended that the heavy load system set this parameter to 512MB or 1GB.

Limitations of Online DDL

If an Online DDL operation runs for a long time, it may cause the delay of the database master-slave replication environment and affect the read business.

Then, for the replication environment, how to change the larger data table will be explained later.

If you are interested, please follow Subscription account's Database Best practices (DBBestPractice).