소소한 데이터 이야기 – pt-online-schema-change 편 –

Posted on 2017-08-24 | by gywndi | 3 Comments

Overview

MySQL 5.6부터는 Online ddl 기능을 제공하기 시작하였지만, 사실은 이전에도 트리거 기반의 online alter 유틸로 서비스 중단없이 테이블 스키마 변경을 수행했었습니다. 이중 percona에서 제공해주는 pt-online-schema-change가 많이들 활용되고 있는데요. 오늘은 돌다리도 망치로 때려가면서 안정성에 신중히 접근한 우리의 케이스에 대해서 데이터 기준으로 얘기를 해보고자 합니다.

pt-online-schema-change?

얘기하기에 앞서서, 이 툴에 대해서 다시한번 짚어보겠습니다. 대충 동작 순서는 아래와 같이..

변경할 스키마 구조의 임시 테이블을 생성하고,
insert/update/delete 트리거를 만들어서 최근 변경 데이터를 동기화하고,
처음부터 끝까지 일정 청크 사이즈로 읽으면서 임시 테이블에 복사한 후,
완료되면 RENAME TABLE하여 완료

동작합니다.

조금더 시각화된 설명을 원한다면. 하단 블로그를 참고하세요.
>> http://small-dbtalk.blogspot.kr/2014/02/mysql-table-schema.html

Goals

24*365 서비스인만큼, 목표는 여전히 명쾌합니다. 심플하쥬?

무중단 스키마 변경
서비스 영향도 제로

그런데, 구닥다리 MySQL 버전을 사용하지 않으면서, 왜 pt-online-schema-change와 같은 툴 얘기를 꺼내냐고요? 우선은 상황에 따라 가능하지 않기 때문입니다.
>> https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

DML이 블록킹되는 케이스(Permits Concurrent DML이 NO인 경우) 에서는 절대적으로 온라인 서비스 적용이 불가합니다.

혹은 아래와 같이 다수의 alter를 동시에 적용하고자 하는 케이스도 찾아볼 수 있고..

alter table tab
 add excol01 varhcar(10),
 add excol02 text,
 add key ix_col01(col01),
 add key ix_excol01(excol01);

로그성 테이블이 일정 사이즈 이상된 시점에 “파티셔닝 적용하는 케이스”도 생각해볼 수 있겠네요.

그렇기에, (개인적인 생각으로는) 아무리 online ddl 기능이 좋아질지라도, pt-online-schema-change와 같은 트리거 기반의 스키마 변경 유틸은 여전히 유효할 것으로 조심스레 예측해봅니다. 적용 여부 판단은 데이터쟁이의 판단 하에..ㅎㅎ

Risk Point

아무튼 지금까지 우리의 상황을 정리해보고자 한다면..

MySQL의 online ddl 사용 불가
서비스 영향도 없은 무중단 스키마 변경

두 가지 상황이고, 이 난관 극복을 위해서 “트리거 기반의 유틸”인 pt-online-schema-change를 활용하기로 하였습니다.

우선 pt-online-schema-change 동작 로직 중, 트리거를 통한 트래픽 발생은 어느정도 예측할 수 있습니다. 타겟 테이블에 발생하는 트랜잭션 양만큼 딱 증가할 것이기에, 현재 데이터 변경량을 보면 어느정도 트랜잭션이 더 늘어날지는 어느정도 판단이 가능하죠.

문제는 처음부터 끝까지 청크 사이즈로 읽어가면서 임시 테이블에 데이터를 복사하는 경우 이 부분인데요. 데이터 복제를 위함이든, 데이터 복구를 위함이든, MySQL에는 바이너리 로그가 거의 필수입니다. 즉, 데이터 복사를 위한 처리 부분도 어떤 방식이든 바이너리 로그에 기록됩니다. 최근에는 바이너리 로그 포멧이 변경된 ROW 자체가 기록이 되는 ROW 포멧 방식으로 대부분 동작합니다. 게다가 만약 트랜잭션 ISOLATION LEVEL을 READ-COMMITTED 사용하고자 한다면, ROW FORMAT이 전제 조건입니다.

여기서 우리의 상황에 세번째 항목을 붙여 아래와 같이 얘기해봅니다.

MySQL의 online ddl 사용 불가
서비스 영향도 없은 무중단 스키마 변경
바이너리 로그는 ROW 포멧으로 동작

처음부터 끝까지 데이터를 카피하는 상황에서, 바이너리 로그 사이즈가 기하급수적으로 늘어나는 것에 대한 영향도를 최소화해야 합니다. 대략 다음 두가지 정도?

