DDL en ligne : ALGORITHM=INSTANT / INPLACE, LOCK=NONE et suivi de progression

Commande

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

Ce que fait la commande

INSTANT ne modifie que le data dictionary et se termine immédiatement quelle que soit la taille de la table. INPLACE + LOCK=NONE traite la table sur place tout en autorisant les lectures et écritures concurrentes. La dernière requête montre à quelle étape se trouve un ALTER en cours et son pourcentage.

Quand l'utiliser

Avant des changements de schéma sur des tables de production ; pour savoir quelles opérations peuvent s'exécuter sans interruption et estimer quand un long ALTER se terminera.

Attention

INSTANT et INPLACE prennent quand même un bref metadata lock exclusif au début et à la fin ; si une transaction longue détient la table, l'ALTER attend et toutes les requêtes qui le suivent s'accumulent (voir le diagnostic des metadata locks). Abaissez lock_wait_timeout dans la session (SET SESSION lock_wait_timeout = 5). Les DML arrivant pendant INPLACE sont journalisés jusqu'à innodb_online_alter_log_max_size ; en cas de dépassement, l'ALTER est annulé à la fin. Les changements de type de colonne, la conversion de charset et les changements de PK nécessitent COPY et bloquent les écritures ; sur les grandes tables, utilisez gh-ost / pt-online-schema-change. Sur une réplique, l'ALTER s'exécute en mono-thread pendant la même durée ; un gros ALTER INPLACE provoque un retard de réplique, INSTANT non.