Online 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는 data dictionary만 변경하므로 테이블 크기와 관계없이 즉시 끝난다. INPLACE + LOCK=NONE은 동시 읽기와 쓰기를 허용하면서 테이블을 제자리에서 처리한다. 마지막 쿼리는 실행 중인 ALTER가 어느 단계에 있는지와 진행률을 보여 준다.

사용 시점

운영 테이블의 스키마 변경 전. 어떤 작업을 다운타임 없이 실행할 수 있는지 알고 긴 ALTER가 언제 끝날지 추정하기 위해.

주의

INSTANT와 INPLACE도 시작과 끝에 짧은 배타 metadata lock을 잡는다. 장시간 트랜잭션이 테이블을 잡고 있으면 ALTER가 대기하고 그 뒤의 모든 쿼리가 대기열에 쌓인다(metadata lock 진단 참고). 세션에서 lock_wait_timeout을 낮춘다(SET SESSION lock_wait_timeout = 5). INPLACE 중 들어오는 DML은 innodb_online_alter_log_max_size까지 기록되며, 이를 초과하면 마지막에 ALTER가 롤백된다. 컬럼 타입 변경, charset 변환, PK 변경은 COPY가 필요하며 쓰기를 블로킹한다. 대형 테이블에는 gh-ost / pt-online-schema-change를 사용한다. replica에서는 ALTER가 같은 시간 동안 단일 스레드로 실행되므로 대형 INPLACE ALTER는 replica 지연을 일으키지만 INSTANT는 그렇지 않다.