라포랩스에서는 RDB를 퀸잇 서비스의 주요 데이터베이스로 사용하고 있습니다. 서비스 개발을 하다보면 schema 변경을 위해 DB에서 DDL을 실행해야 하는 일이 잦습니다. 특히 기존에 있는 table에 칼럼 추가나 변경 등을 위해 Alter table을 해야 하는 경우, INPLACE algorithm 이 아닌 COPY algorithm 을 사용하게 되면 변경이 진행되는 동안 해당 table에 SELECT, INSERT와 같은 DML을 실행할 수 없게 됩니다.

MySQL에서는 5.6 버전부터 online ddl feature를 사용할 수 있게 되었습니다. ALTER TABLE ... , ALGORITHM=INPLACE, LOCK=NONE 와 같은 쿼리로 운영 중인 서비스의 중단 없이 schema 변경을 할 수 있습니다.

하지만 변경하려는 테이블의 크기가 크고 데이터 변경(DML)이 자주 일어나는 테이블인 경우에는 online ddl feature를 사용하더라도 DDL의 실행이 매우 느려지고 DB의 성능 저하를 초래해 서비스의 장애를 발생시키는 문제가 발생했습니다.

다른 회사에서는 중단 없이 schema 변경을 어떻게 하고 있는지 조사하던 도중 percona-toolkit 의 online-schema-change 라는 툴을 접하게 되었고, 이를 도입해보고자 PoC를 진행하게 되었습니다.

pt-osc 는 어떻게 동작하는가?

이하의 글에서는 percona-toolkit-online-schema-change 를 pt-osc로 줄여서 쓰도록 하겠습니다.

pt-osc는 online-ddl을 구현하기 위해 아래와 같은 작업을 순차적으로 진행하게 됩니다. 예시로 A라는 테이블에 DDL을 하는 상황이라고 하면,

DDL이 적용된 table _A_new 를 생성합니다

A table 내의 row들을 _A_new 로 복사합니다

데이터 복사가 진행되는 도중 기존 table A 에 일어난 INSERT, UPDATE, DELETE 등은 trigger를 통해 _A_new table에 반영됩니다

RENAME _A_new to A, A to _A_old 를 통해 DDL이 적용된 table을 기존 table과 swap합니다

이때 두 테이블에 RENAME 을 위해 metadata lock을 획득하지만 RENAME은 매우 빠른 시간 안에 실행되므로 아주 잠깐동안만 lock을 잡고 있게 됩니다

pt-osc 실행 예시

pt-osc의 동작 원리도 알아보았으니, 일단 한 번 써보면서 사용법을 숙지해보기 위해 테스트용 데이터베이스에 pt-osc를 실행해 보았습니다.

mydb schema의 A라는 table에 new_column 이라는 VARCHAR(127) 타입의 column을 추가하는 online DDL과 pt-osc로 같은 동작을 수행하기 위한 command는 아래와 같습니다.

•

DDL

ALTER TABLE mydb.A ADD COLUMN new_column VARCHAR(127), ALGORITHM=INPLACE, LOCK=NONE;
SQL
복사

•

pt-osc command

pt-online-schema-change \
--alter "ADD COLUMN new_column VARCHAR(127)" \
--host=$DB_HOST \
--port=$DB_PORT \
--user=$DB_USER \
--ask-pass \
--charset=UTF8MB4 \
D=mydb,t=A \
--execute
Shell
복사

pt-osc의 주요 option들

pt-osc 를 통해 online DDL을 실행할 때 여러 옵션들을 설정해주어야 합니다. 어떤 옵션들은 pt-osc를 실행할 target DB의 engine이 무엇인지 혹은 DB clustering이 되어있는지 등 상황에 맞게 설정해주어야 하는 옵션도 있습니다.

이 글을 읽으시는 분들이 차후 pt-osc를 사용하실 때에 문서를 읽는 시간을 조금이라도 줄여드릴 수 있도록, 주요 옵션들을 정리해 보았습니다.

--alter

pt-osc실행에 꼭 필요한 옵션으로 실행할 DDL을 이곳에 적어줍니다. 이때 ALTER TABLE $TABLE_NAME 이 들어가면 에러가 발생하므로 ALTER TABLE 이후의 내용을 적어주어야 합니다.

--no-drop-old-table

pt-osc의 실행이 완료된 이후 old (위의 예시에서는 _A_old ) table 을 삭제하지 않고 보존합니다. schema 변경 작업 이후 혹시라도 발생할 수 있는 문제에 대비하기 위해 old table을 보관하고 싶은 경우에 사용하면 좋습니다.

—-progress

