gh-ost vs pt-online-schema-change: Choosing an Online DDL Tool

You have a 300-million-row MySQL table, an ALTER that forces a full rewrite, and two tools on the table: Percona’s pt-online-schema-change and GitHub’s gh-ost. Someone on the team ran pt-osc last quarter and watched p99 write latency double for the six hours the copy ran; someone else tried gh-ost and hit binlog_format errors before it would even start. Both finished the migration, but they behaved nothing alike under load, and the wrong pick on a hot table turns a routine schema change into a paging incident. This page settles the choice between the trigger-based and binlog-based models — what each does to write latency, how each throttles on a lagging replica, and where each fails — so you can defend the decision before you run it against production. It is the head-to-head companion to the broader online schema change tools guide, which covers the copy/sync/cutover shape both tools share.

Symptom / Error Signatures

You are on this page because one of these is true — either you are choosing up front, or a running migration is misbehaving in a way that names the tool:

  • Application write latency climbs the moment a pt-online-schema-change run starts and returns to baseline the instant it finishes — the signature of synchronous triggers in the write path.
  • pt-online-schema-change refuses to start: Cannot connect to the database because a foreign key constraint... or it warns Child tables ... reference this table.
  • gh-ost aborts immediately with FATAL Error 1236 ... Could not find first log file name in binary log index or binlog_format must be set to ROW.
  • gh-ost throttles and stalls: throttle: lag ... exceeds max-lag-millis printed on repeat while a replica is behind, and copy percentage stops climbing.
  • A pt-osc run left triggers behind after being killed — pt_osc_<db>_<table>_ins/upd/del still firing on every write to the original table.
  • The cutover queued traffic: Waiting for table metadata lock piling up in SHOW PROCESSLIST around the RENAME TABLE.

Root Cause Analysis

Both tools build a shadow table, copy existing rows in primary-key chunks, keep the shadow current with live writes, and swap it in under a brief lock. The entire divergence is in how the shadow stays current — and that one design choice cascades into latency, throttling, and failure behavior.

pt-online-schema-change uses synchronous triggers. It installs AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the original table, so every application write also executes trigger code that mirrors the row into the shadow table inside the same transaction. The mirror is always current by construction, but the application pays for it: each write does extra work before it can commit, which is why latency rises for the whole run. Triggers also collide with pre-existing triggers (MySQL allowed only one trigger per event per table before 8.0) and with foreign keys, since the referenced table is renamed out from under child constraints — the reason handling foreign keys with pt-online-schema-change is a topic of its own.

gh-ost uses asynchronous binlog streaming. It connects to MySQL as if it were a replica, reads committed changes from the row-based binary log, and applies the equivalent change to the shadow table out-of-band — nothing runs inside the application’s transaction. Production writes feel almost no added latency, and because gh-ost already speaks replication, it can watch a real replica’s Seconds_Behind_Source and pause the copy the moment a follower falls behind. The cost is prerequisites: binlog_format=ROW, binlog_row_image=FULL, a replication-capable user, and a small, ever-present apply lag that must reach zero before cutover.

Decision axis pt-online-schema-change (trigger) gh-ost (binlog)
Sync mechanism Synchronous AFTER triggers Asynchronous binlog stream (acts as a replica)
Added write latency Yes — trigger runs inside every write Minimal — apply is off the write path
Prerequisites Works on default config; needs no binlog binlog_format=ROW, binlog_row_image=FULL, repl user
Throttle signal Replica lag, load, custom queries (--max-lag, --check-interval) Replica lag (--max-lag-millis, built-in, aggressive)
Existing triggers Blocks pre-8.0; conflicts possible Unaffected — no triggers installed
Foreign keys Complex — --alter-foreign-keys-method required Refuses child-FK tables by default
Interactive control Limited once running Rich — throttle/cut-over via unix socket
Cutover Atomic RENAME TABLE Two-step atomic swap with lock-safety checks
Runs against Primary directly Primary, or --test-on-replica / --migrate-on-replica
Trigger sync versus binlog sync pt-osc mirrors each write into the shadow table synchronously inside the transaction via triggers; gh-ost reads the binary log as a replica and applies changes to the shadow table out of band. pt-osc · synchronous trigger App write one transaction orders (original) _orders_new (shadow) AFTER INS/UPD/DEL trigger commit waits for both writes → added latency gh-ost · async binlog App write orders commit, then done ROW binlog gh-ost (as replica) _orders_gho (shadow) apply is off the write path → near-baseline latency
The one design difference: pt-osc mirrors every write synchronously inside the transaction, while gh-ost applies changes from the binlog out of band.

Immediate Mitigation

If a migration is running right now and hurting, act before you re-plan the tool choice.

  1. pt-osc is spiking write latency: throttle or pause it. pt-osc self-throttles on replica lag and thread count; tighten the thresholds so it backs off instead of hammering the write path. If it is already running you must stop and re-launch with stricter limits — there is no live socket.
# bash · run from a host that can reach the primary · safe only at low-write windows
# --max-load pauses the copy when too many threads are running; --critical-load aborts.
pt-online-schema-change \
  --alter="MODIFY COLUMN total_amount DECIMAL(14,2) NOT NULL" \
  --max-load="Threads_running=20" \
  --critical-load="Threads_running=60" \
  --max-lag=1 --check-interval=1 \
  --chunk-size=500 \
  D=shop,t=orders --execute
  1. gh-ost is stalled on replica lag: pause it cleanly, do not kill it. gh-ost exposes a unix socket for interactive control, so you can throttle without losing progress.
