MySQL online schema changes at scale: INSTANT and INPLACE DDL, gh-ost and pt-online-schema-change

On a 500 GB table, the wrong ALTER locks writes for hours or floods replicas with lag. The right one finishes in milliseconds. Choosing well starts with knowing what MySQL can do natively.

#Native algorithms

Always state the algorithm so MySQL refuses instead of silently doing something slower:

ALTER TABLE orders ADD COLUMN channel VARCHAR(20) NULL, ALGORITHM=INSTANT;
ALTER TABLE orders ADD INDEX idx_orders_created (created_at), ALGORITHM=INPLACE, LOCK=NONE;

If the operation cannot use that algorithm, you get an error explaining why, and nothing changes.

Warning

INPLACE is online on the primary but replicas apply it as one long statement. A 2-hour index build means 2 hours of replica lag (unless the replica uses parallel replication cleverly, which still waits for the DDL). For big tables with lag-sensitive readers, use gh-ost or pt-osc.

There is a limit on the number of INSTANT column changes per table (row versions) before a rebuild is required; check INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS.

#The metadata lock trap

Every ALTER needs an exclusive metadata lock briefly at start and end. If a long transaction holds a shared lock on the table, the ALTER waits, and every new query on the table queues behind the ALTER. The app freezes.

Guard against it:

SET SESSION lock_wait_timeout = 5;       -- give up after 5 s instead of blocking the world
ALTER TABLE orders ADD COLUMN channel VARCHAR(20) NULL, ALGORITHM=INSTANT;

Check for long transactions first:

SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_mysql_thread_id
FROM information_schema.innodb_trx ORDER BY trx_started LIMIT 10;

#gh-ost: triggerless, binlog-based

gh-ost creates a ghost table, copies rows in chunks, and applies ongoing changes by reading the binary log (row format required). Cut-over is a short atomic rename.

gh-ost \
  --host=replica01.internal --database=shop --table=orders \
  --alter="MODIFY COLUMN amount DECIMAL(14,2) NOT NULL" \
  --user=ghost --ask-pass \
  --chunk-size=1000 --max-load=Threads_running=40 --critical-load=Threads_running=200 \
  --max-lag-millis=1500 --throttle-control-replicas=replica02.internal,replica03.internal \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --exact-rowcount --concurrent-rowcount \
  --verbose --execute

• Connecting to a replica (with --assume-rbr or row-based binlogs there) reduces primary load; gh-ost still writes to the primary.
• --max-lag-millis and --throttle-control-replicas pause copying when replicas fall behind.
• The postpone flag file keeps gh-ost ready but not cutting over until you delete the file, so you choose the cut-over moment.

Interactive control while it runs:

echo throttle | nc -U /tmp/gh-ost.shop.orders.sock
echo "chunk-size=500" | nc -U /tmp/gh-ost.shop.orders.sock
echo status | nc -U /tmp/gh-ost.shop.orders.sock

Limitations: no foreign keys on the table (child or parent) by default, needs row-based binlog, and on RDS/Aurora some flags differ (--allow-on-master, --assume-rbr); read the gh-ost docs for your platform.

#pt-online-schema-change: trigger-based

pt-online-schema-change \
  --alter "ADD INDEX idx_orders_customer_created (customer_id, created_at)" \
  D=shop,t=orders,h=primary01.internal,u=ptosc --ask-pass \
  --chunk-size=1000 --max-lag=2 --check-interval=1 \
  --max-load Threads_running=40 --critical-load Threads_running=200 \
  --set-vars lock_wait_timeout=5 \
  --dry-run
# then replace --dry-run with --execute

It uses triggers to keep the new table in sync, which adds write overhead on the primary and can conflict with existing triggers. It handles foreign keys with --alter-foreign-keys-method (read the trade-offs carefully).

#Choosing

#Before and after

1. Check free disk: online rebuilds need roughly the table's size again.
2. Run on a staging copy with production-like size; time it.
3. Schedule during lower traffic; monitor Threads_running, replica lag, and disk.
4. After cut-over, keep the old table (gh-ost _del table, pt-osc --no-drop-old-table) until you are confident, then drop it in small steps if very large.

Dropping a multi-hundred-GB table can stall the server while the file is deleted. A common approach is to create a hard link to the .ibd file before the DROP (so the DROP returns quickly), then truncate the linked file in steps at OS level.

#Prevent it next time

• Migrations specify ALGORITHM and LOCK explicitly; CI fails ALTERs without them.
• lock_wait_timeout set low for migration sessions.
• Large-table changes go through a reviewed gh-ost/pt-osc runbook, not an ORM migration at deploy time.