InnoDB의 Adaptive Hash Index로 쿼리 성능에 날개를 달아보자!!

Overview

MySQL과 같은 RDBMS에서 대표적으로 가장 많이 사용되는 자료 구조는 B-Tree입니다. 데이터 사이즈가 아무리 커져도 특정 데이터 접근에 소요되는 비용이 크게 증가되지 않기 때문에 어느정도 예상할 수 있는 퍼포먼스를 제공할 수 있기 때문이죠. 그치만 상황에 따라서, B-Tree 사용에 따른 잠금 현상으로 최대의 퍼포먼스를 발휘하지 못하는 경우도 있습니다.

이에 대한 해결책으로 InnoDB에는 Adaptive Hash Index 기능이 있는데, 어떤 상황에서 효과가 있고 사용 시 반드시 주의를 해야할 부분에 대해서 정리해보겠습니다.

InnoDB B-Tree Index?

소개하기에 앞서서 먼저 InnoDB에서 B-Tree가 어떠한 방식으로 활용되는 지 알아볼까요?

InnoDB에서는 데이터들은 Primary Key순으로 정렬이 되어서 관리가 되는데.. 이것을 곧 PK로 클러스터링 되어 있다라고 합니다. 데이터 노드 자체가  PK 순으로 정렬이 되어있다는 말인데, 이는 곧 특정 데이터에 접근하기 위해서는 PK가 반드시 필요하다는 말이지요.

그리고 Secondrary Key는 [인덱스키+PK]를 조합으로 정렬이 되어 있습니다. 즉, 특정 데이터를 찾기 위해서는 Secondrary Key에서 PK를 찾아내고, 그 PK를 통해 데이터 트리로 접근하여 원하는 데이터로 최종 접근을 하는 것이죠.

InnoDB B-Tree Index

트리가 가지는 가장 큰 장점은, 데이터 접근 퍼포먼스가 데이터 증가량에 따라서도 결코 선형적으로 증가하지 않다는 점에 있습니다.  다들 아시겠지만, B-Tree에서 특정 데이터 접근에 소요되는 비용은 O(logN)인데, 이는 일정 데이터 건 수에서는 거의 선형으로 비용이 유지됩니다. (참고로, PK접근 시에는O(logN), Secondrary Key ㅈ버근 시에는 2 *O(logN) 이겠죠?)

그런데, B-Tree를 통하여 굉장히 빈도있게 데이터로 접근한다면, 어떻게 될까요? DB 자체적으로는 꽤 좋은 쿼리 처리량을 보일지는 몰라도, 특정 데이터 노드에 접근하기 위해서 매번 트리의 경로를 쫓아가야하기 때문에, “공유 자원에 대한 잠금”이 발생할 수 밖에 없습니다. 즉, Mutex Lock이 과도하게 잡힐 수 있는데, 이 경우 비록 데이터 셋이 메모리보다 적음에도 불구하고 DB 효율이 굉장히 떨어지게 됩니다.

InnoDB Adaptive Hash Index?

앞선 상황에서 좋은 성능을 보이기 위해서, InnoDB에서는 내부적으로 Adaptive Hash Index 기능을 제공합니다. “Adpative”라는 말에서 느껴지듯이, 이 특별한 자료구조는 명쾌하게 동작하지는 않고, “자주” 사용되는 데이터 값을 InnoDB 내부적으로 판단하여 상황에 맞게 해시를 생성” 합니다.

InnoDB-Adaptive-Hash-Index

위 그림에서 자주 사용되는 데이터들이 1,5,13,40이라고 가정할 때 위와 같이 내부적으로 판단하여 트리를 통하지 않고 “직접 원하는 데이터로 접근할 수 있는 해시 인덱스”를 통해 직접 데이터에 접근합니다.

참고로, Adative Hash Index에 할당되는 메모리는 전체 Innodb_Buffer_Pool_Size의 1/64만큼으로 초기화됩니다. 단, 최소 메모리 할당은 저렇게 할당되나, 최대 사용되는 메모리 양은 알 수는 없습니다. (경우에 따라 다르지만, Adaptive Hash Index가 사용하는 인덱스 사이즈를 반드시 모니터링해야 합니다.)

이 기능은 MySQL5.5버전(InnoDB Plugin 1.0.3)부터는 이 기능을 동적으로 On/Off할 수 있습니다. 아래와 같이.. ^^;;

## 켜다
mariadb> set global innodb_adaptive_hash_index = 1;
 
## 끄다
mariadb> set global innodb_adaptive_hash_index = 0;