진행 상황 출력을 조절하는 옵션으로 기본값은 time,30 으로 설정되어 있어 30초에 한 번씩 진행상황이 표시됩니다. 더 잦은 빈도로 진행 상황을 확인하기 위해 time,3 과 같이 설정하여 3초에 한 번씩 진행상황을 출력하도록 설정할 수 있습니다. time 이외에도 percentage, iteration 등으로도 설정할 수 있습니다.

—-chunk-size

DDL이 적용된 table을 만든 뒤 data를 copy하는 과정 중 한 번에 insert하는 row의 수로, 기본값은 1000입니다. insert를 하기 위해 원본 테이블에 shared lock을 걸고 데이터를 읽어오므로 업데이트가 빈번한 테이블일수록 작게 설정하는 것이 좋습니다. 이 옵션을 설정하게 되면 pt-osc가 db의 성능 부하에 맞춰 dynamic하게 chunk size를 조절하는 기능을 사용할 수 없게 되어 아래에서 설명드릴 --chunk-time 옵션을 사용하는 것을 추천합니다.

--chunk-time

pt-osc 실행 시 처음에는 —-chunk-size 기본값인 1000개씩 insert를 진행하게 되나, 이후에 --chunk-time 옵션값으로 지정된 시간에 맞게 insert query가 진행될 수 있도록 동적으로 chunk size를 조절합니다. 기본값은 0.5초로 설정되어 있습니다.

--recursion-method DB clustering이 되어있는 경우 pt-osc 에서는 insert를 한 결과가 replication DB에 잘 적용되었는지 확인하는 과정을 중간중간 진행하게 됩니다. 이 때 replication DB의 주소를 알아내는 방법을 설정하는 옵션입니다.

--alter-foreign-keys-method

RENAME 명령어로 table swap 작업을 마치고 기존 테이블에 foreign key constraint가 걸려있던 child table 이 있다면 이를 update하는 작업이 추가로 필요합니다. 이때 어떤 방법으로 foreign key update를 할 것인지에 대한 옵션입니다.

--preserve-triggers

기존 table에 있던 trigger를 data copy 단계 이전에 new table로 복사합니다.

--sleep

DB 성능의 부하를 줄이기 위해 data copy chunk 사이에 sleep을 걸 수 있는 옵션입니다.

--max-load , --critical-load

pt-osc는 chunk별로 insert 이후 SHOW GLOBAL STATUS 를 실행해 Threads_running 값을 보고 --max-load (기본값 25)보다 크면 data copy를 잠시 중단했다가 이후 재개하고 --critical-load (기본값 50)보다 크면 바로 pt-osc 실행을 멈추게 됩니다. Threads_running이 아닌 다른 variable로도 설정할 수 있습니다.

pt-osc를 사용하며 겪었던 문제들

라포랩스에서는 AWS aurora mysql을 사용하고 있습니다. 로컬 머신에서 실행한 mysql db에 pt-osc를 테스트해보았을때는 발생하지 않았던 문제가 AWS RDS에 pt-osc를 실행했을 때 발생하는 경우들도 있었고, 옵션을 제대로 이해하지 못한 상태로 pt-osc를 사용하여 문제가 되는 경우들도 있었습니다. pt-osc를 사용하면서 겪은 문제들과 이를 해결하면서 얻은 교훈들을 정리해 보았습니다.

•

AWS aurora의 경우 log_bin_trust_function_creators = 1 로 설정하자

pt-osc는 data copy를 위해 trigger를 생성하는데, binary logging이 활성화된 db에서의 trigger 생성은 SUPER 권한이 있어야 합니다. 하지만 AWS RDS에서는 SUPER 권한을 가진 user는 aws 내부에서 사용되는 rdsadmin user만 있을 뿐, rds 생성시 발급받은 master user는 SUPER 권한이 없습니다. 따라서 pt-osc를 실행할 때 trigger 생성 시 실패하는 문제가 있었습니다.

이를 해결하기 위해 DB parameter 중 log_bin_trust_function_creators 를 1로 설정하여 SUPER 권한을 가진 user가 아니더라도 trigger를 생성할 수 있도록 해야 합니다.

•

cdc를 하는 다른 서비스들에 영향이 없는지 확인하자

pt-osc 실행 이후, 예상치 못한 곳에서 에러가 발생했었는데요, schema 변경을 실행한 table의 cdc를 하던 AWS DMS task가 실패하는 문제가 있었습니다. AWS DMS는 현재 RENAME 을 통해 table의 이름이 변경되는 경우 무조건 실패하게 되어 있고, AWS측에서 RENAME 명령어를 지원하도록 DMS를 개선하지 않는 이상 실패한 task를 수동으로 재시작 해 주어야 합니다.

binlog를 읽어 cdc를 하고 있는 또 다른 컴포넌트인 debezium은 문제가 없을지 확인을 해보니

Renaming non-whitelisted table x to whitelisted table y, this can lead to schema inconsistency

