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

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

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

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

• INSTANT: 테이블의 데이터는 전혀 변경하지 않고, 메타 데이터만 변경하여 작업을 완료함. 스키마 변경 도중 테이블의 읽고 쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에는 크게 영향을 미치지 않음.

• INPLACE: 임시 테이블로 데이터를 복사하지 않고 스키마 변경을 실행. 테이블의 모든 레코드를 리빌드해야 하기 때문에 테이블의 크기에 따라 많은 시간이 소요될 수 있음. 아직 스키마 변경 중인 경우에도 읽고 쓰기가 가능하지만, 최초 시작 시점과 마지막 종료 시점에는 테이블의 읽고 쓰기가 불가능함.

• COPY: 변경된 스키마를 적용한 임시 테이블을 생성하고, 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME하여 스키마 변경을 완료함. COPY 알고리즘은 테이블의 읽기만 가능하고, DML은 실행할 수 없음.

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

  1. ALGORITHM=INSTANT 옵션으로 스키마 변경 시도
  2. 실패한 경우, ALGORITHM=INPLACE, LOCK=NONE 옵션으로 시도
  3. 실패한 경우, ALGORITHM=INPLACE, LOCK=SHARED 옵션으로 시도
  4. 실패한 경우, ALGORITHM=COPY, LOCK=SHARED 옵션으로 시도
  5. 실패한 경우, ALGORITHM=COPY, LOCK=EXCLUSIVE 옵션으로 시도
  6. 1, 2번으로 되지 않는다면 DML을 멈추고 스키마 변경을 진행해야 함.

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

• Online DDL 별 적용 가능한 알고리즘은 다음과 같음:

  • INSTANT: 메타 데이터 변경으로만 스키마 변경이 가능한 경우
  • INPLACE: 임시 테이블 생성 없이 스키마 변경이 가능한 경우, 테이블 크기에 따라 시간 소요가 달라짐
  • COPY: 테이블의 모든 레코드를 임시 테이블로 복사해야 하는 경우, 테이블의 읽기만 가능하고 DML은 실행할 수 없음.