Row가 3억 개인 테이블에 column 추가/삭제하기(Online DDL)

2023. 11. 18. 05:22Database

Intro

최근에 RDB 테이블 중 하나에 column을 추가/삭제해야 하는 작업이 있었습니다.
다만, 이 테이블이 user 계정의 audit을 기록하는 테이블이라 row의 개수가 3억 건을 넘었고, 테이블의 크기도 70GB에 달했습니다.

컬럼 수
테이블 크기

 

무튼, 이 테이블에 더이상 사용하지 않는 컬럼이 있어서 drop하는 작업이 필요했는데요,
사용중인 MySQL은 DDL을 실행할 때 Table Lock이 걸리기때문에 무턱대고 쿼리를 날리면 안됩니다.
이 컬럼을 drop하는 트랜잭션이 종료될 때 까지 다른 트랜잭션들은 해당 테이블에 Insert/Update/Delete 작업이 불가능하기 때문입니다.

실행 결과

작업에는 2시간 33분가량 걸렸습니다. 이 시간동안 I/O 작업이 불가능해 데이터의 정합성이 깨지게 된다면 회사의 골프채가 휠 수도 있습니다.


이때 Online DDL을 이용하면 됩니다.

Online DDL이란?

Online 이라는 용어는 ddl 등의 변경 작업이 데이터베이스 시스템이 온라인 상태로 계속 작동하는 동안에도 수행될 수 있다는 것을 의미합니다. mysql 5 버전부터 지원됩니다.

아래의 쿼리로 손쉽게 버전을 확인하실 수 있습니다. 

SELECT VERSION();

필자는 MySQL 5.7을 이용하고 있습니다. 

 

How to Use

사용 방법은 매우 간단합니다.

alter table 테이블_명 drop column 컬럼_명, algorithm=inplace, lock=none;

alter문에 Algorithm과 Lock 속성을 추가로 명시해서 쿼리를 실행하면 Online DDL이 실행됩니다.
각각의 속성에 어떤 값을 사용할 수 있는지 Algorithm부터 보겠습니다.

 

Algorithm

  • Copy
  • Inplace
  • Instant(MySQL 8.0.12)

1. Copy

변경된 스키마가 적용된 임시 테이블을 생성합니다. 해당 테이블에 기존 테이블의 데이터를 row-by-row로 복사한 후, 임시 테이블의 이름을 원본 테이블의 이름으로 변경하는 방식입니다. 다만, 임시 테이블과 기존 테이블의 데이터가 동일해야 하므로 임시테이블을 생성하고 있을 때 Select는 가능하나 CUD 작업은 불가능합니다. 또한, 임시 테이블을 추가로 생성하므로 시스템 자원을 많이 소모합니다.

2. Inplace

이번 작업에 사용한 알고리즘입니다.
단어 그대로, 원본 테이블에 직접 변경 작업을 적용합니다. 변경 작업 도중에 들어오는 쿼리들을 Online Alter Log Buffer에 적재하고 변경이 완료된 후 해당 DML을 순차 실행하는 방식으로 이루어집니다. 작업 시작 시점과 마지막 시점에는 메타 데이터에 잠금(테이블이나 뷰 등의 이름이나 구조를 변경하는 경우에 획득하는 잠금)을 걸게 되어 테이블의 읽고 쓰기가 불가한 순간이 있지만, 이 시간은 매우 짧기 때문에 다른 커넥션의 쿼리 처리에 대한 영향도는 높지 않습니다. 

 

Inplace 알고리즘의 작업 순서는 다음과 같습니다.

1) Inplace 스키마 변경이 지원되는 커맨드인지, InnoDB 인지 확인
2) Online DDL 작업 동안 변경되는 데이터를 저장할 준비  (innodb_online_alter_log_max_size) 
3) 테이블 스키마 변경 및 DML 로깅
4) 변경 완료된 테이블에 DML 로깅 적용
5) Inplace 작업 완료

