DB-유

Managing Large Tables: Online DDL with ALGORITHM=INPLACE

· mysql, innodb, ddl, schema

I've sat through enough 3 a.m. maintenance windows for a single ALTER TABLE on a 200 GB table to appreciate online DDL. Many operations can now run "in place" without a full copy, and even the ones that rebuild can allow concurrent reads and writes. The trick is knowing which is which, asking for the behavior you want explicitly so MySQL refuses instead of silently doing a blocking copy, and handling the metadata lock at the start and end.

This covers MySQL 5.7 and 8.0, and MariaDB 10.2+, which supports the same clauses but differs in which operations qualify.

The two clauses

ALTER TABLE accepts two hints:

  • ALGORITHM=INPLACE performs the change within the existing tablespace, sometimes rebuilding it, but without creating a separate copy under an exclusive lock. ALGORITHM=COPY is the old behaviour. On 8.0.12 and later there is also ALGORITHM=INSTANT for a small set of metadata-only changes such as adding a column at the end.
  • LOCK=NONE allows concurrent reads and writes; LOCK=SHARED allows reads only; LOCK=EXCLUSIVE blocks both.

The value of specifying them is that MySQL errors out immediately if it cannot honour the request, instead of falling back to a table copy that locks the application for an hour.

Which operations are in-place

Roughly, on 5.7:

  • In-place, no rebuild, LOCK=NONE: add or drop a secondary index, rename an index, rename a column, change a column default, extend a VARCHAR within the same byte-length class (for example 50 to 200 with utf8mb4), add or drop a foreign key with foreign_key_checks=0, set AUTO_INCREMENT.
  • In-place with rebuild, LOCK=NONE: add or drop a column, reorder columns, change the primary key, change ROW_FORMAT, OPTIMIZE TABLE, set NULL/NOT NULL.
  • Copy only: change a column's data type, add a FULLTEXT or SPATIAL index (in-place but requires LOCK=SHARED), change the character set, drop a primary key without adding a new one.

I never trust my memory on this list. I just ask MySQL:

ALTER TABLE orders ADD INDEX idx_customer (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

If it is not possible you get ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: ... with the reason spelled out, and nothing happens.

Before a long online operation

During an in-place rebuild, concurrent DML is written to an online log buffer and applied at the end. Its size is limited by innodb_online_alter_log_max_size (128 MB default). On a write-heavy table, a long rebuild can overflow it and the ALTER fails after hours of work. Raise it before starting:

SET GLOBAL innodb_online_alter_log_max_size = 1073741824;  -- 1 GB

Also check free disk. A rebuild needs space for a second copy of the table in the same directory until the swap. On 5.7 the temporary files go to innodb_tmpdir if set, otherwise to tmpdir, so point that at a filesystem with room:

[mysqld]
innodb_tmpdir = /var/lib/mysql-tmp

Finally, make sure no long-running transaction has the table open, because the ALTER needs an exclusive metadata lock for a moment at the start and again at the end:

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Run it with a lock timeout

The metadata lock wait is where online DDL bites people, and it bit me first. If a report is holding the table open, the ALTER waits in Waiting for table metadata lock, and every new query on that table then queues behind the ALTER. The application appears to hang even though the ALTER itself is doing nothing. Protect against it with a short lock timeout so the ALTER gives up instead of blocking everyone:

SET SESSION lock_wait_timeout = 30;
ALTER TABLE orders
  ADD COLUMN shipped_at DATETIME NULL,
  ADD INDEX idx_shipped (shipped_at),
  ALGORITHM=INPLACE, LOCK=NONE;

Combine multiple changes in one statement; a single rebuild is far cheaper than several.

On 8.0.12+, adding a nullable column at the end can be instant:

ALTER TABLE orders ADD COLUMN notes TEXT NULL, ALGORITHM=INSTANT;

Watching progress

On 5.7 and 8.0 the Performance Schema stage instruments expose progress for InnoDB ALTER TABLE:

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%';

Then from another session:

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED
FROM performance_schema.events_stages_current;

stage/innodb/alter table (read PK and internal sort) followed by (merge sort), (insert), (log apply index) and (log apply table) tell you which phase you are in. The log apply stages are where the accumulated concurrent DML is replayed.

MariaDB 10.3 shows the same information in SHOW PROCESSLIST under the Progress column and in information_schema.processlist.stage.

Was it really online?

Confirm the change and that the table wasn't copied behind your back. A rebuild changes the tablespace but keeps the same table ID; a copy produces a new one. The simplest check is the time and the lock state:

SHOW CREATE TABLE orders\G
SELECT * FROM information_schema.innodb_sys_tables WHERE name = 'appdb/orders';

More importantly, during the run, a second session should be able to insert:

INSERT INTO orders (customer_id, total, status) VALUES (1, 9.99, 'open');

If that statement returns immediately while the ALTER is mid-flight, you have online DDL. If it hangs in Waiting for table metadata lock, the ALTER is stuck at the start waiting on another transaction; find it with the innodb_trx query above and kill it or wait.

Errors and their fixes

ERROR 1799 (HY000): Creating index 'idx_shipped' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again. Concurrent writes overflowed the online log. Raise the variable as described above and retry during a quieter period.

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. The operation genuinely needs a copy. Schedule a window, or use pt-online-schema-change from Percona Toolkit, which builds a shadow table and swaps it while writes continue.

ERROR 1205 (HY000): Lock wait timeout exceeded on the ALTER itself. That is the lock_wait_timeout saving you from a metadata lock pileup. Find the blocker in sys.schema_table_lock_waits (5.7+) and try again.

Replication lag on slaves after the change. The ALTER replicates as a statement and each slave performs its own rebuild, single-threaded. Budget for the lag or run the change on each slave first with SET sql_log_bin = 0, then on the master.

I always spell out ALGORITHM and LOCK, even when I'm sure of the default. It costs nothing and it has saved me from a surprise copy more than once.