Online-DDL: ALGORITHM=INSTANT / INPLACE, LOCK=NONE und Fortschrittsverfolgung

Befehl

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

Was es tut

INSTANT ändert nur das Data Dictionary und ist unabhängig von der Tabellengröße sofort fertig. INPLACE + LOCK=NONE bearbeitet die Tabelle an Ort und Stelle und erlaubt dabei parallele Lese- und Schreibzugriffe. Die letzte Abfrage zeigt, in welcher Phase sich ein laufendes ALTER befindet und mit wie viel Prozent.

Wann einsetzen

Vor Schemaänderungen an Produktionstabellen; um zu wissen, welche Operationen ohne Ausfallzeit laufen können, und um abzuschätzen, wann ein langes ALTER fertig wird.

Vorsicht

INSTANT und INPLACE nehmen am Anfang und Ende trotzdem kurz einen exklusiven Metadata Lock; hält eine lang laufende Transaktion die Tabelle, wartet das ALTER und jede Abfrage dahinter reiht sich ein (siehe Diagnose von Metadata Locks). Senke lock_wait_timeout in der Session (SET SESSION lock_wait_timeout = 5). Während INPLACE eintreffendes DML wird bis innodb_online_alter_log_max_size protokolliert; wird das überschritten, rollt das ALTER am Ende zurück. Änderungen von Spaltentyp, Zeichensatz und PK brauchen COPY und blockieren Schreibvorgänge; bei großen Tabellen gh-ost / pt-online-schema-change verwenden. Auf einer Replica läuft das ALTER genauso lange single-threaded; ein großes INPLACE-ALTER verursacht Replica-Lag, INSTANT nicht.