관련 통계 정보는 아래와 같이 확인..ㅋ

mariadb> show global status like 'Innodb_adaptive_hash%';
+----------------------------------------+------------+
| Variable_name                          | Value      |
+----------------------------------------+------------+
| Innodb_adaptive_hash_cells             | 42499631   |
| Innodb_adaptive_hash_heap_buffers      | 0          |
| Innodb_adaptive_hash_hash_searches     | 21583      |
| Innodb_adaptive_hash_non_hash_searches | 3768761684 |
+----------------------------------------+------------+

자주 사용되는 데이터는 해시를 통해서 직접 접근할 수 있기에, Mutex Lock으로 인한 지연은 확연하게 줄어듭니다. 게다가 B-Tree의 데이터 접근 비용(O(LogN))에 비해, 해시 데이터 접근 비용인 O(1)으로 굉장히 빠른 속도로 데이터 처리할 수 있습니다.

단, “자주” 사용되는 자원만을 해시로 생성하기 때문에, 단 건 SELECT로 인하여 반드시 해당 자원을 향한 직접적인 해시 값이 만들어지지 않습니다.

InnoDB는 Primary Key를 통한 데이터 접근을 제일 선호하기는 하지만, 만약 PK접근일지라도 정말 빈도있게 사용되는 데이터라면 이 역시 Hash Index를 생성합니다. (처음에는 단건 PK 접근에는 절대로 Hash Index를 만들지 않을 것이라 생각했지만, 곧 생각을 고쳐먹었습니다. ㅋㅋ)

Adaptive Hash Index Power!!

흠.. 말만 하지말고.. 눈에 보이는 효과를 보도록 할까요? 글빨이 안되니.. 비주얼로 승부를!!ㅋㅋ

아래와 같이 간단한 테스트 케이스(1300만 건 데이터)를 만들어서, IN 조건으로 PK를 통하여 데이터를 추출하는 테스트를 해보도록 해요. IN절에는 약 30개 정도의 파라메터를 넣고, 300개의 쓰레드에서 5ms 슬립을 줘가며 무작위로 트래픽을 보내봅니다.ㅋㅋ

## 테이블 스키마
create table ahi_test(
  i int unsigned not null primary key auto_increment,
  j int unsigned not null,
  v text,
  key ix_j(j)
);
 
## SELECT 쿼리
select left(v, 1) from ahi_test 
where i in (x,x,x,x,x,...x,x,x,,);

 

Adaptive Hash Index를 사용하지 않는 오른쪽 결과에서는 CPU가 100%였으나, Adaptive Hash Index를 사용한 이후에는 60%정도로 사용률이 내려갔습니다.

InnoDB-Adaptive-Hash-Index-Effect1

게다가, CPU는 줄었으나, 쿼리 응답 시간이 줄었기에 처리량 또한 20,000 -> 37,000으로 늘어났습니다. 참으로 놀라운 결과지요?? ㅋㅋ

InnoDB-Adaptive-Hash-Index-Effect2

Adaptive Hash Index를 켠 이후에는 확실히 B-Tree를 통해서 데이터에 접근하는 빈도가 줄어든 것도 확인할 수 있고요~

InnoDB-Adaptive-Hash-Index-Effect3

이에 따라 내부적인 잠금 현상도 확연하게 줄어들었습니다.

InnoDB-Adaptive-Hash-Index-Effect4

다른 것은 손대지 않고, Adaptive Hash Index만 켰을 뿐인데….

결론은 따~봉!! 굉장히도 풍요로운 꽁짜 점심이죠? ㅋㅋ

Cautions

자.. 이제 InnoDB Adaptive Hash Index 효과를 보았으니, 영혼없이 아무 곳에서나 무조건 쓰도록 할까요? 노노~! InnoDB Adaptive Hash Index 가 필요할 정도라면, SELECT가 꽤 많이 발생하는 서비스라고 생각해볼 수 있는데요.. 이 상황에서 테이블 DROP시 문제가 발생할 수 있습니다.

아래 그림은 percona-online-schema-change 툴을 활용하여 스키마를 변경한 전/후 Adaptive Hash Index사이즈를 체크한 것인데, 전후로 두 배로 해시 사이즈가 증가하였습니다. 그런데 두 배가 문제가 되기보다는, 아래와 같은 상황이 수 개월 동안 유지될 수 있다는 점입니다.

InnoDB-Adaptive-Hash-Index-Effect5

