From Midnight Table Locks to Instant DDL: MySQL’s Online Schema Evolution Explained

Background

DBAs who grew up with MySQL 5.5 remember the dread of altering a tens‑of‑millions‑row table at 3 AM, often waiting hours while the table was locked and the service unavailable.

Over the past ten years the MySQL team has refined Online DDL, moving from a basic, often disruptive implementation to a nearly invisible operation.

DDL Execution Methods

COPY : Create a temporary table, copy all rows, then swap names. Writes are blocked; execution time grows linearly with data size.

INPLACE : Modify the table in‑place without copying all rows. Only a short metadata lock is taken; duration depends on the operation.

INSTANT : Change only the data dictionary metadata. No data files are touched, locks are essentially none, and the operation completes in milliseconds.

Why "Online"? It means the DDL runs while the table remains readable and writable—no service downtime. Common Misconception : INPLACE is not automatically online. True online DDL requires LOCK=NONE . COPY always uses LOCK=SHARED and is therefore not online.

Rebuild‑Table Dimension

Even with INPLACE, some operations still rebuild the whole table (e.g., adding a primary key). Rebuilding consumes extra disk space—typically 2‑3× the original table size—and generates temporary files and logs.

Rule of thumb : Reserve 2‑3× space for rebuilds; for non‑rebuild operations, keep at least 20 % free space.

innodb_online_alter_log_max_size Pitfall

During Online DDL, concurrent DML is logged to a temporary row log whose size is limited by innodb_online_alter_log_max_size (default 128 MB). Large, long‑running DDL may exceed this limit and abort.

SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';
SET GLOBAL innodb_online_alter_log_max_size = 1073741824; -- 1 GB

For heavy workloads, increase the variable or use tools like gh‑ost or pt‑osc to bypass the limit.

Version History

MySQL 5.5 (pre‑Online)

DDL required full table copies; even adding an index could lock writes for hours.

MySQL 5.6 (Online DDL debut, 2013)

Introduced the INPLACE algorithm and lock levels ( NONE, SHARED, EXCLUSIVE, DEFAULT). Most secondary‑index additions became online.

ALTER TABLE orders ADD INDEX idx_user_id (user_id) ALGORITHM=INPLACE, LOCK=NONE;

MySQL 5.7 (2015)

Added true RENAME INDEX (INPLACE, metadata‑only) and support for virtual generated columns, enabling functional‑index patterns.

ALTER TABLE users ADD COLUMN age INT GENERATED ALWAYS AS (JSON_EXTRACT(profile,'$.age')) VIRTUAL;
ALTER TABLE users ADD INDEX idx_age (age);

MySQL 8.0 (2018‑present)

Implemented INSTANT DDL. From 8.0.12 onward, adding a column with a default value completes instantly, regardless of table size.

ALTER TABLE orders ADD COLUMN remark VARCHAR(500) DEFAULT '' ALGORITHM=INSTANT, LOCK=NONE;

MySQL now tries algorithms in order: INSTANT → INPLACE → COPY, picking the least disruptive method automatically unless overridden.

Limitations: INSTANT supports only adding columns at the end (pre‑8.0.29) and has a per‑table version‑count limit (64 in 8.0, 255 in 9.1). Auto‑increment columns cannot be added instantly.

Metadata Locks (MDL)

MDL protects schema consistency. DML holds a read lock; DDL needs a write lock, which blocks until all reads finish. A single long‑running SELECT can stall an ALTER indefinitely, causing request back‑log and possible service outage.

SHOW PROCESSLIST; -- look for State='Waiting for table metadata lock'

Set a short lock_wait_timeout (e.g., 2 seconds) to fail fast instead of waiting for hours.

Best‑Practice Checklist for Native Online DDL

Identify the DDL type : consult the official “Online DDL Operations” page to see supported ALGORITHM, LOCK, and whether the operation rebuilds the table.

Check disk space : for COPY or rebuilds, ensure 2‑3× free space.

Estimate execution time in a staging environment.

Choose a low‑traffic window and avoid overlapping with backup windows.

Set session parameters :

SET lock_wait_timeout = 2;
SET innodb_lock_wait_timeout = 2;

Execute with explicit hints :

ALTER TABLE orders ADD COLUMN remark VARCHAR(500) DEFAULT ''
  ALGORITHM=INSTANT, LOCK=NONE;

Validate : SHOW CREATE TABLE, monitor replication lag, and watch business KPIs.

When to Prefer Third‑Party Tools

If the operation forces a table rebuild, the table is huge, or you have strict latency requirements, use gh‑ost or pt‑online-schema-change. These tools copy only DML, avoid MDL contention, and allow pausing or throttling.

Conclusion

MySQL’s DDL has progressed from a nightly nightmare to near‑instant schema changes, but hidden costs remain: MDL locks, temporary disk usage, version‑count limits, and unsupported operations. Explicitly specifying ALGORITHM, LOCK, and a short lock_wait_timeout is essential for predictable deployments, and for massive tables or critical workloads, third‑party online schema‑change tools are still the safest choice.