네트워크 트래픽 과도로 인한 서비스 영향 발생 가능
바이너리 로그 과다 적재로 인한 디스크 Full 발생 가능

서비스에 직접적인 영향을 미칠 뿐만 아니라, 잘못하면 서비스 불능 상태로까지 이어질 수 있습니다. (특히 2번 케이스는.. 서비스 멈춰요~ ㅜㅜ)

Let’s solve

문제가 있으면 해결하면 되고, 해결할 수 없으면 대안을 마련하면 되고.. 아무튼.. 임팩 최소화 노력을 해보도록 하죠.

1. Reduce Chunk Size

Chunk 단위로 데이터를 카피하는 구조이기 때문에, 다량의 로그가 슬레이브 서버로 스파이크 튀듯이 전송되는 상황은 막아야합니다. 순간순간 바이너리 로그 폭발(?)이 일어나며 서비스 영향을 줄 수 있는 요소가 있습니다.

예를들자면, 1G짜리 테이블 100만건과 20G짜리 100만건 테이블 중, 우리에게 주어진 상황에서 더욱 리스크한 녀석은 누구일까요? -_-; 당연히 20G짜리 테이블입니다.

동일한 ROW 사이즈로 데이터 복사를 해버리면, 매 트랜잭션마다 꽤나 큰 바이너리로그가 한방에 생성됩니다. 특히 semi-sync를 쓰는 경우에는 이 전송에 따른 지연이 기존 트랜잭션에 영향을 줄 수도 있습니다. 그렇다면.. 이런 케이스에서 적용해볼 수 있는 방법은 Chunk Size를 줄여서 이런 리스크 요소를 최소화해보는 것입니다. 잘게잘게 잘라서.. 임팩을 줄여가면서.. 조금씩 조금씩.. (대신 쿼리량은 늘어나게 되버리는.. 쥬릅ㅜㅜ)

pt-online-schema-change 툴에서는 chunk-size 옵션으로 제거 가능하며, 이 값을 기본값(1000)을 적절하게 하향 조정해봅니다. 물론 각 Copy 사이사이마다 일정 시간 쉬어갈 수 있는 interval이 있다면.. 더욱 제어가 쉬웠을텐데. 아쉽게도, 아직은 제공하지 않습니다. (만들어서 percona에 적용해달라고 푸시해볼까요? ㅋㅋ)

아무튼 이렇게해서 만들어진 스크립트 실행 구문은 아래 형태를 보이겠네요.

pt-online-schema-change \
--alter "add excol01 varhcar(10)" D=db1,t=tbname \
--chunk-size=200 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--chunk-index=PRIMARY \
--charset=UTF8 \
--execute

2. Change Session Variables

테이블 사이즈가 너무 커서, 바이너리 로그를 담기에 여의치 않을 때를 생각해봅시다. 물론 미리미리 binlog purge하면서 과거 로그를 제거해볼 수 있겠지만, 사실 백업/복구 입장에서 “데이터 변경 이력 로그” 삭제는 리스크할 수도 있습니다. Point-In Recovery가 안될 수도 있기 때문이죠.

이 경우에서는 데이터 측면에서 조금 다르게 접근해 보자면, 우선 서비스 환경은 아래와 같습니다.

현재 트랜잭션 ISOLATION LEVEL은 READ-COMMITTED이다.
현재 바이너리 로그는 ROW 포멧이다.

그렇다면.. 데이터를 카피하는 백그라운드 프로세스 기준에서도 위 조건을 충족해야할까요? 데이터 카피시 발생하는 쿼리를 SQL기반의 statement 방식으로 바이너리로그에 기록을 해보면 안될까요?

pt-online-schema-change에서의 세션 파라메터를 아래와 같이 지정을 해본다면,

COPY 프로세스의 트랜잭션 ISOLATION LEVEL은 REPEATABLE-READ이다.
COPY 프로세스의 바이너리 로그는 STATEMENT 포멧이다.

상황으로 접근해보면 어떨까요? pt-online-schema-change에서는 세션 파라메터로 set-vars 옵션에 각 파라메터 지적을 콤마로 구분해서 적용해볼 수 있습니다.

pt-online-schema-change \
--alter "add excol01 varhcar(10)" D=db1,t=tbname \
--chunk-size=200 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--chunk-index=PRIMARY \
--charset=UTF8 \
--set-vars="tx_isolation='repeatable-read',binlog_format='statement'" \
--execute

