Tag Archives: partition

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 발표 후인지라.. 피곤..ㅠㅜ) 이 다음부터는 더욱 재미난 내용을 붙이도록 하겠습니다. 꾸벅.

MySQL에서 파티션 일부를 다른 파티션 테이블로 옮겨보기

Overview

한동안 운영에 치여, 문서를 못봤더니, 재미난 사례를 많이 놓친듯.
그래서 여기저기 떠도는 문서 중 재미난 사례 하나를 내 입맛에 맞게 샘플을 변경해서 공유해봅니다.
(영혼없이 붙여넣기만 해도 알아보기 쉽게 ㅋㅋ)

Preview

파티셔닝 특정 부분을 다른 테이블 혹은 파티셔닝 일부로 넘기는 방안에 대한 것인데..

move-partition-data-file

move-partition-data-file

하단 포스팅 내용 중 미흡한 부분을 보완해서 정리해본 것입니다
https://dzone.com/articles/how-to-move-a-mysql-partition-from-one-table-to-an?utm_medium=feed&utm_source=feedpress.me&utm_campaign=Feed:%20dzone

Generate Test Data

먼저 테스트 데이터를 생성해야할테니..

mysql> create table f_tb (
      seq bigint(20) not null default '0',
      regdate date not null,
      cont text not null,
      primary key (seq,regdate)
  ) engine=innodb collate=utf8_unicode_ci
  /*!50500 partition by range columns(regdate)
  (partition p09 values less than ('2016-10-01'),
   partition p10 values less than ('2016-11-01'),
   partition p11 values less than ('2016-12-01'),
   partition p12 values less than ('2017-01-01')) */;

아래처럼 테스트로 사용할 데이터를 간단하게 생성해봅니다. 2017-01-01 기점으로 랜덤하게 120일 사이 일을 빼서 마치 파티셔닝 테이블이 관리된 것처럼 데이터를 밀어넣는 것이죠.

## 1건 데이터 생성
mysql> insert ignore into f_tb values 
    (rand()*1000000000, date_sub('2017-01-01', interval rand()*120 day), repeat(uuid(),5));

## 원하는 만큼 반복 수행
mysql> insert ignore into f_tb 
  select rand()*1000000000, 
      date_sub('2017-01-01', 
      interval rand()*120 day), repeat(uuid(),5) 
  from f_tb;

위에서 만든 이후 실제 테이블 데이터 건 수를 보면.. (전 대략 8000건만 만들었어요. 귀찮아서. ㅋ)

mysql> select count(*) from f_tb;
+----------+
| count(*) |
+----------+
|     8064 |
+----------+

Move Datafile to Temporay Table

자~ 이제 특정 파티셔닝 파일을 다릍 파티셔닝의 일부로 옮기는 작업을 해볼까요.

1) 동일한 테이블을 만들고, 파티셔닝이 없는 일반 테이블로 구성한다.

mysql> create table f_tb_p09 like f_tb;
mysql> alter table f_tb_p09 remove partitioning;

2) 임시 테이블 discard처리

f_tb에서 9월 데이터를 가져오기 위해, 테이블스페이스에서 discard 하도록 수행합니다. 임시 깡통 데이터가 사라지겠지요.

mysql> alter table f_tb_p09 discard tablespace;

3) 테이블 데이터 복사

파일 복사 이전에, 원본 테이블에 export를 위한 락을 걸고, 데이터 카피 후 락을 풀어줍니다.
(두개 세션 열기 귀찮으니.. MySQL 콘솔 클라이언트에서 바로 파일을 복사하도록.. ㅋㅋ)

mysql> flush table f_tb for export;
mysql> \! cp /data/mysql/test_db/f_tb#p#p09.ibd /data/mysql/test_db/f_tb_p09.ibd
mysql> unlock tables;

4) 임시 테이블에 데이터파일 Import

옮겨온 데이터파일을 실제 임시로 생성한 테이블에서 읽을 수 있도록 import 처리 해줍니다.

