MySQL Online Schema Change: pt-osc and gh-ost in Production

Your DBA just filed a ticket: add a NOT NULL index to the order_events table — 500 million rows, peak traffic at 11 PM, and a hard SLA of 99.9% uptime. A vanilla ALTER TABLE in MySQL 5.7 would lock the table for hours; even MySQL 8.0's ALGORITHM=INPLACE holds a metadata lock during the cutover that can block writes for seconds or longer on a busy server. That gap is exactly why online schema change tools exist. Choosing the wrong one — or running it wrong — can silently stall replication, spike CPU, or corrupt data during the cutover window.

Background

MySQL's native ALTER TABLE has improved significantly over the years. MySQL 5.6 introduced ALGORITHM=INPLACE for certain operations, and MySQL 8.0 added ALGORITHM=INSTANT for an even broader set. But for large tables under continuous write load, even an "online" native ALTER TABLE introduces risks: the metadata lock (MDL) acquired at the end of the operation can queue and starve incoming connections for several seconds, long enough to trigger application-level timeouts.

Online schema change tools sidestep this by building a shadow copy of the table incrementally, replaying writes in real time, and then performing a fast atomic swap — keeping the MDL window as short as possible. Percona's pt-online-schema-change (part of Percona Toolkit) and GitHub's gh-ost (Go-based, open source) are the two tools that dominate MySQL production deployments.

Why You Need Online Schema Change

Consider what happens with a standard ALTER TABLE ADD INDEX on a 500-million-row InnoDB table:

• MySQL acquires an exclusive metadata lock on the table.
• A full table scan is performed to build the index — potentially 30–90 minutes depending on hardware.
• All DML statements (INSERT, UPDATE, DELETE) queue behind the MDL for the entire duration.
• Connection pool exhaustion follows within seconds on any busy application.

Even with ALGORITHM=INPLACE, LOCK=NONE, the final lock to swap the new table definition still causes a brief but real MDL hold. For read-heavy operations this may be acceptable; for write-heavy tables at peak, it is not.

How pt-osc Works

pt-online-schema-change follows a trigger-based pattern introduced by the Facebook Online Schema Change tool and refined by Percona:

The Three-Phase Approach

1. Create shadow table. pt-osc creates _tablename_new with the desired schema already applied.
2. Install three triggers on the original table: AFTER INSERT, AFTER UPDATE, and AFTER DELETE. These triggers mirror every write to the shadow table in real time.
3. Copy existing rows in chunks. pt-osc scans the original table in configurable chunk sizes (default 1000 rows) and inserts those rows into the shadow table. It pauses between chunks based on replication lag and server load.
4. Atomic RENAME. Once all rows are copied and the shadow table is fully current, pt-osc performs an atomic RENAME TABLE original TO _original_old, _original_new TO original to swap the tables.
5. Drop the old table and triggers. Cleanup follows automatically.

Key pt-osc Configuration Parameters

pt-online-schema-change \
  --host=prod-mysql.internal \
  --user=dba \
  --ask-pass \
  --alter "ADD INDEX idx_user_created (user_id, created_at)" \
  --chunk-size=2000 \
  --max-lag=1 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=80" \
  --no-drop-old-table \
  --dry-run \
  D=app_db,t=order_events

The most important parameters to understand:

• --chunk-size: Number of rows copied per iteration. Increase for faster migrations on low-traffic tables; decrease to reduce I/O impact on busy ones.
• --max-lag: Pause chunk copying if any replica's replication lag exceeds this value in seconds. Default is 1 second — keep it here or lower for synchronous replication setups.
• --max-load and --critical-load: Throttle or abort if the MySQL status variable exceeds the threshold. Threads_running is the most reliable indicator of live query pressure.
• --no-drop-old-table: Recommended for production runs. Keep the old table as a rollback option; drop it manually once you confirm the new table is correct.

How gh-ost Works

gh-ost (GitHub Online Schema Transmogrifier) takes a fundamentally different approach: instead of using triggers to capture writes, it connects to MySQL's binary log stream and replays change events directly. This decouples the migration from the write transaction path entirely.

The Binlog-Based Architecture

1. Create the ghost table (_tablename_ghc) with the new schema.
2. Connect as a replica to the MySQL binary log stream. gh-ost registers itself as a fake MySQL replica and streams binlog events from the primary (or an actual replica).
3. Copy rows in chunks using range queries on the primary key, similar to pt-osc but without triggers in the write path.
4. Apply binlog events to the ghost table as they arrive, keeping the ghost table current with all ongoing DML.
5. Execute the cutover once the ghost table is fully caught up.

Running gh-ost

