MariaDB 10.0 멀티 쓰레드 복제

개요

MariaDB 10.0과 MySQL 5.6은 모두 Multi-threaded replication feature를 가지고 있지만,

두 Main-stream의 Multi-threaded replication의 구현 기능은 상당한 차이를 가지고 있다.

MySQL 5.6은 자동화된 DB 단위의 Parallel replication을 지원하지만, MariaDB 10.0은 반 자동화된 GTID의 domain_id 기반의 Parallel replication 기능을 지원한다.

여기서 MySQL의 자동화는 사용자가 Parallel replication apply에 어떻게 관여할 수 있는 방법이 없다는 것을, 그리고 MariaDB의 반자동화는 응용 프로그램이나 개발자가 Parallel replication을 컨트롤 할 수 있다는 의미이기도 하다.

하지만 MariaDB 10.0에는 많이 알려져 있지 않은 자동화된 Parallel replication feature도 가지고 있는데, 간단히 그 기능을 소개하고자 한다.

MariaDB의 자동화된 Parallel replication을 이해하기 위해서는 먼저 MariaDB의 Binary log "GROUP-COMMIT"을 이해해야 하므로, 간단히 살펴보고 본론으로 넘어가도록 하자.

바이너리 로그 그룹 커밋

바이너리 로그의 그룹 커밋 기능은 MySQL 5.0 시절부터 Binary log 쓰기 성능 향상을 위해서 계속 이슈가 되어 왔던 기능이다.

물론 MySQL 5.6 버전도 가지고 있는 기능이지만, 이 기능이 MySQL 5.6이나 MariaDB 10.0에서 처음 구현된 기능은 아니며, 예전부터 추가되었다가 다른 제약때문에 제거되었다가 다시 구현된 기능이다.

그룹 커밋은 여러 개의 아이템 (바이너리 로그 이벤트나 트랜잭션 등..)을 한번에 모아서 처리하는 기능을 의미할 때 자주 선택되는 용어이기도 한데,

MariaDB의 바이너리 로그의 그룹 커밋 기능은 단순히 이벤트를 바이너리 로그 파일에 모아서 기록하는 수준이 아니라, 실제 트랜잭션의 커밋 처리까지 모아서 수행하는 것을 의미한다.

즉 바이너리 로그 이벤트의 그룹 커밋을 효율적으로 모아서 처리하기 위해서는 트랜잭션의 Commit 성능을 조금 포기해야 할 수도 있다는 것을 의미한다.

MariaDB 10.0에서는 바이너리 이벤트의 그룹 커밋을 위해서는 아래와 같은 2개의 시스템 변수를 지원하고 있다.

• binlog_commit_wait_usec
  짧은 간격을 두고 여러 클라이언트에서 커밋되는 트랜잭션의 바이너리 로그 이벤트를 모아서 한번에 바이너리 로그 파일로 기록하게 된다. 이 때 순수하게 동시점에 수행된 트랜잭션만 그룹 커밋 대상으로 선정할 수도 있지만, MariaDB 서버가 그룹 커밋의 효율을 높이기 위해서 일정 시간을 기다리면서 커밋되는 트랜잭션을 모아서  그룹 커밋으로 처리하도록 할 수도 있다. 이때 MariaDB 서버가 최대 얼마만큼의 시간을 기다리도록 할지를 설정하는 시스템 변수이다. binlog_commit_wait_usec 시스템 변수는 Milli-seconds 단위로 설정하므로, 만약 최대 10 밀리 초 이내에는 커밋이 수행되도록 하고자 한다면 binlog_commit_wait_usec을 10000로 설정하면 된다.
• binlog_commit_wait_count
  MariaDB 서버가 그룹 커밋의 효율을 높이기 위해서 조금은 기다리면서 일정 개수 이상의 이벤트가 커밋되면 이들을 모아서 그룹 커밋으로 처리하게 된다. 이때 MariaDB 서버가 최대 몇 개까지의 커밋을 기다릴지를 설정하는 시스템 변수이다. 이때 개수의 대상의 바이너리 로그의 이벤트 개수나 쿼리의 수가 아니라 트랜잭션의 개수를 의미한다.

어떤 트랜잭션들이 얼마나 모아서 그룹 커밋 처리되었는지는 바이너리 로그 파일의 내용을 살펴보면 알 수 있는데, 간단한 예제로 그룹 커밋 비율을 살펴보도록 하자.

먼저 컬럼을 3개 가지는 test라는 테이블을 생성하고, 4개의 Client connection을 통해서 동시에 각각 INSERT문장을 실행해 보았다. (실제 바이너리 로그의 그룹 커밋은 sync_binlog 옵션과 innodb_flush_log_at_trx_commit 옵션의 값이 1일 때에만 활성화되는 것은 아니다.)

MariaDB [test]> set global binlog_commit_wait_usec=5000; /* 5 milli-seconds */ Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> set global binlog_commit_wait_count=50; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> set global sync_binlog=1; Query OK, 0 rows affected (0.00 sec)   MariaDB [test]> set global innodb_flush_log_at_trx_commit=1; Query OK, 0 rows affected (0.00 sec)   Client1 > insert into test values (1,1,1); Client2 > insert into test values (2,2,2); Client3 > insert into test values (3,3,3); Client4 > insert into test values (4,4,4);

위와 같이 쿼리가 처리되면, 각 트랜잭션들은 최대 5 milli-seconds를 기다렸다가 커밋을 수행하게 된다. 이때 같이 커밋된 바이너리 로그 이벤트(바이너리 로그 파일에 기록된 트랜잭션)들은 동일한 "COMMIT ID"를 가지게 된다.

mysqlbinlog나 show binlog events 명령의 결과로 decoding된 바이너리 로그 파일에서 "cid"라는 값으로 표시된다.

위의 INSERT 명령 실행 결과로 생성된 바이너리 로그 파일의 내용은 아래와 같았다.

MariaDB [test]> show binlog events in 'binary-log.000015'; +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | Log_name          | Pos | Event_type        | Server_id | End_log_pos | Info                                           | +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------+ | binary-log.000015 |   4 | Format_desc       |         1 |         248 | Server ver: 10.0.11-MariaDB-log, Binlog ver: 4 | | binary-log.000015 | 248 | Gtid_list         |         1 |         287 | [0-1-1213]                                     | | binary-log.000015 | 287 | Binlog_checkpoint |         1 |         327 | binary-log.000014                              | | binary-log.000015 | 327 | Binlog_checkpoint |         1 |         367 | binary-log.000015                              | | binary-log.000015 | 367 | Gtid              |         1 |         407 | BEGIN GTID 0-1-1214 cid=1786                   | | binary-log.000015 | 407 | Query             |         1 |         501 | use `test`; insert into test values (3,3,3)    | | binary-log.000015 | 501 | Xid               |         1 |         528 | COMMIT /* xid=1786 */                          | | binary-log.000015 | 528 | Gtid              |         1 |         568 | BEGIN GTID 0-1-1215 cid=1786                   | | binary-log.000015 | 568 | Query             |         1 |         662 | use `test`; insert into test values (1,1,1)    | | binary-log.000015 | 662 | Xid               |         1 |         689 | COMMIT /* xid=1788 */                          | | binary-log.000015 | 689 | Gtid              |         1 |         729 | BEGIN GTID 0-1-1216 cid=1787                   | | binary-log.000015 | 729 | Query             |         1 |         823 | use `test`; insert into test values (4,4,4)    | | binary-log.000015 | 823 | Xid               |         1 |         850 | COMMIT /* xid=1787 */                          | | binary-log.000015 | 850 | Gtid              |         1 |         890 | BEGIN GTID 0-1-1217 cid=1787                   | | binary-log.000015 | 890 | Query             |         1 |         984 | use `test`; insert into test values (2,2,2)    | | binary-log.000015 | 984 | Xid               |         1 |        1011 | COMMIT /* xid=1789 */                          | +-------------------+-----+-------------------+-----------+-------------+------------------------------------------------+   16 rows in set (0.00 sec)

이 결과에서 4개의 트랜잭션이 저장된 것을 알 수 있다. (각 트랜잭션은 BEGIN GTID라는 문자열로 시작됨)

4개의 트랜잭션이 모두 다른 GTID 값을 가지고 있지만, 그 뒤에 명시된  cid는 동일한 값이라는 것을 알 수 있다.

즉 여기에서는 4개의 트랜잭션이 모두 같은 cid 1786을 가지고 있는데, 이것이 4개의 트랜잭션이 모두 한번의 그룹 커밋으로 저장된 이벤트들이라는 것을 의미한다.

MariaDB 10.0의 Multi-threaded Replication

MariaDB 10.0의 Multi-threaded replication은 크게 In-Ordered와 Out-of-Ordered로 구분될 수 있는데, Out-of-Ordered 복제는 GTID의 Domain_id 기반으로 처리되는 Multi-threaded replication을 의미한다.

