A migration that ran for four hours on a table nobody could lock

The change was one column: reference VARCHAR(32) becoming VARCHAR(64), on a table with forty-one million rows. It was tested on staging with eight thousand rows, took four hundred milliseconds, and was approved. On production it held a metadata lock, blocked every write to the orders table, and was killed after eleven minutes with the site down.

The symptom

mysql> ALTER TABLE orders MODIFY reference VARCHAR(64) NOT NULL;
-- (running)

mysql> SHOW PROCESSLIST;
| Id   | Command | Time | State                           |
| 8814 | Query   |  664 | copy to tmp table               |
| 8815 | Query   |  412 | Waiting for table metadata lock |
| 8816 | Query   |  412 | Waiting for table metadata lock |
| ...  |         |      | 340 more                        |

-- "copy to tmp table" is the answer. it is rebuilding
-- 41 million rows, and holding the lock while it does.

Three hundred and forty connections waiting on a metadata lock is the whole application stopped. The ALTER itself would have finished in about four hours; the site could not wait eleven minutes.

Why it happens

Some ALTER operations are metadata changes, some rebuild the table in place while allowing concurrent writes, and some copy the whole table while holding a lock. The syntax is identical and the difference is a lookup in a table in the manual.

Widening a VARCHAR is instant if it stays under the length that changes the row format encoding and is a full copy if it crosses it. Thirty-two to sixty-four crosses it, because the length prefix goes from one byte to two.

The fix

Making the algorithm explicit, so a copy fails loudly

-- fails in under a second rather than running for four hours
ALTER TABLE orders MODIFY reference VARCHAR(64) NOT NULL,
  ALGORITHM=INPLACE, LOCK=NONE;

-- ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported.
--   Reason: Cannot change column type INPLACE.
--   Try ALGORITHM=COPY.

-- and the one that IS instant, for a column at the end:
ALTER TABLE orders ADD COLUMN note TEXT NULL, ALGORITHM=INSTANT;

Demanding the algorithm turns “I hope this is online” into a statement that fails immediately when it is not. The error message names the reason, which is the fastest available documentation for what each operation costs, and it belongs in every migration file as a matter of habit.

the three, and what they mean:

  INSTANT   metadata only. milliseconds regardless of size.
            adding a column at the end, renaming a column,
            changing a default, adding a virtual column.

  INPLACE   rebuilds the table without copying it to a new
            file, and mostly allows concurrent writes.
            adding an index, dropping an index, adding a
            foreign key.

  COPY      builds a new table, row by row, holding a
            metadata lock. every column type change.

and LOCK=NONE / SHARED / EXCLUSIVE is a separate axis.

What a metadata lock actually blocks

-- the lock is acquired at the START and released at the END,
-- and it queues behind any open transaction on the table.

-- so a long-running SELECT started before the ALTER blocks
-- the ALTER, and the ALTER then blocks everything after it:

SELECT ... FROM orders;   -- session 1, in a transaction, idle
ALTER TABLE orders ...;   -- session 2, waiting on session 1
INSERT INTO orders ...;   -- session 3, waiting on session 2

-- one idle transaction can stop the entire application.
SET SESSION lock_wait_timeout = 5;   -- fail fast instead

The queueing behaviour is what turns a slow migration into an outage: the ALTER does not need to finish to block writes, it only needs to be waiting. An idle transaction left open by a connection pool is enough to start the pile-up.

Setting lock_wait_timeout low in the migration session means the ALTER gives up rather than queueing, which converts an outage into a failed deploy. That is the correct trade and it needs a retry loop, because the migration will legitimately need several attempts on a busy table.

The external tools, and how they differ

pt-online-schema-change
  creates a copy, adds TRIGGERS to the original so writes
  are mirrored, copies in chunks, then swaps.
  → every write pays the trigger cost, synchronously,
    for the whole run
  → conflicts with existing triggers
  → foreign keys referencing the table are awkward

gh-ost
  creates a copy, reads the BINLOG as a replica would,
  applies changes asynchronously, then swaps.
  → no triggers, no synchronous cost on writes
  → needs row-based binlog, and somewhere to read it
  → pausable and throttleable mid-run

both end with a brief lock for the swap. that moment is
the only genuinely dangerous part of either.

The synchronous trigger cost is what makes the Percona tool risky under heavy write load — it roughly doubles the work of every write for the duration, which on a four-hour run is four hours of degraded write performance. gh-ost being pausable is the property that matters at three in the morning.