mysql> alter table f_tb_p09 import tablespace;

자~ 이제 정상적으로 데이터가 잘 옮겨졌는지 카운트를 해볼까요?

mysql> select count(*) from f_tb_p09;
+----------+
| count(*) |
+----------+
|     1860 |
+----------+

굳~ 잘 되었네요.

Import to archive

아카이브 테이블이 없다는 가정하에.. 아래와 같이 타겟 파티셔닝 테이블을 생성합니다. 당연한 이야기겠지만, 파티셔닝 정의를 제외한 테이블 구조는 동일해야하겠지요?

mysql> create table t_tb (
      seq bigint(20) not null default '0',
      regdate date not null,
      cont text not null,
      primary key (seq,regdate)
  ) engine=innodb collate=utf8_unicode_ci
  /*!50500 partition by range columns(regdate)
  (partition p09 values less than ('2016-10-01'),
   partition p10 values less than ('2016-11-01')) */;

mysql> select count(*) from t_tb;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

자~ 끝판왕.. 마지막으로.. 타겟 파티셔닝 파일에 데이터파일을 변경해준다는 하단 alter 구문만 날려주면 끝~

mysql> alter table t_tb exchange partition p09 with table f_tb_p09;

mysql> select count(*) from t_tb;
+----------+
| count(*) |
+----------+
|     1860 |
+----------+

중요치는 않지만.. 임시 테이블 데이터는 깡통으로 남지요.
(COPY가 아닙니다. 임시 테이블에 데이터는 사라져요. ㅋ)

mysql> select count(*) from f_tb_p09;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

Conclusion

신기하기는 했지만.. 이걸 어디에 쓸 수 있을까 잠깐 생각을 해봤는데.. 대고객 온라인 서비스에서는 큰 의미는 없다고 생각되는데요.

MySQL의 가장 큰 강점은 개인적으로 “데이터 복제”라고 생각합니다. Replication.. 그러나.. 위와 같이 데이터파일을 옮긴다면.. 아무래도 OS 상에서 동작하기에.. 데이터 복제 개념과는 동떨어진 처리입니다. 즉.. 설혹 마스터에서 특정 테이블 일부 데이터를 다른 테이블의 일부로 옮겨놓아도, 실제 슬레이브에서는 이 명령이 그대로 적용되지 않음을 의미하죠.

그러나!

만약 “특정 테이블”에 대한 데이터 분석 혹은 보관과 같은 이슈가 있을 시.. 덤프 없이, 놀고 있는 슬레이브, 혹은 스탠바이, 장비에서 일시적으로 잠금 처리를 한 후 파일 단위로 빠르게 옮겨봄으로써 의외로 쉽게 대응이 가능합니다. 물로 반드시 파티셔닝 테이블에 포함해야할 필요는 없지만.. ^^ 어플리케이션 복잡도를 줄이기 위해서라면 위와 같이 파티셔닝 테이블의 일부로 적용하는 것도 좋은 케이스이겠고요.

감사합니다. ^^

파티션 제약 극복기! 유니크한 토큰 값을 만들어보자!

Overview

MySQL에는 날짜 별 데이터 관리를 위해 파티셔닝이라는 좋은 기능(?)을 5.1버전부터 무료(!)로 제공합니다. 일정 시간 지난 후 불필요한 데이터는 간단하게 해당 파티셔닝을 제거하면, 굳이 DELETE 쿼리로 인한 오버헤드를 방지할 수 있죠.

그러나, 파티셔닝 적용 시, “파티셔닝 키는 반드시 PK에 포함되어야 한다”, “추가 제약조건(유니크 속성)을 부여할 수 없다”라는 대표적인 제약 조건으로 인하여, 유니크 속성을 가지는 데이터를 파티셔닝 적용이 불가한 경우가 있는데.. 이것을 해결할 수 있는 간단한 트릭을 이 자리에서 설명하고자 합니다. ^^

Plan to..

