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 도 병맛나게 활용해볼 수 있다”라는 이야기를 해보고 싶었어요.

좋은 밤 되세요, ㅎㅎ

PMM 이야기 1편 – INTRO

Overview

정말 오랜만에 글을 써봅니다. 은행이 오픈한지도 어언 8개월째를 훌쩍 접어들었네요. 여전히 MySQL 서버군에는 이렇다할 장애 없이, 무난(?)하게 하루하루를 지내고 있습니다.. (아.. 그렇다고 놀고만 있지는 않았어요!!)

사실 그동안의 경험과 삽질을 바탕으로, 필요성을 느꼈던 다양한 부분을 중앙 매니저에 최대한 녹여보았고, 그 집대성의 결과가 지금 뱅킹 MySQL시스템입니다. MHA 관리, 스키마 관리, 파티션 관리, 패스워드 관리, 백업/복구 관리..아.. 또 뭐있더라.. -_-;; 암튼, 귀찮은 모든 것들은 최대한 구현을 해놓았지요.

그러나, 예전부터 늘 부족하다고 생각해왔던 한가지 분야가 있는데.. 그것은 바로 모니터링입니다. 시스템에 대한 가장 정확한 최신 정보는 바로 모니터링 지표입니다. 만약, 제대로된 모니터링 시스템 환경 속에서, 실제 서비스의 영속성과 시스템의 매니지먼트를 “모니터링 지표”를 통해서 제대로된 “에코 시스템”을 구축할 수 있다면? 등골이 오싹할 정도의 선순환 작용으로 엄청 견고한 시스템을 구축할 수 있겠죠.

그래서, 한동안 관심가지는 분야가 바로 모니터링, 그중 Percona에서 오픈소스로 제공하는 PMM 요 녀석입니다. 우선은 서두인만큼.. 간단하게 PMM 소개를 해보고, 무엇이 부족한지 썰을 풀어보도록 하겠습니다.

PMM?