사실 이렇게 수행을 하면, 바이너리 로그 사이즈는 걱정할 필요 없습니다. 게다가 네트워크 트래픽도 거의 차지 않을 것이고. 참으로 안전해보이고, 무조건 이렇게 사용하면 될 것 처럼 생각할 수도 있겠습니다만.. 적어도 이로인한 영향도는 미리 파악하고 사용하는 것이 좋겠죠?

1. isolation level 에 따른 Locking

아무래도 isolation level 이 한단계 높은 수위(read-committed -> repeatable-read)로 관리되다보니, Lock 영향도를 무시할 수 없겠죠? “next key lock“이라든지.. “gap lock“이라든지.. 이런 영향도를 동일하게 받을 수 있다는 점을 인지하고 있어야 합니다. (물론 대용량 테이블에서는 영향도가 제한적이기는 합니다. ㅎㅎ)

2. 슬레이브는 여전히 ROW FORMAT

마스터에서는 STATEMENT FORMAT으로 바이너리로그 기록이 잘 되고는 있습니다만, 문제는 슬레이브에서는 여전히 “ROW FORMAT”으로 기록됩니다. 이건 쿼리 패턴(insert ignore .. select .. )에 따른 어쩔 수 없는 요소로.. 슬레이브는 그냥 주기적으로 purge하면서 대응을 하는 것이 제일 현명해 보이네요. 아.. log-slave-updates 옵션을 ON 상태로 운영하는 경우만 해당되겠네요.

Conclusion

물이 흐르듯 데이터도 흐릅니다. 물길이 변하면 유속이 바뀌듯, 데이터도 마찬가지입니다.

흐름을 “잘 제어하기” 위해 온라인 툴을 활용하였고, 내가 원하는 모양으로 만들기 위해 거쳐갈 “데이터의 흐름“을 생각해보았습니다. 그리고, 발생할 수 있는 “리스크를 예측“해볼 수 있었죠.

예전에는 아무 생각없이 썼던, 손 쉬운 online alter 툴로만 인지를 했었지만.. 서비스가 무시무시하게 사악(?)해지고 나니 돌다리도 쇠망치로 두드려보며 건너보게 되더군요.

사실 이것이 정답은 아닙니다. 더 좋은 방안도 있을 것이고. 효율적인 개선안도 있을 것이고. 그렇지만, 데이터쟁이답게, 닥쳐올 미션들을 “데이터의 흐름”에 촛점을 두어 앞으로도 “장애없는 서비스“를 만들어가도록 노력만큼은 변함이 없을 것입니다. 🙂

좋은 밤 되세요. ㅋ

pt-online-schema-change에 숨겨진 무시무시한 이슈!

Posted on 2016-09-26 | by gywndi

Overview

최근들어 거의 연단위로 블로깅을 하나씩 올리는 듯 하는군요. 여기저기 시국이 어지럽고, 바쁘다는 말도 안되는 핑계를 무마시키기 위해.. 아무튼 간만에 블로깅 하나 올려봅니다.

MySQL은.. 특히나 온라인 스키마 변경이 취약합니다. 물론 5.6부터는 online alter기능이 포함되어 있다고는 하나.. 100% 완벽하게 모든 상황을 온라인스럽게 제공해주지도 않고.. 그렇다하더라도, 일정 트래픽 이상의 데이터 변경이 이루어지는 경우, 게다가 슬레이브 지연을 염두한다면.. 꺼려지는 상황이 있지요. (참고로, 마스터에서 온라인 스키마 변경이 이루어졌을지라도, 이 관련 alter구문이 슬레이브로 넘어갔을 때는, alter이후 데이터 변경을 수행해야 하므로, 그만큼 복제 지연이 발생합니다. 미네럴~)

아무튼.. 이런저런 이유로.. 기승전툴이라는 생각이드는데요. 그중, Percona에서 오픈소스 라이선스로 제공하는 pt-toolkit에 포함된 pt-online-schema-change툴을 저는 애용합니다.

참 좋지만.. 반드시 짚고 넘어가야할 이슈 하나만은 반드시 공유할 필요가 있다는 생각이 들어, 이렇게 늦은 시간 포스팅을 합니다. 🙂

pt-online-schema-change?

문제를 말하기에 앞서서, 먼저 pt-online-schema-change의 동작 방식에 대해서 정말 간~략하게 알아볼 필요가 있겠군요.

이 툴은 서비스 중지 없이 실시간으로 테이블 DDL Alter구문을 적용할 수 있도록 도와주는 하나의 “유틸리티”이며, 흔한 RDBMS에서 제공하는 기능 몇가지와, 머리좋은 개발자의 “꼼수”가 환상적인 조합을 이루며 만들어진 멋진 툴입니다. 짝짝짝~