평소 운영 시에는 전~혀 문제가 되지 않지만, 수 개월동안 전~혀 사용하고 있지 않던 테이블을 영혼없이 정리하다보면 치명적인 장애에 직면할 수 있는 것이죠. 바로 저처럼.. -_-;;

아래 수치는 앞선 테스트 테이블에 트래픽을 주는 상황에서 OLD 테이블을 DROP하였는데, 테이블 정리 도중에는 처리량이 급감(3.8만 -> 2.7만)한 상황을 보여줍니다. 아래 현상이 굉장히 많은 SELECT가 발생하던 서버에서 발생을 하였다면, 단위 쿼리 처리량이 줄어듬에 따라 어플리케이션 쪽에 문제가 발생할 수도 있겠죠. ㅠㅠ

| Com_select | 39041 |
| Com_select | 39189 |
| Com_select | 38774 |
| Com_select | 38953 |
| Com_select | 39527 |
| Com_select | 37906 |
| Com_select | 39316 |
| Com_select | 37541 |
| Com_select | 37972 |
| Com_select | 32484 | <=== DROP OLD TABLE START
| Com_select | 27514 |
| Com_select | 27602 |
| Com_select | 27692 |
| Com_select | 27918 |
| Com_select | 27818 |
| Com_select | 28266 |
| Com_select | 28383 |
| Com_select | 28350 |
| Com_select | 37047 | <=== DROP OLD TABLE END
| Com_select | 39572 |
| Com_select | 38868 |
| Com_select | 39315 |
| Com_select | 38738 |
| Com_select | 39548 |
| Com_select | 39413 |
| Com_select | 38978 |

설혹, 데이터가 2G일지라도, 또한 파일 시스템이 xfs일지라도.. 이것은 디스크적인 요소라기 보다는 Memory 내부적인 잠금 이슈이기 때문에.. 어찌 해결해볼 방법은 없습니다.

InnoDB 내부적으로는 테이블 DROP시 Sleep없이 죽어라고 Hash Index에서 관련 노드를 모두 삭제한 후 테이블이 제거합니다. 그런데 이것이 단일 Mutex로 관리되기 때문에 기존 SELECT 성능에도 지대한 영향을 끼치는 것이죠. 그나마 최대한 이러한 현상을 회피할 수 있는 방법은 innodb_adaptive_hash_index_partitions을 수십개(기본값은 1)로 늘려놓고, 경합을 최대한 줄이는 방법만이 유일할 듯하네요. ^^ (테이블 드랍은 트래픽이 제일 없을 때로!!)

아.. 혹은 테이블 드랍을 수행할 때는 최대한 트래픽이 없는 새벽에, Adaptive Hash Index를 순간 OFF/ON을 하여 메모리를 해제하고, 테이블을 DROP하는 방법도 될 수 있겠네요. ^^ 선택은 서비스 상황에 맞게..

InnoDB Adaptive Hash Index가 사용하는 메모리 사이즈도 지속적으로 모니터링을 해야할 요소라는 것도 잊지 말아야할 것이고요. ^^

Conclusion

InnoDB Adaptive Hash Index는 B-Tree의 한계를 보완할 수 있는 굉장히 좋은 기능임에는 틀림 없습니다. 특히나 Mutex와 같은 내부적인 잠금으로 인한 퍼포먼스 저하 상황에서는 좋은 튜닝요소가 될 수 있습니다.

그러나, “자주” 사용되는 데이터를 옵티마이저가 판단하여 해시 키로 만들기 때문에 제어가 어려우며, 테이블 Drop 시 영향을 줄 수 있습니다. Hash Index 구조가 단일 Mutex로 관리되기 때문에, 수개월간 테이블이 사용되지 않던 상황에서도 문제가 발생할 수 있는 것입니다.

굉장한 SELECT를 Adaptive Hash Index로 멋지게 해결하고 있다면, 이에 따른 Side Effect도 반드시 인지하고 잠재적인 장애에 대해서 미리 대비하시기 바래요. ^^

 

MariaDB의 FederatedX 엔진으로 데이터를 주물러보자.

Overview

FederatedX는 MariaDB에서 제공하는 확장된 기능의 Federated이며, 기본적으로 MariaDB에서는 별다른 옵션 없이 사용할 수 있습니다.

