MySQL ALTER TABLE Without Downtime: INSTANT, INPLACE, gh-ost or pt-osc

Running a MySQL ALTER TABLE without downtime on a large table is mostly a question of picking the right method before you press Enter. Some changes are pure metadata and finish instantly even on terabytes. Others rebuild the whole table, need as much free disk as the table itself, and leave every replica behind for hours. And any of them, even the instant ones, can freeze the application if a single forgotten transaction holds the table open. Here is how to tell which case you are in and what to do about it.

Three algorithms, very different costs

InnoDB has three ways to run an ALTER TABLE. The online DDL operations table in the MySQL manual says which ones each change supports:

• INSTANT changes only the data dictionary. The data is not touched, so the size of the table does not matter. In MySQL 8.4 it is the default for operations that support it.
• INPLACE works inside InnoDB without a full table copy through the server layer. Depending on the operation it either only builds something new (such as a secondary index) or rebuilds the table, but in most cases it allows reads and writes while it runs.
• COPY creates a new table and copies every row. Concurrent DML is not allowed. On a large table, that means an outage.

Always state the algorithm and lock level explicitly, for example ALGORITHM=INSTANT or ALGORITHM=INPLACE, LOCK=NONE. If the change cannot be done that way, MySQL returns an error instead of quietly choosing something heavier. An error on a test server is far better than an unexpected table copy on production.

What is instant, and what is not

The good news is that the most common changes are cheap. According to the manual, these support INSTANT:

• adding a column, at any position;
• dropping a column;
• renaming a column;
• setting or dropping a column default;
• modifying the definition of an ENUM or SET column (with the conditions the manual describes).

ALTER TABLE orders ADD COLUMN source VARCHAR(32) NULL, ALGORITHM=INSTANT;

-- how many instant row versions has the table used? (limit: 64 in 8.4)
SELECT NAME, TOTAL_ROW_VERSIONS
FROM INFORMATION_SCHEMA.INNODB_TABLES
WHERE NAME = 'shop/orders';

Two limits matter on long-lived tables. Each instant add or drop creates a new row version, and MySQL 8.4 allows at most 64 (255 as of 9.1). After that you get ERROR 4092 (HY000): Maximum row versions reached and the next change has to rebuild the table. A rebuild with INPLACE, COPY or OPTIMIZE TABLE resets the counter. Instant column addition also does not work on tables with ROW_FORMAT=COMPRESSED, tables with a FULLTEXT index or temporary tables.

These are the operations that hurt on a large table:

• Changing a column's data type, for example INT to BIGINT, is only supported with ALGORITHM=COPY. No concurrent DML. This is the classic case for gh-ost or pt-online-schema-change.
• Extending a VARCHAR is in-place and metadata-only as long as the number of length bytes stays the same. Up to 255 bytes uses one length byte, 256 bytes and more use two. Crossing that boundary needs a table copy. Note that the limit is in bytes, not characters: with utf8mb4 (up to 4 bytes per character), going from VARCHAR(63) to VARCHAR(64) already crosses it.
• Making a column NULL or NOT NULL, reordering columns and adding a primary key rebuild the table in place. DML is allowed, but the table is rewritten.
• Adding a secondary index is in-place and does not rebuild the table, but it still reads the whole table and sorts the index, so on a big table it takes a long time.

With an explicit algorithm, a change that cannot be done in place fails right away. The manual's example, for a single-byte character set going from VARCHAR(255) to VARCHAR(256):

ALTER TABLE t ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

The metadata lock queue: how an "online" ALTER freezes production

This is one of the most common schema-change incidents, and it happens even with instant changes. Every ALTER TABLE needs an exclusive metadata lock on the table, at least briefly. The manual describes the sequence exactly:

1. Some transaction has read from the table and has not committed yet. It holds a shared metadata lock. It might be a report, a batch job, or a connection left idle in a transaction by the application.
2. Your ALTER TABLE asks for the exclusive lock and waits for that transaction.
3. Every new query on the table now waits behind the pending ALTER, including plain SELECTs.

From the outside it looks like the table is down. Connections pile up, the pool runs out, and the application starts failing, all because of one transaction that was doing nothing. SHOW FULL PROCESSLIST shows the waiting sessions.

The default lock_wait_timeout is 31536000 seconds, which is one year. So by default the ALTER will wait, and keep everyone else waiting, practically forever. Before any DDL on a busy table, lower it in your own session and check for long transactions first:

