[Mysql] 서비스 다운타임을 최소화할수 있는 테이블 스키마 업데이트

예전엔 Mysql/Maria DB 테이블에 컬럼을 추가하거나 수정하는등 ALTER (DDL) 실행시 테이블에 Lock 이 걸려 Select, Insert, Update, Delete 같은 DML 이 동시에 실행되지 못하는 문제가 있었다.

 

이렇게 될 경우 해당 테이블에 쿼리를 날리던 서버에서 문제가 발생할수 있다. 가장 좋은 방법은 서비스 점검을 띄운후 이런 작업을 진행하는것인데 서비스마다 상황이 달라 다운타임이 허용되지 않는 24시간 서비스의 경우 난감하다.

 

그리고 이러한 문제를 해결할수 있는 기능이 Online DDL 이다. (테이블 스키마 변경도중 DML 실행 가능)

- Myqsl 5.6 이상 부터

- MariaDB 10 이상 부터

 

Online DDL 은 알고리즘에 따라 작동 방식이 다른데 종류는 다음과 같다

 

- COPY (전통적인 방식)

변경된 스키마를 적용한 임시 테이블을 생성하고, 원본 테이블의 레코드를 모두 임시 테이블로 복사한 후 최종적으로 임시 테이블을 RENAME해서 스키마 변경을 완료. 이 방법은 테이블 읽기만 가능하고 DML은 실행할 수 없음.

 

- INPLACE (Mysql5.6, MariaDB 10 이상)

COPY 와 다르게 원본 테이블의 복사하여 또 다른 테이블을 만들지 않고 원본 테이블을 변경. (그래서 알고리즘명이 INPLACE 인가보다.) 스키마 변경 도중 DML 실행이 가능하다.

 

하지만 모든 DDL 에 대하여 이 알고리즘을 사용할수 있는건 아니기 때문에 어떤 경우에 적용할수 있는지 알아보아야한다.

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

 

그리고 주의할점으로 INPLACE 를 지원하지 않는 버전( Mysql5.6, MariaDB 10 이전) 에서 생성된 테이블의 경우 바로 INPLACE 사용이 불가능하고 COPY 를 사용해 테이블을 리빌드하는 작업이 필요하다. 이 리빌드 작업이후 INPLACE 사용이 가능해진다.

 

- INSTANT (MySQL 8.0, MariaDB 10.3.7 이상)

테이블의 데이터는 전혀 변경하지 않고, 메타데이터만 변경하고 작업을 완료함. 테이블이 가진 레코드 건수와 무관하게 작업 시간은 매우 짧음. 스키마 변경 도중 테이블의 읽고 쓰기는 대기하게 되지만 스키마 변경 시간이 매우 짧기 때문에 다른 커넥션의 쿼리 처리에는 크게 영향을 미치지 않음.

 

 

디비에선 테이블 스키마 업데이트시 INSTANT -> INPLACE -> COPY 순으로 시도.

- INSANT 실패시 INPLACE

- INPLACE 실패시 COPY

 

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

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을 멈춘 다음 스키마 변경을 해야 하는 작업.

 

그리고 운영 DB 에 바로 적용하기 이전에 앞서 개발 DB 를 통해 꼭 테스트해보자.  

 

참고자료

- https://jsonobject.tistory.com/515

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

- https://trend21c.tistory.com/2333

- https://blog.bespinglobal.com/post/mysql-online-ddl-%EB%B3%84-%EC%A0%81%EC%9A%A9-%EA%B0%80%EB%8A%A5%ED%95%9C-%EC%95%8C%EA%B3%A0%EB%A6%AC%EC%A6%98-8-0-%EC%9D%B4%EC%83%81/

- https://dolsup.work/tech-blog/update-db-schema-without-downtime/

- https://leezzangmin.tistory.com/51

- https://m.blog.naver.com/parkjy76/221790031480

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