Tag Archives: partition

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 관리를 계획하고 있으신 분에게는 꽤 좋은 솔루션이 될 수 있다고 생각해요. 설명장애가 있어서.. 매끄럽지 않았지만.. 의미 전달이 잘 되었기를..

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