Understanding MySQL Online DDL, PT-OSC, and GH-OST for Large Table Schema Changes

When altering a table’s structure (DDL) in MySQL, especially for tables containing millions of rows, the database must avoid long‑lasting transaction locks that would block DML operations. This article examines how MySQL achieves that and introduces tools such as PT‑OSC and GH‑OST that further improve online schema changes.

Before MySQL 5.5

Two primary methods existed: Copy Table , which creates a temporary table, copies all data, swaps names, and locks the original table for the entire operation; and IN‑Place , introduced in 5.5, which builds new index pages directly in the existing .ibd file, greatly reducing I/O and execution time.

Fast Index Creation (FIC)

Starting with InnoDB 1.0.x, Fast Index Creation allows adding or dropping secondary indexes without copying the whole table; a shared lock is taken, permitting reads but blocking writes until the index is built. FIC does not apply to primary keys, which still require a temporary table.

Since MySQL 5.6 – Online DDL

MySQL 5.6 introduced Online DDL, which keeps the table readable and writable during most schema changes, eliminating the exclusive lock problem of earlier versions.

ALGORITHM and LOCK clauses

Alter table …, ALGORITHM [=] {DEFAULT|INPLACE|COPY}, LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

Online DDL operations are classified as COPY , Online‑Rebuilds , or Online‑No‑Rebuilds based on whether a temporary table or file is needed.

Online DDL Execution Phases

1. Initialization

Create temporary .frm file.

Acquire EXCLUSIVE_MDL lock (no reads/writes).

Determine execution path (COPY, Online‑Rebuilds, Online‑No‑Rebuilds).

Update in‑memory data‑dictionary objects.

If rebuilding, allocate row_log and temporary .ibd file.

2. Execution (skipped if only metadata changes)

Downgrade lock to allow reads (COPY allows reads only).

Record concurrent DML into row_log (for rebuilds).

Scan old table’s clustered index, construct new index entries, insert them into sort_buffer, and merge into the new index.

Apply row_log changes to the temporary table.

3. Commit Table Definition

Re‑acquire EXCLUSIVE_MDL lock.

Apply remaining row_log increments.

Update InnoDB data dictionary and write redo logs.

Refresh statistics and rename temporary .ibd/.frm files.

Finalize DDL changes.

PT‑Online‑Schema‑Change (PT‑OSC)

PT‑OSC, part of Percona Toolkit, performs online schema changes by creating a shadow table, copying data in chunks, and swapping tables, allowing concurrent DML without locking the original table.

GitHub’s Online Schema Transformer (GH‑OST)

GH‑OST implements online schema changes without using triggers, differing from PT‑OSC in its non‑trigger design while still providing near‑zero‑downtime migrations.

Comparison of Online DDL, PT‑OSC, and GH‑OST

Summary

Traditional DDL locks the table for the entire ALTER operation. Starting with MySQL 5.5, InnoDB introduced Fast Index Creation to avoid full table copies for secondary indexes. MySQL 5.6 added Online DDL, which further reduces lock time and permits concurrent DML. MySQL 5.7 added online index rename, and tools like PT‑OSC and GH‑OST provide practical, lock‑free schema migrations.

References

https://segmentfault.com/a/1190000014924677

https://www.cnblogs.com/abclife/p/7505064.html

http://seanlook.com/2016/05/24/mysql-online-ddl-concept/

https://cloud.tencent.com/developer/article/1005177

https://www.cnblogs.com/xinysu/p/6732646.html

http://m.mamicode.com/info-detail-2311611.html

http://www.debugger.wiki/article/html/1550203202837500

https://www.cnblogs.com/TeyGao/p/9089997.html

https://www.w3xue.com/exp/article/20192/21034.html