online ddl 알고리즘

mysql 5.6 부터 online ddl이 추가 되었다.

 

각 버전별 online ddl 지원 범위가 다르다 메뉴얼을 한번 읽어보자.
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html

 

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 이 실패하고 변경된 데이터는 롤백된다. 

  2시간 정도 돌아가다 에러나면서 롤백되면 환장한다... (경험담)

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

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

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

 

 

instant 

- mysql 8.0 에서 추가됨

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

  스키마 변경중에 메타 데이터 잠금을 획득하지 않으며, 테이블의 데이터 파일도 건드리지 않는다. 

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

 

단점

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

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

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

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

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

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

 

 

LOCK 

- 테이블에 대한 동시 엑세스 수준을 조정

- mysql은 ddl 작업시 가능한 작은 level 의 lock을 사용한다. 

  더 제한적인 감금을 적용하기 위해서 LOCK 절을 지정함 

- 특정 DDL 조작에서 허용되는 lock 레벨보다 더 적은 제한 레벨 lock을 지정하면 SQL Statement는 오류와 함께 실패한다. 

 

- LOCK=NONE

  Concurrent query,  Concurrent DML 허용

- LOCK=SHARED

  Concurrent query 허용,  Concurrent DML 불가

- LOCK=DEFAULT

  수행 가능한 동시성 작업에 대해서 허용 

- LOCK=EXCLUSIVE

  Concurrent query 불가,  Concurrent DML 불가

  빠른 시간내에 DDL 이 완료되거나, 동시 쿼리나 DML 이 없을 경우 사용

 

 

oline ddl 단계

- Initializaton

  Storage Engine 기능, 명령문으로 지정된 작업, 사용자 지정의 ALGORITHM 및 Lock 옵션을 고려하여 

  작업에 허용가능한 옵션 결정

  테이블 정의를 보호하기 위해 가능한 Shared Upgradeable Metadata Lock 을 수행

 

- Execution

  명령문이 준비되고 실행됨 

  Metadata Lock의 Exclusive 여부는 Initializaton 단계에서 평가된 내역에 따라 다르며, Exclusive Metadata Lock 이 필요한 경우 Alter 문을 주비하는 동안 잠깐 수행됨 

 

- Commit Table Definition

  테이블의 구조가 완료됨 

  Metadata Lock은 테이블 정의를 커밋하기 위해 Exclusive 로 업그레이드됨 

  Exclusive Metadata Lock 은 짧은 시간동안 잡힘