# bash · run on the host running gh-ost · non-destructive · progress is preserved
echo throttle    | nc -U /tmp/gh-ost.shop.orders.sock   # pause the copy
echo status      | nc -U /tmp/gh-ost.shop.orders.sock   # inspect lag / ETA
echo no-throttle | nc -U /tmp/gh-ost.shop.orders.sock   # resume when the replica recovers
  1. A killed pt-osc left triggers firing: remove them before any retry. Orphaned triggers keep mirroring into a dead shadow table and add latency to every write with no benefit.
-- MySQL 8.0 · run as the migration role · safe: original table is authoritative
-- Names follow pt_osc_<db>_<table>_<event>; confirm with SHOW TRIGGERS LIKE 'orders'.
DROP TRIGGER IF EXISTS pt_osc_shop_orders_ins;
DROP TRIGGER IF EXISTS pt_osc_shop_orders_upd;
DROP TRIGGER IF EXISTS pt_osc_shop_orders_del;
DROP TABLE   IF EXISTS _orders_new;
  1. gh-ost refuses to start on binlog config: fix the format, do not force it. A STATEMENT or MIXED binlog cannot be streamed row-for-row.
-- MySQL 8.0 · run as an admin with SUPER/SYSTEM_VARIABLES_ADMIN · session + persisted
-- gh-ost needs full row images to reconstruct each change against the shadow table.
SET PERSIST binlog_format = 'ROW';
SET PERSIST binlog_row_image = 'FULL';
-- Existing connections keep their old format; new connections pick this up.
Write latency over one migration run pt-osc keeps write latency elevated for the whole copy then takes a brief RENAME lock; gh-ost holds latency near baseline with small throttle gaps and a brief cutover at the end. Write latency across one copy run pt-osc baseline elevated p99 for entire copy (triggers in write path) RENAME gh-ost baseline throttle throttle near-baseline; pauses when a replica lags cut-over start time →
pt-osc holds write latency high for the full copy; gh-ost stays near baseline and simply pauses when a replica falls behind.

Permanent Fix / Long-Term Pattern

The durable decision is not “which tool is better” but “which model fits this table and this database deployment,” codified so every engineer picks the same way. Reach for a tool only after confirming the change actually rewrites — a MySQL 8.0 ALGORITHM=INSTANT add or a metadata-only change needs neither tool, a point the migration tool comparison makes when it maps each ALTER to its lock behavior. Once a rewrite is confirmed, apply this rule:

Pick gh-ost when the table is write-hot and latency-sensitive, you run replicas you can throttle against, and your cluster already emits a ROW-format binlog. Its out-of-band apply and aggressive lag awareness make it the safer default for the busiest tables, and its interactive socket lets you postpone the cutover to a genuinely quiet second. This is also the path that plays nicely with tuning chunk size against replica lag, the same trade-off worked in backfill optimization.

Pick pt-online-schema-change when you cannot enable ROW binlogging or attach a replication user, when the change involves foreign keys you need the tool to rebuild (gh-ost simply refuses child-FK tables), or when the table’s write rate is low enough that trigger overhead is in the noise. pt-osc runs on stock configuration and is the pragmatic choice on managed databases where you do not control binlog_format.

Whichever model you choose, the tool only executes the DDL — ordering, immutability, and rollback still belong upstream. Treat the online change as one step inside the expand-and-contract methodology: add the new shape, backfill, cut reads over, then contract, so the rewrite is never the load-bearing moment. And because both tools do a rewriting ALTER that MySQL cannot roll back once cutover lands, understand your engine’s transactional versus non-transactional DDL behavior before you commit to the swap. Codify the rule in your runbook so the next 300-million-row table is decided in a minute, not debated in an incident.

Tool-selection decision tree Confirm a rewrite, then branch on ROW binlog plus replication user, foreign-key references, and write-hotness to reach gh-ost or pt-online-schema-change. Rewrite confirmed? (INSTANT attempt fails) ROW binlog + repl user available? Referenced by foreign keys? yes Write-hot / latency-sensitive? no gh-ost out-of-band, throttle-aware yes pt-online-schema-change stock config; triggers; --alter-foreign-keys-method no pt-osc also fine — trigger cost is in the noise no
Codify the pick: prerequisites and foreign keys route to pt-osc; a write-hot table with ROW binlog and a replica routes to gh-ost.

Verification Checklist

Frequently Asked Questions

Which tool adds less load to a busy production table? gh-ost, in almost every write-heavy case. Because it reads the binary log as a replica and applies changes out-of-band, nothing runs inside the application’s transaction, so write latency stays near baseline for the whole copy. pt-online-schema-change installs triggers that execute synchronously inside every INSERT, UPDATE, and DELETE, so latency rises for the entire duration of the run. On a low-write table the difference is negligible; on a hot table it is the deciding factor.

When must I use pt-online-schema-change instead of gh-ost? When you cannot meet gh-ost’s prerequisites or when foreign keys are involved. gh-ost requires binlog_format=ROW, binlog_row_image=FULL, and a replication connection — on some managed databases you cannot change those. gh-ost also refuses tables referenced by foreign keys by default, whereas pt-osc offers --alter-foreign-keys-method (rebuild_constraints or drop_swap) to handle them explicitly. In both situations pt-osc, which runs on stock configuration with triggers, is the workable path.

Do either of them cause downtime at cutover? Only a single brief lock — a RENAME TABLE for pt-osc or gh-ost’s two-step atomic swap — typically milliseconds. The risk is not the swap’s duration but contention: if a long-running transaction holds the table, the cutover queues behind it and traffic stacks up as Waiting for table metadata lock. Set a short lock-wait timeout and trigger the swap at a low-write moment (gh-ost’s postpone flag and socket make this precise) so a blocked cutover fails fast instead of throttling the whole application.

Up one level: Online Schema Change Tools

Related Articles