Understanding MySQL DDL Algorithms and How to Minimize Their Impact

Introduction

DDL operations on large MySQL tables are a common pain point because they can be time‑consuming, increase load, consume extra space, and affect replication and metadata locks. This article explains the underlying principles of MySQL DDL and offers ways to reduce its impact on production workloads.

MySQL DDL Algorithms

MySQL provides three built‑in algorithms:

COPY – the oldest method, default before MySQL 5.6. It creates a temporary table, copies all data, then swaps the tables, blocking writes for the entire operation.

INPLACE – introduced in MySQL 5.6 and default thereafter. It has two sub‑types: rebuild‑table and not‑rebuild‑table . MySQL automatically chooses not‑rebuild‑table when possible, otherwise it falls back to rebuild‑table. Certain operations (e.g., primary‑key changes) always require rebuild‑table.

INSTANT – added in MySQL 8.0.12 and default for supported DDL types. It currently only supports a limited set of operations such as adding a column, adding/removing a virtual column, changing column defaults, modifying ENUM/SET definitions, and index type changes. INSTANT updates the table’s metadata without rebuilding the data.

Third‑party tools that implement similar copy‑based approaches include Percona’s pt-online-schema-change and GitHub’s gh‑ost , both supporting MySQL 5.5+.

Using Specific Algorithms

To force a particular algorithm you can specify the ALGORITHM clause in the ALTER TABLE statement:

ALTER TABLE table1 ADD COLUMN column1 INT ALGORITHM=COPY;

For INPLACE on MySQL 8.0+:

ALTER TABLE table1 ADD COLUMN column1 INT ALGORITHM=INPLACE;

For INSTANT (MySQL 8.0+):

ALTER TABLE table1 ADD COLUMN column1 INT ALGORITHM=INSTANT;

Note that INSTANT cannot use the AFTER clause, cannot be used on compressed InnoDB tables, tables with full‑text indexes, temporary tables, or tables using the old file‑format.

Performance and Operational Considerations

Execution Time – Highly variable; testing on a replica is recommended for critical workloads.

Load – All methods increase I/O; schedule DDL during low‑traffic windows for heavily loaded instances.

Extra Space – COPY, INPLACE rebuild‑table, pt‑osc, and gh‑ost duplicate the table, requiring roughly the same amount of free space as the original plus temporary logs.

Replication Lag – COPY and INPLACE rebuild‑table block binlog shipping until the master finishes DDL, potentially doubling latency. Tools like gh‑ost generate smaller lag.

Metadata Locks (MDL) – Every DDL acquires an EXCLUSIVE‑MDL at start, briefly releases it during the execute phase, and reacquires it at commit. If a long‑running transaction holds locks, DDL (and subsequent statements) will wait, showing “waiting for metadata lock” in SHOW PROCESSLIST . You can monitor MDL via performance_schema.metadata_locks or enable performance-schema-instrument=stage/innodb/alter%=ON .

Monitoring DDL Progress

Enable DDL instrumentation in my.cnf :

performance-schema-instrument=stage/innodb/alter%=ON

Then query:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;

For MDL details:

SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_STATUS, processlist_id FROM performance_schema.metadata_locks mdl INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id WHERE processlist_id <> @@pseudo_thread_id;

Tool‑Specific Notes

pt-online-schema-change follows the COPY approach using triggers for data sync, which can increase load under heavy write traffic.

gh‑ost reads the binary log instead of using triggers, reducing load but requiring binlog access.

Additional Caveats

INPLACE is limited by innodb_online_alter_log_max_size (default 128 MiB); increase if necessary.

INSTANT columns must be added at the end of the table and are not supported on compressed tables or older MySQL versions.

Conclusion

Understanding the characteristics of COPY, INPLACE, and INSTANT algorithms, along with the appropriate use of external tools, allows you to choose the least disruptive method for MySQL DDL, monitor its progress, and mitigate impacts on load, space, replication, and metadata locking.