이는 이미 잘 알고 있는 부분이므로 생략하도록 하겠다.

나머지 In-Ordered 방식은 위에서 잠깐 살펴본 "GROUP-COMMIT"을 기반으로 작동하는 Multi-threaded Replication을 의미한다.

Replication Slave의 Coordinator는 바이너리 로그에서 그룹 커밋된 트랜잭션들을 모두 모아서(cid 기반), SQL Thread들에게 동시에 나눠서 처리하도록 한다.

이렇게 Parallel 처리가 가능한 이유는, 하나의 그룹 커밋내에 있던 모든 트랜잭션들은 서로 충돌 (동일 Row에 대한 Exclusive lock을 필요로 하는)이 없다는 것이 이미 Master에서 증명된 것들이기 때문이다.

결론적으로 마스터에서 하나의 그룹 커밋내에 포함된 트랜잭션이 많으면 많을수록 Multi-threaded slave의 parallelism이 높아지게 되는 것이다.

그룹 커밋이 아닌 트랜잭션들에 대한 Multi-threaded Replication

또한 MariaDB 10.0의 Multi-threaded slave에서는 그룹 커밋에 소속되지 않은 트랜잭션이라 하더라도, 각 트랜잭션의 일부분에 대해서는 Parallel하게 처리할 수 있도록 지원하는 것으로 기술(메뉴얼)되어 있다.

즉 2개의 트랜잭션이 슬레이브에서 실행되어야 하는 상태에서, Slave의 Coordinator는 첫번째 트랜잭션을 읽어서 1번 SQL thread에게 넘겨주고 1번 쓰레드가 Commit을 시작하는 시점까지 기다리다가 

1번 쓰레드가 Commit을 실행하는 시점에 Coordinator는 두번째 트랜잭션을 2번 SQL Thread에게 넘겨주게 된다.

즉 그룹 커밋에 포함되지 않은 트랜잭션이라 하더라도 최소한 COMMIT 작업은 Multi-threading이 가능하도록 설계한 것이다.

COMMIT만 Multi-threading하는 것이 무슨 효과가 있을까라고 생각하겠지만, sync_binlog=1이거나 log-slave-updates가 활성화된 경우 그리고 innodb_flush_log_at_trx_commit이 1로 설정된 경우(COMMIT이 고비용으로 처리되는 경우)에는 이것만으로도 많은 도움이 될 것으로 보인다.

Multi-threaded slave의 성능 테스트 (Test env)

Hardware spec

• 3.40GHz * 4 with HyperThreading

• 32G memory

• 2 SAS + Raid controller (R-1) with 512MB cache

MySQL configuration

• innodb_buffer_pool_size = 20G

• binlog_commit_wait_usec = 1000

• binlog_commit_wait_count = 50 ## only for MTS env.

• slave_parallel_threads = 10       ## only for MTS env.

• slave_parallel_max_queued = 524288

Sysbench

• 20 ~ 50 clients
• table rows : 10 million
• run only update_nokey (sysbench) update statement

Multi-threaded slave의 성능 테스트 (Replication performance)

15k Update statement / second env

25k Update statement / second env

이 테스트에서는 최대 초당 25k Update statements / sec 부하를 발생했었는데, MTS 환경에서는 Replication 지연이 발생하지 않았다.

MTS 환경이 최대 얼마만큼의 Update를 처리할 수 있을지는 테스트해보진 않았지만, 사실 이는 서비스 환경이나 특성에 따라서 가변적인 부분이므로 테스트 결과가 운영에 도움이 되지 않을 것으로 보여서 생략한다. 

하지만 MTS환경에서는 Slave sql thread간의 동기화를 위해서 많은 CPU 자원을 사용한다는 것을 알 수 있다. (이는 일반적으로 Stand-by 용도로 사용되는 Slave 환경에서는 특별히 문제되지는 않을 것으로 고려된다.)

Multi-threaded slave의 성능 테스트 (Group commit performance regression)

Multi-threaded replication을 위해서 Binary log의 Group commit 을 활성화한 경우에는 동일한 클라이언트 부하에서도 위와 같은 서버의 성능 차이를 보였다.

이는 Marster의 client thread가 Group commit을 대기하는 1ms 시간때문에 발생하는 현상이며, 이 테스트에서는 50개의 client thread만 사용했는데 10% 정도의 성능 저하가 발생했다.

하지만, 이는 그다지 큰 이슈가 될 것으로 보이지는 않는다. 일반적인 서비스 환경에서는 5000여개의 client thread가 동시에 기동되어서 Transaction을 발생시키는데, 이 트래잭션들이 최대 1ms를 더 기다리는 것이 되므로 MySQL  서버의 전체적인 Throughput에는 크게 영향을 미치지 않을 것으로 보인다.

MTS로 처리되는 슬레이브에서 프로세스 목록을 조회해보면, 각 SQL Thread가 서로 동기화되면서 병렬로 처리되는 과정을 볼 수 있다.

MariaDB [(none)]> show processlist; +-----+-------------+-----------------+----------+---------+------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+ | Id  | User        | Host            | db       | Command | Time | State                                                                          | Info                                                                                                 | Progress | +-----+-------------+-----------------+----------+---------+------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+ |   3 | system user |                 | sysbench | Connect |    1 | Waiting for prior transaction to start commit before starting next transaction | COMMIT                                                                                               |    0.000 | |   4 | system user |                 | sysbench | Connect |    1 | init                                                                           | COMMIT                                                                                               |    0.000 | |   5 | system user |                 | sysbench | Connect |    1 | init                                                                           | UPDATE sbtest set c='491483753-416518378-451160866-183205132-1026097669-61695471-1011294415-12704074 |    0.000 | |   6 | system user |                 | sysbench | Connect |    1 | init                                                                           | UPDATE sbtest set c='909812426-309814605-1019264342-836244958-228197628-970942614-755265063-45250027 |    0.000 | |   7 | system user |                 | sysbench | Connect |    1 | Waiting for prior transaction to start commit before starting next transaction | COMMIT                                                                                               |    0.000 | |   8 | system user |                 | sysbench | Connect |    1 | Waiting for prior transaction to start commit before starting next transaction | COMMIT                                                                                               |    0.000 | |   9 | system user |                 | sysbench | Connect |    1 | init                                                                           | UPDATE sbtest set c='112858395-197504108-673909257-770681497-1023414038-895922925-320043291-26580038 |    0.000 | |  10 | system user |                 | sysbench | Connect |    1 | freeing items                                                                  | NULL                                                                                                 |    0.000 | |  11 | system user |                 | sysbench | Connect |    1 | init                                                                           | UPDATE sbtest set c='55183839-759991643-1044340954-640849878-599408370-12160985-325658104-762744694- |    0.000 | |  12 | system user |                 | sysbench | Connect |    1 | Waiting for prior transaction to start commit before starting next transaction | COMMIT                                                                                               |    0.000 | |  14 | root        | 127.0.0.1:39423 | NULL     | Sleep   |    8 |                                                                                | NULL                                                                                                 |    0.000 | |  18 | system user |                 | NULL     | Connect | 2327 | Waiting for master to send event                                               | NULL                                                                                                 |    0.000 | |  19 | system user |                 | NULL     | Connect |   27 | Slave has read all relay log; waiting for the slave I/O thread to update it    | NULL                                                                                                 |    0.000 | | 155 | root        | localhost       | NULL     | Query   |    0 | init                                                                           | show processlist                                                                                     |    0.000 | +-----+-------------+-----------------+----------+---------+------+--------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+

Memcached 복제 (3)

KMC 설치

KMC (Kakao Mem Cached)를 빌드하기 위해서는 MySQL 5.6 빌드시에 필요한 기본 라이브러리는 모두 설치가 되어 있어야 한다. 대표적으로 libaio-devel과 ncurses-devel 그리고 cmake 등은 설치되어 있지 않은 경우가 많으니 꼭 확인해보도록 하자. (물론 빌드하면서 에러가 출력되면 그때 그때 하나씩 설치해도 무방하다.)

그리고 KMC에서 꼭 필요한 아래 Memcached client 관련 라이브러리도 다운로드해서 설치하도록 하자.

• libmemcached-1.0.4-1.el5.remi.x86_64.rpm
• libmemcached-devel-1.0.4-1.el5.remi.x86_64.rpm

패키지의 이름은 무관하나 버전은 가능하면 크게 벗어나지 않도록 하자.

이제 KMC를 빌드하면 되는데, cmake를 실행할때에는 아래와 같이 몇가지 옵션들이 추가로 필요하다.

