Pilotbase on MySQL 8.4: an INSTANT ALTER TABLE waiting for the table metadata lock behind one open transaction, with INSERTs and a SELECT on orders queued behind it

On a small table you never think about ALTER TABLE. On a table with ten million rows that the application writes to all day, the same statement can finish in a millisecond or block every INSERT for a minute, and the difference is often a single word in the column definition. MySQL 8.4 runs each change one of three ways: INSTANT (only the table's metadata changes), INPLACE (InnoDB works on the table but lets writes continue) or COPY (it builds a new table row by row and writes wait until it's done). When none of them is good enough there are two outside tools, gh-ost and pt-online-schema-change, that copy the table themselves while the application keeps writing.

We timed all of it on one table. The setup: MySQL 8.4.11 on a Mac mini, an orders table with 10,000,000 rows (570 MB of data, 398 MB of indexes), and a writer thread running the whole time, doing one INSERT and one UPDATE by primary key per loop, autocommit, like a busy application. Unbothered it managed 3,439 loops a second with a worst-case loop of 9.2 ms. For each ALTER we recorded how long it ran, how many writes got through meanwhile, and the longest stretch in which no write finished at all: that's the stall your users would notice.

sql
CREATE TABLE orders (
  id bigint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  customer_id int unsigned NOT NULL,
  status varchar(12) NOT NULL,
  amount decimal(10,2) NOT NULL,
  created_at datetime NOT NULL,
  note varchar(40) NULL,
  KEY orders_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;   -- 10,000,000 rows

Eleven ALTERs, measured

We ran each statement first with ALGORITHM=INSTANT, then ALGORITHM=INPLACE, LOCK=NONE, then ALGORITHM=COPY, and kept the first one MySQL accepted. That's also what MySQL picks on its own when you leave the clause out: the cheapest one the change allows. A refused attempt costs nothing; MySQL checks before it starts.

text
ALTER on 10M rows                          runs as             time      writes during   longest stall
ADD COLUMN coupon varchar(20) NULL         INSTANT            0.00 s               2          2.3 ms
ADD COLUMN ... NOT NULL DEFAULT 'web'
   AFTER status (middle of the table)      INSTANT            0.00 s               1          2.4 ms
DROP COLUMN coupon                         INSTANT            0.01 s               1          5.4 ms
RENAME COLUMN note TO memo                 INSTANT            0.00 s               1          2.3 ms
ALTER COLUMN memo SET DEFAULT ''           INSTANT            0.00 s               1          1.6 ms
MODIFY memo varchar(40) -> varchar(60)     INPLACE, no copy   0.00 s               2          1.2 ms
ADD INDEX (status, created_at)             INPLACE            16.48 s         17,291          2.4 s
MODIFY created_at ... NOT NULL -> NULL     INPLACE, rebuild   49.38 s         37,784          3.0 s
ALTER TABLE orders FORCE (rebuild)         INPLACE, rebuild   52.17 s         28,051          3.6 s
MODIFY memo varchar(60) -> varchar(100)    COPY               40.24 s               2         40.2 s
MODIFY amount decimal(10,2) -> (12,2)      COPY               67.46 s               1         67.5 s

Three groups, and the gap between them is the point. INSTANT changes took no measurable time on ten million rows. INPLACE changes took 16 to 52 seconds, and the application kept writing, but slower: about 1,050 loops a second during the index build and 540 to 760 during the rebuilds, against 3,439 normally, with a pause of two to four seconds at the end, when MySQL briefly locks the table to apply the changes that arrived meanwhile and swap the new version in. COPY changes blocked writes for their whole duration. During the 67-second decimal change our writer completed exactly one loop.

What makes a change INSTANT

In 8.4, adding a column is INSTANT in any position (since 8.0.29; before that only at the end), dropping a column is INSTANT (also since 8.0.29), and so are renaming a column, setting or dropping a default, and adding values at the end of an ENUM or SET. A NOT NULL column with a default is INSTANT too: MySQL stores the default once and hands it out for old rows. ADD COLUMN ... DEFAULT (expression) is not; an expression default in parentheses makes it a COPY.

Any change to a column's type is a COPY, with one exception: a longer VARCHAR is INPLACE and doesn't touch the rows if the column's maximum length in bytes stays on the same side of 255. That's why our two VARCHAR changes behaved so differently. In utf8mb4 every character can take 4 bytes, so varchar(40) is 160 bytes and varchar(60) is 240: same side, done in a millisecond. varchar(100) is 400 bytes, which needs a 2-byte length prefix on every row, so the table was copied for 40 seconds. In utf8mb4 the limit is varchar(63); in latin1 it's varchar(255). Changing decimal(10,2) to decimal(12,2), int to bigint, datetime to timestamp, or a column's character set: all COPY.

NULL to NOT NULL (or the reverse), moving a column with FIRST/AFTER in a MODIFY, and adding or dropping the primary key are INPLACE but rebuild the table: slower, writes go on. Adding a secondary index is INPLACE without a rebuild. Adding a foreign key is COPY unless you run it with SET foreign_key_checks = 0 first, in which case it's INPLACE and existing rows aren't checked.

Always write the algorithm clause

Leaving the clause out means "whatever is cheapest", and when the cheapest is COPY, MySQL just does it. Writing ALGORITHM=INPLACE, LOCK=NONE (or ALGORITHM=INSTANT) turns that into an error you get before anything happens:

sql
ALTER TABLE orders MODIFY amount decimal(12,2) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ALTER TABLE orders ADD INDEX orders_status_created (status, created_at), ALGORITHM=INSTANT;
ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

Put the clause in every migration that touches a big table. If the migration tool you use generates its own ALTER statements, check what it sends.

The 64-version limit on INSTANT

INSTANT add and drop work by keeping several row formats for one table: each instant ADD or DROP COLUMN creates a new "row version", and old rows are read in their old format. MySQL 8.4 allows 64 of them (MySQL 9.1 raised it to 255). We added and dropped a column in a loop on the 10M-row table:

sql
SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME = 'alt/orders';

-- after 32 ADD + DROP pairs, TOTAL_ROW_VERSIONS = 64:
ALTER TABLE orders ADD COLUMN tmp32 int NULL, ALGORITHM=INSTANT;
ERROR 4092 (HY000): Maximum row versions reached for table alt/orders. No more columns can be added or dropped
instantly. Please use COPY/INPLACE.

-- the same ADD COLUMN with no ALGORITHM clause:
-- INPLACE with a rebuild: 65.38 s, longest write stall 7.7 s, TOTAL_ROW_VERSIONS back to 0

Without the clause, the column that took 0.00 s the last 64 times took 65 seconds and stalled writes for 7.7 seconds, with no hint in the statement why. A table whose migrations add and drop columns often will reach the limit sooner or later. Check TOTAL_ROW_VERSIONS before a deploy; any rebuild (OPTIMIZE TABLE, ALTER TABLE ... FORCE) resets it to 0, so you can do that at a quiet moment instead of finding out during a release.

The trap in every ALTER: the metadata lock

Even an INSTANT change needs an exclusive metadata lock on the table for a moment. It can't get one while any open transaction has touched the table, even with a plain SELECT. And while the ALTER waits, every new query on the table, reads included, queues behind it. We left one transaction open after a single-row SELECT and ran an INSTANT ADD COLUMN:

sql
-- session A
BEGIN;
SELECT id FROM orders WHERE id = 1;
-- (left open; committed 8 s later)

-- session B
ALTER TABLE orders ADD COLUMN coupon3 varchar(20) NULL, ALGORITHM=INSTANT;
-- 8.01 s, "Waiting for table metadata lock"; our writer: 1 loop in 8 s, stall 8,014 ms

-- the same with a timeout
SET SESSION lock_wait_timeout = 2;
ALTER TABLE orders ADD COLUMN coupon4 varchar(20) NULL, ALGORITHM=INSTANT;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction   -- after 2.15 s
Pilotbase on MySQL 8.4: the process list with an INSTANT ADD COLUMN waiting 21 s for the table metadata lock, and three INSERTs and a SELECT on orders waiting behind it

A zero-second change stopped all writes for as long as the forgotten transaction stayed open. MySQL's default lock_wait_timeout is 31,536,000 seconds, a year. Set it to a few seconds in the session that runs the migration and retry on error 1205; a failed attempt costs a two-second pause instead of an outage. Before a big change, look for long transactions: SELECT * FROM information_schema.innodb_trx ORDER BY trx_started.

When the built-in options aren't enough

Three situations push people to an online schema change tool. The change is a COPY (a type change) and the table is too big to block. The change is INPLACE but the table is replicated: the ALTER reaches the replicas only after it has finished on the source, and each replica then runs it in one piece, so a 50-second rebuild becomes 50 seconds of replica lag, and longer on a replica with less spare capacity. Or you want to throttle the work, pause it, or choose the moment of the switch yourself.

Both tools work the same way in outline: create an empty copy of the table with the change applied, copy the rows over in small chunks, keep the copy in step with new writes, then swap the two tables with a quick RENAME. They differ in how they keep in step. pt-online-schema-change (Percona Toolkit) puts triggers on the original table, so every INSERT, UPDATE and DELETE also writes to the copy in the same transaction. gh-ost (from GitHub) uses no triggers: it reads the changes from the binary log, as a replica would, and applies them to the copy itself.

We ran both on the same table and the same kind of change, a decimal precision change that MySQL can only do as a COPY, with the writer running. By then the writer had grown the table to 12.8 million rows for gh-ost and 15.1 million for pt-osc; we ran each tool with nothing else on the machine (an earlier gh-ost run that overlapped our other tests is left out). Native COPY is the 10-million-row run from the table above.

text
decimal precision change, writer running     rows       time       writer loops/s   longest stall   per million rows
ALTER ... ALGORITHM=COPY (native)            10.0M      67.5 s            0 (blocked)     67.5 s          6.7 s
pt-online-schema-change 3.7.1, defaults      15.1M     209.6 s              502            1.9 s          13.9 s
gh-ost 1.1.11, --chunk-size=1000             12.8M   1,811.8 s            1,274            3.6 s         141.3 s
nothing running (baseline)                                                 3,439            9.2 ms

pt-osc copied about 72,000 rows a second and left the writer a seventh of its normal speed: every write now fired a trigger that wrote the same row into the copy, in the same transaction. gh-ost took more than eight times as long, at about 7,100 rows a second, and left the writer with more than a third of its speed; it copies in chunks of 1,000 rows (its default, which we kept) and gives the stream of new changes from the binary log priority over copying. Its cut-over, an atomic RENAME under a lock, took 1.05 seconds by its own log ("Lock & rename duration: 1.045884s", with queries on orders blocked meanwhile), and our longest stall during the whole run was 3.6 seconds. pt-osc's swap took under a second; its longest stalls, 1.9 s, came while copying.

Neither number is "the" speed of the tool. Both throttle by design: pt-osc pauses when Threads_running passes 25 or a replica lags, gh-ost on --max-load, --max-lag-millis (replica lag, 1.5 s by default) and a flag file you can touch to pause it, and it takes commands on a Unix socket while it runs (echo status | nc -U /tmp/gh-ost.alt.orders.sock, also throttle, no-throttle, chunk-size=5000). A bigger chunk size makes gh-ost faster at the cost of more load. What the numbers do show: both turned a 67-second total write block into a few seconds of stalls, and you pay for that in time, in disk (a full second copy of the table, plus with gh-ost the binary logs: about 3.1 GB for this run) and in slower writes for as long as it runs.

Foreign keys decide a lot

gh-ost doesn't support foreign keys at all, on either side. With order_notes.order_id referencing orders.id, it stopped before doing anything:

text
ERROR found 1 parent-side foreign keys on `alt`.`orders`. Parent-side foreign keys are not supported. Bailing out

pt-online-schema-change refuses too, unless you choose how to handle the tables that point at the one you change (--alter-foreign-keys-method). With rebuild_constraints it ran (248.8 s on about 13 million rows, the writer at 334 loops a second, longest stall 1.9 s) and then re-pointed order_notes' constraint at the new table with an ALTER on order_notes: fine for our 1,000-row child, but that ALTER is a normal, possibly blocking ALTER on the child table, and on a big child it's a problem of its own. The other choice, drop_swap, briefly drops the original table and has no FK protection for a moment. One more side effect: pt-osc names the foreign keys on the new table with a leading underscore, so orders_customer_fk came back as _orders_customer_fk, and order_notes' became _order_notes_ibfk_1. A later migration that drops a constraint by its old name will fail.

Which one to use

text
the change                                   on a big, busy table
add / drop / rename a column, set a default  INSTANT; check TOTAL_ROW_VERSIONS first
longer VARCHAR, same side of 255 bytes        INPLACE, instant in practice
add an index                                 INPLACE, LOCK=NONE; slower writes for a while
NULL <-> NOT NULL, rebuild, reorder           INPLACE with rebuild; mind replica lag
any type change, VARCHAR across 255 bytes    COPY: use pt-online-schema-change or gh-ost
add a foreign key                            foreign_key_checks = 0 + INPLACE, check orphans first
replicated, can't afford replica lag          gh-ost (throttles on lag) or pt-osc --max-lag
table has foreign keys                       pt-osc with --alter-foreign-keys-method; not gh-ost

every time: SET SESSION lock_wait_timeout = 5; ALGORITHM=... in the statement; no long transactions open

You don't have to remember the table. Paste the migration into sqlInspector, one of our free data tools, and set it to MySQL: it marks each ALTER as INSTANT, INPLACE or COPY the way MySQL 8.4 runs it, warns about the missing lock_wait_timeout, and writes the statement again with the algorithm clause that makes MySQL refuse instead of copying. We ran these eleven statements through it: it matched the measured algorithm for nine from the ALTER text alone. For the two MODIFYs it can't know the old column type, so it marks them "depends on the old type", lists the rules above, and adds ALGORITHM=INPLACE, LOCK=NONE, which would have stopped the 67-second copy before it started.

Paste a migration and see, statement by statement, whether MySQL 8.4 runs it INSTANT, INPLACE or as a COPY that blocks writes, with the safe way to write it.

Open sqlInspector

Run the same statements against Postgres, MySQL and SQLite side by side, with the results in a grid you can export. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Related: Adding NOT NULL, a foreign key or a check to a big Postgres table without a long lock · MySQL slow query log: find the queries worth fixing with pt-query-digest and performance_schema

Sources: MySQL 8.4 Reference Manual, Online DDL Operations (INSTANT, INPLACE and COPY per operation, the VARCHAR 255-byte rule, the 64 row versions limit, raised to 255 in 9.1), Metadata Locking and lock_wait_timeout, INFORMATION_SCHEMA.INNODB_TABLES; gh-ost documentation (requirements and limitations: no foreign keys, binlog_format=ROW; cut-over; interactive commands; throttling); Percona Toolkit 3.7 documentation, pt-online-schema-change (triggers, --alter-foreign-keys-method, --max-load, chunk-time). All runs our own, 2026-10-24 and 25, MySQL 8.4.11 (Homebrew) on a Mac mini, 2 GB buffer pool, one writer thread; scripts load.sh, harness.py, versions_mdl.py, run_tool.py.