Category Archives: MariaDB

MySQL에서 Affected Rows를 병맛나게 활용해보자.

Overview

이제 슬슬 날이 풀려가고 있습니다. 얼어붙은 땅이 녹듯이, 오랜시간 얼어있던 블로그 공간도 잠시마나 녹여볼까 합니다. 사실 지난  “PMM 이야기 1편” 이후 2편, 3편 쭉 써야하지만.. 이노무 귀차니즘과 여기저기 산재한 낙서들을 아직 정리하지 못한 탓에.. 쿨럭..

사실 오늘 얘기할 내용은 3년도 훨씬 전 내용으로, 블로그로 이미 정리했다고 지금까지 착각을 했던 이야기입니다. 바로 “Affected Rows” 값을 활용해서, 다양한 요구 사항을 조금 더 재미있게 풀어보자는 내용이죠.

Affected Rows?

다들 아시겠지만, Affected Rows는 DML시 실제로 영향을 미친 데이터 Row 수입니다. 보통 update/delete를 날린 후에 몇 건의 데이터가 변경이 되었는지를 CLI툴에서 확인하는 용도로만 “제 경우”에는 많이 사용하고는 했습니다.

참고로 MySQL에서 Affected Rows는 “정말로 데이터가 변경”된 경우에만 반영되며, 하단과 같이 기존 데이터에 변화가 없는 경우에는 Affected Rows는 0건으로 보여집니다. (이 내용은 중요해요!)

mysql> insert into test values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> update test set j = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update test set j = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MySQL만 제공하는 다음 두 개 쿼리는 조금 재미있게 동작합니다.

REPLACE INTO .. VALUES ..
INSERT INTO .. ON DUPLICATE UPDATE ..

REPLACE 구문은 우선 넣고자 하는 데이터와 충돌이 되는 ROW는 DELETE 후 INSERT하는 특성을 가집니다. 트리거 기반의 온라인 스키마 변경 툴 중 대표적인 pt-online-schema-change의 INSERT/UPDATE 트리거 이벤트는 REPLACE로 되어 있죠. (방해꾼 PK/UK가 없으면, 그냥 계속 누적 INSERT됩니다.ㅋㅋ)

INSERT INTO ..  ON DUPLICATE UPDATE .. 는 PK 혹은 UK로 인한 중복된 값 에러 발생 시 이를 뒤에 명시한 값으로 데이터를 업데이트하라는 의미입니다. (다들 아실꺼예요.) 없으면 알아서 초기화하고, 있으면 업데이트하는 형식의 굳이 초기화할 필요없는 통계 테이블을 관리하는 용도로 사용한다고나할까.. -_-;

오늘 재밌게 가지고 놀 녀석은 바로 두 번째 녀석 “INSERT INTO .. ON DUPLICATE UPDATE”입니다.

Crazy Question

벌써 3년도 더 된 이야기네요. 엯촋 개발자 분에게 아래와 같은 질문을 받습니다.

(1)일정 주기에 따라 이벤트 참여 (2)카운트를 제한하고 싶어요~!

하루 혹은 일정 주기에 따라 이벤트 등록 횟수를 제한하고자 하는 요구사항입니다. 예를들어 한 시간 기준 이벤트을 생각해본다면, 매 0시마다 지금까지 이벤트 참여 카운트는 초기화하고, 다시 정해진 수만큼 응모를 하는 그런 형태의 요구사항이죠. 아.. 물론.. 어플리케이션 레벨에서 트랜잭션을 구성한다면, 아래와 같이 생각해볼 수 있을 것 같네요. (그냥 막 쓴 것 아시죠? ㅋㅋ)

try{
  execute("BEGIN");
  row = execute("SELECT * FROM user_event WHERE user_id = 100 FOR UPDATE");

  // 1. 시간 적절성 체크
  if(last_applied_time == CURRENT_DATE){
    // 2. 카운트 적절성 체크
    if(apply_count < 5){
      execute("UPDATE user_event SET apply_count = apply_count + 1 WHERE user_id = 100");
    }
  }else{
    // 3. 데이터 초기화
    execute("UPDATE user_event SET apply_count = 1 WHERE user_id = 100");
  }
  execute("COMMIT");
}catch(Exception e){
  execute("ROLLBACK");
}

뭐, 저렇게 하면 되니까.. 저렇게 풀어도 되겠지만.. 이런 처리를 조금 더 병맛나고 재미있게 풀어볼계요.

My Solution

자, 우선.. 테스트 하기에 앞서 테이블을 하나 생성합니다. 구조가 참 간단하쥬?

