Altering a 30-million-row table without downtime

The change was one column: sku VARCHAR(32) becoming VARCHAR(64), because a supplier had started issuing longer codes. On order_items, which holds 30 million rows, that ALTER TABLE rebuilds the table and blocks writes for the duration. Checkout writes to that table.

The symptom

mysql> ALTER TABLE order_items MODIFY sku 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.

mysql> ALTER TABLE order_items MODIFY sku VARCHAR(64) NOT NULL;
Query OK, 30114882 rows affected (38 min 12.44 sec)

Thirty-eight minutes on staging, on a machine with the same specification and no other load. Reads would have continued throughout; writes would have queued behind a metadata lock and then behind the table lock, which in practice means the checkout returns a gateway timeout for the better part of an hour.

Why it happens

MySQL 5.6 introduced online DDL, and it is easy to conclude from that headline that ALTER TABLE no longer locks. What 5.6 actually gained is a set of operations that can be performed in place with concurrent DML permitted — adding an index is the obvious one, and it genuinely is online.

Changing a column type is not in that set. Widening a VARCHAR looks like it should be, since the storage format for a short string is unchanged, but 5.6 has no in-place path for it: the length prefix of a VARCHAR column crosses from one byte to two at 255 bytes, and rather than special-case the cases where it does not, the server rebuilds. The error message is unusually helpful about this — it names the reason and the algorithm that would work.

-- online in 5.6: concurrent reads and writes throughout
ALTER TABLE order_items ADD INDEX idx_sku (sku),      ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE order_items DROP INDEX idx_sku,           ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE order_items ADD COLUMN note VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

-- not online in 5.6: the server rebuilds and writes wait
ALTER TABLE order_items MODIFY sku VARCHAR(64) NOT NULL;
ALTER TABLE order_items CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE order_items DROP PRIMARY KEY, ADD PRIMARY KEY (id, order_id);

The second group is worth committing to memory, because the first group is what everyone remembers about 5.6 and the second is what causes the incident. Adding a column is fine; changing one is not. Adding an index is fine; changing the primary key is not.

The fix

A shadow table, triggers, and a chunked backfill

The mechanism is old and well understood: create an empty copy of the table with the new definition, put triggers on the original so every write reaches both, copy the existing rows across in small batches, then swap the two with an atomic rename. Percona Toolkit implements exactly this, and there is no good reason to hand-write it.

pt-online-schema-change 
  --alter 'MODIFY sku VARCHAR(64) NOT NULL' 
  --alter-foreign-keys-method=rebuild_constraints 
  --chunk-size=1000 
  --max-lag=2 --check-interval=1 
  --max-load 'Threads_running=40' 
  --critical-load 'Threads_running=120' 
  --print --dry-run 
  D=shop,t=order_items,u=schema,p=...

The triggers it installs are worth reading rather than trusting, because they are the part that can corrupt data if the table has anything unusual about it.

CREATE TRIGGER pt_osc_shop_order_items_ins AFTER INSERT ON order_items
FOR EACH ROW
  REPLACE INTO _order_items_new (id, order_id, sku, qty, price_cents)
  VALUES (NEW.id, NEW.order_id, NEW.sku, NEW.qty, NEW.price_cents);

CREATE TRIGGER pt_osc_shop_order_items_upd AFTER UPDATE ON order_items
FOR EACH ROW
  REPLACE INTO _order_items_new (id, order_id, sku, qty, price_cents)
  VALUES (NEW.id, NEW.order_id, NEW.sku, NEW.qty, NEW.price_cents);

REPLACE rather than INSERT is what makes the copy and the live writes safe to interleave: a row written by a trigger and then copied by the backfill, or the other way round, converges to the same value either way. That is also why the table must have a unique key — without one there is nothing for REPLACE to match on, and the tool refuses to run.

Chunk size is decided by replication lag

The obvious way to size the batches is by the clock: copy a thousand rows, sleep, repeat. It is the wrong instrument. The primary absorbs the copy easily; the replica applies it single-threaded, and lag is what turns a background migration into a visible incident, because the reporting queries and the read-only pages are served from there.

Copying `shop`.`order_items`:  12% 01:41:22 remain
Replica lag 3s on replica-1, waiting.
Pausing because Threads_running=44.
Copying `shop`.`order_items`:  31% 01:12:04 remain

With --max-lag=2 the tool checks after every chunk and stops until the replica catches up. The migration therefore takes as long as it takes — four hours here rather than thirty-eight minutes — and the trade is deliberate: nothing was ever unavailable, and the run paced itself down automatically through the evening peak without anybody watching it.

The rename

The only moment anything is genuinely locked is the swap, and it is a single atomic statement.

RENAME TABLE order_items     TO _order_items_old,
             _order_items_new TO order_items;

Both tables move in one operation, so there is no instant at which order_items does not exist. It blocks for a few milliseconds while waiting for open transactions to finish — which is the one thing that can go wrong, since a long-running SELECT against the table will hold the rename, and everything else queues behind the rename. Run it when nothing is running a twenty-minute report.

Which half deploys first

For four hours the schema is mid-change, and for a moment around the rename the application may see either definition. That is only survivable if the code tolerates both, which decides the deploy order: the widening ships first, alone, and the code that writes a 64-character SKU ships afterwards.

// deployed before the ALTER: still writes at most 32, reads any length
const SKU_MAX = 32;

// deployed after the rename, once the column is confirmed wide
const SKU_MAX = 64;

Reversing that order truncates data rather than raising an error, because MySQL in the default configuration is happy to cut a string down to the column width and emit a warning nobody reads. Two deploys instead of one, and the second is a two-line change — a cheap way to make the migration itself unremarkable.

Verifying it worked

The tool reports success; that is not the same as the data being identical. Two checks, and neither is expensive.

$ pt-table-checksum --replicate=percona.checksums 
    --databases shop --tables order_items
            TS ERRORS  DIFFS     ROWS  CHUNKS  TABLE
10-23T22:14:07      0      0 30114882    3021  shop.order_items

$ mysqldumpslow -s c -t 3 /var/log/mysql/slow.log | head -5
# no order_items entries during the backfill window

Zero differing chunks across three thousand of them, and the slow log clean for the whole four hours. The row count matching is the weaker of the two checks and the one people stop at — a checksum per chunk is what catches a trigger that was not firing for a code path nobody remembered.

What this costs

Three triggers sit on a hot table for four hours, and every write pays for them. Measured on this table it was about 8% added to the insert latency, which was invisible in the application and would not have been on something writing ten times as often. The tool cannot install its triggers if the table already has one of the same type, which is a real constraint on tables that use them for auditing.

Disk is the other one: two copies of a 30 million row table, plus the binary log traffic for every copied chunk, which on this table was 11 GB of binlog for a change that added nothing. The replica needs the space too. Running out mid-copy leaves the shadow table and the triggers behind, and cleaning that up by hand is the failure mode worth rehearsing before starting.

It is also worth saying that the alternative was not unavailable. A forty-minute maintenance window at four in the morning is a legitimate answer, and on a smaller table it is the right one — the machinery here earns its place only because the table is large enough that the window would have to be long enough to need announcing.