바로 이전 포스팅(http://j.mp/16VA8x6)에서는 이 FederatedX 엔진을 활용하여 대용량 테이블을 서비스에 큰 지장없이 이관을 했던 사례에 대해서 정리를 했었는데요. 이 경험을 바탕으로 서비스에서 조금 더 유용하게 활용할 수 있을 방안에 대해서 상상(?)을 해보았습니다.

즉, 지금부터는 FederatedX 엔진 관련된 테스트를 바탕으로 정리하는 내용이오니, 만약 실 서비스에 적용하고자 한다면 반드시 검증 후 진행하셔야 합니다. ^^

Why FederatedX?

단일 데이터베이스의 성능을 따지자면, 굉장한 퍼포먼스를 발휘하는 MySQL 이기는 합니다만.. SNS 서비스 환경에서는 결코 단일 서버로는 커버 불가능합니다. 이 상황에서는 반드시 샤딩(데이터를 분산 처리) 구조로 설계를 해야하는데, 한번 구성된 샤딩은 온라인 중에 변경하기가 참으로 어렵습니다.

서비스 순단을 발생할 수 있지만, 온라인으로 데이터를 재배치를 할 수 있는 방법은 없을까? 하는 생각을 하던 와중에, 저는 FederatedX 엔진에서 해답을 찾았습니다.

FederatedX-Shard-Reorg1

 

MariaDB에서 제공하는 FederatedX엔진은 MySQL에서 제공하는 기능 외에 트랜잭션 및 파티셔닝 기능이 확장되어 있는데, 이 두가지 특성을 활용한다면 앞선 그림처럼 기존 데이터 구조를 원하는 형태로 재배치할 수 있다는 생각이 번뜩 났습니다. ㅎㅎ

단, 아래에서 설명할 모든 상황들은 아래 4 가지 상황이어야한다는 가정이 필요합니다.

  1. 샤드 키 업데이트는 없어야 한다.
    업데이터 시 기존 샤드 데이터가 DELETE되지 않음
  2. ALTER 작업은 없어야 한다.
    FederatedX 스키마 변경 불가
  3. 샤드 키는 문자열 타입이어서는 안된다.
    문자열로는 Range 파티셔닝 사용 불가
  4. 원본 서버 Binary Log은 ROW 포멧이어야 한다.
    FederatedX 에서 일부 쿼리 미지원
    ex) 
    INSERT INTO .. ON DUPLICATE UPDATE

자, 각 상황에 맞게 이쁜(?) 그림으로 간단하게 살펴보도록 할까요?

1) 샤드 재배치

기존 샤드를 재배치하는 경우인데, 만약 일정 범위(1~100, 101~200)로 데이터가 분산 저장되어 있는 경우를 해시 기준으로 변경하고 싶을 때 유용하게 적용가능할 것이라고 생각되는데요.

FederatedX-Shard-Reorg2

위 상황에서 FederatedX1과 FederatedX2을 다음과 같은 스키마 구성합니다.

CREATE TABLE `tab` (
  `p_id` int(11) NOT NULL,
  `col01` bigint(20) NOT NULL,
  `col02` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=FEDERATED
PARTITION BY LIST (mod(p_id,3))
(PARTITION p0 VALUES IN (0) ENGINE = FEDERATED connection='mysql://feduser:fedpass@Nshard1_host:3306/db/tab',
 PARTITION p1 VALUES IN (1) ENGINE = FEDERATED connection='mysql://feduser:fedpass@Nshard2_host:3306/db/tab',
 PARTITION p1 VALUES IN (1) ENGINE = FEDERATED connection='mysql://feduser:fedpass@Nshard3_host:3306/db/tab');

원본 샤드 데이터를 각 FederatedX에 Import 후 리플리케이션 구성하면, 파티셔닝 정의에 맞게 데이터샤드들이 저장되겠죠? ^^

단, 데이터 동기화에 가장 큰 요소는 바로.. Network Latency입니다. 동일 IDC, 다른 스위치 장비에 물려있는 DB 경우에는 약 초당 500 ~ 700 rows정도 데이터 변경이 이루어졌습니다. 물론, 동일 스위치 경우에는 이보다 훨씬 더 좋은 데이터 변경 처리가 이루어질 것이라고 예측됩니다만.. 흠.. 이것은 기회가 된다면, 테스트하고 싶네요. ^^;;

2) 로컬 데이터 재구성

아래와 같이 현재의 DB 혹은 테이블을 여러 개의 객체로 찢는 경우를 말할 수 있는데요, 앞서 치명적인 요소가 되었던 Network Latency는 이 상황에서는 별다른 문제가 되지 않습니다. 모든 통신이 메모리 상에서 이루어지기 때문에, 본연의 속도가 나올 수 있는 것이죠. ^^

FederatedX-Shard-Reorg3