# cd mysql-5.6.14_kmc
# mkdir Release
# cd Release
# cmake .. \
'-DBUILD_CONFIG=mysql_release' \
'-DCMAKE_INSTALL_PREFIX=/usr/local/mysql' \
'-DWITH_INNODB_MEMCACHED=ON' \
'-DENABLED_LOCAL_INFILE=OFF' \
'-DHAVE_QUERY_CACHE=OFF' \
'-DOPTIMIZER_TRACE=OFF' \
'-DENABLE_DEBUG_SYNC=OFF' \
'-DENABLED_PROFILING=OFF' \
'-DWITH_ARCHIVE_STORAGE_ENGINE=OFF' \
'-DWITH_EMBEDDED_SERVER=OFF' \
'-DCOMPILATION_COMMENT=kmc' \
'-DENABLE_DTRACE=OFF'

물론 위의 모든 옵션이 꼭 필요한 것은 아니지만, 성능이나 Memcached plugin의 바이너리 로깅 기능 등을 위해서 필요하므로 혹시 변경이 필요한 경우에는 신중히 고려해서 적용하도록 하자.

cmake가 끝나면, make && make install 명령으로 KMC를 설치하면 된다. MySQL 프로그램이 설치되는 디렉토리는 INSTALL_PREFIX로 명시했던 /usr/local/mysql로 설치될 것이다.

프로그램의 설치가 완료되면, 일반적인 MySQL 서버 설치와 동일하게 $MYSQL_HOME/scripts/mysql_install_db 스크립트를 이용해서 MySQL 기본 딕셔너리 테이블들을 생성후, MySQL 서버를 시작하면 된다.

Memcached 관련 스키마 생성

MySQL 서버가 정상적으로 시작되었다면,
우선 Memcached 플러그인을 작동시키기 위해서 필요한 기본 스키마들을 생성하도록 하자. MySQL Memcached 플러그인이 작동하기 위해서 필요한 초기 스키마들은 아래 파일이 미리 준비되어 있으므로, source 명령으로 실행해주기만 하면 된다.

mysql> source $MYSQL_HOME/share/innodb_memcached_config.sql
mysql> use innodb_memcache
mysql> show tables

아래와 같은 InnoDB 테이블들이 정상적으로 준비되었는지 확인해보자.

• cache_policies
• config_options
• containers

테이블이 준비되었다면, 이제 KMC를 위한 스키마들을 생성하도록 하자.

mysql> USE innodb_memcache;
mysql> INSERT INTO `containers` VALUES ('default','kmc','kmc_template','k','v','f','c','e','PRIMARY');

mysql> CREATE DATABASE kmc;
mysql> USE kmc;