간단한 테이블 변경 순서는 아래와 같아요.

클론 테이블 생성 후 의도한 스키마를 적용한다.
원본 테이블 변경 시 트리거로 이를 클론 테이블에 적용한다. (replace into/delete)
조각 조각 데이터를 잘라서, 데이터를 중복은 무시한 채 복사한다. (insert ignore)
테이블 복사가 완료되면, 클론<->원본 테이블명을 스위칭한다.

Percona..당신들의 잉여력은 많은 이들을 행복하게 하였습니다. 땡큐~!!

Problem

물론, pt-online-schema-change도 하나의 툴인 이상.. 분명 100% 모든 상황에 적합하지는 않습니다.

뭐.. 예를 들면.. 아무래도 데이터를 조각조각 나누어서 복사를 해야하는만큼, Primary Key는 반드시 존재해야하는 상황이어야하고, Foreign Key가 정의된 경우에도 제약이 있고.. 궁시렁궁시렁..

그렇지만, 오늘 여기서 짚어보자하는 문제는.. 사실은 pt-online-schema-change가 충분히 동작할 수 있는 상황에서, 데이터가 달라질 수 있는.. 곰곰히 생각을 해보면 서비스 오류까지 발생할 수 있는 그런 상황입니다요.

pt-online-schema-change에서 앞서 설명을 간단하게 했던 것처럼, 트리거를 통해서 현재 데이터 변경된 이력을 클론 테이블(스키마 적용된 최종본 구조 테이블)로 적용합니다. 적용시 insert/update 구문 모두 아래와 같이 replace into 구문으로 대동단결 하는데요. (참고로 _test_tmp는 클론테이블입니다.)

DELIMITER $$
CREATE TRIGGER trg_test_update
AFTER UPDATE ON test
FOR EACH ROW
BEGIN
 REPLACE INTO _test_tmp VALUES (NEW.id, .., NEW.created_at);
END$$
DELIMITER ;

동일 트리거이니.. 문제삼을 update 트리거만..

멀쩡한 상황에서는 이슈가 아닙니다만.. 여기서 replace의 동작 방식을 이해하고, 조금만 고개를 갸우뚱거리면 무엇이 문제인지 이해하실 수 있을 것이예요. 귀찮으니, 매뉴얼 원문을 퍼오겠습니다. 크하하

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserte

그렇답니다. 기존의 pk/uk와 같이 충돌나는 것들은 다 날리고.. 신규 데이터로 밀어넣는다는 것인데요. 그렇기에 pk가 없으면 대 재앙일 것이고.. 이런 부분에 대해서는 나름 pt-online-chema-change에서 제약 조건을 체크하기는 합니다. (그랬던가.. 가물가물..)

그렇다면.. 이런 경우 말고.. PK가 변경되는 상황을 생각해볼까요? 이런 상황에서도 역시 replace into 구문으로 트리거는 동작할 것이고.. 트리거 내부에는 “NEW.id, .., NEW.created_at”와 같이 변경될 데이터 기준으로 클론 테이블에 적용될 것이고.. NEW.id가 PK라면, 이 기준과 동일한 데이터를 날리고 엎어버릴 것이고..

그렇다면.. 우움.. 기준이 되는 NEW.id가 변경된 경우에는.. -_-;; 예전 OLD.id의 행방은??

딩동댕~ 맞습니다. ㅠㅠ 어이없게도.. 업데이터로 동작을 해야할 상황이.. 기준점이 변경되었다는 이유로.. 여전히 남아있는 상태가 되겠지요. 미네럴2.

즉.. 로직적인 이슈로.. 기준점이 되는 PK를 변경하는 경우 여전히 과거의 이력이 남아있게 되는.. 어이없는 상황이 발생한다는 것입니다!! <==== 주의주의1

Solutions

사실.. 해결책은 의외로 간단합니다.

PK는 업데이트 하지 말고, 트랜잭션으로 묶어서 DELETE -> INSERT 처리한다. 참 쉽죠잉~! 이것은.. 뭐.. 해결방안이라기 보다는 그냥 “우회처리”라고 해야하나.. -_-;;

그치만.. 사용자를 쿼리를 날리는 경우도 있고.. 기타 이런 상황을 인지 못하는 경우를 대비해서.. 본질적인 해결 방안이 필요합니다.