FederatedX 전용 서버를 다음과 같은 설정(/etc/my_fed.cnf)으로 구동하도록 합니다. 포트는 13306으로 띄운다는 가정으로..

[mysqld_safe]
timezone = UTC
 
[mysqld]
port     = 13306
socket   = /tmp/mysql_fed.sock
character-set-server = utf8
skip-innodb
server-id = xxx
binlog_format = row
 
[client]
port    = 13306
socket    = /tmp/mysql_fed.sock
 
[mysql]
no-auto-rehash
default-character-set = utf8

FederatedX 전용 서버를 간단하게 아래와 같이 띄우면 되겠죠? ㅎ

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my_fed.cnf 

그후 아래와 같이 FederatedX 테이블을 생성 후 원본 테이블 데이터 Import 후 리플리케이션을 맺어주면, DB별로 데이터가 재배치된 샤드 형태가 완성됩니다. ^^

CREATE TABLE `tab` (
  `p_id` int(11) NOT NULL,
  `col01` bigint(20) NOT NULL,
  `col02` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=FEDERATED
PARTITION BY LIST (mod(p_id,3))
(PARTITION p0 VALUES IN (0) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/db1/tab',
 PARTITION p1 VALUES IN (1) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/db2/tab',
 PARTITION p2 VALUES IN (2) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/db3/tab');

테스트를 해보니 초당 수천 건 데이터 변경이 가능하며, 단일 서버 데이터 재배치에 가장 유용하게 활용될 수 있다고 생각되네요.

3) 데이터 통합

늘 서비스가 잘되면 참 좋겠지만.. 때로는 망해가는 서비스의 DB를 한곳으로 몰아야할 경우도 있습니다. ㅜㅜ  앞에서 설명한 것들을 이 상황에 맞게 조금만 수정을 하면 되니, 굳이 길게 설명은 하지 않도록 하겠습니다.

 

FederatedX-Shard-Merge1

단, 기존 샤드의 데이터가 샤드키에 맞게 엄격하게 분리가 되어 있어야 합니다. (리플리케이션에서 충돌날 수 있어요!!)

4) 아카이빙

수년을 보관해야하는 당장 정리가 불가한 콜드데이터 성격의 데이터를 한 곳에 모은다는 컨셉인데요.. 이에 대한 것은 사전에 안정성 테스트 반드시 필요합니다. (어디까지나 테스트를 바탕으로 활용 가능한 상황인지라.. ^^;;)

FederatedX-Shard-Archieving

하루에 한번씩 돌아가면서 리플리케이션 IO_THREAD를 ON/OFF를 하게되면, 다수의 데이터베이스로부터 오는 데이터를 쉽게 한 곳으로 모을 수 있겠죠. 만약, 원본 테이블에서 파티셔닝 관리를 한다면, 이에 대한 에러 스킵 설정을 FederatedX에 미리 정의해놓으면 참 좋겠네요. ^^

Conclusion

처음에 버리다시피 전혀 검토를 하지 않았던 FederatedX를 다른 시각으로 발상을 전환해보니, 매우 활용할 수 있는 분야가 많았습니다. 물론 아직 실 서비스에서 직접 해보지는 않았지만, 이에 대한 각 테스트를 해보니 충분히 활용해볼 만한 여지는 있었습니다.

참고로, 위에서 설명하지는 않았지만, FederatedX +Blackhole을 활용하게 된다면, Network Latency 극복을 어느정도 할 수 있다고 생각합니다.

부족한 설명을 읽어주셔셔 감사합니다. ^^

MariaDB의 FederatedX 엔진을 활용한 9억 데이터 이관기

Overview

대용량 로그 테이블은 때로는 서비스에 지대한 영향을 미치기도 합니다. 게다가 이 테이블을 파티셔닝 구성을 해야하는데, 이를 서비스 운영 중인 상태에서 마스터 장비에서 Import하는 것은 사실 대단히 위험한 시도이기도 하죠.

이런 상황에서 얼마 전 FederatedX엔진을 활용하여 9억 데이터를 이관한 사례가 있는데, 이에 대해 공유하도록 하겠습니다. ^^

Goal

9억 건의 데이터를 Import하는 동안 서비스에는 어떠한 영향도 없어야 하며, 구성 후 어플리케이션 적용 전까지 데이터가 정상적으로 동기화되어야 합니다.

  1. 데이터 이동하는 동안 기존 서비스 영향 최소화 및 문제 발생 시 빠른 원복
  2. 데이터 구성 후 어플리케이션 코드 배포 전까지 데이터 동기화
  3. 데이터 보관 주기 정책에 따른 유연한 대처
    현재는 삭제 주기가 없으나, 추후 정책에 따라 변경 가능