mysql> DROP TABLE IF EXISTS `kmc_template`;
mysql> CREATE TABLE `kmc_template` (
  `k` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `v` mediumblob,
  `f` int(11) NOT NULL DEFAULT '0',
  `c` bigint(20) unsigned NOT NULL DEFAULT '0',
  `e` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 STATS_PERSISTENT=0;

mysql> INSERT INTO `kmc_template` VALUES ('1','DO-NOT-REMOVE',0,0,0);

KMC를 위한 my.cnf 설정

이제 마지막으로 Memcached를 위한 설정들을 my.cnf 파일에 적용하고 Memcached 서비스가 가능토록 MySQL 서버를 기동해보도록 하자. (MySQL 서버가 기동중이라면 셧다운하도록 하자.)

...
innodb_data_file_path = ibdata1:64M:autoextend
...
innodb_log_file_size = 32M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M

## Memcached -----------------------------------------------------------------------------
daemon_memcached_option = '-m 20480 -p 11211 -c 80000 -t 8 -s /tmp/memcached.sock'
innodb_api_enable_binlog = 1
innodb_api_trx_level=0 ## READ-UNCOMMITTED
innodb_api_bk_commit_interval=1
daemon_memcached_r_batch_size=1
daemon_memcached_w_batch_size=1
innodb_api_enable_mdl=OFF

## Replication:Binary log
server-id  = 1
## Replication query is always idempotent
slave_exec_mode = IDEMPOTENT
## Memcached connection string :: with socket file
kmc_connect_string = '--SOCKET="/tmp/memcached.sock" --BINARY-PROTOCOL --NOREPLY --TCP-NODELAY --TCP-KEEPALIVE'
binlog-checksum = NONE
sync_binlog = 0
master_info_repository = FILE
relay_log_info_repository = FILE
sync_master_info    = 0
sync_relay_log      = 0
sync_relay_log_info = 0
slave_checkpoint_group = 100000
slave_checkpoint_period = 1000
max_binlog_size = 100M
expire_logs_days=1
expire_max_log_files = 15
binlog-format = ROW

대 부분 기본적으로 설정되어 있는 옵션들도 있지만, 안정성과 성능을 위해서 설정되어 있는 것이므로, 큰 문제가 없다면 최소한 이 정도의 옵션은 my.cnf에 포함시키도록 하자.

daemon_memcached_option 옵션은 Memcached 쓰레드가 기동되면서 사용하는 옵션이므로 신중히 설정하도록 하자. 그리고 kmc_connect_string는 MySQL Slave의 SQL Thread가 바이너리 로그를 읽어서 Replay시에 접속하는 Memcached 서버의 정보이므로, 로컬 Memcached 서버의 접속 정보를 설정하면 된다. 또한 InnoDB는 거의 데이터 저장소로 사용되지 않으므로, innodb의 데이터 파일과 리두 로그의 크기는 최소화해서 설정해주는 것이 좋다.

이제 MySQL 서버를 기동하고, Memcached의 기능을 테스트해보자.
물론, KMC의 복제를 연결하는 방법과 복제 상태를 확인하는 방법은 기존 MySQL 서버와 동일하다.

KMC 테스트

MySQL 서버가 기동되면, 아래와 같이 간단히 Memcached의 기능을 테스트를 수행해볼 수 있다.

[root@matt001 ~]# telnet localhost 11211
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.

set matt 0 0 9
Seong Uck
STORED

get matt
VALUE matt 0 9
Seong Uck
END

delete matt
DELETED

get matt
END

quit
Connection closed by foreign host.

만약 Memcached로 접속하는 socket file (/tmp/memcached.sock)이 정상적으로 작동하는지 확인하고자 한다면 아래와 같이 netcat 유틸리티를 이용하면 된다.

[root@matt001 ~]# nc -U /tmp/memcached.sock
set matt 0 0 9
SEONG UCK
STORED

get matt
VALUE matt 0 9
SEONG UCK
END

quit
[root@matt001 ~]#

물론 복제된 데이터를 확인하고자 한다면, 마스터에서 SET을 실행하고 슬레이브 Memcached로 접속해서 GET으로 데이터를 확인해보면 된다. 또한 바이너리 로그를 mysqlbinlog 유틸리티로 열어서 내용을 확인해보는 것이 좋은 방법일 것이다.

Memcached 복제 (2)

Memcached(Cache-only 모드) 복제 아키텍쳐

1편에서 언급했던 것처럼, MySQL Memcached 플러그인이 Caching과 InnoDB-only 모드로 작동하는 경우에는 Memcached를 통한 변경 내용이 InnoDB 스토리지 엔진에 적용되기 때문에 바이너리 로그에도 그 변경 내용이 기록된다. 즉 Caching과 InnoDB-only 모드에서는 자동적으로 복제 기능이 적용되는 것이다.

하지만 이 프로젝트의 목적은 InnoDB를 완전히 사용하지 않고 MySQL 서버를 Memcached 전용 서버로 사용하는 것이기 때문에, Cache-only 모드만 고려 대상인 것이다. 그런데 1편에서도 언급했듯이, 문제는 Cache-only 모드에서는 Memcached의 변경 내용이 MySQL 서버의 바이너리 로그에 기록되지 않는다는 것이다.

Cache-only 모드에서 바이너리 로그를 작동하도록 하는 것이 이 프로젝트의 목표인데, 이를 위해서는 아래 그림과 같이 마스터 MySQL의 바이너리 로그 기록과 슬레이브 MySQL의 릴레이 로그 재생(Replay) 기능을 수정했다.
(그림에서 파란색 선은 원래 MySQL 5.6이 가지고 있던 데이터의 흐름이며, 갈색 두꺼운 선이 수정된 기능을 의미한다.)

위의 그림에서 파란색 점선 화살표는, 이해를 돕기 위해서 MySQL Memcached의 Caching과 InnoDB-only 모드 작동 방식을 표시해둔 것이다.

그림에 표시된 번호별로 조금 더 상세히 살펴보도록 하자.

1. InnoDB API에서는 Memcached를 통해서 유입된 변경 내용을 정해진 규칙(innodb_api_bk_commit_interval, daemon_memcached_r_batch_size, daemon_memcached_w_batch_size 시스템 변수)에 따라서 InnoDB 스토리지 엔진으로 전달하고, 바이너리 로그에 기록하도록 구현되어 있었다. 하지만 이 로직은 Cache-only 모드에는 해당되지 않는데, 이를 Cache-only 모드에서도 작동하도록 수정했다. Original MySQL에서는 Cache-only 모드에서는 InnoDB 테이블이 필요치 않지만, 이 기능을 위해서 Caching이나 InnoDB-only 모드와 같이 Memcached 플러그인을 위한 설정 테이블(innodb_memcache DB)과 컨테이너 테이블이 필요하다. 물론 데이터가 저장되지는 않지만, 껍데기 구조가 필요한 것이다.
   
2. 슬레이브에서는 IO Thread에 의해서 만들어진 Relay log를 가져와서 다시 Memcached에 적용(SET/ADD/DELETE)해야 한다. 하지만 Original MySQL 서버에서는 Replication SQL Thread가 Memcached를 통하지 않고 즉시 InnoDB Storage Engine으로 데이터를 반영해버린다. 그래서 Replication SQL Thread가 Memcached Plugin으로 접근할 수 있는 방법이 필요한데, 이를 위해서 MySQL 서버에 libmemcached.so를 같이 빌드해서 Replication SQL Thread가 Memcached client로 작동할 수 있도록 했다.
   
3. Memcached Client로 작동하는 Replication SQL Thread에서는 최대한 빠르게 Relay 로그의 내용을 Replay해야 하므로, TCP Socket 포트(11211)보다는 Unix socket을 사용하도록 했으며, Async 모드로 Memcached Plugin에 변경 내용을 적용하도록 수정했다.

그 이외의 개선 사항

• MySQL Memcached Plugin이 TCP socket과 Unix domain socket을 동시에 지원하도록 개선
• Memcached Plugin의 상태를 MySQL 서버의 상태 값(Global status variables)으로 모니터링할 수 있도록, 매 초단위로 Memcached의 상태 값을 MySQL 서버 상태 값으로 복사
  mysql> show global status like '%kmc%';
  +----------------------------------+--------------+
  | Variable_name                    | Value        |
  +----------------------------------+--------------+
  | Innodb_kmc_connection_structures | 2004         |
  | Innodb_kmc_curr_connections      | 2002         |
  | Innodb_kmc_curr_items            | 70638969     |
  | Innodb_kmc_pointer_size          | 64           |
  | Innodb_kmc_threads               | 8            |
  | Innodb_kmc_total_connections     | 5090         |
  | Innodb_kmc_total_items           | 778120599    |
  | Innodb_kmc_bytes                 | 18648687816  |
  | Innodb_kmc_bytes_read            | 460650586044 |
  | Innodb_kmc_bytes_written         | 497924594841 |
  | Innodb_kmc_cmd_get               | 7781206118   |
  | Innodb_kmc_cmd_set               | 778120599    |
  | Innodb_kmc_evictions             | 498675673    |
  | Innodb_kmc_get_hits              | 1043938514   |
  | Innodb_kmc_get_misses            | 6737267604   |
  | Innodb_kmc_limit_maxbytes        | 21474836480  |
  +----------------------------------+--------------+
• Caching이나 Cache-only 모드와는 달리 InnoDB 잠금과 무관하게 작동함
  (하지만 여전히 InnoDB의 트랜잭션이나 일부 자원은 할당해서 사용함 --> 개선 예정)
• MySQL 5.6.14의 메모리 릭과 Memcached plugin의 expiration time 설정 버그 보완
• MySQL 서버의 각종 Disk sync 로직을 Async로 전환
• expire_max_log_files 시스템 변수 추가
  바이너리 로그 파일의 개수를 이용한 자동 삭제 기능
• 바이너리 로그 자동 삭제 기능의 이상 현상 모니터링용 상태 변수 Binlog_purge_failed 추가
• 0 : 정상
• 1 : ACTIVE 상태의 바이너리 로그를 삭제하려고 시도한 경우 설정
• 2 : USE 상태의 바이너리 로그를 삭제하려고 시도한 경우 설정
• kmc_connect_string 시스템 변수 추가
  Replication SQL Thread가 릴레이 로그 Replay시 접속할 Memcached 접속 정보 설정

사용 가능한 Memcached Operation

안타깝게도 KMC(Kakao Mem Cached, 복제 가능한 Memcached 서버)의 특성이나 구현 제약으로 인해서, 현재 버전에서는 일부 Memcached operation을 지원하지 않는다.

사용 가능

• GET
• SET
• ADD
• DELETE
• REPLACE

사용 불가능

• INCREMENT
• DECREMENT
• CAS

성능 고려 사항

일반적인 Original Memcached와 비교해서 GET 성능은 차이가 없다. 하지만 SET이나 ADD 그리고 DELETE와 같은 Operation은 Original Memcached와는 달리 KMC는 Memcached의 캐시에 기록후 바이너리 로그에 기록해야 한다. 물론 바이너리 로그 기록은 Sync mode로 작동하는 것은 아니지만 여전히 Original Memcached에 비해서 상당히 많은 자원을 사용하게 된다. 그래서 SET/ADD/DELETE Operation의 성능은 Original Memcached에 비해서 상당히 낮은 결과를 보일 것이다.

하지만, 일반적으로 Memcached의 특성상 SET/ADD/DELETE Operation의 수치는 그렇게 높지 않은 것이 일반적이다. 한번이라도 SET되면 그 이후부터는 SET operation이 줄어들고 GET operation이 늘어나기 때문이다. 만약 SET/ADD가  초당 10K 이상씩 실행되는 Memcached가 있다면, 이는 캐시 효율이 상당히 낮은 Memcached라고 볼 수 있다. 또한 이런 Memcached는 오히려 서비스의 처리 성능에 오버헤드만 유발하고 있다고 볼 수도 있다.

일반적인 Intel x86 장비에서 대략 초당 10K 정도 SET/ADD/DELETE operation을 한계로 고려하고 있는데, 이는 단일 서버의 한계가 아니라 복제 지연으로 인한 한계이다.

KMC 기타 제약 사항

• Memcached 컨테이너 테이블의 이름은 "kmc_" 키워드로 시작해야 한다.
• CAS와 INCR 그리고 DECR 명령 사용 불가
• KMC 내부적으로 Memcached 프로토콜에서 flags 필드의 상위 2바이트를 사용함
• Memcached의 flags중에서 하위 2바이트만 사용한다면 문제 없음

KMC 다운로드

https://github.com/kakao/mysql_5.6.14_kmc

** 마지막 편에서는 MySQL Memcached를 설치하고, 사용하는 방법 그리고 추천하는 설정에 대해서 간단히 소개하도록 하겠다.

Memcached 복제 (1)

개요

모든 응용 프로그램은 영구적으로 저장되어야 하는 데이터를 가지게 마련이며,
이런 데이터들은 MySQL 서버와 같은 RDBMS나 NoSQL류의 DBMS에 저장되고 있다.
하지만 웹 기반의 응용 프로그램에서는 매우 빈번하게 데이터를 DBMS로부터 가져와서 가공하고 그 결과를 클라이언트로 전송해야 하는 요건을 가지고 있는데,
이렇게 많은 요청들을 매번 DBMS에서 가져간다는 것은 상당히 부담스러운 작업중 하나이다.
MySQL과 같은 RDBMS에서는 레코드 하나를 가져가기 위해서도 내부적으로 많은 복잡한 과정들을 거쳐야 하며,
때로는 메모리에 상주되지 않은 데이터를 위해서 고 비용의 디스크 읽기를 거쳐야 할 수도 있다. 
이는 HBase나 Cassandra와 같은 NoSQL류의 데이터베이스에서도 마찬가지이다. 때로는 NoSQL류의 데이터베이스는 데이터 읽기 작업이 RDBMS보다 훨씬 고비용인 경우가 많다.

그래서 이렇게 자주 사용되는 데이터는 빠르게 접근할 수 있도록 Memcached나 Redis와 같은 메모리 기반의 캐시 솔루션을 도입해서 구현하는 것이 매우 일반적인 형태이다.
Memcached와 Redis 모두 많은 개발자들로부터 관심을 받고 있는 캐시 솔루션이지만, 여전히 이 솔루션들은 각자의 단점들을 가지고 있다.

(이 글은 Memcached와 Redis중 어떤 솔루션이 더 나은지를 비교하는 것이 목적이 아니므로, 관심 대상이 아닌 Redis에 대한 언급은 생략하도록 하겠다.)
각 단점중에서 Memcached가 가진 단점 중 대표적인 것은 Memcached의 데이터가 복제되지 않는다는 것이다. (물론 그 이외에도 많은 단점들이 있을 것이다.)
최근에는 많은 회사들이 Memcached 서버의 데이터 복제나 여러 Memcached 서버간의 데이터 동기화에 대한 요건을 가지고 있으며, 페이스북의 경우에는 MySQL 서버의 바이너리 로그를 분석해서 Memcached로 변경 내용을 Relay해주는 기능들을 구현해서 사용중이기도 하다.
카카오에서도 Memcached의 이런 단점을 보완하기 위한 방법을 고려하던중, Memcached 플러그인이 내장된 MySQL 5.6 버전이 릴리즈되었다.

MySQL 5.6  Memcached 플러그인

우선 본격적인 이야기에 앞서서, MySQL 5.6의 Memcached 플러그인에 대한 기본적인 내용을 조금 살펴보도록 하자.
MySQL 5.6에 내장된 Memcached 플러그인은 아래와 같이 3가지 캐시 모드로 작동할 수 있다.

1. Innodb-only
2. Caching
3. Cache-only

애초에 MySQL Memcached 플러그인은 InnoDB의 NoSQL 인터페이스로 도입된 솔루션인 관계로, InnoDB 스토리지 엔진과 매우 밀접하게 연결되어 있다.

• 첫 번째 Innodb-only 모드에서는 Memcached 플러그인이 자체적인 캐시 메모리 공간을 가지지 않고, InnoDB 스토리지 엔진에 Memcached 프로토콜을 이용해서 데이터를 저장하고 읽어가기 위한 방법을 제공하는 것이다.즉 Memcached 프로토콜을 이용해서 SET/DELETE 그리고 GET 오퍼레이션이 실행되면, MySQL Memcached 플러그인은 그 명령을 InnoDB 스토리지 엔진으로 그대로 전달해서 실제 내부적으로는 InnoDB 테이블의 데이터를 변경하거나 조회해서 클라이언트로 반환하게 되는 것이다.

• 두 번째 Caching 모드에서는 InnoDB 버퍼 풀과는 별도로 MySQL Memcached 플러그인이 자체적인 캐시 메모리 공간을 가지는 방식이다. 이 모드에서는 데이터를 ADD/SET/DELETE 오퍼레이션이 수행되면, MySQL Memcached 플러그인의 캐시 메모리 공간에 데이터를 변경함과 동시에 변경 내용을 InnoDB 테이블에도 같이 적용하게 된다.물론 Memcached 플러그인에 적용된 변경 사항이 InnoDB 테이블에 즉시 적용될지 또는 모아서 배치 형태로 처리될지는 MySQL 서버의 옵션을 통해서 조절할 수 있다.그리고 GET 오퍼레이션이 수행되면, Memcached 플러그인의 캐시 메모리에 존재하는 데이터라면 Memcached 플러그인 레벨에서 처리가 완료되고 그렇지 않다면 Memcached 플러그인이 InnoDB 테이블을 조회해서 클라이언트로 반환하게 된다.이 캐시 모드는 마치 MySQL 서버와 Memcached 서버를 하나로 묶어둔 것처럼 작동하는 방식이다.

• 세 번째 Cache-only 모드는 MySQL Memcached 플러그인이 주 역할을 수행하며 InnoDB 스토리지 엔진은 전혀 처리에 관여하지 않게 된다.물론 Memcached의 캐시 메모리 공간에만 데이터가 기록되기 때문에 MySQL 서버가 재시작되면 캐시 메모리의 데이터는 모두 사라지게 될 것이다.

첫 번째와 두 번째 캐시 모드에서는 모두 Memcached 플러그인을 통해서 최종적으로는 InnoDB 스토리지 엔진으로 데이터가 전달되기 때문에 Memcached 플러그인을 통해서 유입된 데이터라 하더라도 바이너리 로그에 기록되어서 슬레이브 MySQL 서버로 전달된다.
하지만 순수한 Memcached 서버와 같은 방식으로 작동하는 세 번째 모드는 Memcached를 통한 데이터 유입이 InnoDB 스토리지 엔진으로 전달되지 않기 때문에 바이너리 로그에 기록되지 않는다.
MySQL Memcached 플러그인이 제공하는 3가지 캐시 모드중에서 첫번째와 두번째는가 각각의 목적과 용도에 맞게 훌륭하게 사용될 수 있을 것으로 보지만, 사실 3번째 Cache-only 모드는 왠지 MySQL 서버에서 필요로 할 기능처럼은 보이지 않는다. 
그런데 여기에서 제일 쓸모가 없을 것으로 보이는 세 번째 캐시 모드에서 Memcached 서버의 데이터 복제에 대한 가능성을 찾을 수 있었다.

만약 MySQL Memcached 플러그인의 Cache-only 모드에서, Memcached 플러그인을 통해서 유입된 데이터가 InnoDB 스토리지 엔진으로는 전달되지 않지만 바이너리 로그에는 기록된다면 어떻게 될까 ?

이 기능이 보완된다면, MySQL 서버를 이용해서 Memcached 서버의 데이터 복제나 동기화가 구현되는 것이다.
결국 이는 MySQL 서버의 복제 기능을 Memcached 서버에 결합하는 효과를 얻을 수 있게 되는 것이다.

물론, 바이너리 로그 기록 하나만 수정해서 모든 것이 해결되는 것은 아닐 것이다. 언급하지 않은 작은 문제들과 제약 사항들이 도사리고 있을 것이다.
하지만 MySQL 서버의 안정적인 복제 방식을 Memcached 서버에 도입할 수 있게 된다면 더 없이 좋은 솔루션이 될 것이다. 그것도 많은 시간을 투자하지 않고서 ...

(2편에서 계속 ...)

pt-online-schema-change 개선

Percona Toolkit에는 많은 훌륭한 도구들이 있지만, 그 중에서도 pt-online-schema-change는 아마도 대 부분의 사용자들이 경험해보았을 정도로 유용한 툴이다.
하지만 아무리 좋은 도구도 모든 사용자의 요건을 만족하기는 어려우며, pt-online-schema-change도 우리의 요건을 만족하기에 조금은 부족한 부분이 있었다.
우선 급하게 아래와 같이 필수적인 부분들 위주로, 조금 보완해서 pt-online-schema-change 도구를 개선해보았다. 

1. 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우
2. 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기
3. pt-online-schema-change 도구가 서비스에 미치는 영향을 최소화하기 위해서 청크(Chunk)간 대기(Sleep) 시간 넣기

위의 기능들을 위해서 pt-online-schema-change 툴에 아래와 같이 4개의 파티미터가 추가되었다.

• --prompt-before-copy
  "--ask-pass"와 같이 별도의 파라미터 값을 필요로 하지 않으며, 이 파라미터가 정의되면 pt-online-schema-change 스크립트가 복사용으로 사용할 테이블을 생성하고 사용자의 확인을 기다리게 된다. 만약 새로운 테이블에 데이터가 복사되기 전에, 새로운 테이블의 구조를 확인한다거나 추가로 변경해야 할 사항이 있다면 이 옵션을 사용하면 된다. 대표적으로 파티션을 생성하는 작업을 위해서 프라이머리 키의 구조를 변경해야 할 때도 있다. 하지만 하나의 ALTER 구문으로는 프라이머리 키를 변경하는 작업과 파티셔닝을 수행하는 작업을 동시에 처리할 수 없다. pt-online-schema-change도구에서도 이 제약은 동일하게 적용된다. 이럴 때에는 --prompt-before-copy 옵션을 활성화하고, pt-online-schema-change가 실행을 잠깐 멈추었을 때 추가로 스키마 변경 작업을 해줄 수 있다.
• --skip-copy-columns
  pt-online-schema-change 도구가 서비스중인 테이블에서 새로운 테이블로 레코드를 복사할 때, 구조 변경 전의 테이블과 구조 변경후의 테이블에서 공통되는 이름의 컬럼은 자동으로 값을 복사하게 된다. 하지만 때로는 이름이 동일한 컬럼이라 하더라도, 기존 테이블의 컬럼 값을 새로운 테이블로 가져오지 말아야 할 때도 있다. 이런 경우에는 --skip-copy-columns 옵션에 무시할 컬럼들을 ","로 구분해서 나열하면 된다.
• --sleep-time-us
  pt-online-schema-change는 기본적으로 MySQL 서버의 시스템 부하 정도를 판단해서 스키마 변경 작업(레코드 복사)의 처리 속도를 조절할 수 있도록 제공하고 있다. 하지만 아주 가벼운 형태의 쿼리가 빈번하게 처리되는 MySQL 서버에서는 이 규칙은 별로 도움이 되지 않는다. 그래서 --sleep-time-us 옵션을 이용하면 pt-onine-schema-change 툴이 청크(chunk) 단위로 복사를 수행하고 나서 일정 시간동인 대기(Sleep)하도록 해서, 서비스용 쿼리들이 거의 지연되지 않고 처리될 수 있도록 할 수 있다. --sleep-time-us 는 마이크로 단위(1/1,000,000)의 초를 파라미터로 받기 때문에, 실제 pt-online-schema-change를 이용하는 경우에는 --sleep-time-us는 1000(1밀리 초) ~ 50000(50 밀리 초) 정도의 값이 적절해 보인다.
• --print-sql
  pt-onine-schema-change 툴은 내부적으로 기존의 테이블은 변경하지 않고, 변경하고자 하는 내용을 포함하는 새로운 테이블을 생성하고 트리거를 이용해서 기존 테이블의 레코드를 새로운 테이블로 복사하는 형태로 처리된다. 이때 pt-onine-schema-change가 생성하는 트리거나 신규 테이블의 구조가 궁금할 때도 있다. 이런 경우에는 --print-sql 옵션을 명시해주면, 생성되는 트리거와 신규 테이블의 구조를 화면에 출력한다. 물론 오리지널 버전의 pt-online-schema-change에도 디버깅 모드로 실행해서 모든 내용을 출력하도록 할 수 있다. 하지만 이는 너무 많은 불필요한 내용들이 출력되므로, 꼭 필요한 내용만 간략히 확인하고자 할 때에는 --print-sql 옵션이 더 도움이 될 것이다.

개선된 pt-online-schema-change에서 위의 옵션들이 명시되면, 아래와 같이 명시된 옵션이 추가로 표기되므로 재확인할 수 있다.
-- Additional parameters ----------------------------------
  >> skip columns : Not specified
  >> sleep time (us) : 50000
  >> prompting user operation : Yes
-----------------------------------------------------------

추가된 기능들을 간단히 살펴보았으니, 이제 위의 1~3번 케이스에 대해서 추가된 기능들을 어떻게 사용할 수 있는지 예제로 살펴보자.

1) 프라이머리 키 변경과 테이블 파티션을 동시에 수행하는 경우