gh-ost \
  --host=prod-mysql.internal \
  --user=gh-ost \
  --password="${DB_PASS}" \
  --database=app_db \
  --table=order_events \
  --alter="ADD INDEX idx_user_created (user_id, created_at)" \
  --chunk-size=2000 \
  --max-lag-millis=1500 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=80" \
  --switch-to-rbr \
  --allow-master-master \
  --cut-over=default \
  --exact-rowcount \
  --concurrent-rowcount \
  --default-retries=120 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --panic-flag-file=/tmp/ghost.panic \
  --initially-drop-old-table \
  --initially-drop-ghost-table \
  --verbose \
  --execute

The Postpone Cutover Flag

One of gh-ost's most operationally valuable features is the postpone flag file. While gh-ost is running and the ghost table is fully caught up, it will not perform the cutover as long as the file at --postpone-cut-over-flag-file exists on disk. This lets you pre-run the migration, keep it in the "fully caught up and paused" state, and then initiate the cutover at your exact chosen moment by removing the file:

# Start migration — will pause before cutover as long as this file exists
touch /tmp/ghost.postpone

# Run gh-ost (it will copy all rows, stay current via binlog, and wait)
gh-ost ... --postpone-cut-over-flag-file=/tmp/ghost.postpone --execute

# When you are ready to cut over (e.g., during planned maintenance window)
rm /tmp/ghost.postpone
# gh-ost performs the atomic cutover within seconds

# Check migration status while running
echo "status" | nc -U /tmp/gh-ost.app_db.order_events.sock

# Throttle on the fly
echo "throttle" | nc -U /tmp/gh-ost.app_db.order_events.sock

# Resume after throttle
echo "no-throttle" | nc -U /tmp/gh-ost.app_db.order_events.sock

The Cutover Mechanism

gh-ost's cutover uses a lock-based atomic swap. It acquires a write lock on the original table, lets all in-flight writes complete, performs a sub-millisecond RENAME, and releases the lock. The design ensures that no writes are lost and that the lock window is as short as the RENAME latency — typically under 1 second on local storage, under 2–3 seconds on networked storage.

pt-osc vs gh-ost Comparison

The table below compares all four approaches side by side, including MySQL's native DDL options:

Running a Schema Change Safely

Whether you choose pt-osc or gh-ost, the following discipline applies to every production schema change on a large table.

Step 1: Check Whether INSTANT DDL Applies First

-- MySQL 8.0: test if your ALTER is instant before using any external tool
ALTER TABLE order_events
  ADD COLUMN processed_at DATETIME NULL,
  ALGORITHM=INSTANT;

-- If it succeeds without error, the operation is instant — no tool needed
-- If it returns "ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported",
-- proceed with pt-osc or gh-ost

Step 2: Run a Dry-Run First

# pt-osc dry-run
pt-online-schema-change \
  --alter "ADD INDEX idx_user_created (user_id, created_at)" \
  --dry-run \
  D=app_db,t=order_events

# gh-ost dry-run (--execute omitted)
gh-ost \
  --database=app_db \
  --table=order_events \
  --alter="ADD INDEX idx_user_created (user_id, created_at)" \
  --verbose

Step 3: Monitor Replication Lag During the Migration

-- Watch replication lag on all replicas
SELECT
  MEMBER_ID,
  MEMBER_HOST,
  MEMBER_PORT,
  MEMBER_STATE,
  MEMBER_ROLE
FROM performance_schema.replication_group_members;

-- On traditional replication, monitor Seconds_Behind_Master
SHOW REPLICA STATUS\G

Step 4: Prepare a Rollback Script Before Starting

-- If using pt-osc with --no-drop-old-table, your rollback is:
RENAME TABLE order_events TO order_events_new_broken,
             _order_events_old TO order_events;
DROP TABLE order_events_new_broken;

-- Verify triggers are cleaned up
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_SCHEMA = 'app_db'
  AND EVENT_OBJECT_TABLE = 'order_events';

Step 5: Verify Row Counts After Completion

-- Confirm the new table has the expected row count
SELECT
  table_name,
  table_rows,
  data_length,
  index_length
FROM information_schema.tables
WHERE table_schema = 'app_db'
  AND table_name IN ('order_events', '_order_events_old')
ORDER BY table_name;

Working with JusDB on MySQL

JusDB manages MySQL for engineering teams who need production-grade reliability without the operational overhead. Schema changes on large tables are one of the most common sources of production incidents — a misconfigured chunk size, a replication lag spike, or a cutover timed against peak traffic can turn a routine maintenance task into a multi-hour outage. Our DBAs design and execute schema changes with a tested playbook: dry-runs, per-replica lag monitoring, rollback scripts prepared and verified before execution begins, and cutover scheduling that accounts for your application's traffic patterns.

Explore JusDB MySQL Management →  |  Talk to a DBA

Related reading:

• MySQL Replication Lag: Causes, Diagnosis, and Fixes
• MySQL GTID Replication: A Complete Setup Guide
• Using EXPLAIN to Optimize MySQL Queries