Let me SEE..

가야할 골이 정해졌으니.. 현재 상황에 대해서 분석을 해봐야겠죠. ㅎㅎ 다음은 DB 사용 현황에 대한 내용입니다.

1) 소스 서버

  • Engine : InnoDB
  • QPS(Except SELECT) : 200qps, (MAX)500qps
  • 대상 테이블
    • download_log – 76G / 3.8억
    • open_log – 96G / 5.5억

2) 타겟 서버

  • Engine : TokuDB
  • QPS(Except SELECT) : 150qps, (MAX)300qps

서버 트래픽이 크지는 않지만, 9억 건 이상의 데이터를 타 서비스 DB로 마이그레이션을 해야하는 상황이었습니다. 그렇기에 서비스에 어떠한 영향도 없어야하며, 문제 발생 시에도 빠르게 롤백할 수 있는 방법이 되어야만 합니다.

How to Migrate?

이런 상황에서라면, 타겟 마스터 장비를 소스 마스터 혹은 슬레이브 장비와 리플리케이션을 걸어서 특정 테이블만 데이터 싱크를 맞추는 방법이 있겠습니다.

그러나, 9억 건 이상의 데이터이고, 서비스 영향없이 마스터 장비로 데이터를 넣어야 하기 때문에.. 목적에 적합한 방법은 아닌 듯 하네요. 슬레이브를 활용하고자 해도, 멀티 소스 리플리케이션을 활용할 수 있는 상황도 아니었으며, 기존 서비스 리플리케이션을 건들여야하기 때문에 깔끔해 보이지는 않았고요.

그래서 아이디어를 낸 방법이, 슬레이브 장비에 FederatedX 스토리지 엔진 전용의 MySQL 데몬을 하나 더 띄워서 데이터 이관을 하는 것이었습니다. 서비스 투입 직전 데이터 흐름을 간단하게 그림으로 표현해 보았습니다.

Migrate-With-FerderatedX-1

그렇다면, FederatedX? 뭐냐고요?

바로 이전 포스팅에서 이럴줄 알고 슬쩍 정리를 해봤습니다. ^^ 아래 링크를 쿡~!!
>> https://gywn.net/2014/12/let-me-introduce-federatedx/

자~! 그렇다면.. 데이터 이관에 대해 차근차근 단계적으로 설명하도록 하겠습니다.

  1. Data Dump Backup
  2. Table Creation
  3. Data Import & Sync
  4. Master/Slave Switching
  5. Slave Restore

1) Data Dump Backup

첫 번째 단계입니다. 옮길 대용량 테이블은 두 개이고, 조금이라도 빠르게 데이터를 이관하기 위해서 각 테이블 별로 덤프 파일을 생성합니다. 두 개의 덤프 파일이 모두 동일한 바이너리 로그 포지션을 가져야하기 때문에, UserDB 쪽 슬레이브의 SQL_Thread를 일시적으로 중지 후 포지션을 기록합니다.

>> UserDB Slave (상단 이미지 왼쪽 아래 서버)

슬레이브 시작 시 영향도를 최소화하기 위해, 슬레이브의 SQL Thread만 중지합니다. 그리고 현재 바이너리 로그 포지션을 기록해놓습니다.

mysql> stop slave sql_thread;
Query OK, 0 rows affected(0.00 sec)
 
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.006027 | 18752723 |
+------------------+----------+

>> ContentsDB Slave (상단 이미지 오른쪽 아래 서버)

UserDB 슬레이브 장비에 3306으로 붙어서 직접 데이터를 받아옵니다. 사실 UserDB에서 로컬로 데이터를 내릴 수 있겠지만, 파일로 전송하는 단계를 생략하기 위함입니다.

$ mysqldump -udumpuser -pdumppass \
  --single-transaction --no-create-info \
  --add-locks=false -h user-slave \
  --databases user_db --tables open_log \
  > /backup/open_log.sql &

$ mysqldump -udumpuser -pdumppass \
  --single-transaction --no-create-info \
  --add-locks=false -h user-slave \
  --databases user_db --tables download_log \
  > /backup/download_log.sql &

아참, 덤프하기 전에 dumpuser를 사전에 생성을 해놔야한다는 사실을 잊으면 안되겠죠. ^^

>> UserDB Slave (상단 이미지 왼쪽 아래 서버)