CREATE TABLE test.test_partition (
  id INT AUTO_INCREMENT,
  fd1 VARCHAR(10),
  fd2 DATETIME,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

ALTER TABLE test.test_partition DROP PRIMARY KEY, ADD PRIMARY KEY(id, fd2);
ALTER TABLE test.test_partition PARTITION ...

위의 2개 ALTER 문장은 하나로 묶어서 실행할 수 없으며, pt-online-schema-change 도구를 사용하는 경우에도 마찬가지이다.
그래서 이런 경우에는 아래와 같이 (두개의 ALTER 중에서) 프라이머리 키 변경만 pt-online-schema-change 도구 옵션으로 명시하고, pt-online-schema-change를 --prompt-before-copy 옵션과 함께 실행하는 것이다.

/usr/bin/pt-online-schema-change --alter "DROP PRIMARY KEY, ADD PRIMARY KEY(id, fd2)" D=test,t=test_partition \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8MB4 \
--no-check-alter \
--sleep-time-us=50000 \
--prompt-before-copy \
--print-sql \
--execute

--prompt-before-copy 옵션이 명시되었기 때문에, pt-online-schema-change는 새로운 테이블을 생성하고 사용자가 별도의 사인을 줄때까지 기다리게 된다.
...
-- Additional parameters ----------------------------------
  >> skip columns : Not specified
  >> sleep time (us) : 50000
  >> prompting user operation : Yes
-----------------------------------------------------------
...
-- Create Triggers ---------------------------------------
CREATE TRIGGER `pt_osc_test_test_partition_ins` AFTER INSERT ON `test`.`test_partition` FOR EACH ROW REPLACE INTO `test`.`_test_partition_new` ...
CREATE TRIGGER `pt_osc_test_test_partition_upd` AFTER UPDATE ON `test`.`test_partition` FOR EACH ROW REPLACE INTO `test`.`_test_partition_new` ...
CREATE TRIGGER `pt_osc_test_test_partition_del` AFTER DELETE ON `test`.`test_partition` FOR EACH ROW DELETE IGNORE FROM `test`.`_test_partition_new` WHERE `test`.`_test_partition_new`.`id` <=> OLD.`id`;
----------------------------------------------------------

Table copy operation is paused temporarily by user request '--prompt-before-copy'.
pt-online-schema-change utility created new table, but not triggers.
   ==> new table name : `test`.`_test_partition_new`

So if you have any custom operation on new table, do it now.
Type 'yes', when you ready to go.
Should I continue to copy [Yes] ? : <== pt-online-schema-change는 새로운 임시 테이블 _test_partition_new를 생성한 상태에서 처리를 멈추고, 사용자의 입력을 기다리게 된다.


이때 다른 터미널을 이용해서 MySQL 서버에 로그인하여 새로운 테이블(_test_partition_new)을 ALTER TABLE PARTITION 명령으로 파티션을 생성하고, pt-online-schema-change 터미널에서 "Yes"를 입력하면
프라이머리 키도 변경되고 파티션도 추가된 테이블로 데이터를 복사하게 된다. 이 이후 과정은 오리지널 버전의 pt-online-schema-change와 동일하게 작동하게 된다. 

ALTER TABLE _test_partition_new
PARTITION BY RANGE COLUMNS(CRT_DT)
(
 ...
 PARTITION PF_20140420 VALUES LESS THAN ('2014-04-21 00:00:00') ENGINE = InnoDB,
 PARTITION PF_20140421 VALUES LESS THAN ('2014-04-22 00:00:00') ENGINE = InnoDB,
 PARTITION PF_20140422 VALUES LESS THAN ('2014-04-23 00:00:00') ENGINE = InnoDB,
 PARTITION PF_20140423 VALUES LESS THAN ('2014-04-24 00:00:00') ENGINE = InnoDB
);

참고로, 이렇게 프라이머리 키가 변경되는 경우에는 pt-online-schema-change는 그냥 경고 메시지와 함께 멈춰버리게 되는데, 이 때에는 --no-check-alter 옵션으로 강제 진행되도록 해야 할 필요가 있다. 물론 --no-check-alter 옵션을 사용하는 경우에는 새로 복사되는 테이블의 데이터가 문제없이 복사된다는 것을 사용자 입장에서 검증해야 할 것이다.

2) 이미 존재하는 이름의 컬럼의 기본(Default) 값을 변경하면서, 해당 컬럼의 값을 모두 새로운 기본값으로 채우기

