Schema changes that do not need a maintenance window

The migration was a column rename. It ran in staging in under a second, against a table with four thousand rows. In production the same table had forty million, and the deploy took the site down for nineteen minutes before anyone worked out what was holding it.

The symptom

Connections piled up until the pool was exhausted. The interesting part is what they were waiting on: not the table being altered, and not each other.

mysql> SHOW PROCESSLIST;
+-----+------+---------------------------------+---------------------------+
| Id  | Time | State                           | Info                      |
+-----+------+---------------------------------+---------------------------+
| 412 | 1140 | copy to tmp table               | ALTER TABLE orders ...    |
| 887 | 1103 | Waiting for table metadata lock | SELECT * FROM orders W... |
| 891 | 1102 | Waiting for table metadata lock | SELECT id FROM orders ... |
| 903 | 1101 | Waiting for table metadata lock | INSERT INTO orders ...    |
+-----+------+---------------------------------+---------------------------+
214 rows in set

One ALTER copying a table, and two hundred queries queued behind a metadata lock — including plain reads, which is the part that surprises people. A read does not conflict with a copy, but it does need the table definition, and the definition is what the lock protects.

Why it happens

MySQL 5.6 introduced online DDL and the phrase did a lot of damage, because online means “in place and non-blocking” for some operations and “copies the entire table” for others, and the two look identical in a migration file.

-- in place, no copy, no lock beyond a moment
ALTER TABLE orders ADD COLUMN note VARCHAR(255) NULL, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.31 sec)

-- copies the table. 40M rows.
ALTER TABLE orders CHANGE customer_ref customer_id BIGINT, ALGORITHM=INPLACE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported. Try ALGORITHM=COPY.

-- which is the check worth putting in CI: state the algorithm, and
-- let the server refuse rather than silently choosing COPY.

Naming the algorithm explicitly turns a nineteen-minute outage into an error message during review. It is one clause, it costs nothing, and it should be in every migration that touches a large table.

The second half of the problem is that even a genuinely in-place alter needs a brief exclusive lock at the start and end — and brief means “as soon as every currently running query on that table finishes”. A long-running report holds the door open, and everything arriving in the meantime queues behind the waiting ALTER.

The fix

Expand, migrate, contract

The rename was never one change. It is three, spread across three releases, and each one leaves the application working with both the old and the new shape.

// release 1 — expand. add the new column, nothing reads it.
Schema::table('orders', function (Blueprint $table) {
    $table->unsignedBigInteger('customer_id')->nullable()->after('customer_ref');
});

// the application writes both from this release onward
$order->customer_ref = $id;
$order->customer_id = $id;

// release 2 — migrate. backfill in batches, then read the new column.
// release 3 — contract. stop writing the old one, then drop it.

Nothing in that sequence takes a lock for longer than adding a nullable column, which is genuinely instant in 5.7. The cost is three deploys and a period where two columns hold the same value, and that period has to be tolerated rather than rushed.

The backfill, which is where this usually goes wrong

// not this — one statement, one transaction, one very long lock
// DB::statement('UPDATE orders SET customer_id = customer_ref');

$last = 0;

do {
    $affected = DB::update(
        'UPDATE orders SET customer_id = customer_ref
         WHERE id > ? AND customer_id IS NULL
         ORDER BY id LIMIT 2000',
        [$last]
    );

    $last = DB::table('orders')->where('id', '>', $last)
        ->orderBy('id')->limit(2000)->max('id') ?: $last;

    usleep(200000);            // 200ms. deliberately slow.
} while ($affected > 0);

The sleep is the part that gets removed by someone impatient and then causes replication lag. Two thousand rows every two hundred milliseconds is ten thousand a second, which finishes forty million rows in just over an hour and is invisible in every graph. Removing the sleep finishes in four minutes and puts the replicas twenty seconds behind.

For the alters that still copy

Some changes cannot be expressed as expand-migrate-contract: changing a column type in place, adding a column to a table in the middle rather than at the end, or converting a character set. For those the answer is a tool that does the copy without the lock.

$ pt-online-schema-change 
    --alter 'MODIFY total DECIMAL(12,2) NOT NULL' 
    --max-lag 2 --critical-load Threads_running=60 
    --execute D=shop,t=orders

Copying `shop`.`orders`: 34% 00:41 remain
Replica lag is 2s, sleeping
Copying `shop`.`orders`: 68% 00:19 remain
Successfully altered `shop`.`orders`.

It builds a copy with the new definition, keeps it in sync with triggers, copies in chunks while watching replication lag, and swaps the tables with a rename. The --max-lag and --critical-load flags are the reason to use it rather than writing the same thing badly; both are checked between chunks and both pause rather than fail.

It needs the table to have a primary key, and it will not work on a table that already has triggers in this version. Finding that out during the change rather than before it is a bad afternoon, so both are worth a dry run first.

Verifying it worked

# during the backfill, from a second terminal
$ while true; do mysql -Nse 'SHOW STATUS LIKE "Threads_running"'; sleep 2; done
Threads_running  6
Threads_running  7
Threads_running  6

# and the replica
$ mysql -h replica -Nse 'SHOW SLAVE STATUSG' | grep Seconds_Behind
Seconds_Behind_Master: 0

# after: the two columns agree, everywhere
mysql> SELECT COUNT(*) FROM orders WHERE customer_id <=> customer_ref = 0;
+----------+
| COUNT(*) |
|        0 |
+----------+

The null-safe comparison in that last query is the point: customer_id != customer_ref returns nothing when either side is NULL, which is exactly the rows a backfill is most likely to have missed. It is the difference between a check that passes and a check that means something.

What this costs

Three deploys for one column rename, forever, and the discipline to actually do the third one. A codebase that has adopted this pattern and never contracts accumulates duplicated columns, dual writes that nobody remembers the reason for, and an ORM model with two properties meaning the same thing. Scheduling the contraction as part of the same piece of work — a ticket that exists from the start rather than one created optimistically at the end — is the only thing that reliably prevents it.

There is a genuine argument on the other side: for a table of ten thousand rows this is ceremony, and a maintenance window at four in the morning is cheaper than three releases. That argument is correct, and the only thing worth insisting on is that the row count is checked rather than assumed. Tables grow, and the migration that was fine last year is the one that takes the site down.