[MySQL - Online DDL] part 1. 알고리즘

Online DDL이란

테이블의 구조를 변경(스키마 변경)을 실행하는 도중에도, INSERT나 UPDATE와 같은 DML들을 실행할 수 있도록 해주는 기능입니다. MySQL 서버에서 Online DDL이 지원되지 않았던 시절에는 pt-online-schema-change와 같은 3-rd party 도구들을 이용해야 했지만, (이런 도구들은 불필요한 작업이 너무 많이 수반되어 서비스 영향도가 높은 편이어서) 이제는 대부분의 경우 MySQL 서버의 Online DDL을 이용합니다.

 

 

 InnoDB에서 지원하는 online ddl 알고리즘 

- copy

- inplace 

- instant (mysql 8.0)

 

 

■ Copy

- 변경된 스키마가 적용된 임시 테이블을 생성하여 기존 테이블의 데이터를 복사한 후 테이블 이름을 변경하는 방식

- ALGORITHM 구문 미사용시 INPLACE -> COPY 순으로 처리

  INPLACE 로 처리 가능한 경우 INPLACE 를 사용하고 처리 불가능시 COPY 사용

 

단점 

- DDL 작업 일시중지, 작업중 I/O, CPU 사용제한 메커니즘 없음

- 작업중 롤백이 발생할 수 있으며, 롤백시 많은 비용이 소모

- ALTER TABLE 작업 동안 Concurrent DML (inserts,updates,deletes) 이 차단

  LOCK = SHARED 일 때 Select는 가능

- 복제 지연이 발생 가능성 있음 

 

 

■ Inplace

- 원본 테이블에 직접 변경작업을 적용

- 작업 준비(during perparation) 실행단계(executioin pahases of the operation)에서 테이블에 대한 배타적 메타 데이터 잠금(exclusive metadata lock)이 잠깐 동안 수행될 수 있습니다.

- 일반적으로 Concurrent DML 을 지원

 

단점

- 지원하지 않는 DDL 구문이 있습니다.

  지원여부 확인을 위해 ALGORITHM=INPLACE 구문을 사용해서 확인 가능 

- 테이블을 수정하는 동안 변경된 데이터량이 innodb_online_alter_log_max_size 를 초과할 경우 

  online ddl 이 실패하고 변경된 데이터는 롤백된다.  => 보통 1GB로 설정하고 하면 된다고 한다. session level에서도 변경이 가능하기 때문에 1GB로 설정 후 진행

- 장기간 실행되는 DLL 작업은 슬레이브에서 복제 지연을 유발할 수 있습니다.

  ALTER TABLE 작업은 마스터에서 작업이 완료되면 슬레이브로 전달

- 동시성이 높은 서버의 큰 테이블에 대해 높은 I/O 사용량을 유발할 수 있습니다.

 

 

■ Instant

- mysql 8.0 에서 추가되었습니다.

- 메타 정보만 수정하여 변경 사항 반영

  Data dictionary의 metadata만 변경하기 때문에 Table에 metadata lock을 걸지 않고 Table data에도 영향을 받지 않기 때문에 바로 반영합니다.

- ALGORITHM 구문 미사용시 INSTANT -> INPLACE -> COPY 순으로 처리

 

단점

- 컬럼 추가시 테이블의 마지막 열로만 추가 가능, 컬럼 위치 지정 불가

- ROW_FORMAT=COMPRESSED 를 사용하는 테이블에는 컬럼 추가 불가

- FULLTEXT 인덱스를 포함하는 테이블에는 컬럼 추가 불가

- 임시 테이블에는 컬럼 추가 불가

  임시테이블은 ALGORITHM=COPY 만 지원

- 데이터 딕셔너리 테이블 스페이스 (공유 테이블 스페이스)에 있는 테이블에는 컬럼 추가 불가

Instant > InPlace > Copy 순으로 효율적이며 Mysql 8에서는 Instant가 default로 설정.

해당 알고리즘 사용 부분이 매우 제한적이기 때문에 상황에 맞는 전략 필요.

 

※ 온라인 DDL(In-Place) 방식의 경우 innodb_online_alter_log_max_size 파라미터에 지정한 만큼 작업 중에 발생되는 Concurrent DML에 대한 임시 로그를 기록하게 되는데 파라미터에 지정된 용량 이상 되면 MySQL 서버는 에러를 발생시키고 지금까지 했던 테이블 스키마 변경 작업을 ROLLBACK하게 됩니다.(innodb_online_alter_log_max_size 기본 값 : 128MB)

 

 

 

참고

 

https://medium.com/daangn/mysql-online-ddl-faf47439084c

MySQL Online-DDL

https://iamwhat.tistory.com/entry/online-ddl-%EC%95%8C%EA%B3%A0%EB%A6%AC%EC%A6%98

online ddl 알고리즘