아래와 같은 테이블에서 fd1 컬럼의 기본 값을 'Y'에서 'N'으로 변경하고자 한다. 그런데 이때 pt-online-schema-change로 스키마가 변경된 이후 test_defaultvalue 테이블의 fd1 컬럼은 모두 'N' (새로운 기본값)으로 업데이트되어야 한다고 가정해보자. (물론 당연히 test_defaultvalue 테이블의 fd1 컬럼은 'Y'와 'N'이 혼재되어서 저장되어 있는 상황)

CREATE TABLE test.test_defaultvalue (
  id INT AUTO_INCREMENT,
  fd1 CHAR(1) DEFAULT 'Y',
  fd2 DATETIME,
  PRIMARY KEY(id)
) ENGINE=InnoDB;

ALTER TABLE test.test_defaultvalue MODIFY fd1 CHAR(1) DEFAULT 'N';
UPDATE test.test_defaultvalue SET fd2='N' WHERE fd2='Y';

이때 pt-onine-schema-change 툴을 아래와 같이 실행하면, 새로 생성되는 테이블의 fd1 컬럼의 기본 값은 'N'으로 바뀌겠지만 실제 컬럼의 저장된 값은 이전 테이블의 fd1 컬럼의 값을 그대로 복사해서 가져오게 된다.

/usr/bin/pt-online-schema-change --alter "MODIFY fd1 CHAR(1) DEFAULT 'N'" D=test,t=test_defaultvalue \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8MB4 \
--execute

그래서 오리지널 버전의 pt-online-schema-change 도구를 사용하면, fd1 컬럼의 값을 새로운 기본 값인 'N'으로 채울수가 없다. 그래서 결국 아래오 같이 UPDATE 문장으로 전체 업데이트를 해야 할 것이다. 물론 이런 류의 UPDATE 문장은 인덱스를 이용하지 못해서 테이블의 모든 레코드에 대해서 배타적 잠금을 걸 가능성이 높아서 실제 스키미 변경 만큼이나 영향도가 큰 작업이 되어버리게 된다.

이런 경우에는 개선된 pt-online-schema-change 툴의 --skip-copy-columns 옵션을 이용해서 기존 테이블에서 신규 테이블로 복사할 컬럼에서 일부를 배제시키는 것이 가능하다.
아래와 같이 pt-online-schema-change 를 실행하면, fd1 컬럼의 값을 기존 테이블에서 새로운 테이블로 복사를 하진 않지만 fd1 컬럼의 기본 값이 'N'이기 때문에 fd1 컬럼의 모든 값을 'N'으로 초기화까지 할 수 있게 된다.