gh-ost 
  --host=db-replica-01 --database=shop --table=orders 
  --alter="MODIFY reference VARCHAR(64) NOT NULL" 
  --max-load='Threads_running=40' 
  --critical-load='Threads_running=120' 
  --chunk-size=1000 
  --max-lag-millis=1500 
  --postpone-cut-over-flag-file=/tmp/gh-ost.postpone 
  --execute

The postpone flag is the piece that makes this usable: the copy runs to completion and then waits for somebody to delete a file before the cut-over. That separates the four hours of work from the one dangerous second, and lets the cut-over happen when a person is watching.

The load thresholds are what stop the migration making things worse. max-load pauses the copy and critical-load aborts it, and choosing the numbers requires knowing what the table normally does — which is another argument for having the metric before starting.

Expand and contract, so the deploy and the migration are independent

the pattern that removes the coupling entirely:

  1  EXPAND    add the new column, nullable, no default.
                instant. deploy nothing.

  2  BACKFILL  a job, in chunks, at a rate you choose.
                interruptible, resumable, no lock.

  3  DUAL      deploy code that writes BOTH columns and
     WRITE      reads the old one. reversible.

  4  SWITCH    deploy code that reads the new one.
                reversible.

  5  CONTRACT  drop the old column, weeks later.
                instant, and the only irreversible step.

five deploys instead of one. every step is reversible
except the last, and the last is not urgent.

This is more work than an ALTER and it is the only version where a rollback at any point is a deploy rather than a database restore. The step that people skip is the gap between four and five — dropping the column the same afternoon means a rollback needs it back, which is another four-hour copy.

// step 2, as a resumable job
public function handle(): void
{
    Order::query()
        ->whereNull('reference_v2')
        ->orderBy('id')
        ->chunkById(1000, function (Collection $orders): void {
            DB::table('orders')->upsert(
                $orders->map(fn ($o) => [
                    'id' => $o->id, 'reference_v2' => $o->reference,
                ])->all(),
                ['id'], ['reference_v2'],
            );

            usleep(50_000);      // deliberate, to bound the load
        });
}

The deliberate sleep is what makes a backfill a background activity rather than an incident. Without it a chunked backfill runs as fast as the database allows, which is fast enough to saturate the write path and looks exactly like the problem being avoided.

chunkById rather than chunk matters because rows are being written during the backfill: offset-based chunking skips rows when the set shifts underneath it, which produces a backfill that silently misses some. The whereNull filter is what makes it resumable after an interruption.

Rehearsing on a production-sized copy

# the only estimate worth having
$ ./bin/restore-to-scratch --from=latest --tables=orders
restored 41,204,118 rows in 18m22s

$ time gh-ost --alter="MODIFY reference VARCHAR(64)" ... --execute
Copy: 41204118/41204118 100.0%; Applied: 88104; Backlog: 0/1000
real	3h51m04s

# 3h51 on an idle copy. under production write load, the
# throttle will make it longer — plan for 6h.

Timing it on a restored copy is the difference between “this will take a while” and a number somebody can plan around. The estimate from staging was four hundred milliseconds, which was wrong by four orders of magnitude and was the reason the change was approved without discussion.

Verifying it worked

mysql> SHOW CREATE TABLE ordersG
  `reference` varchar(64) NOT NULL,

mysql> SELECT COUNT(*) FROM orders WHERE reference IS NULL;
0

mysql> SELECT COUNT(*) FROM orders o
    -> JOIN orders_ghost_backup b ON b.id = o.id
    -> WHERE b.reference <> o.reference;
0

-- and the write path, throughout the run:
--   p95 insert latency: 4.1ms → 6.8ms → 4.2ms
--   errors: 0

Comparing every row against the retained original table is the check that the copy was faithful, and gh-ost leaves that table behind precisely so it can be done. Dropping it immediately is the reflex to resist — it is the only rollback available for the hour after the cut-over.

The insert latency rising by sixty per cent during the run and returning afterwards is the expected shape and is worth recording, because the next migration on this table can be planned against a real number rather than a hope.

What this costs

A schema change is now a project rather than a file: a rehearsal, a tool with a dozen flags, a cut-over window and five deploys for the expand-contract path. That is a substantial tax on every change to a large table, and the alternative is the eleven minutes of downtime this started with.

It also means the migration is no longer in the migration file, which is a real loss. The schema history for this table now says “see runbook 14” for one column, and reconstructing what happened requires reading a shell command in a wiki page. Recording the gh-ost invocation in the migration file as a comment is the compromise that keeps the history readable.

The deeper cost is that the tooling has to be maintained and rehearsed. gh-ost run for the first time during an incident is a tool nobody knows, with flags that matter, against a table that is on fire — and the failure mode of the cut-over step is a table swap that leaves the application pointing at the wrong one. It belongs in a game day before it belongs in a runbook.