자~ 이제 덤프가 시작되었으니.. UserDB 쪽 슬레이브를 재계합니다.

mysql> start slave sql_thread;
Query OK, 0 rows affected(0.00 sec)

2) Table Creation

FederatedX는 원격의 테이블을 연결시켜주는 실체가 없는 브릿지 역할을 합니다. 즉, “원격 테이블”“형상 테이블” 모두 생성을 해줘야하는 것이죠.테이블 스키마 생성 작업은 모~두 서비스와는 전혀 연관이 없는 ContentsDB 슬레이브(상단 이미지 오른쪽 하단)에서 이루어집니다.

>> 원격 테이블 – 3306포트

먼저, 원격 테이블을 생성합니다. 추후 효과적인 데이터 관리를 위해 파티셔닝 설정도 이 기회에 같이 합니다. ㅎㅎ

CREATE TABLE download_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL DEFAULT '0',
  sid bigint(20) NOT NULL DEFAULT '0',
  download_dt datetime NOT NULL,
  val1 varchar(45) NOT NULL DEFAULT '',
  val2 varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (id, download_dt),
  KEY ix_userid (user_id, download_dt)
) ENGINE=TokuDB AUTO_INCREMENT=400000000
/*!50500 PARTITION BY RANGE COLUMNS(download_dt)
(PARTITION PF_201306 VALUES LESS THAN ('2013-07-01'),
 PARTITION PF_201312 VALUES LESS THAN ('2014-01-01'),
 PARTITION PF_201406 VALUES LESS THAN ('2014-07-01'),
 PARTITION PF_201412 VALUES LESS THAN ('2015-01-01')) */;

CREATE TABLE open_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL,
  sid bigint(20) NOT NULL DEFAULT '0',
  product_type char(4) NOT NULL DEFAULT '',
  open_dt datetime NOT NULL,
  PRIMARY KEY (id, open_dt),
  KEY ix_userid_productid (user_id, open_dt)
) ENGINE=TokuDB AUTO_INCREMENT=600000000
/*!50500 PARTITION BY RANGE COLUMNS(open_dt)
(PARTITION PF_201306 VALUES LESS THAN ('2013-07-01'),
 PARTITION PF_201312 VALUES LESS THAN ('2014-01-01'),
 PARTITION PF_201406 VALUES LESS THAN ('2014-07-01'),
 PARTITION PF_201412 VALUES LESS THAN ('2015-01-01')) */;

>> 형상 테이블(FederatedX) – 13306포트

UserDB로부터 받아온 데이터를 ContentsDB로 전달하는 FederatedX 테이블 스키마입니다. 원본 서버와는 완벽하게 같을 필요는 없으나, 만약 바이너리 로그가 SQL기반으로 기록된다면, 관련 인덱스를 어느정도 맞춰놓는 것이 좋습니다. 여기서는 생략~!

CREATE TABLE download_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL DEFAULT '0',
  sid bigint(20) NOT NULL DEFAULT '0',
  download_dt datetime NOT NULL,
  val1 varchar(45) NOT NULL DEFAULT '',
  val2 varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY (id)
) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/contents_db/download_log';
 
CREATE TABLE open_log (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id int(11) unsigned NOT NULL,
  pid bigint(20) unsigned NOT NULL,
  sid bigint(20) NOT NULL DEFAULT '0',
  product_type char(4) NOT NULL DEFAULT '',
  open_dt datetime NOT NULL,
  PRIMARY KEY (id)
) ENGINE = FEDERATED connection='mysql://feduser:fedpass@127.0.0.1:3306/contents_db/open_log';

아참~! 3306 포트로 올라와있는 서버에는 feduser/fedpass로 생성된 유저가 있어야 합니다. 슬레이브가 READ_ONLY일테니 SUPER권한과 함께.. (슬레이브 READ_ONLY를 풀 수도 있겠지만.. 이보다는 전용 DB 접속 유저에 권한을 주는 것이 더 좋을 듯 하네요. ^^)

지금까지 단계가 완료되면, ContentsDB에는 다음과 같이 서버가 구성이 되어 있겠네요.

Migrate-With-FerderatedX-2

3) Data Import & Sync

옮겨야할 데이터도 있고 옮길 그릇도 있으니, 이제 실제 데이터 이관 작업 후 데이터 동기화를 하는 작업만 남았겠네요. ^^

mysql -ufeduser -pfedpass contents_db < /backup/open_log.sql &
mysql -ufeduser -pfedpass contents_db < /backup/download_log.sql &