와 같은 로그가 debezium에서 발생하지만 기존의 cdc task가 실패하지는 않았습니다. 검증을 위해 pt-osc를 이용한 column 추가 실험을 해본 결과 추가된 column에 대해서도 별 다른 작업 없이 cdc가 잘 되는것을 확인할 수 있었습니다.

•

DB replication이 적용되어 있고 pt-osc 실행환경과 DB cluster가 다른 네트워크에 분리되어 있다면 --recursion-method=none 으로 설정하자

pt-osc는 --recursion-method 옵션의 기본값으로 SHOW PROCESSLIST 를 하여 replication DB의 host를 알아오려고 동작하게 되어 있습니다. 하지만 AWS RDS와 같은 DB에서 replication db를 찾기 위해 show processlist 방법을 사용하게 되면 pt-osc에서는 binlog dump를 찍고 있는 host를 replication db로 인식하고, 이 ip로 접속을 시도하며 pt-osc가 멈추는 에러가 발생합니다.

SHOW PROCESSLIST 를 실행한 모습입니다. Binlog Dump 작업을 하고 있는 connection의 host가 10.0.25.79 인 것을 확인할 수 있습니다.

PTDEBUG=1 환경변수를 설정한 채로 pt-osc를 실행해서 동작들을 확인해보니, SHOW FULL PROCESSLIST 실행 이후 host 10.0.25.79 를 parsing하는 것을 볼 수 있습니다.

이후 10.0.25.79 주소로 연결을 시도하는 것을 볼 수 있습니다.10.0.25.79 는 pt-osc가 실행중인 환경과 같은 네트워크 상에 있는 ip가 아니라 아무런 응답도 받지 못하고 이 상태로 pt-osc가 멈춰버리는 문제가 발생합니다. 실제로 10.0.25.79 는 replication db의 ip도 아닌 rds가 있는 네트워크 내에서 binlog dump 역할만을 수행하는 서버의 주소로 보여집니다.

이를 해결하기 위해 replication db 조회를 아예 하지 않도록 --recursion-method=none 으로 설정하여 문제를 해결할 수 있습니다. 이렇게 되면 pt-osc에서 제공하는 insert 이후 replication 이 잘 되었는지 검사하는 등의 기능을 사용할 수 없게 되는데요, 이러한 기능을 사용하기 위해서는 dsn table 을 두고 replication db host를 table에 명시해주는 방법 인 —recursion-method=dsn 옵션을 사용할 수도 있습니다.

•

--alter-foreign-keys-method=rebuid_constraints 로 설정하자

pt-osc 는 rebuild_constraints , drop_swap 두 가지 방법으로 foreign key update를 할 수 있습니다.

이때 drop_swap 방법은 --no-drop-old-table option을 무시하고 old table을 강제로 drop시킨다는 risk가 있습니다. --recursion-method 를 명시하지 않고 기본값인 auto 로 설정할 경우 child table의 크기가 클 경우 drop_swap 방식을 택해버리므로 명시적으로 rebuild_constraints 로 설정하여 drop_swap 이 일어나지 않도록 설정하는 것이 좋습니다.

•

--sleep 과 --chunk-time option을 잘 사용해서 부하를 조절하자

data copy 과정에서 INSERT문을 처리해야 하는 writer db의 경우 CPU 사용량이 올라가게 됩니다.

master DB의 CPU 사용량이 너무 올라가게 되면 pt-osc가 아닌 다른 요청을 처리하는 데에도 지장이 발생하게 되니, 이를 테스트해볼 수 있는 환경에서 —chunk-time 을 적절히 줄이고 copy chunk 사이 --sleep 을 넣어가며 CPU가 급격히 오르지 않도록 설정했습니다.

Thread간 CPU time starvation이 발생하는 경우도 Thread가 실행중인 query의 처리 속도를 늦추게 되니, Threads_running 값이 늘어나는 결과를 만들기 때문에 --max-load 와 같은 옵션으로 인해 일시 중지 후 재시작하도록 설정할 수도 있을 것 같지만, CPU 사용량이 90%, 100% 가 되어 starvation이 생기는 상황이 만들어지면 pt-osc 실행을 중지하더라도 이로 인한 다른 여러 문제들이 동시다발적으로 발생할 가능성이 있어 결국은 failover 처리 등을 해주어야 할 수도 있습니다.

따라서 pt-osc 실행 중 CPU 사용량 모니터링은 꼭 같이 해주어 CPU 사용량이 어느 수치 이상 오르지 않는지를 확인하는 과정이 필요합니다.

•

pt-osc가 안전하게 실행될 수 있는 환경을 만들자