PK가 변경된 경우 트리거 내부적으로 과거 데이터를 날려주는 로직을 넣어준다.

트리거 정의하는 부분에.. PK 변경 시 OLD 이미지에 해당하는 데이터를 날리도록 강제하면 되지요. 뭐.. 아래 예시는.. 쿼리 레벨 간단하게 소스를 약간 수정한 것일뿐.. 조금 수정해서 트리거 내부에 if else 구문을 넣어도 되고..암튼 그렇답니다.

sub create_triggers {
  .. 중략 ..
  ## 추가 ##
  my $upd_index_cols = join(" AND ", map {
    my $new_col = $_;
    my $old_col = $old_col_for{$new_col} || $new_col;
    my $new_qcol = $q->quote($new_col);
    my $old_qcol = $q->quote($old_col);
    "OLD.$old_qcol <=> NEW.$new_qcol"
  } @{$tbl_struct->{keys}->{$del_index}->{cols}} );

  .. 중략 ..

  ## DELETE 부분 추가 ##
  my $update_trigger
    = "CREATE TRIGGER `${prefix}_upd` AFTER UPDATE ON $orig_tbl->{name} "
    . "FOR EACH ROW "
    . "BEGIN "
    . "DELETE IGNORE FROM $new_tbl->{name} WHERE !($upd_index_cols) AND $del_index_cols;"
    . "REPLACE INTO $new_tbl->{name} ($qcols) VALUES ($new_vals);"
    . "END ";
  .. 중략 ..
}

PK가 변경된 경우에는 데이터를 지워버리고, 그렇지 않으면.. 그냥 예전처럼 replace~ 트리거 내부적으로 update시에 쿼리는 많아지겠고, 트랜잭션이 없는 상황에서는 약간 걱정될만한 사항도 있겠지만.. 다들 기본적으로 InnoDB사용하시잖아요? ㅋㅋ

사실.. 이런 부분은 간단하게 소스를 수정해서 활용을 해도 됩니다. 어려운것도 아니고.. 잉여력이 조금 남아있다면 얼마든지 상황에 맞게 고쳐 쓸 수 있겠지요. 그치만.. 이 부분에 대해서 Percona에 문의를 해보니.. 그들의 입장은.. 번역하면 아래와 같았습니다.

어째서 PK가 변경이 되어야하는 것인가? 테이블에서 PK는 절대적이므로, 결코 업데이트 작업이 이루어지지 않아야 한다. 우리들은 이런 현상을 버그로 인정하지 않는다. – 어느 개발자 –

버그가 아니라면.. 이런 상황을 감지해서 제약을 걸든가.. 미네럴3..

사실.. 가장 경계를 해야하는 문제는.. (제 개인적인 의견이지만) 런타임 오류라고 생각합니다. 서비스 오픈 전에 발생하는 모든 이슈는 사전에 불을 끄고 튜닝하고 해결할 수 있지만.. 런타임 도중 특이 케이스에서만 노출되는 이슈는 정말로 답이 없습니다.

위와 같은 동일한 방식은 아니더라도, 적어도 PK변경 시 뒤따르는 이러한 데이터 꼬임 현상에 대한 것들은 Percona 측에서 제대로 이해하고 GE버전에 적용을 해줬으면 하는 마음이 있고.. 뭐.. 개인적으로, 조직적으로 푸시를 넣고 있습니다만..

좋은 소식이 있겠지요. ^^

Conclusion

pt-online-schema-change 툴은 굉장히 유용하고, 제가 정말 자주 쓰는 권고할만한 유틸입니다. 게다가 소스도 오픈되어 있고, 누구나 수정할 수 있는 라이선스 정책이고요.

그렇지만, 오픈소스를 사용하는만큼.. 그렇게 접근을 하기에.. 상용 솔루션과는 다르게 발생하는 이슈에 대해서 분석을 해보고 해결책을 가져가보고, 대안도 고민해보고.. 사용자 자신도 오픈소스를 사용하는 대가를 치뤄야 하겠지요. 뭐.. 제 능력으로는 지금과 같이 경험을 공유하는 것이 그나마 최선일 듯 합니다만.. ^^

올해 금융업에 뛰어들어, 오픈소스DBMS 적용을 어찌어찌 하다보니.. 예전에는 별 생각이 없이 넘어가던 가벼운 생각들도, 꽤나 진지한 주제로 제게 다가오네요.

다음 포스팅은 언제일지는 모르지만.. 시간이 될 때.. adt를 주제로 한번 읊어보는 것도 좋겠다는 생각이..

좋은 밤 되시와요~!!