-- give up after 5 seconds instead of queueing everyone behind us
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

If the lock is not granted in 5 seconds, the ALTER fails, the queue clears and you can try again later. A failed attempt costs nothing, while a blocked table costs an incident. This metadata lock wait is different from InnoDB row lock waits, which we cover in our lock wait timeout article.

Large tables: disk, logs and replicas

On a table of a few gigabytes, an in-place rebuild is a coffee break. On hundreds of gigabytes, check these before you start.

• Disk space. According to the space requirements page, a rebuild can create an intermediate table in the same directory as the original, up to the size of the original table. Temporary sort files go to the MySQL temporary directory and can need as much space as the table plus its indexes. If tmpdir is small, point innodb_tmpdir at a larger volume. Running out of disk in the middle of a rebuild on production is a bad day.
• The online alter log. Writes made during an in-place rebuild are recorded in a temporary log, limited by innodb_online_alter_log_max_size (default 128 MB). If heavy writes fill it, the operation fails with DB_ONLINE_LOG_TOO_BIG, and uncommitted concurrent DML is rolled back. A larger limit allows more writes, but it also makes the locked phase at the end longer, because the logged changes have to be applied.
• No throttling. The manual lists the limitations for large tables: an online DDL operation cannot be paused or throttled, and rolling it back after a failure can be expensive.
• Replicas. The replica starts the ALTER only after it finished on the source, and the writes made on the source in the meantime are applied on the replica only after the ALTER completes there. A six-hour rebuild means roughly six hours of replica lag. If your reads, backups or failover depend on replicas, this is often the deciding factor. Our MySQL replication lag guide covers how to monitor it.

You can follow a long ALTER through Performance Schema stage events. WORK_ESTIMATED may change as it runs, so treat it as a rough indicator:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES'
 WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES'
 WHERE NAME LIKE '%stages%';

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
FROM performance_schema.events_stages_current;

gh-ost and pt-online-schema-change

When a change needs a rebuild or a copy and the table is large and busy, online schema change tools are usually the better choice. Both create a new table with the new structure, copy rows in small chunks, keep it in sync with ongoing writes and finally swap the tables. The difference is how they keep it in sync.

pt-online-schema-change from Percona Toolkit uses triggers on the original table and switches the tables with an atomic RENAME TABLE. Things to know:

• In most cases it requires a PRIMARY KEY or UNIQUE index, because the DELETE trigger needs one.
• Existing triggers on the table are a problem, unless you use --preserve-triggers.
• Foreign keys that reference the table need a decision via --alter-foreign-keys-method. The drop_swap method is faster but leaves a moment when the table does not exist.
• It pauses when --max-load is exceeded (default Threads_running=25) and aborts at --critical-load (default Threads_running=50). Without --execute, it only runs its checks.

gh-ost from GitHub does not use triggers. It reads changes from the binary log instead, so writes to the original table do not fire extra triggers. Its requirements:

• row-based binary logs with FULL row image;
• no foreign keys and no triggers on the table;
• a shared PRIMARY KEY or UNIQUE key between the old and new table.

Its strength on large tables is control: it throttles on replica lag with --max-lag-millis, on load with --max-load, bails out on --critical-load, and can postpone the final cut-over until you remove a flag file, so the swap happens when you choose:

gh-ost --host=replica-1 --database=shop --table=orders \
  --alter="MODIFY amount DECIMAL(14,2) NOT NULL" \
  --max-lag-millis=1500 --max-load=Threads_running=30 \
  --critical-load=Threads_running=80 \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

Both tools copy the table, so they need free space for a full second copy of it. The copy consists of ordinary writes, so it also goes into the binary log and to every replica. It is slower than a native rebuild, but it is throttled, pausable and keeps replicas close to current, which is usually what you want on production.

A checklist before ALTER on a large table

1. Find out the algorithm: run the statement with an explicit ALGORITHM on a copy with the same schema and see whether MySQL accepts it.
2. For instant changes, check TOTAL_ROW_VERSIONS.
3. For rebuilds, check free space in the data directory and in tmpdir/innodb_tmpdir.
4. Check for long-running and idle transactions on the table, and set a short lock_wait_timeout in your session.
5. Estimate the duration and replica lag on a full-size copy, and decide whether native DDL or a tool fits.
6. Have a fresh, verified backup and decide in advance how to stop or roll back.