Online DDL: ALGORITHM=INSTANT / INPLACE, LOCK=NONE and progress tracking

Command

-- Always state ALGORITHM/LOCK: unsupported operations fail immediately instead of silently falling back to COPY
ALTER TABLE shop.orders ADD COLUMN status TINYINT NOT NULL DEFAULT 0, ALGORITHM=INSTANT;
ALTER TABLE shop.orders RENAME COLUMN note TO remark, ALGORITHM=INSTANT;
ALTER TABLE shop.orders ADD INDEX ix_status (status), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE shop.orders MODIFY remark VARCHAR(200), ALGORITHM=INPLACE, LOCK=NONE;

-- INSTANT row version counter (INSTANT is refused at 64, a rebuild resets it)
SELECT NAME, TOTAL_ROW_VERSIONS FROM information_schema.INNODB_TABLES WHERE NAME = 'shop/orders';

-- Progress of a long ALTER (enable the instruments once)
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_stages_%';
SELECT t.PROCESSLIST_ID, s.EVENT_NAME, s.WORK_COMPLETED, s.WORK_ESTIMATED,
       ROUND(100 * s.WORK_COMPLETED / NULLIF(s.WORK_ESTIMATED, 0), 1) AS pct_done
FROM performance_schema.events_stages_current s
JOIN performance_schema.threads t ON t.THREAD_ID = s.THREAD_ID
WHERE s.EVENT_NAME LIKE 'stage/innodb/alter%';

What it does

INSTANT changes only the data dictionary and finishes immediately regardless of table size. INPLACE + LOCK=NONE processes the table in place while allowing concurrent reads and writes. The last query shows which stage a running ALTER is in and its percentage.

When to use it

Before schema changes on production tables; to know which operations can run without downtime and to estimate when a long ALTER will finish.

Caution

INSTANT and INPLACE still take a brief exclusive metadata lock at start and end; if a long-running transaction holds the table, the ALTER waits and every query behind it queues up (see metadata lock diagnosis). Lower lock_wait_timeout in the session (SET SESSION lock_wait_timeout = 5). DML arriving during INPLACE is logged up to innodb_online_alter_log_max_size; if exceeded, the ALTER rolls back at the end. Column type changes, charset conversion and PK changes need COPY and block writes; on large tables use gh-ost / pt-online-schema-change. On a replica the ALTER runs single-threaded for the same duration; a large INPLACE ALTER causes replica lag, INSTANT does not.