Introduction

In versions earlier than MySQL 5.6, DDL operations on the structure of a large table usually cause data manipulation language (DML) statements to be blocked and increase replication delay, so the database looks abnormal. This chapter introduces the DDL-based COPY and INPLACE algorithms of MySQL, the open-source tool gh-ost, and the INSTANT ADD COLUMN algorithm newly added in MySQL 8.0.

• The native COPY algorithm of MySQL adds a metadata write lock to the source table during data copy, causing DML statements to be blocked for a long time. This algorithm is no longer recommended.
• The INPLACE algorithm has great improvements over the COPY algorithm by making changes directly on the original table without generating temporary tables, so it occupies less space. In addition, the INPLACE operation holds metadata write locks for a short period of time, which does not cause long-term blocking of DML operations. However, modifying the structure of a large table still takes much time, and there will be a long replication delay when the standby instance replays the DDL statements.
• The open-source gh-ost splits a DDL operation into multiple small operations, reducing the time required for each operation to decrease replication delay. Reads and writes are briefly blocked only when the ghost table and original table are being renamed. gh-ost replays incremental data based on binlogs and maintains an extra heartbeat table to record the DDL execution process, supporting temporary suspension of the DDL process. gh-ost takes more time than the native DDL algorithm.
• The INSTANT ADD COLUMN algorithm proposed in MySQL 8.0 does not need to rebuild the entire table. It only records basic information about new columns in the metadata of the table. In this way, adding columns to a large table only takes several seconds. However, this algorithm applies only to a few DDL operations, such as adding columns, setting default values for columns, deleting default values from columns, and changing definitions of ENUM/SET columns.

Based on the characteristics of each algorithm and tool, you are advised to use the INSTANT algorithm to minimize the impact of DDL on your whole workload in every possible case. In other cases, if your DB instance uses a primary/standby deployment or has read replicas and your workload is sensitive to replication delay, use gh-ost to perform DDL operations. If you need to quickly change a table structure and a short replication delay is acceptable, use INPLACE. The COPY algorithm, as it blocks DML operations for a long time, occupies a large amount of storage space, and takes a long time to execute, is not recommended when there is any other alternative.