Cutting Over gh-ost Migrations Safely

The row copy finished overnight, and gh-ost has been sitting at State: postponing cut-over all morning, applying binlog events and keeping the ghost table current. Now someone has to decide when to swap the tables — and the last time a cut-over ran at peak traffic, it failed four times with Timeout while waiting for lock before succeeding, and the application logged a burst of stalled writes each time. The cut-over is the only moment in a gh-ost migration when the original table is locked, so it is the only moment that can hurt. This guide explains what gh-ost does during those seconds, how to schedule it, and how to tune it so it either succeeds quickly or backs off cleanly. It completes the gh-ost workflow in Online Schema Change Tools.

gh-ost's Atomic Cut-Over Sequence between gh-ost connection C1, gh-ost connection C2, the original table and application writers. C1 creates the sentinel _tbl_del and runs LOCK TABLES tbl WRITE, _tbl_del WRITE. Application writes queue. gh-ost drains remaining binlog events into the ghost table. C2 issues RENAME TABLE tbl TO _tbl_del, _tbl_gho TO tbl, which blocks behind the lock. C1 drops the sentinel and unlocks; the rename executes atomically and queued writes land on the new table. gh-ost's Atomic Cut-Over gh-ost C1 gh-ost C2 MySQL App writers CREATE TABLE _tbl_del (sentinel) LOCK TABLES tbl WRITE, _tbl_del WRITE INSERT … (queued) gh-ost drains remaining binlog events RENAME tbl→_tbl_del, _tbl_gho→tbl (waits) DROP _tbl_del; UNLOCK TABLES rename executes atomically queued writes hit new tbl
Writers are blocked only between the LOCK TABLES and the rename — normally well under a second — and the rename swaps both tables in one atomic step.

Symptom / Error Signatures

Cut-over problems show up in gh-ost’s output and in the application:

  • gh-ost logs Timeout while waiting for lock or Lock wait timeout exceeded during cut-over, followed by a retry. After --default-retries failures (60 by default) it gives up and exits.
  • The application logs a spike of write latency, or ERROR 1205, at the moment of each cut-over attempt.
  • SHOW PROCESSLIST during the attempt shows application threads Waiting for table metadata lock or Waiting for table level lock on the migrated table.
  • gh-ost reports State: postponing cut-over indefinitely because a postpone flag file exists — intended, but easy to forget.
  • After a successful cut-over, the old table remains as _tbl_del and consumes disk until dropped.

Root Cause Analysis

gh-ost’s default cut-over (--cut-over=default) is designed so the swap is atomic: there is no moment when the table name points at nothing. It achieves this with two connections. The first creates a sentinel table and takes LOCK TABLES on the original table and the sentinel, which stops application writes. gh-ost then applies the last binlog events so the ghost table is fully current. The second connection issues a combined RENAME TABLE that moves the original to the sentinel’s name and the ghost to the original’s name; it blocks because of the held lock. The first connection then drops the sentinel and unlocks, and MySQL executes the queued rename before any queued application statement, because renames get priority. Queued writes then land on the new table.

The fragile part is acquiring the initial lock. LOCK TABLES ... WRITE needs an exclusive metadata lock, so it waits behind any open transaction on the table — the same queueing problem described in diagnosing “Waiting for table metadata lock”. gh-ost bounds that wait with --cut-over-lock-timeout-seconds (default 3), and while it waits, application writes queue behind it. If the timeout fires, gh-ost releases everything, application traffic resumes, and it retries after a pause.

Setting Effect Guidance
--postpone-cut-over-flag-file cut-over waits while the file exists always set; remove the file in a chosen window
--cut-over-lock-timeout-seconds max wait for the initial lock and rename 1–3; lower on very hot tables
--default-retries attempts before giving up default 60 is usually fine
--cut-over=two-step non-atomic: rename original away, then ghost in avoid; brief “table does not exist” window
One Failed Attempt, Then a Clean One Timeline over 12 seconds. A long transaction holds the table from 0 to 5 seconds. The first cut-over attempt at 1 second waits for its lock until the 3 second timeout at 4 seconds, stalling application writes for 3 seconds, then releases. The second attempt at 7 seconds acquires the lock immediately and completes in 0.4 seconds. One Failed Attempt, Then a Clean One Long transaction open txn on tbl Cut-over attempts #1 times out #2 App writes ok stalled ok on new table 0 s 2 s 4 s 6 s 8 s 10 s 12 s waiting for lock swap writes stalled
A lock timeout during cut-over costs at most the timeout in stalled writes, then everything releases; the retry succeeds once the blocking transaction ends.

