[MySQL] Online DDL 별 적용 가능한 알고리즘 (8.0 이상)

안녕하세요, 오늘은 베스핀글로벌 D&A실 왕승준님이 작성해 주신 [MySQL] Online DDL 별 적용 가능한 알고리즘 (8.0 이상)에 대해 알아보겠습니다 궁금하신 부분이 있으시면 댓글을 달아주세요 🙂

— ALTER TABLE 명령을 실행하면 MySQL 서버는 다음과 같은 순서로 스키마 변경 알고리즘을 찾음.

1. ALGORITHM=INSTANT로 스키마 변경이 가능한지 확인 후, 가능하다면 선택
2. ALGORITHM=INPLACE로 스키마 변경이 가능한지 확인 후, 가능하다면 선택
3. ALGORITHM=COPY 알고리즘 선택

— 알고리즘의 우선순위가 낮을수록 MySQL 서버는 스키마 변경을 위해 더 큰 잠금과 많은 작업을 필요로 하고, 서버의 부하도 많이 발생시킴.

1. INSTANT : 테이블의 데이터는 전혀 변경하지 않고, 메타데이터만 변경하고 작업을 완료함. 테이블이 가진 레코드 건수와 무관하게 작업 시간은 매우 짧음. 스키마 변경 도중 테이블의 읽고 쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에는 크게 영향을 미치지 않음.
2. INPLACE : 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행. 하지만 내부적으로는 테이블의 리빌드를 실행할 수도 있음. 레코드의 복사 작업은 없지만 테이블의 모든 레코드를 리빌드해야 하기 때문에 테이블의 크기에 따라 많은 시간이 소요될 수 있음. 하지만 스키마 변경 중에도 테이블의 읽기와 쓰기 모두 가능. INPLACE 알고리즘으로 스키마가 변경되는 경우에도 최초 시작 시점과 마지막 종료 시점에는 테이블의 읽고 쓰기가 불가함. 하지만 이 시간은 매우 짧기 때문에 다른 커넥션의 쿼리 처리에 대한 영향도는 높지 않음.
3. COPY : 변경된 스키마를 적용한 임시 테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료. 이 방법은 테이블 읽기만 가능하고 DML은 실행할 수 없음.

— 서비스 영향을 최소화하면서 가능한 알고리즘을 확인해 보는 방법

1. ALGORITHM=INSTANT 옵션으로 스키마 변경을 시도
2. 실패하면 ALGORITHM=INPLACE, LOCK=NONE 옵션으로 스키마 변경을 시도
3. 실패하면 ALGORITHM=INPLACE, LOCK=SHARED 옵션으로 스키마 변경을 시도
4. 실패하면 ALGORITHM=COPY, LOCK=SHARED 옵션으로 스키마 변경을 시도
5. 실패하면 ALGORITHM=COPY, LOCK=EXCLUSIVE 옵션으로 스키마 변경을 시도

>> 1, 2번으로 되지 않는다면 DML을 멈춘 다음 스키마 변경을 해야 하는 작업임.

— 스키마 변경 작업의 진행 상황은 performance_schema.events_stages_current 테이블을 통해 확인

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED

FROM performance_schema.events_stages_current;

>> WORK_COMPLETED / WORK_ESTIMATED * 100 : 진행률

— Online DDL 별 적용 가능한 알고리즘

감사합니다 🙂

문의: info@bespinglobal.com | 대표번호: 02-1668-1280