Chapter 18 · Partitioning, Large Tables, Archiving, and Data Lifecycle
Online DDL, Instant/In-Place/Copy Algorithms, Locks, and Schema Change Planning
Plan MySQL schema changes by operation-specific INSTANT, INPLACE, COPY, and LOCK capabilities, then observe metadata-lock behavior and define reversible production gates.
Learning outcomes
ServiceHub needs a new lifecycle tag and a new query index. The SQL syntax is easy; the production risk is not. On a large table, a schema change can be metadata-only, rebuild data in place, copy the whole table, wait for a metadata lock, consume temporary disk, amplify replica lag, or fail late. This lesson treats DDL as an operation with explicit concurrency and rollback gates.
Differentiate ALGORITHM=INSTANT, INPLACE, and COPY and understand that support is operation- and version-dependent.
Use explicit ALGORITHM and LOCK clauses as preflight contracts rather than relying on silent fallback.
Observe metadata-lock waits using Performance Schema/sys/processlist evidence in multiple sessions.
Design a compatibility/lock-risk failure drill and a reversible staged alternative.
Define production DDL gates for duration, disk headroom, concurrency, replica lag, abort, and post-change verification.
INSTANT operations can still need brief metadata locks. INPLACE operations can permit concurrent DML for many changes but still need metadata locks at initialization/finalization and can consume I/O, CPU, temporary space, and online-alter-log capacity.
Algorithm mental model
| Algorithm | Core idea | Typical operational consequence |
|---|---|---|
| INSTANT | metadata-oriented change when the specific operation supports it | no table-data rewrite; brief metadata locking can still matter |
| INPLACE | storage engine performs the change without the old full server-layer table-copy path | may rebuild table/index structures; concurrency depends on operation and LOCK |
| COPY | create/copy into a new table representation then switch | large I/O/space/time cost; concurrency can be much more restrictive |
Do not infer the algorithm solely from how quickly a small lab finishes. The reliable preflight technique is to request the algorithm and lock level you are willing to accept. If MySQL cannot satisfy the request for that operation/version/table, the statement fails rather than silently choosing a more disruptive path.
Create a representative DDL target
USE servicehub_lifecycle_lab;DROP TABLE IF EXISTS ddl_events;CREATE TABLE ddl_events LIKE work_order_events_plain;INSERT INTO ddl_events SELECT * FROM work_order_events_plain;ANALYZE TABLE ddl_events;SELECT COUNT(*) AS ddl_rows FROM ddl_events;SHOW CREATE TABLE ddl_events\GThe lab uses tens of thousands of rows so it remains safe locally. A production preflight must use a clone whose row count, row width, indexes, data distribution, disk class, and concurrent workload are representative enough to expose the real cost.
Use explicit algorithms as safety contracts
ALTER TABLE servicehub_lifecycle_lab.ddl_events ADD COLUMN lifecycle_tag VARCHAR(24) NULL, ALGORITHM=INSTANT;SHOW CREATE TABLE servicehub_lifecycle_lab.ddl_events\GSELECT COUNT(*) AS rows_with_null_tagFROM servicehub_lifecycle_lab.ddl_eventsWHERE lifecycle_tag IS NULL;For supported InnoDB add-column cases in MySQL 8.4, ALGORITHM=INSTANT is available. The explicit clause is valuable because it makes your maximum acceptable algorithm part of the deployment command. The exact limitations still depend on table features and the requested alteration.
ALTER TABLE servicehub_lifecycle_lab.ddl_events ADD INDEX idx_ddl_type_date (event_type, occurred_on, event_id), ALGORITHM=INPLACE, LOCK=NONE;SHOW INDEX FROM servicehub_lifecycle_lab.ddl_eventsWHERE Key_name='idx_ddl_type_date';EXPLAIN SELECT event_idFROM servicehub_lifecycle_lab.ddl_eventsWHERE event_type='closed' AND occurred_on >= '2026-05-01' AND occurred_on < '2026-06-01';Acceptance of LOCK=NONE proves that this specific operation/table/version supports concurrent reads and writes at the requested level; it does not prove the operation is free. Measure elapsed time, I/O, CPU, temporary space, transaction latency, and replica lag where applicable.
Compatibility failure: do not allow a silent expensive fallback
ALTER TABLE servicehub_lifecycle_lab.ddl_events MODIFY payload TEXT NOT NULL, ALGORITHM=INSTANT;-- Expected: rejected because this type change does not support INSTANT.The wrong response is to remove ALGORITHM=INSTANT and run the change in production without understanding the new path. The repair is to test the supported algorithm on a representative clone, estimate disk/concurrency/replica consequences, or stage an expand/contract migration: add a new compatible column, backfill in bounded batches, dual-read/write at the application boundary, validate, cut over, and only later remove the old column.
Metadata locks: an instant change can still wait behind a transaction
A metadata lock (MDL) protects an object definition while statements and transactions use it. A long transaction that touched the table can retain a shared metadata lock. A DDL statement needing an exclusive metadata lock can wait; once it is queued, later statements may also wait behind the pending DDL. Reproduce this only on the disposable table.
USE servicehub_lifecycle_lab;START TRANSACTION;SELECT COUNT(*) FROM ddl_events WHERE event_id BETWEEN 1 AND 100;-- Keep this transaction open temporarily. Do not do this on a production table.SET SESSION lock_wait_timeout=5;ALTER TABLE servicehub_lifecycle_lab.ddl_events ADD COLUMN mdl_probe INT NULL, ALGORITHM=INSTANT;-- Expected while Session A stays open: metadata-lock wait then timeout.SELECT OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_DURATION, LOCK_STATUS, OWNER_THREAD_IDFROM performance_schema.metadata_locksWHERE OBJECT_SCHEMA='servicehub_lifecycle_lab' AND OBJECT_NAME='ddl_events'ORDER BY LOCK_STATUS, OWNER_THREAD_ID;SELECT *FROM sys.schema_table_lock_waitsWHERE object_schema='servicehub_lifecycle_lab' AND object_name='ddl_events';SHOW FULL PROCESSLIST;Now ROLLBACK Session A, retry Session B, and confirm the column appears. The lesson is not to kill the oldest session automatically. First identify ownership and business impact; an idle application transaction may indicate a pool/transaction bug that will recur after the DDL succeeds.
DDL progress, disk, and replica gates
Performance Schema stages can expose progress for supported operations, and OS tools reveal temporary-space and I/O pressure. For large online index builds, reserve space for sort/intermediate work and remember that concurrent DML can enlarge the online alteration log. In a replicated topology, monitor receiver/applier state and GTID/lag evidence from Chapter 14; a DDL acceptable on the source may still create an unacceptable apply backlog.
SELECT @@innodb_online_alter_log_max_size AS online_alter_log_max, @@innodb_ddl_threads AS ddl_threads, @@innodb_parallel_read_threads AS parallel_read_threads;SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATEDFROM performance_schema.events_stages_currentWHERE EVENT_NAME LIKE 'stage/innodb/alter table%';Stage rows are transient and may not appear for a fast change. Absence of a row is not proof that no DDL ran. Capture processlist, logs, disk free space, I/O latency, and application SLOs at the same time.
Production change plan: preflight, gate, execute, verify, recover
| Gate | Example acceptance question |
|---|---|
| algorithm/lock | Will the exact DDL fail rather than fall back if INSTANT/INPLACE or LOCK=NONE is unavailable? |
| metadata locks | Are there old transactions/DDL queues likely to block the switch phase? |
| space/I/O | Is there enough temporary/rebuild headroom and can storage absorb the added I/O? |
| replication | What lag/queue threshold causes abort or pause? |
| application | Are latency/error SLOs healthy during canary/preflight? |
| rollback | Can we stop safely, or do we need an expand/contract path because destructive rollback is not instant? |
| verification | Do schema, row counts, constraints, query plans, and business checks match after the change? |
Cleanup and bridge to retention work
DROP TABLE IF EXISTS servicehub_lifecycle_lab.ddl_events;Online DDL is a capability matrix, not a promise that every ALTER is nonblocking. Lesson 4 uses the same operational discipline for retention: archive first, verify recoverability, then purge in bounded work or drop a verified old partition—never run a massive historical DELETE just because it is syntactically simple.
Knowledge check
- What does ALGORITHM=INSTANT protect you from in a deployment command?
- Why can an INSTANT change still block?
- What does LOCK=NONE acceptance prove?
- Why test DDL on representative size and concurrency?
- When is expand/contract preferable?
Reveal answers
- It makes the statement fail if that exact change cannot use INSTANT rather than silently accepting a more expensive algorithm.
- It may need brief exclusive metadata locks, which can wait behind transactions holding metadata locks.
- That the specific operation/table/version permits concurrent DML at that requested lock level; it does not prove zero I/O or zero latency impact.
- Algorithm support alone does not reveal duration, temporary space, storage saturation, online-log growth, or replica lag under real workload.
- When a direct alteration is incompatible, too disruptive, hard to roll back, or requires a long copy/rebuild that exceeds production risk gates.