/usr/bin/pt-online-schema-change --alter "MODIFY fd1 CHAR(1) DEFAULT 'N'" D=test,t=test_defaultvalue \
--no-drop-old-table \
--no-drop-new-table \
--chunk-size=500 \
--chunk-size-limit=600 \
--defaults-file=/etc/my.cnf \
--host=127.0.0.1 \
--port=3306 \
--user=root \
--ask-pass \
--progress=time,30 \
--max-load="Threads_running=100" \
--critical-load="Threads_running=1000" \
--chunk-index=PRIMARY \
--charset=UTF8MB4 \
--sleep-time-us=50000 \
--skip-copy-columns='fd1' \
--prompt-before-copy \
--print-sql \
--execute

이때 --prompt-before-copy 옵션과 --print-sql 옵션을 사용하면, --skip-copy-columns에 의해서 적용된 트리거와 INSERT .. SELECT .. 복사 SQL 문의 내용을 확인하고 문제가 없으면 작업을 진행하도록 할 수 있다.

3) pt-online-schema-change 도구가 서비스에 미치는 영향을 최소화하기 위해서 청크(Chunk)간 대기(Sleep) 시간 넣기

pt-online-schema-change 도구 자체적으로 부하를 조절하면서 복사하는 기능을 가지고 있다는 것은 잘 알고 있을 것이다. 하지만 이미 간단히 언급했듯이, MySQL 서버가 디스크를 읽지 않고 PK나 인덱스 기반으로 1~2건 레코드를 메모리만 읽어서 (아주 빠르게) 서비스하는 경우에는 경우에는 오리지널 버전의 pt-online-schema-change가 가지고 있는 부하 제어 기능은 부족할 수도 있다.
이런 경우에는 개선된 pt-online-schema-change의 --sleep-time-us 옵션으로 하나의 청크(chunk)가 완료되면 일정 시간동안 pt-online-schema-change의 복사 작업이 잠깐 Sleep하도록 할 수 있다.
--sleep-time-us 옵션에는 숫자 값으로 마이크로 단위의 초를 입력해주면 된다. 즉 "--chunk-size=500 --sleep-time-us=5000" 옵션이 사용되면 500개의 레코드를 복사하고 5 밀리초를 Sleep하게 되는 것이다.

물론 이렇게 매 청크마다 일정 시간동안 Sleep을 수행하면 스키마 변경 시간이 더 길어지게 되는 문제점이 있다. 하지만 pt-online-schema-change 도구 자체의 특성상 다른 트랜잭션의 처리를 잠금 차원에서 방해하거나 리두 로그나 언두 레코드를 대량으로 발생시키는 작업이 아니므로, pt-online-schema-change 자체의 완료 시간이 길어진다 하더라도 데이터의 정합성을 훼손하지는 않으므로 크게 걱정하지 않아도 된다. 또한 pt-online-schema-change를 이용해서 온라인 스키마 변경을 한다 하더라도 사용량이 적은 시간대를 골라서 수행하므로, 소요 시간이 더 걸린다고 해서 크게 문제되지는 않을 것이다. 물론 테이블의 전체 레코드 건수를 기반으로 Sleep할 시간을 결정해야 할 필요가 있지만, 여전히 그만큼의 가치는 있는 작업이 될 것이다.
오리지널 버전의 pt-online-schema-change에서는 --max-load 옵션이 최적으로 설정되지 않으면, 쉬지 않고 레코드를 새로운 테이블로 복사하게 될 수도 있다. 하지만 이는 InnoDB의 Checkpoint age를 급증시켜서 InnoDB 서버를 Sync나 Async 모드로 전환시켜버릴 수도 있다. 하지만 --sleep-time-us 옵션을 적절히 설정해준다면, 천천히 데이터를 복사하므로 Checkpoint age가 급증하는 현상을 막을 수도 있다. 

많은 프로그램들이 초단위나 밀리 초 단위의 값을 입력받기 때문에, 가끔 --sleep-time-us에 입력되는 값을 밀리초로 착각하고 사용할 수도 있으므로 주의할 필요가 있다. --sleep-time-us 옵션을 사용하는 방법은 위의 예제를 참조하도록 하자.

설치 및 패치

1. Download percona toolkit percona-toolkit-2.2.7-1.noarch.rpm from http://www.percona.com/downloads/percona-toolkit/LATEST/RPM/
2. Install percona toolkit 2.2.7-1
   $ rpm -Uvh percona-toolkit-2.2.7-1.noarch.rpm
3. Patch modified code to original pt-online-schema-change
   $ patch /usr/bin/pt-online-schema-change < pt-online-schema-change_kakao.patch

첨부된 패치 파일은 Percona toolkit 2.2.7-1 버전을 기준으로 생성되었습니다.

Linux Kernel Parameters and MySQL

Overview

리눅스 커널 파라미터들 중 데이터 기록 빈도와 관련된 파라미터들에 대해 조사해보았고, 이에 따라 MySQL 서버에 어떤 영향을 미칠지 정리해보았습니다.

Definition

Linux의 /proc/sys/vm 디렉토리에는 서버의 가상메모리와 관련된 커널 파라미터들이 파일로 존재합니다.

이 중 데이터의 기록, 즉 Page Cache에 있는 dirty data를 disk에 flush하는 것과 관련한 6개의 파라미터들에 대해 설명드리겠습니다.

Kernel Parameter	description	Default
dirty_background_ratio	전체 시스템 메모리에서 dirty page 비율, pdflush 데몬이 dirty page를 쓰기 시작하는 기준값임.	10(%)
dirty_ratio	전체 시스템 메모리에서 dirty page 비율, 그러나 이 값은 process가 dirty page를 쓰기 시작하는 기준값임.	40(%)
dirty_background_bytes	위의 dirty_background_ratio와 같은 의미지만 이 변수에는 바이트 값이 들어감.  (비율과 달리 이 값은 절대적인 값임.) 일반적인 경우 dirty_background_ratio보다 이 변수값이 더 우선적이나, 값이 0으로 설정된 경우에는 dirty_background_ratio의 설정이 우선권을 가짐	0
dirty_bytes	위의 dirty_ratio와 같은 의미지만 이 변수에는 바이트 값이 들어감.  (비율과 달리 이 값은 절대적인 값임.) 일반적인 경우 dirty_ratio 보다 이 변수값이 더 우선적이나, 이 값이 0으로 설정된 경우에는 dirty_ratio의 설정이 우선권을 가짐	0
dirty_writeback_centisecs	이 값에 해당하는 시간을 주기로 캐시를 체크해서 dirty page들을 디스크에 flush함	500(1/100sec)
dirty_expire_centisecs	page들이 수정된 상태로 있을 수 있는 시간을 나타냄, 즉 dirty page가 캐시에 존재할 수 있는 시간을 말함	3000(1/100sec)

Process

커널 스레드 pdflush는 'drity_writeback_centisecs' 마다 깨어나서 writeback 여부를 확인합니다. 

즉, 전체 모든 파일들의 page cache를 체크하여 전체 메모리에 대한 dirty page의 비율이 'dirty_background_ratio'를 넘는지 검사하고

이 값을 넘는 경우 'dirty_expire_centisecs' 이상 존재한 dirty page들을 disk에 flush합니다.

Opinion

dirty_background_ratio 값을 크게 설정할 경우 변경해야될 페이지들이 그만큼 캐시에 많이 존재할 수 있게 됩니다.

그렇게 되면 pdflush 데몬이 disk에 flush할 dirty page들이 많아지는 것이며, 이 때문에 I/O 시간이 길어집니다.

보통 일반 시스템에서 디스트I/O는 병목지점이기 때문에  I/O 시간이 길어지면 그만큼 시스템이 느려질 것이라 생각됩니다.

또한 이렇게 변경될 데이터들이 캐시에 많이 있는데 서버 장애가 나면 그 데이터는 손실되기 때문에 

dirty_background_ratio 값은 줄여서 사용하는 것이 효율적이라 생각합니다. 

(특히 DB에서 insert나 update 작업이 많아서 데이터 파일에 쓰기 작업이 많은 경우!)

dirty_writeback_centisecs 값도 클 경우 그만큼 체크하는 주기가 길어지는 것이기 때문에 한번에 처리할 page 수가 많아질 것입니다.

따라서 이 값도 어느정도 줄여서 한번에 비워내야할 page수를 줄이는 것이 효율적이라고 생각합니다.

dirty_expire_centisecs은 디폴트값이 30초로, 이 시간이 지나기 전까지는 실질적으로 disk에 쓰지 않는다는 것입니다.

그렇다면 30초동안 dirty data가 계속 캐시에 머무른다는 것이므로 이 값도 줄여서 사용하는 것이 옳다고 생각합니다.

하지만 그렇다고 해서 너무 줄이면 너무 빈번하게 disk에 쓰기를 시도하기 때문에 너무 낮추지는 말고 적절히 줄여서 사용하는 것이 바람직하다고 생각하다고 생각합니다. 

