Throttling gh-ost on Replica Lag and Server Load

A gh-ost migration on a 600 GB table is two hours in, and the read replicas that serve the product catalogue are now twelve seconds behind the primary. Customers are seeing stale prices, and the on-call engineer is weighing whether to kill the migration and lose two hours of copy. You do not have to: gh-ost is designed to be slowed down, paused and resumed while it runs, and it will do so automatically if you tell it what “too much” looks like. This guide covers the throttling controls that keep a long gh-ost run inside your lag and load budgets, and how to change them live without restarting. It extends the tool comparison in Online Schema Change Tools.

gh-ost's Throttle Feedback Loop gh-ost copies row chunks into the ghost table and applies binlog events. It writes a heartbeat to its changelog table on the primary every 100 milliseconds and reads it on the control replicas to measure lag. If lag exceeds max-lag-millis, or a status variable exceeds max-load, or a throttle flag file exists, the row copy pauses while binlog application continues. gh-ost's Throttle Feedback Loop gh-ost row copy + binlog apply Primary heartbeat in _tbl_ghc Control replicas read heartbeat age Throttle checks lag, max-load, flag files, throttle-query Copy paused binlog apply continues Copy resumes all checks under limits writes reads over under
gh-ost measures lag with its own heartbeat rather than Seconds_Behind_Source, so the throttle reacts within a fraction of a second.

Symptom / Error Signatures

These are the signs that a running gh-ost migration needs throttling, or that its throttle is set too loosely:

  • Replica lag graphs climb steadily while gh-ost is in the row-copy phase, and flatten when you pause it.
  • The primary’s Threads_running or CPU rises above its normal peak in step with the migration’s chunk rate.
  • Application p99 latency on writes to the migrated table rises, because each chunk’s INSERT ... SELECT competes for the same pages and locks.
  • gh-ost’s status line reports a high rate and no throttling, for example Copy: 18400000/421000000 4.4%; Applied: 3120; Backlog: 0/1000; Time: 1h52m; streamer: mysql-bin.004411:...; Lag: 11.84s, HeartbeatLag: 11.91s, State: migrating; ETA: 40h12m.
  • Or the reverse: gh-ost reports State: throttled, lag=... for hours, meaning the budget is so tight the migration will never finish.

Root Cause Analysis

gh-ost does two kinds of work. It copies existing rows from the original table into the ghost table in chunks (--chunk-size, default 1000 rows), and it tails the binary log to apply ongoing changes to the ghost table. Row copy is the bulk of the load and is entirely discretionary: gh-ost can pause it at any time without falling behind on correctness, because binlog application keeps the ghost table consistent with whatever has been copied so far. Throttling pauses row copy; it never pauses binlog application.

Every copied chunk is an ordinary write on the primary, so it is also written to the binlog and replayed on every replica. If gh-ost copies faster than the slowest replica can apply, replicas fall behind. gh-ost detects that by writing a heartbeat timestamp into its changelog table (_<table>_ghc) every --heartbeat-interval-millis and reading it back on the replicas listed in --throttle-control-replicas; the age of that heartbeat is the lag.

Control What it measures Default Typical production value
--max-lag-millis heartbeat age on control replicas 1500 1000–3000, below your read-routing tolerance
--max-load status variables on the primary none Threads_running=40
--critical-load status variables; aborts instead of throttling none Threads_running=400
--chunk-size rows per copy chunk 1000 500–2000
--nice-ratio sleep per chunk, as a fraction of its copy time 0 0.5–2 on busy primaries
--throttle-flag-file pause while the file exists none a path your tooling can touch
Replica Lag During Copy, With and Without a Lag Throttle Line chart of replica lag over 60 minutes of row copy. Without a lag throttle, lag climbs steadily from 0 to about 14 seconds. With max-lag-millis set to 1500, lag oscillates between about 0.5 and 1.5 seconds as gh-ost pauses and resumes the copy. Replica Lag During Copy, With and Without a Lag Throttle 0 5 10 15 0 10 20 30 40 50 60 minutes into row copy replica lag (s) max-lag-millis no lag throttle max-lag-millis=1500
The throttle trades migration duration for replica freshness; the sawtooth is gh-ost pausing whenever the heartbeat age crosses 1.5 s.

Immediate Mitigation

If the migration is lagging replicas right now, change its behaviour through the interactive socket rather than killing it. gh-ost listens on the file named by --serve-socket-file (by default /tmp/gh-ost.<schema>.<table>.sock).

1. Pause the copy immediately. Throttling is instant and loses no progress.

# Shell · on the host running gh-ost · no database privileges needed
# WARNING: pauses row copy only; binlog events keep being applied to the ghost table.
echo throttle | nc -U /tmp/gh-ost.shop.products.sock
echo status   | nc -U /tmp/gh-ost.shop.products.sock   # confirm "State: throttled, commanded by user"

