オンラインDDL: ALGORITHM=INSTANT / INPLACE、LOCK=NONEと進捗の追跡

コマンド

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

概要

INSTANTはデータディクショナリのみを変更し、表のサイズにかかわらず即座に完了する。INPLACE + LOCK=NONEは、同時の読み取りと書き込みを許可しながら表をその場で処理する。最後のクエリは、実行中のALTERがどの段階にあるかとその割合を表示する。

使用する場面

本番の表でのスキーマ変更の前に。どの操作が停止なしで実行できるかを知り、長いALTERがいつ終わるかを見積もるため。

注意

INSTANTとINPLACEも開始時と終了時に短時間の排他メタデータロックを取得する。長時間実行のトランザクションが表を保持している場合、ALTERは待機し、その後ろのすべてのクエリがキューに並ぶ(メタデータロックの診断を参照)。セッションでlock_wait_timeoutを下げる(SET SESSION lock_wait_timeout = 5)。INPLACE中に到着したDMLはinnodb_online_alter_log_max_sizeまで記録され、超過するとALTERは最後にロールバックされる。列の型の変更、文字セットの変換、PKの変更にはCOPYが必要で、書き込みをブロックする。大きな表ではgh-ost / pt-online-schema-changeを使用する。レプリカではALTERは同じ時間シングルスレッドで実行される。大きなINPLACEのALTERはレプリカ遅延を引き起こすが、INSTANTは引き起こさない。