(하지만 InnoDB 스토리지 엔진을 사용하는 경우 innodb_flush_method 옵션에 따라 위 커널 파라미터들에 대해 영향을 받지 않을 수도 있을 것 같습니다.

이런 경우 innodb_max_dirty_pages_pct 값을 조정해줄 수 있습니다.)

** innodb_max_dirty_pages_pct : innodb buffer fool 내에 허용된 dirty page 비율

Latest Issue and Kernel Parameters

최근 일부 서버에서 디스크 i/o의 급증으로 인해 서버의 부하가 심한 이슈가 있었습니다. 

이슈가 발생한 원인은 해당 서버의 Filesystem Cache 대부분이 dirty data들로 꽉 차있는 경우,

swap 방지를 위해 설정해놓은 cache unmap 스크립트가 디스크에 dirty page를 쓰면서 1기가정도까지 

Cache를 줄이지만 Cache에 남은 그 data들이 모두 dirty data이기 때문에 disk i/o가 다시 발생하여 서버에 부하가 온 것입니다.

그래서 현재는 해당 서버의 커널 파라미터를 아래와 같이 조정해준 상태입니다.

/proc/sys/vm/dirty_background_ratio : 10 ===> 1

/proc/sys/vm/dirty_expire_centisecs : 3000 ===> 1000

dirty_background_ratio 값을 1로 낮춤으로써 전체 cache의 1%만큼만 dirty page가 존재할 수 있게 하였고, 

dirty_expire_centisecs도 10초로 줄임으로써 너무 오랫동안 dirty page가 cache에 남아있지 않도록 조치를 취하였습니다.

Details of Mysql_Cache_Unmap

이전에 Kakao DB Team 블로그에서 Filesystem Cache의 swap 문제와 함께 cache_unmap 스크립트에 대해 언급했었습니다.  (참고 url : http://kakaodbe.blogspot.kr/2013/09/mysql-linux-filesystem-cache-2.html)

이 챕터에서는  cache unmap에 대해 실제로 서버에 어떻게 적용하고 있는지에 대해 살펴보겠습니다.

지금 db서버에 적용된 mysql_cache_unmap 스크립트는 Matt이 작성한 것으로 요시노리 개발자가 작성한 스크립트를 참고한 것입니다.

(** 소스 코드 파일 다운 : mysql_cache_unmap.c)

주기적으로 cache unmapping을 수행하기 위해 Crontab에는 아래와 같이 설정되어 있습니다.

# Crontab Setting */10 * * * * root LD_LIBRARY_PATH=/otp/mysql/lib: /otp/mysql/admin/mysql_cache_unmap --defaults-file=/etc/my.cnf --binary_os_cache_size=1024M > /otp/mysql/admin/mysql_cache_unmap.log2>&1

 --defaults-file 옵션을 통해 mysql server의 configuration files 경로를 지정할 수 있습니다. cache unmap 유틸리티에서는 이 설정 파일을 읽어서 binary log와 data file 그리고 innodb redo log 파일의 경로를 얻게 됩니다.

 --binary_os_cache_size 옵션은 Linux OS Cache에서 제거하지 않고 남겨둘 용량을 의미하는데, cache_unmap  유틸리티는 최근에 발생한 바이너리 로그부터 지정된 용량(위 경우 1024M)을 Linux OS Cache에 남겨두게 됩니다.

다음은 mysql_cache_unmap 스크립트 실행로그 입니다.

root@host:~ 14:10:09> cat /opt/mysql/admin/mysql_cache_unmap.log Read configuration     innodb_data_dir : /opt/mysql/data     innodb_log_dir : /opt/mysql/data     binary_log_dir : /opt/mysql/data/mysql-binary     binary_os_cache_size : 1073741824     relay_log_dir : /opt/mysql/data/mysql-relay > unmap_file_all : datafile : /opt/mysql/data/dbname1/tablename1.ibd > unmap_file_all : datafile : /opt/mysql/data/dbname1/tablename2.ibd > unmap_file_all : logfile : /opt/mysql/data/ib_logfile0 > unmap_file_all : logfile : /opt/mysql/data/ib_logfile1 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041728 : 0 ~ 60083119 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041727 : 0 ~ 104857974 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041726 : 0 ~ 104857975 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041725 : 0 ~ 104857883 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041724 : 0 ~ 104857852 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041723 : 0 ~ 104857920 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041722 : 0 ~ 104857989 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041721 : 0 ~ 104857781 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041720 : 0 ~ 104857784 > skip unmap file: binary : /opt/mysql/data/mysql-binary.041719 : 0 ~ 104857666 > unmap_file_segment : binary : /opt/mysql/data/mysql-binary.041718 : 0 ~ 34920063 of 104857944 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041717 : 0 ~ 104857764 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041716 : 0 ~ 104857852 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041715 : 0 ~ 104857772 > unmap_file_all : binary : /opt/mysql/data/mysql-binary.041714 : 0 ~ 104857847

mysql_cache_unmap은 위 로그에서 볼 수 있듯이 filesystem cache에서 datafile 및 redo_log_file, binary_log_file의 caching 영역을 unmap합니다.

mysql_cache_unmap을 사용하는 이유는 centOS 5.x에서 Filesystem Cache를 가장 우선시함에 따라 실제 free memory가 없는 경우 memory를 가장 많이 사용하고 있는 application의 일정량을 swap으로 내려버리는 것에 있습니다.

만약 memory를 가장 많이 사용하고 있는 것이 MySQL이라면 MySQL에서 캐싱된 데이터들이 swap되어 내려갈 것이고, 쿼리 수행 시 다시 데이터를 디스크에서 퍼올려야하는 문제가 발생하게 됩니다.

따라서 이러한 문제가 생기지 않도록 하기 위해 mysql_cache_unmap을 주기적으로 실행해주며 cache size가 너무 많이 증가하지 않도록 하고 있습니다.

** Attention 

cache_unmap은 redo log와 innodb system data file의 내용은 무조건 하나도 남기지 않고 Cache에서 purge합니다.

만약 redo log나 innodb data file의 쓰기 방식 (innodb_flush_method 옵션)이 innodb_flush_method =O_DIRECT인 경우에는 innodb data file은 direct io를 수행하지만 innodb redo log는 direct_io가 아니라 cached io를 수행하게 됩니다.

따라서 이 경우에는 redo log 파일에 대한 cache unmap은 피하는 것이 좋으며, innodb_flush_method가 O_DIRECT나 ALL_O_DIRECT (MariaDB & PerconaServer only)를 사용하지 않는다면 redo log와 Innodb data file의 cache unmap은 사용하지 않는 것이 좋습니다.

이는 mysql_cache_unmap 프로그램을 변경해서 적용하는 것을 추천합니다.

mysql_cache_unmap 스크립트에서는 'posix_fadvise'라는 커널 함수를 이용하여 caching된 영역을 unmap합니다.

## posix_fadvise SYSNOPSIS #define _XOPEN_SOURCE 600 #include <fcntl.h> int posix_fadvise(int fd, off_t offset, off_t len, int advice);

fd가 참조하는 파일내에서 offset을 시작으로 len bytes까지의 영역에 대해 advice값에 해당하는 설정을 적용합니다.

이때 len의 값이 '0'일 경우, 파일의 끝이라 간주하고 파일 전체가 설정 적용 영역에 해당됩니다.

함수가 제대로 수행이되면 '0'값을 반환하고, 에러가 발생한 경우에는 해당 에러에 대한 넘버를 반환합니다.

아래는 mysql_cache_unmap 소스에서 발췌한 코드로 posix_fadvise 함수를 통해 실질적으로 cache unmap이 이루어지는 부분입니다.

#define _XOPEN_SOURCE 600 #include <fcntl.h> int unmap_file_segment(const char *fpath, size_t start, size_t len){   int fd = open(fpath, O_RDONLY);   if (fd < 0){     fprintf(stderr, "ERROR : Failed to open %s\n", fpath);     return 1;   }   int r = posix_fadvise(fd, start, len, POSIX_FADV_DONTNEED);   if (r != 0){     fprintf(stderr, "ERROR : posix_fadvice failed for %s\n", fpath);   }   close(fd);   /* if posix_fadvise is succeeded, then sleep 25 milli seconds */   usleep(25 * 1000);   return 0; }

코드에서 advice 파라미터의 값이 'POSIX_FADV_DONTNEED'로 설정되어 있는 것을 볼 수 있습니다.

advice 타입에는 여러 종류가 있으나, 'POSIX_FADV_DONTNEED'로 값을 설정한 경우 커널이 page cache(filesystem cache)에서 해당 file이 차지하는 영역 중 offset을 기준으로 len길이 까지 만큼의 영역에 대해 unmapping을 진행하게 됩니다.