2. Tighten the budgets live. Lower the lag ceiling and add a load ceiling without restarting.

# Shell · gh-ost interactive commands · values take effect on the next throttle check
echo "max-lag-millis=1000"             | nc -U /tmp/gh-ost.shop.products.sock
echo "max-load=Threads_running=40"     | nc -U /tmp/gh-ost.shop.products.sock
echo "chunk-size=500"                  | nc -U /tmp/gh-ost.shop.products.sock
echo "nice-ratio=1"                    | nc -U /tmp/gh-ost.shop.products.sock

3. Resume once replicas have caught up. Watch lag drop below the new ceiling, then release the manual throttle; the automatic throttle takes over from there.

# Shell · resume row copy; automatic lag/load throttling still applies
echo no-throttle | nc -U /tmp/gh-ost.shop.products.sock

4. Make sure every replica that serves reads is a control replica. If lag is climbing on a replica gh-ost is not watching, add it with throttle-control-replicas, which is also settable live.

# Shell · comma-separated host:port list · gh-ost connects with its own credentials
echo "throttle-control-replicas=replica-a:3306,replica-b:3306,replica-analytics:3306" | nc -U /tmp/gh-ost.shop.products.sock

Permanent Fix / Long-Term Pattern

Start every gh-ost run with explicit budgets rather than defaults, derived from what your application can tolerate. --max-lag-millis should sit below the staleness your read routing accepts, as discussed in handling stale replica reads after a schema change. --max-load should be set just above normal peak Threads_running, and --critical-load far above it, as an emergency stop. List every read-serving replica in --throttle-control-replicas. And give your deploy tooling a throttle flag file so a single command pauses every running migration during an incident.

# Shell · migration host · gh-ost user needs REPLICATION CLIENT, REPLICATION SLAVE and ALTER
# WARNING: --execute performs the migration; omit it for a no-op validation run first.
gh-ost \
  --host=replica-a --database=shop --table=products \
  --alter="ADD COLUMN search_rank INT NULL" \
  --max-lag-millis=1500 \
  --throttle-control-replicas="replica-a:3306,replica-b:3306" \
  --max-load=Threads_running=40 --critical-load=Threads_running=400 \
  --chunk-size=1000 --nice-ratio=0.5 \
  --throttle-flag-file=/var/run/migrations/throttle \
  --postpone-cut-over-flag-file=/var/run/migrations/postpone-products \
  --serve-socket-file=/tmp/gh-ost.shop.products.sock \
  --execute
# ROLLBACK PATH: before cut-over, stop gh-ost and drop _products_gho and _products_ghc; the original table is untouched.

Budget the calendar as well as the load. A heavily throttled migration can take days, so estimate duration from a short unthrottled trial on a replica and schedule accordingly; postpone the cut-over so it happens in a window of your choosing, as covered in cutting over gh-ost migrations safely. Emit gh-ost’s status as metrics so lag, throttle state and ETA sit on the same dashboard as the replicas, as recommended in alerting on replication lag during backfills.

Where the Time Goes in a Throttled Run Stacked bars of total migration time for the same 600 GB table under three throttle settings. Unthrottled: 9 hours copying, 0 throttled. Lag budget 1.5 seconds: 9 hours copying plus 5 hours throttled. Lag budget 1.5 seconds with nice-ratio 1: 18 hours copying plus 2 hours throttled. Where the Time Goes in a Throttled Run no throttle 9 h max-lag 1.5 s 9 h 5 h + nice-ratio 1 18 h 2 h copying throttled
Tighter budgets stretch the migration, but every extra hour is spent protecting replicas and the primary — which is the point of running online.

Verification Checklist

Frequently Asked Questions

Does throttling gh-ost risk losing changes made to the original table? No. Throttling pauses only the row copy. gh-ost keeps reading the binary log and applying changes to the ghost table while throttled, so the ghost table remains consistent with everything copied so far, and copying resumes where it left off.

Why does gh-ost use its own heartbeat instead of Seconds_Behind_Source? Seconds_Behind_Source is coarse, can read zero while a replica is stalled, and jumps when large transactions arrive. gh-ost writes a timestamp to its changelog table every 100 milliseconds by default and measures its age on each replica, which gives sub-second, per-replica lag.

What happens if --critical-load is exceeded? gh-ost aborts the migration rather than throttling, leaving the original table untouched and the ghost table behind. It is an emergency brake for conditions where continuing is dangerous; set it well above normal peaks so it only fires in real trouble.

Can I change chunk-size while the migration is running? Yes. Send chunk-size=<n> to the interactive socket. Smaller chunks mean shorter transactions and gentler replica load at the cost of more round trips; larger chunks copy faster when the servers have headroom.

Related Articles