시간이 꽤 걸리는 이 작업이 완료가 되면, 이제 FederatedX가 떠있는 DB에 접속하여 슬레이브 구성을 합니다.

## replicate_do_table 설정
mysql> set global replicate_do_table = 'user_db.download_log,user_db.open_log ';
 
## 슬레이브 설정
mysql> CHANGE MASTER TO
MASTER_HOST='use-slave',
MASTER_PORT= 3306,
MASTER_USER='repl',
MASTER_PASSWORD='xxxxxx',
MASTER_LOG_FILE='mysql-bin.006027',
MASTER_LOG_POS=18752723;
 
## 슬레이브 시작
start slave;

이 과정까지 되면, 제일 처음 표현했던 이미지대로 데이터가 흐르게 됩니다.

4) Master/Slave Switching

가장 트래픽이 적은 시점에 ContentsDB 쪽 마스터/슬레이브 장비를 스위칭합니다.

MHA같은 솔루션을 사용하고 있다면, 더욱 쉽게 마스터/슬레이브 스위칭이 이루어질 것이고, 스위칭 직후에는 아래와 같은 형태로 데이터가 흐르게 되겠죠. 아참, 스위칭 전에 반드시 다음 단계에서 진행할 슬레이브 복구를 위해, 백업을 해주는 것이 좋겠네요.

Migrate-With-FerderatedX-3

당연한 이야기겠지만, 로그 테이블들이 신규 슬레이브(구 마스터)에는 없기 때문에 ContentsDB 쪽 마스터/슬레이브는 끊어지겠죠.

5) Slave Restore

스위칭 직전 백업 데이터로, 끊어진 슬레이브를 연결합니다. 그리고, 어플리케이션에서 UserDB에 위치한 로그 테이블을 더이상 참조하지 않는 시점에 로그 테이블을 제거하도록 합니다. 이 시점에서는 더이상 FederatedX 테이블도 필요 없기 때문에, 13306포트로 구동 중인 DB서버를 셧다운하도록 합니다.

최종적으로는 대형 로그 테이블이 타 DB로 이관이된 아래와 같은 모습이 됩니다. (휴~! 끝)

Migrate-With-FerderatedX-4

 

마스터/슬레이브 스위칭 시 순단 현상은 있었겠지만, 적어도 9억 데이터를 이관하는 동안 어떠한 영향도 없었습니다.

Result

InnoDB를 TokuDB로 이관을 하면서 디스크 사용률이 30% 이하로 줄었습니다. TokuDB에 대한 설명은 굳이 여기에서 하지 않아도 될 것 같네요. ^^ 만약 궁금하시다면, 아래 포스팅 내용을 참고하세요.
https://gywn.net/2014/05/fractal-index-in-tokudb/

이관 후 데이터 사이즈는 다음과 같습니다.

  • download_log
    – 76G -> 21G
  • open_log
    – 96G -> 23G

파티셔닝 구조로 변경하면서, 데이터 보관 주기에 정책에 따라 유연하게 데이터를 유지할 수 있게 되었습니다.

참고로, FederatedX에서 슬레이브를 연결한 직후 동기화 되는 최대 속도는 다음과 같습니다. TokuDB Small 포멧임에도 단일 쓰레드로 3000 query 이상을 발휘합니다.

| Com_insert     | 3268    |
| Com_insert     | 3255    |
| Com_insert     | 3223    |
| Com_insert     | 3200    |
| Com_insert     | 3233    |

Conclusion

지금까지 FederatedX를 사용하여 9억건 데이터를 타 DB로 이관한 사례를 정리하였습니다.

물론, 굳이 이 방법이 아니라고 하더라도 대형 테이블을 타 DB로 이관할 수 있는 방법은 있겠죠. Tungsten Replicator와 같은 솔루션을 활용하거나, 혹은 개발 부서의 적극적인 지원을 받거나..^^

잘 활용되지 않는 FederatedX 엔진이라고 할 지라도, 이러한 용도로 활용을 한다면 꽤 난감한 상황(이를테면 DB 혹은 테이블 명이 변경되는)에서도 유연하게 대처할 수 있겠습니다. 때마침 좋은 사례가 있어서 공유 드립니다.

간만의 포스팅이라 내용이 매끄럽지 않네요. 게다가 하나하나 모두 설명하기에는 무리가 있어서.. 많은 부분을 생략하기도 했고요. ㅠㅠ 기회가 된다면, 이에 대해 조금 더 자세하게 정리할 수 있는 자리가 있으면 좋겠습니다.ㅎㅎ