MySQL 파티셔닝 테이블 SELECT가 느려요.

Overview

네이티브 파티셔닝 적용 이전의 MySQL은, 파티셔닝 파일들은 각각이 테이블로써 관리되었죠. 그래서, table cache 로 인한 메모리 부족 현상은 인지하고 있었습니다만.. 이것 외에는 특별히 성능 저하 요소는 없다고 생각해왔어요. (http://small-dbtalk.blogspot.com/2013/09/mysql-table-cache.html)

그런데, 얼마전 서버당 4개의 데이터베이스를 만들고, 각각 데이터베이스 안에 26개월로 분할된 파티셔닝된 테이블을 넣고, 간단한 Range scan 성능 테스트를 하였는데.. 말도안되는 수치를 보였습니다. 이 관련하여 간단하게 이에 대해 알아보도록 할께요. 🙂

Problem

하단과 같은 테이블 구조에서, 단순히 최근 10건의 데이터만 끌어오는 형식의 SQL을 다수 실행시켜 간단한 트래픽을 주었을 때.. 성능적으로 별다른 문제는 없을 것이라고 생각을 했습니다. 우리의 메모리는 기대치보다 훨씬 웃돌았기 때문에.. ㅎㅎ (참고로, InnoDB 버퍼풀 사이즈 대비 데이터 사이즈는 약 10배 이상이지만, 최근 파티셔닝 사이즈를 따지면, 버퍼풀 안에 충분히 들어올만한 상황이었습니다.)

## table
CREATE TABLE `tab` (
`COL01` varchar(16) COLLATE utf8mb4_bin NOT NULL,
`PAR_DATE` varchar(8) COLLATE utf8mb4_bin NOT NULL,
`COL02` decimal(10,0) NOT NULL,
`COL03` decimal(10,0) NOT NULL,
.. skip ..
`APLY_TMST2` timestamp(3) NOT NULL,
PRIMARY KEY (`COL01`,`PAR_DATE`,`COL02`,`COL03`),
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(PAR_DATE)
(PARTITION PF_201707 VALUES LESS THAN ('20170801') ENGINE = InnoDB,
 PARTITION PF_201708 VALUES LESS THAN ('20170901') ENGINE = InnoDB,
 .. skip ..
 PARTITION PF_201907 VALUES LESS THAN ('20190801') ENGINE = InnoDB,
 PARTITION PF_201908 VALUES LESS THAN ('20190901') ENGINE = InnoDB);

## Query
select * from tab
where col01 = ?
order by par_date desc, col02 desc, col03 desc
limit 10

그러나.. 예상과는 다르게, 말도안되는 퍼포먼스와, 이상한 IO패턴을 보이는 결과를 보였죠.

Why?

결론부터 이야기를 하자면, InnoDB. 파티셔닝에서.. 실행계획을 세우는 단계에서 모든 파티션에 약 1~3개 정도의 페이지를 읽게되는데.. 이런 동작으로 인하여 엄청난 비효율이 발생하고 마는 것이죠. 모든 파티션에 해당하는 블록들이 InnoDB 버퍼풀에 올라와있지 않기 때문이죠. (실제로 메모리에 존재할지라도, 쿼리당 수십개의 페이지에 접근한다는 것 역시도 부하라고 생각합니다.)

실행계획 단계의 요 부분에서, 불필요하게 파티셔닝을 접근을 시도해서, 이런 비효율을 발생시키는데…

for (part_id = m_part_info->get_next_used_partition(part_id);
     part_id < m_tot_parts;          part_id = m_part_info->get_next_used_partition(part_id)) {
  index = m_part_share->get_index(part_id, keynr);
  .. skip ..
  int64_t n = btr_estimate_n_rows_in_range(index, range_start, mode1,
                                           range_end, mode2);
  n_rows += n;
  DBUG_PRINT("info", ("part_id %u rows %ld (%ld)", part_id, (long int)n,
                      (long int)n_rows));
}

출처: https://github.com/mysql/mysql-server/blob/4869291f7ee258e136ef03f5a50135fe7329ffb9/storage/innobase/handler/ha_innopart.cc#L3281

사실, 이렇게 대략적인 데이터 건 수를 판단한 후 일부 파티셔닝에 접근할 필요없이, 모든 파티셔닝에 데이터가 존재한다는 가정으로 데이터에 접근을 해도 큰 무리없다고 생각이 마구마구 드는 시점이네요.

Solution

사실 당장의 솔루션은 없습니다. 그냥, 파티셔닝 수를 최소화하는 수밖에 없답니다. 미네랄..ㅜㅜ

단, Percona MySQL경우에는 하단과 같이 추가 파라메터(force_index_records_in_range, records_in_range)를 추가하여, 해당 테이블에 데이터가 특정 건수(할당한 파라메터값)으로 지정을 함으로써 전체 파티셔닝에 접근하지 않도록 우회할 수 있는데요. (이렇게 유도하기까지 꽤나 어려웠어요.ㅠㅠ)

깃헙: https://github.com/dutow/percona-server/commit/677aa7c8aa64ca0aca25c07813e48309e5f06109

이렇게 소스를 변경해서, 테스트트를 해본 결과 놀라운 성능 변화가 있었습니다.

이런 말도 안되는 성능을 보였던 녀석이!!

set records_in_range = 0;
set force_index_records_in_range = 0;
=============================
Device: r/s rkB/s %util
disk01 28364.00 453824.00 100.00
disk01 29050.00 464800.00 99.80
disk01 31827.00 509232.00 99.90
disk01 29660.00 474560.00 99.40
disk01 31570.00 505120.00 99.90

time opr ms/opr
11:31:18 4553 11.01
11:31:19 4639 10.76
11:31:20 4581 10.86

단 몇줄 수정만으로도, 이런 성능 수치를 보였답니다. 대충 봐도 4배정도 향상이 이루어졌습니다, ㅎㅎ (4500 qps vs 17000 qps)

set records_in_range = 1000;
set force_index_records_in_range = 1000;
=============================
Device: r/s rkB/s %util
disk01 24930.00 398896.00 96.70
disk01 25671.00 410720.00 97.10
disk01 24245.00 387920.00 96.90
disk01 24938.00 399008.00 97.10
disk01 25155.00 402480.00 97.10

time opr ms/opr
11:36:48 17156 2.89
11:36:49 16873 2.94
11:36:50 16965 2.92

이 픽스는 다음번 GA 릴리즈 버전에 포함되어서 나오게 될 예정이고.. 동시에 Oracle 커뮤니티 버그 사이트에서 진행하던 동일 이슈도 나름 긍정적인 형태로 진행 중에 있는 듯 하군요.

관련 : https://bugs.mysql.com/bug.php?id=96233

Conclusion

엄청난 갯수의 파티셔닝을 가진 상황에서, 이상하게 쿼리가 느린 것을 경험하신 분들! 파티셔닝 테이블 조회 시 이와 같은 이슈로 리소스가 충분히 활용되고 있지 않은지를 한번쯤 확인을 해보세요. ㅎㅎ 어서어서 오라클에서도 관련된 픽스가 이루어져서, 이런 비효율이 없어지기를 빌어봅니다. ㅋ

간만에 끄적인 블로그를 너무 서술한듯한 느낌이네요. (오늘 ifkakao 발표 후인지라.. 피곤..ㅠㅜ) 이 다음부터는 더욱 재미난 내용을 붙이도록 하겠습니다. 꾸벅.

PMM팁1탄! MySQL을 READ-ONLY 기준으로 표기해보기.

Overview

어느덧 1월이 마무리되어가는 이 시점.. 한달 내내 놀다 시간 보내기에는 아쉬움이 많이 남아, 블로그 한두개 정도는 남겨보고자, 아주 간만에 노트북 앞에 앉습니다. 가장 기억 속에 맴도는 주제를 찾던 중, 작년 나름 많은 분석을 했었던 내용들을 한번 몇가지 주제로 정리해보고자 합니다.

PMM(Percona Monitoring and Management)이라는 녀석으로 퉁 쳐서 이야기를 했지만, 사실 이번에 이야기할 내용은 Prometheus 쿼리와 Grafana를 사용하는 간단한 꼼수(?)에 대한 이야기입니다.

혹시 PMM이 어떤 녀석인지 궁금하시다면? PMM 이야기 1편 – INTRO 편을 읽어보주세요. ㅎㅎ

Definition

이 주제는 MySQL의 READ-ONLY 설정 값에 따른 서버들 표기로 되어 있지만, 제 입장에서는 이는 곧 마스터/슬레이브를 구분하는 주제이기도 합니다. 물론, MySQL을 다양한 토폴로지로 이를테면, (M)-(M/S)-(S)와 같은 체이닝 구조로도 서비스를 운영해볼 수 있겠지만.. 개인적인 생각으로는 다양한 장애를 대비했을 때 가장 간단한 구성이 최고의 강력함을 선사한다고 생각을 하기에.. 제 입장에서 READ-ONLY가 ON으로 활성화된 녀석을 슬레이브로라고 정의합니다.

  1. 마스터 (READ_ONLY: OFF)
    – 데이터 변경의 주체가 되는 인스턴스
    (M)-(M/S)-(S) 와 같은 중간 체이닝 구조에서 (M/S)는 마스터로 인지
  2. 슬레이브 (READ_ONLY: OFF)
    데이터 변경이 불가한 인스턴스

개인의 정의에 따라 다르게 쿼리가 나오겠지만, 제가 원하는 그림은, “READ_ONLY가  ON/OFF에 따른 서버 분류” 혹은 “전체 인스턴스 기준”에 따른 표현입니다.

Grafana Variable

MySQL에서 READ-ONLY는 ON/OFF 정도만을 의미합니다만, 여기서는 전체 서버를 의미하는 값으로 2값을 추가로 의미부여해보았습니다.

  • var_read_only : 0 (READ-ONLY  OFF)
  • var_read_only : 1 (READ-ONLY  ON)
  • var_read_only : 2 (ALL)

Grafana에서 새로운 Variable을 추가하는 방법은 대시보드의 Setting의 Variables 탭에서 새롭게 추가하는 것으로~ (설명은 패스) 그리고, 아래와 같이 Custom 타입으로 2,0,1로 정의해봅니다. 2가 제일 앞인 이유는.. 그냥 기본 값으로 지정을 해보기 위함이죠.

Grafana Variable - READ-ONLY
Grafana Variable – READ-ONLY

그런데 문제는, 이렇게 만든 Variable 셀렉트 박스를 보면, 표기가 2,0,1로 보여지고 있어서 직관적이지가 않습니다. 2->ALL, 0->OFF, 1->ON으로 셀렉트 박스에 표현되는 것이 사용상으로 괜찮겠죠? (물론 사용에는 무리가 없지만..)

Grafana의 UI 세팅에서는 불가(아직까지는)한 듯 하고.. 이는 Grafana를 Export한 후 약간 조작 후 다시 Import하는 과정이 필요합니니다. “Share Dashboard” > “Export” 탭으로 들어가서 json 파일로 내린 후 열어보면, 아래와 같이 “text” 부분도 역시 value와 같은 값으로 설정되어있는데,

{
  "allValue": null,
  "current": {
    "text": "2",
    "value": "2"
  },
  "hide": 0,
  "includeAll": false,
  "label": "Read-only",
  "multi": false,
  "name": "read_only",
  "options": [
    {
      "selected": true,
      "text": "2",
      "value": "2"
    },
    {
      "selected": false,
      "text": "0",
      "value": "0"
    },
    {
      "selected": false,
      "text": "1",
      "value": "1"
    }
  ],
  "query": "2,0,1",
  "type": "custom"
}

위 text 붉은 부분을 아래와 같이 직관적인 문구로 바꾸어주고, 해당 Dashboard를 다시 Import해주면~

{
  "allValue": null,
  "current": {
    "text": "ALL",
    "value": "2"
  },
  "hide": 0,
  "includeAll": false,
  "label": "Read-only",
  "multi": false,
  "name": "read_only",
  "options": [
    {
      "selected": true,
      "text": "ALL",
      "value": "2"
    },
    {
      "selected": false,
      "text": "OFF",
      "value": "0"
    },
    {
      "selected": false,
      "text": "ON",
      "value": "1"
    }
  ],
  "query": "2,0,1",
  "type": "custom"
}

최종적으로 Grafana 화면에서 직관적인 문구로 확인이 가능합니다. 쉽게 조작이 어렵다는 점이.. 참으로 서글프네요. 아무튼 첫번째 검색어 세팅 완료!

Grafana Read-Only Variable Modify
Grafana Read-Only Variable Modify

Promethues Query

Grafana 에서 원하는 조건대로 전달할 수 있는 기반이 만들었으니, 이번에는 데이터를 조회하는 Prometheus 쿼리를 작성해보도록 하겠습니다. 이 자리에서, Prometheus 쿼리를 하나하나 설명하지는 않을 것으므로, Prometheus 공식 사이트 메뉴얼을 한번씩은 꼭 방문하셔서, 쿼리 구문을 읽어봐 주시와요~ (여기여기요~)

제 관심사는, READ-ONLY ON/OFF에 따른 그룹핑된 결과물입니다. 앞선 정의에서, read_only 값이 0이면 마스터, read_only가 1이면 슬레이브, 그리고 2이면 전체 서버를 의미하는 것으로 이야기를 했었죠. Grafana에서 전달해주는 이 데이터를 기반으로 원하는 데이터들만 선별해서 가져와보도록 하겠습니다.

우선 아래와 같이 max_over_time으로 1분간 최대값을 가져오는 Prometheus 쿼리로 현재 시스템 로드를 추출해봅니다. 이는, 전체 인스턴스에 해당하는 결과값들입니다.

max_over_time(node_load1[1m])

READ-ONLY 상태 값에 대한 조건을 추가하여, READ-ONLY ON/OFF에 해당하는 결과를 조회해봅니다. 여기서 “on (instance)” 항목은 어떤 데이터를 기준으로 데이터 연산을 처리할 것인가를 의미하는데, SQL 기준이라면, 아무래도 JOIN 조건이라고 보면 무관하겠네요.

max_over_time(node_load1[1m]) 
  and on (instance) (mysql_global_variables_read_only == $read_only)

그런데 여기서 문제가 하나 생겼습니다. READ-ONLY 값이 존재하는 경우에는 큰 문제없이 쿼리 질의가 되지만, 마스터/슬레이브를 섞어서 한번에 봐야하는 케이스, 즉 전체 서버를 모두 표현하는 경우 정상적으로 쿼리가 동작하지 않는다는 것이죠.

그래서, 여기서 극강의 꼼수 하나를 더 슬그머니 밀어봅니다. ㅋ 세 값의 연산 결과가 2일때만 1 이상이 되는 공식을 만들기만 하면 해결이 될 듯 하죠? 그리고 “OR” 조건을 묶어서, 우선 연산 처리를 하고, 부합되지 않는 경우 READ-ONLY 부분이 동작하도록 쿼리 작성을 해보도록 한다면..? 각 인스턴스 단위로 “무조건 존재”하는 메트릭을 손 꼽아 보자면..??

“up{job=’linux’}” 값을 이용해볼 수 있겠습니다. 이 값의 결과는 0 또는 1이며, Exporter가 존재하는 한 무조건 존재한다고 가정해볼 수 있습니다. 즉, $read_only가 0과 1인 경우 음수로 나오며, 2 이상인 경우에만 1이상의 양수 값이 나오도록 아래와 같이 쿼리를 작성해볼 수 있겠습니다.

max_over_time(node_load1[1m]) 
  and on (instance) (up{job='linux'} <= $read_only^2-3 or mysql_global_variables_read_only == $read_only)

이렇게되면, 우선 2인 경우에는 첫번째 up 조건에서 필터링 되면서 뒤의 mysql_global_variables_read_only 부분을 참조하지 않습니다. (or는 앞의 결과가 없는 경우 수행) 그러나 만약 read_only 필터링 조건이 들어오면서 0 또는 1로 연산이 되면, up 결과는 존재하지 않으므로, mysql_global_variables_read_only 값을 체크하면서 최종적으로 원하는 형태로 데이터를 추출해 내는 것이죠.

설명이.. 쿨럭.. 어렵.. (한국말좀 알려주세요. ㅠㅠ) 그래서 준비했습니다. 어떤 결과로 보여지는지..

테스트로 로컬에 VM을 구성해서 아래와 같은 구조로 MySQL 서버를 세팅한 후에 PMM exporter를 띄워보았습니다. node01과 node03은 당연히 데이터 변경의 주체 역할을 가지므로, read_only를 OFF로 해놓았고요.

node01 : Master
  ㄴ node02 : Slave
node03:  Master(Single)

Case1. ALL Instance

pmm-server 인스턴스를 포함해서 모든 서버들이 모두 보여집니다.

ALL Server
ALL Server

Case2. READ-ONLY: OFF

의도한대로, READ-ONLY가 OFF인 인스턴스만 모여서 같이 그래프를 보여주죠. 참고로, READ-ONLY 속성을 가지고 있는 mysql  인스턴스 데이터들만 표기해주므로, pmm-server 인스턴스는 대상 리스트에서 빠집니다. (당연한 이야기지요. READ-ONLY 속성으로 조회를 했으니..)

READ-ONLY OFF
READ-ONLY OFF

Case3. READ-ONLY: ON

마지막으로.. READ-ONLY가  ON인  슬레이브 녀석만 별도로 보여줍니다. 여기서도 역시 pmm-server는 빠집니다.

READ-ONLY ON
READ-ONLY ON

Conclusion

사실은 별 것 없습니다. 제게 필요한 정보를 추출해보기 위해서.. 그냥 사소한 고민을 해보았었고, 약간의 꼼수로 필터링을 할 수 있도록 쿼리를 작성한 것 뿐이지요.

제가 한 일이라고는..

  1. Grafana에서 READ-ONLY 표기를 직관적으로 표기하기 위한 대시보드 Import 꼼수
  2. 2가지 단계(전체, READ-ONLY ON/OFF)로 필터링할 수 있도록 Prometheus 꼼수 쿼리 작성

이 긴 글에서 위 두 가지 정도로 보여지네요. 허허..;; 그렇기 때문에, 초고수 분들에게는 우스운 포스팅일 수도 있습니다. 그렇지만.. 수십대가 넘어가는 마스터/슬레이브가 섞인 환경에서.. 한눈에 시스템의 리소스 현황을 파악하는 경우.. 롤에 따른 분류가 생각보다 크나큰 의미가 있습니다. ^^

제 문제 상황에서 해결방안을 고민한 아이디어이며, 더욱 좋은 번뜩이는 아이디어가 있으면 같이 나눴으면 하는 생각으로.. 장기 휴가 후 복귀를 얼마 남겨둔 입장에서 포스팅 하나 날려봅니다.

심심해서, 간단하게 만들어본 대시보드를 아래 GitHub에 올려놓았으니, Grafana에 Import를 해서 직접 보시면 이해가 빠를 듯 하네요.
>> https://github.com/gywndi/kkb/blob/master/pmm-dashboard/My_Dashboard.json

PS. 이 예제는 이해를 위해, 가장 기초적인 요건만 고려해서 만든 것입니다. MySQL이 죽어있거나, mysql_up이 OFF인 경우, 즉 read_only 파라메터가 정상 수집이 되지 않는 경우는 풀 수 있는 난제로 남겨두고 도망갑니다. ㅋ

좋은 밤 되세요. ^^

 

[MySQL] Online Alter에도 헛점은 있더구나 – gdb, mysqld-debug 활용 사례

Overview

MySQL에서도 5.6부터는 온라인 Alter 기능이 상당부분 제공되기 시작했습니다. 인덱스과 칼럼 추가/삭제 뿐만 아니라, varchar 경우에는 부분적으로 칼럼 확장이 서비스 중단없이 가능한 것이죠. 물론 오라클 유저들에게는 당연한 오퍼레이션들이, MySQL에서는 두손들고 운동장 20바퀴 돌 정도로 기뻐할만한 기능들입니다. 물론, 대부분의 DDL을 테이블 잠금을 걸고 수행하던 5.5 시절에도 online alter를 위해 트리거 기반의 pt-online-schema-change 툴을 많이들 사용했었기에.. 서비스 중단이 반드시 필요하지는 않았지만요. (https://gywn.net/2017/08/small-talk-pt-osc/)

아무튼 이렇게 online alter가 대거 지원하는 상황 속에서, MySQL의 메뉴얼과는 다르게 잘못 동작하는 부분이 있었는데, 이 원인을 찾아내기 위해서는 MySQL 내부적으로 어떻게 동작을 하는지 알아내기 위해 며칠 우물을 신나게 파보았습니다.

오늘 블로그에서는 지난 일주일간의 삽질에 대해서 공유를 해보고자 합니다.

VARCHAR size extension

MySQL에서 데이터의 타입이 변경되는 경우에는 테이블 잠금을 잡은 상태에서 데이터 리빌딩을 하기 마련이지만.. VARCHAR 타입의 경우에는 조금 다르게 동작을 합니다. VARCHAR 칼럼에는 데이터의 사이즈(바이트)를 알려주는 Padding 값이 포함되어 있는데, 매뉴얼 상으로는 패딩의 바이트 수가 동일한 경우에는 테이블 메타 정보만 수정함으로써 Online Alter 가 진행되는 것이죠.

The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY)

출처 : https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

바이트 단위로 계산이 되기 때문에, 글자수로 기록이 되는 VARCHAR 타입 입장에서는 캐릭터셋에 따라 글자 수 구간이 달라집니다. 예를 들어 2바이트를 차지하는 euckr인 경우, 1~127글자 구간과 128이상 구간으로 나누어볼 수 있을 것이고, 4바이트 utf8 캐릭터셋인 utf8mb4 경우에는 1~63글자 구간 그리고 64이상 구간으로 그룹을 지어볼 수 있겠습니다.

간단하게 예를 들어보겠습니다. 우선 하단과 같이 테이블을 만들어보고..

CREATE TABLE `test` (
  `a` varchar(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

알고리즘을 명시적으로 줘서 테스트를 해보면.. 우선 VARCHAR(8) -> VARCHAR(16) 사이즈 확장은 큰 문제 없이 잘 동작을 합니다. (둘다 바이트 수가 255 이하이므로, 동일 패딩 1바이트입니다.)

mysql> alter table test modify a varchar(16), algorithm=inplace;
Query OK, 0 rows affected (0.04 sec)

그러나 63글자를 넘어서 255사이즈로 inplace 알고리즘(online alter)으로 칼럼을 확장하고자 하면, 아래와 같이 에러를 뱉습니다. 에러 메시지를 존중하여, 알고리즘을 COPY로 지정을 해야 “테이블 잠금”을 품고 “데이터 재구성”하는 과정을 거쳐 비로서 정상적으로 칼럼 확장이 마무리되죠. (둘다 바이트 수가 256 이상이므로, 동일 패딩 2바이트입니다.)

mysql> alter table test modify a varchar(255), algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

mysql> alter table test modify a varchar(255), algorithm=copy;
Query OK, 1 row affected (0.03 sec)

매뉴얼 내용은 제대로 각인되었을 것으로 간주하고.. 이제 오늘 이야기하고자 하는 주제로 넘어가겠습니다.

Indexed Column Extension

그렇습니다. 이야기대로라면, Padding 조건에 맞는 칼럼이 경우에는 무중단, 리빌딩 없이 순식간에 칼럼 사이즈가 확장되어야 하는 것이 정석이건만.. 인덱스를 품은 VARCHAR칼럼인 경우에는 그렇지가 않았습니다. 위에서 테스트로 간단하게 생성을 했었던 테이블 기준으로 테스트 케이스를 만들어서 확인해보겠습니다.

CREATE TABLE `test` (
  `a` varchar(8) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> insert into test select left(uuid(),8);

## 20회 수행
mysql> insert into test select left(uuid(),8) from test;

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+

확실한 차이를 보이기 위해서, 우선 인덱스가 없는 현재 상황에서 VARCHAR(8) -> VARCHAR(16)으로 사이즈를 확장해서 0.02초에 백만건 넘는 데이터의 칼럼 사이즈가 바로 확장이 되는 것을 보이겠습니다.

mysql> alter table test modify a varchar(16), algorithm=inplace;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

이 상태에서 인덱스를 하나 추가하고 앞과 비슷하게 VARCHAR(16) -> VARCHAR(17)로 확장을 시도해보았는데.. 바로 완료가 될 것이라는 기대와는 다르게.. 초단위로 DDL이 수행되었습니다.

mysql> alter table test add key ix_a_1(a);
Query OK, 0 rows affected (1.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test modify a varchar(17), algorithm=inplace;
Query OK, 0 rows affected (1.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

여기에 추가로 인덱스를 하나 더 생성을 해서 VARCHAR(17) -> VARCHAR(18)로 칼럼을 확장을 해보니 인덱스 한개 대비 대략 두배 정도의 시간이 더 소요된 것을 확인할 수 있겠죠.

mysql> alter table test add key ix_a_2(a);
Query OK, 0 rows affected, 1 warning (4.43 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> alter table test modify a varchar(18), algorithm=inplace;
Query OK, 0 rows affected (3.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

참고로, 이 alter 구문의 프로파일링은 하단과 같으며.. “altering table” 단계에서 병목이 걸려있습니다. 무언가 내부적으로 이상한 짓을 하고 있는 것이죠. ㅎㅎ 이 당시 상황에서 performance_schema.metadata_locks을 확인해볼 수 있다면 약간의 정보를 더 얻을 수 있겠지만.. 대세에 큰 영향은 없습니다. ㅠㅠ 정보가 너무 부족합니다.

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000069 |
| checking permissions           | 0.000007 |
| checking permissions           | 0.000005 |
| init                           | 0.000006 |
| Opening tables                 | 0.000297 |
| setup                          | 0.000019 |
| creating table                 | 0.009754 |
| After create                   | 0.000255 |
| System lock                    | 0.000012 |
| preparing for alter table      | 0.000275 |
| altering table                 | 2.993340 |
| committing alter table to stor | 0.014045 |
| end                            | 0.000056 |
| query end                      | 0.000336 |
| closing tables                 | 0.000024 |
| freeing items                  | 0.000041 |
| cleaning up                    | 0.000062 |
+--------------------------------+----------+

참고로 이 상황에서 서비스 쿼리가 중단되거나 하지는 않아요. 그냥 내부적으로 InnoDB Buffer Pool I/O가 늘어났을 뿐 서비스 쿼리는 큰 문제가 없습니다. 문제는 DDL 구문이 오래걸리는 상황이라면, 자연스럽게 “복제 지연”으로 이어질 수 있다는 점인데요. ALTER구문은 데이터를 담고 있는 “그릇”이 변경되는 의미하는 것이기 때문에.. 데이터를 복제하는 슬레이브 입장에서는 DDL 구문 이후에 변경된 그릇 기준으로 생성되는 DML 쿼리를 처리할 수 있습니다.

생각을 해보세요. 1000만건 테이블의 특정 VARCHAR칼럼에 5개 이상의 인덱스가 관여하고 있다면..?? 생각보다 슬레이브 지연 영향도는 심각할 수 있겠죠.

즉, 순식간에 종료될 것이라 생각했던 Online Alter 구문이 슬레이브 복제 지연에 영향을 줄 수 있다는 점이죠.

Replication-Delay

만약 슬레이브 서버들이 어떤 형태로든 서비스에 관여를 하고 있다면.. 이 상황에서 복제 지연 현상은 데이터를 관리하는 데이터쟁이 입장에서는 그다지 듣기좋은 이야기는 아닙니다.

자~ 그렇다면, 도대체 이 현상은 왜 발생했을까요? 다행히 이 이슈 분석을 위해 “인덱스 있는 VARCHAR 칼럼”이라는 중요한 재현 시나리오는 마련이 되어있군요. 만약 재현이 어려운 상황이라면.. 분석이 정말 난해한 상황이었을 것입니다. ㅋㅋ

Debugging with gdb

우선 멈춰있는 이 상황에서, MySQL이 내부적으로 어떤짓을 하고 있는지 디버깅을 해봅시다. 알수 없는 무언가를 하고 있는가를 확인해봐야겠죠. 위에서 MySQL이 무언가 알수 없는 것을 처리하는 상황에서 gdb로 아래와 같이 트레이스를 떠봅니다.

$ gdb -ex "set pagination 0" \
      -ex "thread apply all bt" \
      -batch -p `pidof mysqld` > result.out

이렇게하면 외계 문자들이 쭈르륵 떨어질 것인데.. 이중 OS의 쓰레드 아이디 기준으로 나온 결과를 보고 현재 이 쓰레드에서 무슨 짓을 하고 있는지 추적을 해봅니다. 참고로, mysql 프로세스 아이디 기준으로 OS 쓰레드 아이디는 아래와 같이 threads 테이블에서 알아낼 수 있어요.

mysql> select thread_id_os from performance_schema.threads
    -> where processlist_id = <프로세스아이디>;

만약 OS의 쓰레드 아이디가 8이었다면.. 이 쓰레드 번호로 검색을 해보면.. 지금 어떤일을 하고있는지 적나라하게 확인할 수 있겠죠. 참고로 여기서는 인덱스 리빌딩(row_merge_build_indexes) 관련하여 무슨 짓을 하고 있는 것으로 보여지네요. ㅎ (외계어기는 하지만.. 동일 버전의 소스를 다운받아서 위치를 따라가보면, 대략 무슨 짓을 하고 있는지 이해할 수 있을 듯..)

$ vi result.out
.. 중략 ..
Thread 8 (Thread 0x7ff8790c3700 (LWP 31032)):
#0  0x0104ed22 in row_merge_blocks .. storage/innobase/row/row0merge.cc:2784
#1  row_merge .. storage/innobase/row/row0merge.cc:2957
#2  row_merge_sort .. storage/innobase/row/row0merge.cc:3077
#3  0x01056454 in row_merge_build_indexes .. storage/innobase/row/row0merge.cc:4550
#4  0x00f902da in ha_innobase::inplace_alter_table .. innobase/handler/handler0alter.cc:6314
#5  0x007619ec in ha_inplace_alter_table .. sql/handler.h:3604
.. 중략 ..
#15 0xf9c07aa1 in start_thread () from /lib64/libpthread.so.0
#16 0xf98e8aad in clone () from /lib64/libc.so.6

Debugging with mysqld-debug

앞서 의심을 했었던, 인덱스 관련된 무슨 짓꺼리를 하고 있을 것이다라는 심증은 확신으로 바뀌면서.. 이제 조금더 상세하게 디버깅해보도록 하죠. 이번에는 mysqld-debug로 이 작업을 해보도록 하겠습니다.

$ mysqld-debug --debug=d,info,error,query,general,where:O,/tmp/mysqld.trace

참고: https://dev.mysql.com/doc/refman/5.7/en/making-trace-files.html

괘나 많은 로그가 떨어지겠지만.. 인덱스 있는 케이스와 없는 케이스 두 개로 나누어서 트레이스를 떠보면.. 대충 칼럼 사이즈 변경 시 fill_alter_inplace_info 함수 부분에서 리빌딩할 인덱스 리스트를 선별할 것 같은 우주의 기운이 팍팍 느껴지기 시작합니다. 왠지.. 동일 패딩 사이즈 경우 메타 정보를 수정해서 순식간에 ALTER 작업이 이루어지는 것처럼, 인덱스 리빌딩 단계에서 이 케이스를 선별해서 빼버린다면..?? 이런 병목은 사라질 것 같은 예감이 듭니다.

## 인덱스 없는 경우 ##
mysql_create_frm: info: wrote format section, length: 10
fill_alter_inplace_info: info: index count old: 0  new: 0

## 인덱스 있는 경우
fill_alter_inplace_info: info: index count old: 2  new: 2
fill_alter_inplace_info: info: index changed: 'ix_a_1'
fill_alter_inplace_info: info: index changed: 'ix_b_2'

다행히 이 문제는 MySQL 5.7.23 버전에서 픽스가 되었습니다. 소스를 살펴보고자, Oracle MySQL의 깃헙에 들어가서 살펴보던 중 왠지 우리와 비슷한 상황일 것 같은 커밋 코멘트(BUG#26848813: INDEXED COLUMN CAN’T BE CHANGED FROM VARCHAR(15))를 발견하였고, 대충 훑어보니 역시나 인덱스 리빌딩 케이스였더군요. (이렇게 절묘하게.. 손 안대고 코풀줄이야.. ㅋㅋ)
참고: https://github.com/…/913071c0b16cc03e703308250d795bc381627e37

5.7.23 버전에 online alter 플래그 중 “Alter_inplace_info::RENAME_INDEX” 비트 플래그를 하나 더 추가하였고, varchar 칼럼 확장이면서 패딩 사이즈가 동일하면 “Alter_inplace_info::RENAME_INDEX” 로 분기 분류함으로써 인덱스를 리빌딩하지 않도록 제어하더군요. (부분부분 발췌합니다.)

## fill_alter_inplace_info 함수 ###########
static bool fill_alter_inplace_info(THD *thd,
                                    TABLE *table,
                                    bool varchar,
                                    Alter_inplace_info *ha_alter_info)
{
  ....
  while ((rename_key= rename_key_it++))
  {
    ....
    if (! has_index_def_changed(ha_alter_info, table_key, new_key))
    {
      /* Key was not modified but still was renamed. */
      ha_alter_info->handler_flags|= Alter_inplace_info::RENAME_INDEX;
      ha_alter_info->add_renamed_key(table_key, new_key);
    }
    else
    {
      /* Key was modified. */
      ha_alter_info->add_modified_key(table_key, new_key); 
    }
    ....
  }
  ....
}

## has_index_def_changed 함수 ###########
static bool has_index_def_changed(Alter_inplace_info *ha_alter_info,
                                  const KEY *table_key,
                                  const KEY *new_key)
{
    ....
    if (key_part->length != new_part->length &&
        ha_alter_info->alter_info->flags == Alter_info::ALTER_CHANGE_COLUMN &&
        (key_part->field->is_equal((Create_field *)new_field) == IS_EQUAL_PACK_LENGTH))
    {
      ha_alter_info->handler_flags|=
          Alter_inplace_info::ALTER_COLUMN_INDEX_LENGTH;
    }
    else if (key_part->length != new_part->length)
      return true;
    ....
}

아무튼 5.7.23에서는 이 코드 적용 결과.. 인덱스가 있을지라도 VARCHAR 칼럼 확장은 순식간에 바로 종료가 되는 쾌거를 이룹니다. 아직 5.7.23 이전 버전을 쓰고 계신 분들이 버전을 업그레이드 해야하는 또하나의 이유가 생겼네요. ㅋㅋ

Conclusion

길고 긴.. 오늘 블로그 정리를 해보겠습니다. 그냥 이 세가지 이야기로 정리해보겠습니다.

  1. MySQL5.7.23 버전 이상으로 업그레이드 하는 것이 정신 건강에 좋다.
  2. MySQL5.7.23 이전 버전 사용자들은, 칼럼에 인덱스가 걸려있는지 확인을 우선 해보고 작업 계획을 세운다.
  3. 무엇보다 매뉴얼은 매뉴얼일뿐, 맹신하지 말자.

무엇보다 가장 알고 싶었던 부분은.. 매뉴얼과 전혀 다른 이 현상이 어디부터 시작된 이슈인지를 원인분석입니다. 이것을 알아야 이는 사이드이펙트를 최소화할 수 있는 우회방안도 고민해볼 수 있기 때문이죠. 피할 수 없으면 즐겨야할테니.. 맞더라도 이유는 알고 맞아야할 것이고.. 궁시렁궁시렁

저같은 C알못 평범남도 파보니 이해는 할 수 있겠드라고요. 엯촋들에게는 평범한 일상이겠지만.. 저같은 보통 사람들에게는 신성한 경험이었기에, 이를 공유 삼아 블로그 포스팅을 해봅니다.

아주 오랜만의 길고 긴 대하 소설.. 마칩니다. ㅋㅋ