처음 pt-osc를 도입하였을 때 local machine에서 RDS에 연결할 수 있도록 네트워크를 구성하여 pt-osc를 실행하였습니다. 하지만 이런 경우 pt-osc가 실행되고 있는 도중 local machine 을 계속 awake 상태로 두어야 하고 인터넷도 전원도 끊기지 않게 두어야 했습니다. 크기가 큰 table일수록 data copy에 오랜 시간이 걸리게 되고 8시간 이상 시간이 소요된 적도 있습니다. 만약 도중에 인터넷 연결이 끊어지거나 하면 처음부터 다시 pt-osc를 실행해야 하는 문제가 있었습니다.

이러한 문제를 개선하기 위해 pt-osc가 설치된 container를 kubernetes에서 job형태로 실행하도록 하였습니다. 라포랩스에서는 여러 실험을 통해 찾은 최적의 parameter로 pt-osc 실행을 할 수 있도록 Helm Chart 로 만들어두어 아래와 같이 몇 가지 override value로 helm install 명령어를 통해 pt-osc 실행을 간편하게 할 수 있게 되었습니다.

pt-osc 사용의 한계점

pt-osc를 도입하여 잘 사용하고 있던 도중 pt-osc가 끝날 때 drop trigger를 하는 와중에 DB에 deadlock이 발생하는 문제가 있었습니다. SHOW PROCESSLIST 및 SELECT * FROM information_schema.innodb_trx 를 통해 데드락을 유발하는 쿼리가 무엇인지 찾아 이를 종료시켜 deadlock을 해소하고자 했으나 단시간 내에 찾아내기 어려웠고, 빠르게 장애 상황을 해소하는 것이 더 중요하다고 판단하여 DB Failover 처리를 통해 문제를 해결했던 경험이 있습니다.

이후 후속 조사를 통해 알게 된 것으로는, trigger를 사용하는 경우 lock이 발생할 수 있으며 이것이 실행 중인 다른 query들과 경합을 하게 되는 상황이 발생할 수 있다는 것이었습니다. trigger를 사용한 data copy시 발생하는 여러 문제를 해결하고자 trigger 대신 binlog 기반으로 data copy를 하는 gh-ost 라는 tool도 있어 도입해보면 좋겠다고 생각됩니다.

마무리

이번 글에서는 service downtime 없이 RDB의 schema 변경을 도와주는 pt-osc 사용법과 겪었던 이슈들을 소개해 드렸습니다.

pt-osc는 DB schema의 변경을 service downtime 없이 할 수 있게끔 도와주는 좋은 툴이지만, 라포랩스에서는 DB Schema 변경이 필요한 모든 상황에 pt-osc를 이용하지는 않습니다. 상황에 따라 크기가 작은 table이고 active transaction이 많지 않다면 online ddl로도 충분히 빠르면서 서비스 중단 없이 schema change를 안전하게 할 수 있어 DB schema를 변경해야 하는 상황에 두 가지 선택지를 충분히 고려하여 사용하고 있습니다.

또한 pt-osc 와 같은 툴을 이용하더라도 테이블의 크기가 너무 크다면 data copy를 하는 과정에서 시간이 너무 오래 소요되고 이 작업이 완료되기를 기다리느라 기능의 배포가 늦어지는 문제가 있습니다. 퀸잇 서비스도 사용자가 빠르게 증가함에 따라 데이터가 늘어나는 속도가 점점 빨라지면서 앞서 설명한 문제를 자주 겪고 있습니다. 라포랩스에서는 NoSQL의 도입을 통해, 또 크기가 큰 테이블들의 데이터를 partioning & archiving을 하는 방법 등으로 이러한 문제를 해결해볼 계획입니다.

References

김영진 Kim Youngjin

라포랩스 서버 플랫폼팀 : 서버 엔지니어 ”서비스를 개발하고 운영하며 다양한 문제들을 해결하는 과정에서 항상 더 나은 코드와 아키텍처를 고민하는 백엔드 개발자입니다.”

빠르게 성장하는 서비스를 개발하고 운영하는 경험에 함께 하고 싶으시다면?

Product Team : 라포랩스 제품팀 소개✨

"퀸잇(Queenit)"은 같은 이커머스 플랫폼이어도 4050세대, 즉 X세대 고객을 위한 서비스이기 때문에 조금 특별합니다. 우리의 고객은 흔히 하는 예상과는 다른 패턴을 보이거나 피드백을 보내기도 합니다. 그렇기 때문에 라포랩스에서는 빠르게 다양한 실험을 시도하고, 실험결과에 따른 데이터로 의사결정을 하는 일이 매우 중요합니다. 이는 우리의 여정이 흔하지 않고 쉽지 않은, 꽤 도전적인 여정이 될 것이란 의미이기도 합니다.

https://www.rapportlabs.kr/product

Update : 2023.03.15.