Mastering MySQL DDL: Algorithms, Tools, and Performance Tips

Author: Wang Wen'an, MySQL DBA at NetEase Games, focuses on MySQL SaaS platform design and maintenance.

1. MySQL DDL Algorithms

MySQL supports three native DDL algorithms:

COPY – the oldest method, default in MySQL 5.5 and earlier.

INPLACE – introduced in MySQL 5.6 and default thereafter; it includes two sub‑types: rebuild‑table and not‑rebuild‑table. MySQL automatically chooses not‑rebuild‑table when possible, falling back to rebuild‑table for primary‑key or full‑text‑index changes.

INSTANT – added in MySQL 8.0.12, currently supports only a limited set of operations such as adding columns.

2. Tool Comparison

Third‑party tools that implement online schema changes include Percona's pt-online-schema-change (pt‑osc) and GitHub's gh‑ost, both supporting MySQL 5.5+.

Recommended tool usage:

3. Using the Algorithms

1) COPY

MySQL 5.5 and earlier: use normal DDL.

MySQL 5.6+ to force COPY, specify the algorithm: ALTER TABLE table1 ADD COLUMN column1 INT ALGORITHM=COPY; 2) INPLACE

MySQL 5.7: normal DDL works.

MySQL 8.0+ to force INPLACE, use: ALTER TABLE ... ALGORITHM=INPLACE; 3) INSTANT

Supported in MySQL 8.0 for a limited set of DDL types, such as adding a column, virtual column, default value changes, ENUM/SET modifications, index type changes, and table rename. Force with: ALTER TABLE ... ALGORITHM=INSTANT; Important limitations:

Cannot use AFTER to position a new column; it must be added at the end.

Compressed InnoDB tables are not supported.

Tables containing full‑text indexes are excluded.

Only tables using the new MySQL 8.0 tablespace format are eligible.

Temporary tables are not supported.

Instant columns cannot be used on older MySQL versions or restored from older physical backups.

If a table or its indexes are corrupted, INSTANT cannot be applied without repair.

4. pt‑online‑schema‑change

Follows the COPY approach: creates a temporary table, syncs data via triggers, then swaps tables. Trigger usage may increase load on write‑heavy workloads.

5. gh‑ost

Uses binary log reading instead of triggers, avoiding the extra load caused by trigger‑based tools.

6. MySQL DDL Best Practices

DDL on large tables can cause long execution time, high I/O load, extra disk usage, metadata locks (MDL), and replication lag. Key considerations:

Execution time: Highly variable; benchmark on a test instance by restoring a backup and running the DDL.

Load: All methods increase I/O; schedule during low‑traffic periods.

Extra space: COPY, INPLACE rebuild‑table, pt‑osc, and gh‑ost duplicate the table, requiring roughly the same space as the original plus temporary logs. Ensure sufficient free space.

Replication delay: COPY and INPLACE rebuild‑table block the replica until the master finishes, potentially doubling the DDL time. Other methods cause only a few seconds of delay.

Metadata lock (MDL): All DDL acquires an EXCLUSIVE‑MDL lock briefly at start and commit; the middle phase holds a shared lock, allowing reads/writes. Long‑running transactions can block MDL acquisition, causing “waiting for metadata lock” errors. Resolve by waiting, killing blocking transactions, or killing the DDL.

Monitoring: Enable InnoDB DDL monitoring: performance-schema-instrument = stage/innodb/alter%=ON Query progress with:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;

Enable MDL monitoring (MySQL 8.0+):

performance-schema-instrument = wait/lock/metadata/sql/mdl=ON

Inspect current MDL holders:

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;

INPLACE write limits: The amount of data written during INPLACE cannot exceed innodb_online_alter_log_max_size (default 128 MiB). Increase the variable if needed.

7. DDL Internals Overview

For MySQL 5.7, the algorithm used for each DDL operation is illustrated below:

COPY creates a temporary table, copies all rows, then swaps tables, blocking writes for the duration.

INPLACE has two sub‑types. The prepare phase creates a temporary .frm file and acquires an EXCLUSIVE‑MDL lock. The execute phase downgrades the lock, scans the old table, builds new indexes, and logs changes. The commit phase re‑acquires EXCLUSIVE‑MDL, replays the log, updates the data dictionary, and releases the lock.

INSTANT (MySQL 8.0.12+) stores new column metadata directly in the table’s metadata without rebuilding the table, dramatically reducing execution time. It adds flags and column counts to each row to indicate whether instant columns need to be materialized.

SQL to list instant columns per table: SELECT * FROM information_schema.innodb_tables; Both pt‑osc and gh‑ost implement online schema changes by creating a temporary table, synchronizing data (pt‑osc via triggers, gh‑ost via binlog), and swapping tables, with the latter avoiding trigger‑induced load.