Immediate Mitigation

1. Always start with the cut-over postponed. If a migration is running without a postpone flag, create the file named by --postpone-cut-over-flag-file if one was configured; otherwise use the interactive socket to postpone.

# Shell · host running gh-ost · no database privileges needed
touch /var/run/migrations/postpone-orders
echo status | nc -U /tmp/gh-ost.shop.orders.sock   # expect "postponing cut-over" once copy completes

2. Clear blockers just before cutting over. Check for long transactions on the table; they are the reason cut-over attempts time out.

-- MySQL 8.0 · read-only · run immediately before releasing the cut-over
SELECT trx_mysql_thread_id, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 2
ORDER BY trx_started;

3. Release the cut-over in a low-traffic window. Remove the flag file (or send unpostpone to the socket) and watch the log.

# Shell · triggers the cut-over; gh-ost retries on lock timeout up to --default-retries
rm /var/run/migrations/postpone-orders
# or: echo unpostpone | nc -U /tmp/gh-ost.shop.orders.sock

4. If attempts keep failing, re-postpone and investigate. Repeated timeouts mean a workload holds the table continuously. Recreate the flag file to stop retrying, identify the workload with performance_schema.metadata_locks, and schedule around it.

Permanent Fix / Long-Term Pattern

Treat cut-over as a separate, scheduled deployment step. Start every gh-ost run with --postpone-cut-over-flag-file, let the copy run for as long as it needs under throttle (see throttling gh-ost on replica lag), and release the cut-over from your deploy tooling in a window when the table is quietest. Keep --cut-over-lock-timeout-seconds short — two or three seconds — so a failed attempt is barely visible, and rely on retries rather than a long wait. Make sure the application tolerates a brief write stall: pool checkout and request timeouts should exceed the cut-over lock timeout.

After the swap, keep the old table as _tbl_del for a defined period rather than dropping it immediately; it is your fastest rollback if the new schema causes problems. Drop it later during a quiet window, remembering that dropping a very large InnoDB table can itself cause a stall on some versions and file systems. Record the cut-over time in the deploy log so any error spike can be correlated, as described in monitoring long-running migrations in production.

-- MySQL 8.0 · rollback within the retention window · requires ALTER and DROP on the schema
-- WARNING: writes made to the new table since cut-over are NOT in _orders_del; reconcile before swapping back.
RENAME TABLE orders TO _orders_failed, _orders_del TO orders;
-- ROLLBACK PATH: RENAME TABLE orders TO _orders_del, _orders_failed TO orders;
Cut-Over as a Scheduled Step Five steps: start gh-ost with the cut-over postponed; row copy completes and binlog apply keeps the ghost current; pre-check for long transactions in a quiet window; remove the postpone flag; after success keep the old table for a retention period before dropping it. Cut-Over as a Scheduled Step STEP 1 Start postponed flag file present STEP 2 Copy completes ghost kept current STEP 3 Quiet-window pre-check no txn > 2 s STEP 4 Release cut-over lock timeout 2–3 s STEP 5 Keep _tbl_del drop after retention
Separating copy from cut-over means the only locking moment happens when you choose, with blockers already cleared and the old table kept as a fallback.

Verification Checklist

Frequently Asked Questions

Is gh-ost’s cut-over really atomic? With the default cut-over mode, yes: the combined RENAME TABLE swaps both names in one operation, so no query ever sees the table missing. Writers are blocked briefly while the lock is held, and queued writes land on the new table afterwards.

What happens to application writes during a failed attempt? They wait for up to --cut-over-lock-timeout-seconds, then proceed against the original table when gh-ost releases its locks. Nothing is lost; the ghost table continues to receive those changes through the binlog, and gh-ost tries again later.

Should I use --cut-over=two-step? Generally not. It renames the original table away and then renames the ghost into place as two operations, leaving a short window in which the table does not exist and queries fail. The default atomic mode avoids that.

When can I drop the _tbl_del table? Once you are confident you will not need to swap back — typically after the application has run on the new schema through a normal traffic cycle. Before dropping, remember that any rollback would need to reconcile writes made to the new table since the cut-over.

Related Articles