CREATE TABLE `user_event` (
  `user_id` int(11) NOT NULL,
  `apply_count` int(11) NOT NULL,
  `last_applied_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB

그냥, 사용자 기준응모 카운트와 마지막 응모 시간(타임스탬프)을 가지는 지극히 간단한 테이블입니다. 이제, 요구 사항에 맞게 쿼리를 아래와 같이 만들어보겠습니다. 우선은 데이터가 없을 수 있으니, INSERT 합니다.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) ;

문제는 그 다음에도 동일한 사용자에 대한 처리를 위해서 ON DUPLICATE KEY UPDATE로 apply_count를 업데이트 관리합니다.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE apply_count = apply_count + 1

그런데 매일 0시마다 초기화를 해야 하겠죠? 마지막 응모 날짜와 오늘 날짜가 동일하면 응모 카운트를 증가합니다. (1)일정 주기를 만족하기 위해서, 아래와 같이 if로 분기 처리합니다.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE
       apply_count = if(date(last_applied_time) = current_date, apply_count + 1, 1)

이제 마지막입니다. (1)일정 주기 조건은 앞에서 맞췄으니, 이번에는 (2)카운트를 제한 조건을 아래와 같이 맞춰봅시다. apply_count는 현재 값이 5보다 작을 때만 1 올리자는 얘기지요.

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE
       apply_count = if(date(last_applied_time) = current_date, if(apply_count < 5, apply_count + 1, apply_count), 1)

이렇게 꼼수같은 꼼수같은 꼼수를 통해, 단 한줄의 쿼리로 원하는 요구사항을 충족시켜 보았습니다.

  • user_id(pk)를 포함한 데이터를 INSERT
  • PK 중복 시, 정해진 기간 내에 존재한다면,
    1. 카운트가 유효하다면, 응모 카운트 1 증가
    2. 정해진 카운트 도달 상태면 아무것도 안함
  • PK 중복 시, 정해진 기간 밖에 있다면,
    1. 응모 건 수를 초기화

자.. 쿼리 요구사항은 맞췄으니, 이제 Affected Rows에 따라 처리 결과를 분기 처리해봐야겠죠? 테스트를 위해 위 쿼리처럼 하루를 기다릴 수 없으니, 시간을 초단위(10초)로 구성해서 반복 수행해봅니다. (아래 쿼리 예시)

INSERT INTO user_event (user_id, apply_count) VALUES (1, 1) 
    ON DUPLICATE KEY UPDATE
       apply_count = if(timestampdiff(second, last_applied_time, now()) < 10, if(apply_count < 5, apply_count + 1, apply_count), 1)

그리고 아래와 같이 예쁜 결과를 얻어봅니다. 어플리케이션에서는 쿼리 결과로 전달받는 Affected Rows 값에 따라, 이 사람이 응모가 되었는지(1 이상), 제한된 상황인지(0)를 판단하면 되겠습니다.

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 1 row affected (0.00 sec) <= 없던 데이터

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count | last_applied_time |
+---------+-------------+---------------------+
| 1 | 1 | 2018-03-25 23:05:38 |
+---------+-------------+---------------------+

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 2 rows affected (0.00 sec) <= 있는 데이터

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count | last_applied_time |
+---------+-------------+---------------------+
| 1 | 2 | 2018-03-25 23:05:41 |
+---------+-------------+---------------------+

.. 중략 ..

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 0 rows affected (0.00 sec) <= 조건에 맞지 않음

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count | last_applied_time |
+---------+-------------+---------------------+
| 1 | 5 | 2018-03-25 23:05:46 |
+---------+-------------+---------------------+

.. 중략 .. 

mysql> INSERT INTO user_event .. ON DUPLICATE KEY UPDATE .. 
Query OK, 2 rows affected (0.00 sec) <= 초기화

mysql> select * from user_event;
+---------+-------------+---------------------+
| user_id | apply_count | last_applied_time |
+---------+-------------+---------------------+
| 1 | 1 | 2018-03-25 23:05:56 |
+---------+-------------+---------------------+

즉 정리를 해보면.. 아래와 같은 동작을 기대해볼 수 있겠습니다. ^^ 단, 자바에서는 이런 이쁜 결과를 받아보기 위해서는 useAffectedRows=true 파라메터를 줘야합니다. (관련: https://bugs.mysql.com/bug.php?id=39352)

Query OK, 1 row affected (0.00 sec)  <= 신규 ROW insert : 1
Query OK, 2 rows affected (0.00 sec) <= 데이터 업데이트 : 2
Query OK, 2 rows affected (0.00 sec) <= 데이터 업데이트 : 3
Query OK, 2 rows affected (0.00 sec) <= 데이터 업데이트 : 4
Query OK, 2 rows affected (0.00 sec) <= 데이터 업데이트 : 5
Query OK, 0 rows affected (0.00 sec) <= 데이터 변경 없음

Conclusion

데이터에 변경 사항이 없으면 Affected Rows 변화량 또한 없는 것은 MySQL의 고유 특성입니다. 이것은 DBMS에 따라 다르게 동작하기 때문에, MySQL에 (아마도) 의존적이죠.

그러나 이런 동작을 이해하고 활용해본다면, 더욱 병맛나는 문제도 해결할 수 있습니다. 일정 기간 응모 횟수를 제한하는 것 뿐만 아니라, 일단위 혹은 월단위 통계 테이블을 유지하는 것이나.. 어플리케이션 레벨의 락 용도로 충분히 써볼만 합니다. 모든 것은 각자의 문제 상황에 맞춰서 재미나게 이야기를 풀어보면 될 것 같네요. ㅎㅎ

“별 생각없이 넘어갈 수 있는 Affected Rows 도 병맛나게 활용해볼 수 있다”라는 이야기를 해보고 싶었어요.

좋은 밤 되세요, ㅎㅎ

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

Overview

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

pt-online-schema-change?

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

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

동작합니다.

pt-online-schema-change

pt-online-schema-change

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

Goals

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

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

그런데, 구닥다리 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 포멧으로 동작

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

  1. 네트워크 트래픽 과도로 인한 서비스 영향 발생 가능
  2. 바이너리 로그 과다 적재로 인한 디스크 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에 숨겨진 무시무시한 이슈!

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에서 제공하는 기능 몇가지와, 머리좋은 개발자의 “꼼수”가 환상적인 조합을 이루며 만들어진 멋진 툴입니다. 짝짝짝~

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

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

pt-online-schema-change

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를 주제로 한번 읊어보는 것도 좋겠다는 생각이..

좋은 밤 되시와요~!!