PMM(Percona Monitoring and Management)은 Prometheus 기반의 모니터링 솔루션입니다. 바로 몇년전 Percona 의 블로그에 이 관련해서 내용이 포스팅된 적이있었고, 한번 해보고 “와~” 하고 넘어갔었는데.. (은행 구축단계라..쿨럭)
(https://www.percona.com/blog/2016/02/29/graphing-mysql-performance-with-prometheus-and-grafana/)

Prometheus의 여러 프로젝트를 Percona 엮어 재구성 하고, 필요한 것들은 추가로 구현하요 Docker 이비지로 배포하고 있는 녀석이 바로 PMM입니다. ^^

pmm-diagram
pmm-diagram

뭔가 굉장히 복잡해 보입니다만.. 여기서 중요한 것 몇가지만 추려본다면..??

1. node_exporter, mysqld_expoter

모니터링 대상 노드(서버)에 서버에 데몬 형태로 구동되는 에이전트입니다. Prometheus에서 에이전트 포트로 매트릭 요청을 하면 즉각 서버의 현 상태를 보내는 역할을 합니다. Percona에서 Prometheus의 exporter를 (아마도) 포트떠서 구현해놓은 듯 하군요.

<Prometheus 프로젝트>
https://github.com/prometheus/node_exporter
https://github.com/prometheus/mysqld_exporter

<Percona 프로젝트>
https://github.com/percona/node_exporter
https://github.com/percona/mysqld_exporter

node_exporter쪽은 제대로 보지 않았지만, percona의 mysqld_expoter는 아래와 같이 크게 세 가지로 나눠서 매트릭을 제공한다는 점에서 기존 Prometheus와는 큰 차이가 있습니다. (참고로, Prometheus는 Parameter 전달 형식으로 매트릭을 선별하여 제공합니다.)

  • mysql-hr (High-res metrics, Default 1s)
    Global Status / InnoDB Metrics
  • mysql-mr (Medium-res metrics, Default 5s)
    Slave Status / Process List / InnoDB Engine Status.. etc
  • mysql-lr (Low-res metrics, Default 1m)
    Global Variables, Table Schema, Binlog Info.. etc

자세한 것은 하단 깃헙 142 라인부터 확인을 해보세요. ㅎㅎ
https://github.com/percona/mysqld_exporter/blob/master/mysqld_exporter.go

참고로, 저기 나열되어있다고, 모두 활성화해서 올라오지 않습니다. exporter의 구동 파라메터에 따라 선별되어서 수집이 됩니다.. (예를들어 Query Digest 정보는 기본 수집되지 않습니다.) /etc/init.d 하단에 위치한 에이전트 구동 스크립트를 보시면 될 듯..^^

2. pmm-admin

각종 exporter들을 구동 및 제어를 하는 녀석입니다. pmm-admin은 노드 관리를 효과적으로 하기 위해, PMM에 포함된 Consul 서버에 exporter 에이전트 접속 정보(아이피와 포트)를 등록합니다. Prometheus는 매 수집주기마다,  Consul 에 등록된 에이전트 리스트를 가져와서 모니터링 매트릭을 수집합니다.

3. pmm-mysql-queries

DB 혹은 OS의 매트릭 정보들은 Prometheus에서 수집/저장을 합니다. 오로지 매트릭만.. -_-;; Prometheus의 동작과는 별개로, DB 서버에서 pmm-server로 데이터를 던지는 녀석이 있는데, 이것은 유입 혹은 슬로우 쿼리에 대한 정보입니다. QAN API로 데이터를 던지는데.. pmm-server의 80포트 /qan-api에 던지면, nginx가 이 데이터를 받아서, 127.0.0.1:9001로 토~스 하여 최종적으로 쿼리를 수집하는 과정을 거칩니다.

한마디로, 쿼리 수집을 위해서는 “[ DB ] –80–> [ PMM ] 형태로 포트 접근이 가능해야한다” 합니다. ㅎㅎ

물론, Prometheus로만으로도 얼마든지 수집이 가능합니다만.. Percona에서 제공하는 QAN 플러그인을 활용할 수 없다는.. -_-;; 쿨럭 사실.. QAN 하나만으로도 블로그 한두개 나올 정도이니.. 나중에 얘기를 해볼께요.

4. Prometheus

타임시리즈 기반의 저장소로, 사실상 PMM의 핵심입니다. 데이터 수집을 비롯해서 저장 그리고 쿼리 질의, Alert까지.. 에이전트의 포트에 접근해서, 모니터링 시스템의 매트릭을 Pull 방식으로 끌어오는 역할을 하지요.
https://prometheus.io/docs/introduction/overview/

앞에서 간단하게 Consul에 대해서 얘기를 했었는데.. pmm-admin이 pmm의 consul에 모니터링이 될 대상들의 아이피와 포트 번호를 넣어주면, Prometheus는 이 정보를 바탕으로 등록된 scrape_interval마다 각 DB서버 에이전트에 접근하여 모니터링 지표를 수집합니다.

- job_name: mysql-hr
  scrape_interval: 1s
  scrape_timeout: 1s
  metrics_path: /metrics-hr
  scheme: http
  consul_sd_configs:
  - server: localhost:8500
    datacenter: dc1
    tag_separator: ','
    scheme: http
    services:
    - mysql:metrics

이것 외에도 Grafana에서 지표를 보여줄 데이터 저장소 역할도 하며, AlertManager 기능도 제공합니다.  Prometheus에 대한 조금더 자세한 설명은.. 훗날(?) 다시 얘기해보도록 하겠습니다. (이거 하나로 책 한권 사실 나옵니다 -_-;; 아놔)

5. Consul

pmm-admin이 consul에 모니터링 대상이 될 장비를 등록하면, 하단과 같이 consul에 저장되고, prometheus는 이 에이전트에 접속하여 매트릭을 수집합니다.

consul
consul

6. Grafana

데이터의 핵심이 Prometheus였다면, Visualization의 핵심은 바로 Grafana입니다. PMM이 강력한 이유가 바로 이미 만들어져 있는 다양한 Grafana 대시보드라고 생각합니다. 적어도 지표를 보여주는 부분에서는 타의 추종을 불허합니다. ^^ 특히나, 내가 원하는 그래프를 내 뜻대로 추가로 마음껏 만들 수 있다는 점에서는 더욱 매력적이죠.

grafana
grafana

Grafana 에서도 나름의 Alert을 설정하고 보낼 수 있습니다만.. Template Variable을 사용할 수 없기 때문에.. 각 지표마다, 각 서버마다 모두 수작업으로 한땀한땀 등록해야하는 노고가.. (향후 Template Variable을 사용할 수 있게 개선된다고는 하는데..흠..)
https://www.percona.com/blog/2017/02/02/pmm-alerting-with-grafana-working-with-templated-dashboards/

상단 링크를 읽어보면, 어떤 상황인지가 확 와닿을듯.^^

7. Orchestrator

MySQL의 Mater/Slave 토폴리지를 그려주는 녀석으로.. 아직까지 주의깊게 구성해보지는 않았습니다. 수십, 수백대 관리를 고려해봤을때.. 왜 해봐야할지 당위성을 가질 수 없더군요. 그래서 팻패스

Problem

PMM의 구성요소들을 쭉 열거해보았습니다만.. 사실.. 저것 요소 하나하나가 굉장히 큰 꼭지예요. ㅠㅠ 가장 만만한 exporter 하나하나도 해당 시스템 세세한 부분까지 수집하는 큰 프로젝트입니다. ㅠㅠ

아무튼, 지금까지 이것저것 해본 결과에 따라 현재 대단히 부족한 점 몇 꼭지를 콕 찝어서 얘기를 해보도록 하겠습니다.

1. 알람 발송

냉정하게 얘기하자면, 현재 PMM에서는 시스템 문제 시 알람을 효과적으로 발송할 수 없습니다. 나름 Grafana의 Alerting 기능을 통해서 흉내 정도 낸 것같은데.. 실무 적용에는 턱없이..아니 절대 불가합니다. 앞서 얘기한 것 처럼, 모든 지표와 인스턴스에 따른 임계치를 한땀한땀 등록하면 가능하겠지만.. 이건 인간이 할 일은 아니겠죠?

가장 좋은 방안은 Prometheus의  AlertManager를 활용하는 것인데.. ㅎㅎ

2. 수집 주기

수집 주기를 유연하게 변경할 수 있는 Docker 파라메터가 부족합니다. PMM에서 기본 수집 주기는 linux/mysql-hr은 1초, mysql-mr은 5초, mysql-lr은 1분입니다. 사실 10대 미만만 모니터링한다면, 이 수집 주기에 큰 이슈는 없습니다만.. PMM 서버 한대에 20대 이상의 모니터링 대상들을 붙이기 시작하면, 대 재앙이 펼쳐집니다. (로드 20,30,40 펑! ㅋㅋㅋ)

문제는 Docker 이미지로만 제공되는 PMM에서, Docker 컨테이너 초기화 시 옵션 제어가 제한적이라는 점에 있습니다. 실제 PMM Docker 컨테이너의 /opt/entrypoint.sh를 확인해보면.. 그나마 1초 수집주기 항목만 1~5초 사이로 조정할 수 있는 정도로만 구현해놓았습니다. -_-;; 뷁!

# Prometheus
if [[ ! "${METRICS_RESOLUTION:-1s}" =~ ^[1-5]s$ ]]; then
    echo "METRICS_RESOLUTION takes only values from 1s to 5s."
    exit 1
fi
sed -i "s/1s/${METRICS_RESOLUTION:-1s}/" /etc/prometheus.yml

3. 익명 로그인

이건 당췌 이유를 알 수 없습니다. PMM에 포함된 Grafana 기본은 익명 사용자도 로그인 없이 지표 확인이 가능하게 되어 있습니다.

https://github.com/percona/pmm-server/blob/master/playbook-install.yml 의 209 라인을 보면 분명 의도적으로 로그인 없이도 접근 가능하도록 설정합니다. (왜?)

- name: Grafana                    | Enable Anonymous access
  when: not grafana_db.stat.exists
  ini_file:
    dest: /etc/grafana/grafana.ini
    section: auth.anonymous
    option: enabled
    value: true

4. 진짜 대시보드

진정한 의미의 대시보드가 없습니다. 특히나, 장애 알람 기능이 현재 전무하기 때문에, 현 시점 이상이 있는 시스템을 재빠르게 캐치할 수 있는 방안이 없습니다. 즉, 지표 보기는 굉장히 최적화가 되어 있습니다만, 문제 발생 시 빠른 감지 및 정보 제공 측면에서는 굉장히 취약한 상태라고 봐야겠지요, ㅜㅜ

참고로, 제가 원하는 대시보드는 아래와 같은 모습이며, 특정 서버 문제 시 바로 현황 파악이 가능한 모습입니다.

dashboard
dashboard

5. Grafana Sqlite

PMM에 포함된 Grafana는 모든 메타 정보(대시보드를 포함한 지표 정보)를 SQLite에 저장합니다. 사실, 지표에 별다른 수정없이 기본값 그대로 사용한다면 큰 문제는 없지만.. 그렇지 않다면 생각보다 꽤나 많은 부분을 변경해야합니다. ㅠㅠ JSON 스트링 그대로 SQLite TEXT컬럼에 저장되어 있기 때문에.. 의외로 대량 편집에 상당 부분 노고가 필요합니다. 게다가.. 데이터 잘못 삭제 시.. 복구 방안은?? -_-;;

PMM안에도 mysql 이 기본 설치되기 때문에.. 이왕이면 이 녀석을 잘 활용했으면 하는 바램이;; 쿨럭 (무엇보다, 이럴꺼면, mysql5.7의  json 타입을 활용해보면 어떨까 생각도 합니다.)

Conclusion

Percona는 PMM을 Docker 이미지로 배포합니다. 사실 Docker로 배포되지 않는다고 한다면, 나름 커스터마이징이 용이할 것 같다는 생각은 듭니다만.. Percona에서 기능 구현 후 새로운 Docker를 배포한다고 생각해봤을 때, 자칫 잘못하면 그간의 노고가 한방에 날라갈 수 있겠습니다.

즉, 개인적인 생각이기는 하지만.. Docker 컨테이너를 임의로 수정하기 보다는, 최대한 PMM 의 흐름에 맞춰서 필요한 부분을 취하는 것이 현명할 것이라 생각합니다. 물론 필요하다면, Percona도 설득도 해봐야겠죠. ^^

우선, 당장은 PMM을 실무에 바로 적용하기에는 무리가 있습니다..만.. 이 부족한 부분을  기존 소스 흐름에서 크게 벗어나지 않는 수준으로 어떻게 대안을 찾아가고 있는지.. 다음 블로그부터 하나하나 정리를 해보도록 하겠습니다.
(참고로, 앞선 문제점들은 대나름의 해결 방안을 만들어놓았습니다. ㅎㅎ)

일단, PMM 서버 구성을 해봐야겠죠? ^^

소소한 데이터 이야기 – 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 툴로만 인지를 했었지만.. 서비스가 무시무시하게 사악(?)해지고 나니 돌다리도 쇠망치로 두드려보며 건너보게 되더군요.

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

좋은 밤 되세요. ㅋ