토큰을 생성하는 경우를 간단하게 생각해볼까요? ^^

토큰, 특히 일정 시간 이후에는 절대로 활용이 되지 않는 Access Token을 생각해본다면.. 수많은 유저들이 어떤 권한을 위해 반드시 발급받아야하는 데이터죠. 기본적으로 이 값은 중복이 발생해서도 안되고, 일정 시간이 지나버리면 폐기해도 무관한 데이터입니다.

  • 반드시 유니크 속성을 보장해야한다.
  • 파티셔닝 관리가 가능해야 한다.

간단하게 스키마를 상상해보면, 아래와 같습니다. ^^

CREATE TABLE access_tokens (
  token         char(64) NOT NULL,
  expires_at    datetime NOT NULL,
  PRIMARY KEY (token)
);

자.. 이제 이 엄청난 데이터를 파티셔닝을 하고 싶은데.. 흠.. 가장 간단한 방법으로는 아래처럼 PK제약 조건을 충족하기 위해 PK 속에 파티셔닝 키를 포함시켜서 테이블을 파티셔닝 하는 방법이 있습니다.

CREATE TABLE access_tokens (
  token         char(64) NOT NULL,
  expires_at    datetime NOT NULL,
  PRIMARY KEY (token, expires_at)
)
PARTITION BY RANGE COLUMN (expires_at)
(PARTITION PF_20150513 VALUES LESS THAN ('2015-05-14') ,
 PARTITION PF_20150514 VALUES LESS THAN ('2015-05-15') ,
 PARTITION PF_20150515 VALUES LESS THAN ('2015-05-16'));

아.. 그런데 여기서 중요한 문제가.. 반드시 유니크성을 보장해야할 Token을 스키마 레벨에서 완벽하게 보장할 수 없다는 말이죠. 즉, 정말 유니크한지 확인을 하려면 발급할 Token값이 현재 테이블에 존재 여부를 사전에 조회해서 체크 해봐야 합니다. 물론, 랜덤한 Hash로 Token을 발급한다면.. 중복은 매우 희박하기는 하나.. 최악의 경우를 무시할 수는 없을테니요. ^^

How to?

이러한 상황에서 과연 어떤 트릭(?)을 써서 파티셔닝을 하면서 유니크한 Token 발급이 가능할까요? Token 특성 상 굉장히 많은 데이터가 적재될 것이기에.. 테이블 사이즈를 고려하여 파티셔닝을 위한 별도의 칼럼(daynum) 칼럼을 생각해봅시다. 이 값은 smallint 타입으로 2바이트로, PK에 8바이트짜리 datetime 타입이 들어가는 것 보다는 훨씬 유리하죠. 특히나 인덱스를 추가로 만들 경우!! Token에 트릭을 가미하기 위해 기존보다 4바이트가 늘어납니다. 즉, PK는 6바이트 증가!

아래와 같이 테이블을 만들어봅시다.

CREATE TABLE access_tokens (
  token         char(68) NOT NULL,
  daynum        smallint unsigned not null,
  expires_at    datetime NOT NULL,
  PRIMARY KEY (token, daynum)
)
PARTITION BY RANGE (daynum)
(PARTITION PF_20150513 VALUES LESS THAN (134),
 PARTITION PF_20150514 VALUES LESS THAN (135),
 PARTITION PF_20150515 VALUES LESS THAN (136));

daynum에는 무슨 값이 들어가냐고요? to_days(now())에서to_days(‘2014-12-31’)을 뺀 값이 들어갑니다. 이렇게 함으로써 단순 2바이트 사이즈로 몇 십년 치(아마도 늙어 죽을 때까지)를 관리할 수 있는 테이블 파티셔닝 관리가 가능한 것이죠.

자, 그럼.. 데이터 처리를 위한 쿼리를 만들어볼까요? Token은 SHA2로 만든다고 가정해봅시다.