Inplace 알고리즘을 이용할 때 주의사항이 몇 가지 존재합니다.
1) 메타데이터 락이 걸리는 2, 4번 작업을 진행할 때 Lock 획득에 실패할 경우 작업에 실패합니다. 대상 테이블에 대해 metadata lock을 취득한 트랜잭션이 있다면, 해당 트랜잭션의 작업이 종료될 때 까지 online ddl은 지연됩니다. 지연 시간이 길어져 timeout에 의해 롤백된다면 눈물이 나겠죠..

2) 2번 작업에서 설정한 innodb_online_alter_log_max_size보다 Online Alter Log Buffer 많은 데이터가 쌓이면 오류가 발생합니다. innodb_online_alter_log_max_size 지정된 사이즈 만큼 저장이 가능하고 크기를 초과하면 온라인 DDL 작업이 실패하고 Concurrent DML 작업이 롤백됩니다

innodb_online_alter_log_max_size 파라미터의 default 값은 128MB 입니다.

3. Instant

MySQL 8.0.12 버전에 추가된 알고리즘으로 메타 정보만 수정하여 변경 사항을 반영합니다. 따라서, 스키마 변경중에 메타 데이터 잠금을 획득하지 않으며, 테이블의 데이터 파일도 건드리지 않습니다. 테이블이 가진 레코드 건수와 무관하게 작업 시간은 매우 신속하게 처리됩니다.

ALGORITHM 구문을 생략하거나 DEFAULT로 지정할 경우

MySQL 8버전이라면 ALGORITHM=INSTANT 옵션으로 스키마 변경을 시도합니다. 실패할 경우 ALGORITHM=INPLACE 이 사용 가능한 구문에 대해서는 우선 사용하며 그렇지 않을 경우 ALGORITHM=COPY 로 사용 되게 됩니다.

즉 ALGORITHM 구문 미사용시 INSTANT -> INPLACE -> COPY 순으로 처리됩니다.

 

Lock

LOCK=NONE
잠금을 걸지 않는 방식(read, writeO)으로 Concurrent DML 을 허용합니다.


LOCK=SHARED

공유잠금을 거는 방식으로(read O, writeX)으로 Concurrent DML을 허용하지 않습니다.


LOCK=EXCLUSIVE

Concurrent query 와 Concurrent DML 둘다 불가 합니다(read, write X)
빠른 시간 내에 DDL이 완료 되거나, 동시 쿼리나 DML 의 엑세스가 필요하지 않을 경우 사용할 수 있을듯 합니다.

 


CPU

총 두개의 테이블에서 작업을 진행했습니다. 파란색이 Write DB의 CPU 사용량입니다.
중간에 사용량이 급감하는 이유는 첫 번째 테이블의 작업이 완료되었기 때문입니다.
두 번째 테이블 작업을 시작하니 다시 35%까지 치솟네요 

 

REF

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

 

MySQL Online-DDL

당근 마켓의 서비스는 쉬지 않고 발전하고 있어요. 하지만 이런 소프트웨어의 개선 작업은 단순히 버튼 한번 클릭으로 완성되는 것은 아니에요. 때로는 마치 전쟁을 치루는 것 같은 과정을 거치

medium.com

MySQL :: MySQL 5.7 Reference Manual :: 14.13 InnoDB and Online DDL

 

MySQL :: MySQL 5.7 Reference Manual :: 14.13 InnoDB and Online DDL

14.13 InnoDB and Online DDL The online DDL feature provides support for in-place table alterations and concurrent DML. Benefits of this feature include: Improved responsiveness and availability in busy production environments, where making a table unavail

dev.mysql.com

MySQL 5.6 한글메뉴얼

 

MySQL 5.6 한글메뉴얼

14.11.2 온라인 DDL의 성능과 동시성 고려 사항 온라인 DDL 하여 성능 동시성, 가용성, 확장 성 등의 MySQL 작업의 일부 측면이 개선됩니다. 테이블에서의 쿼리와 DML 작업은 DDL의 진행하더라도 작업을

www.innodbcluster.com

MySQL 5.6 - table locks even when ALGORITHM=inplace is used

 

MySQL 5.6 - table locks even when ALGORITHM=inplace is used

I'm running the following ALTER command on a MySQL 5.6 database on a large table with 60 million rows: ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL, ALGORITHM=INPLACE, LOCK=NONE; De...

stackoverflow.com