## => shar2함수 안의 스트링 값과 날짜 값만 인수로..
insert into access_tokens 
  ( token, daynum, expires_at )
 values 
 (
   concat(SHA2(?, 256), right(hex(TO_DAYS(?)-735963),4)),
   TO_DAYS(?)-735963,
   ?
 );

setString(1, authInfo + nanoTime)
setString(2, expires_at)
setString(3, expires_at)

ex)
insert into access_tokens
 ( token, daynum, expires_at )
values
 (
   concat(SHA2('user1', 256), right(hex(TO_DAYS('2015-05-17')-735963),4)),
   TO_DAYS('2015-05-17')-735963,
   '2015-05-17'
 );

복잡하쥬? 간단하게 말하자면.. SHA2로 64바이트 키를 만들고, 가장 마지막 4글자를 날짜가 가미된 문자열을 넣자는 것입니다. (4글자를 맞추기 위해.. )

결국 PK는 아래와 같은 형태로 관리가 되기에, Token은 유니크 보장이 된다고 할 수 있습니다. ^^ (슈퍼 울트라 캡숑 “꼼수”)

  • Primary Key : (SHA2+날짜, 날짜)
  • “SHA2+날짜”는 유니크 속성 보장

데이터를 넣었으니, 데이터를 끄집어 내는 방법도 고민해봐야겠죠? 매번 조회 시 전체 파티셔닝을 뒤지지 않기 위해서는 원하는 데이터가 위치한 곳의 적절한 파티셔닝 키도 같이 전달을 해야할텐데..  그렇다고, 어플리케이션에서 늘 daynum을 가지고 있을 수는 없는 노릇! Token에서 날짜 정보를 추출할 수 있는 방안을 고안해야 합니다. 아래처럼..

## => 토큰 값만 쿼리에 인수로..
select * from access_tokens 
where daynum = conv(trim((substr(?, 65))), 16, 10) and token = ?;
setString(1, token_string)
setString(2, token_string)

ex)
select *
from access_tokens
where daynum = conv(trim((substr('0a041b9462caa4a31bac3567e0b6e6fd9100787db2ab433d96f6d178cabfce9089', 65))), 16, 10)
and token = '0a041b9462caa4a31bac3567e0b6e6fd9100787db2ab433d96f6d178cabfce9089';

Token에서 65번째 부터 4글자를 가져와서, 그 데이터를 16진수 -> 10진수로 변환하여 최종적으로 daynum을 만들어보자는 것입니다. 실제 호출되는 쿼리는 바로 위 예제처럼 사용되겠죠. ^^

쿼리가 복잡해지기는 하나.. 나름 서버에 부담없이 데이터를 최적으로 추출할 수 있는 방안이라고 봅니다.

기존 토큰 길이가, 64->68로 길어진다는 단점이 있기는 하나.. 음.. 반드시 64글자여야 한다면, SHA2결과 값에서 마지막 4글자를 빼버리는 것도 나쁘지 않다고 생각합니다. 발급된 Token은 스키마 레벨에서 유니크를 보장하고, 날짜별로 파티셔닝도 가능하니.. 일석이조!!

Conclusion

약간(?)의 트릭으로 파티셔닝 제약을 극복하였습니다. 유니크한 Token을 파티셔닝 관리하는.. 유니크 보장을 위해 사전 SELECT 없이도 쿼리 레벨에서 해결할 수 있는 방안이죠.

Token에 날짜가 가미된 스트링을 넣되, PK를 날짜와 같이 엮어서 Token만으로 유니크를 보장하자는 것이 목적입니다.

  • Primary Key : (SHA2+날짜, 날짜) => “SHA2+날짜”는 유니크

정말 간단한 팁같지 않은 팁이기는 하나.. 대규모 Token 관리를 계획하고 있으신 분에게는 꽤 좋은 솔루션이 될 수 있다고 생각해요. 설명장애가 있어서.. 매끄럽지 않았지만.. 의미 전달이 잘 되었기를..

다음에는 또다른 재미난 팁을 소개하도록 할께요. ^^