[MySQL] 1.MySQL as a NoSQL

출처 : http://www.dbguide.net/knowledge.db?cmd=view&boardUid=166138&boardConfigUid=20&boardStep=&categoryUid=209

DBGuide net에 연재된 내용으로 요새 관심있는 Mysql에 관하여 잘 정리가 되어 있어 스크랩해왔습니다.

MySQL as a NoSQL

NoSQL 데이터베이스로서 MySQL

[저자] 하호진 | KTH에서 포털 서비스 및 플랫폼(Identity/Payment) 개발 업무를 수행했으며, 현재 와이즈에코에서 이사로 일하고 있다. 개인 블로그 mimul.com/pebble/default를 운영한다.

제 1회 NoSQL 데이터베이스로서 MySQL 제 2회 스케일 아웃 측면에서 살펴본 MySQL의 특성 제 3회 NoSQL 데이터 모델링 


최근 NoSQL이 데이터베이스 영역에 한 축으로 자리잡고 있지만, 기존 관계형 데이터베이스(RDB) 사용자들에게 NoSQL은 아직 낯설기만 하다. 이런 상황을 반영해 데이터 정합성이 중시되는 서비스에서 RDB의 장점을 활용하면서 NoSQL 특징을 결합하려는 시도들이 이어지고 있다. NoSQL의 특징들을 추가하면서 RDB 기능을 보완하는 방법과 이왕 NoSQL을 사용하려면 제대로 한번 해보는 차원에서 모델링까지 살펴보기로 했다.

첫 번째 연재에서는 'MySQL as a NoSQL'이라는 제목으로 MySQL의 장점에 HandlerSocket Plugin이나 Memcached Plugin을 활용해 NoSQL의 특성을 가미하는 방법과 성능에 대해 알아본다. 2회차 연재에서는 'MySQL의 스케일 아웃(Scale Out)'에 대해 알아본다. MySQL의 Scale Out 기능의 약점을 보완하는 차원에서 대안적 스케일 아웃 방법들에 대해 살펴볼 계획이다. 3회에서는 'NoSQL 데이터 모델일'이라는 주제로 MySQL을 본격적인 NoSQL로 DB로 사용하기 위한 모델링 방법 대해 소개할 계획이다. 

IT 환경의 변화

데이터베이스 분야는 최근 클라우드 컴퓨팅과 소셜 네트워크 서비스의 등장, 스마트 기기의 확산 등 컴퓨팅 환경 변화에 대처하고 내부적으로 데이터의 폭증과 원활한 서비스 지원을 위해 다음과 같은 과제를 해결해야 하는 환경에 놓여있다. 

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"> ◆ 중저가 PC급으로 읽기/쓰기를 할 수 있으면서 데이터베이스 확장성(Scalability) 보장 ◆ 중단 없는 서비스와 높은 가용성 보장(Auto-failover) ◆ 빅데이터 환경을 고성능(High Performance)과 무결성(Integrity) 있게 지원 ◆ 비즈니스에 적합한 데이터베이스 스키마의 신속한 확장 ◆ 데이터베이스 설계와 액세스 패턴 등의 사용의 편이성(Usability) 제공 </span>

이렇듯 기존의 RDB의 장점인 데이터 무결성, 습관화된 사용 편이성과 NoSQL의 장점인 빅데이터를 수용하는 구조, 짧은 대기 시간을 통해 높은 성능 보장, 심플한 액세스 패턴을 결합하는 사례들이 늘어나고 있다. 이에 따라 필자는 MySQL을 NoSQL로 쓰는 여러 방법을 성능과 확장성 등 여러 관점에서 고찰해 업무에 적용하고자 하는 DB 엔지니어에게 도움을 드리고자 한다. 

1. MySQL에 NoSQL 엔진을 올릴 수 있었던 배경

MySQL에서 쿼리를 던져서 결과를 가져오는 데까지 가장 많은 비용을 차지하는 곳이 SQL Layer의 구문 분석 단계이다. <그림 1>을 보면서 간략히 설명한다.


[그림 1] 쿼리 프로세스

MySQL 데이터베이스 서버에 SQL이 들어오면 SQL Layer는 SQL 구문을 분석, 해당 테이블에 오픈과 락을 걸고 쿼리 실행 계획을 분석한다. 이어서 테이블 락을 해제하고 닫는 처리 과정을 진행하게 된다. 이 과정은 MySQL의 성능에 많은 오버헤드를 안겨준다.

이러한 SQL Layer의 오버헤드를 어떻게 하면 줄일 수 있을까? 하는 생각과 더불어 MySQL 아키텍처가 SQL 처리 레이어와 실제 데이터베이스 스토리지 엔진이 분리된 점에서 MySQL에 NoSQL 엔진을 올려 성능 향상을 꾀할 여지가 생긴 것이다.

MySQL 스토리지 엔진에 직접 API를 호출함으로써 고성능을 보장할 수 있는 것이다. 즉, 기존 관계형 데이터베이스의 장점을 그대로 유지하면서 단순한 key/value의 연산 비용만 소비돼 고성능 처리가 되는 것이다. 

MySQL을 NoSQL처럼 사용한 예로는 HandlerSocket Plug-in, Memcached Plug-in 등의 사례가 있다. 기존에는 별도의 캐시 서버(memcached 등)와 MySQL 데이터베이스를 사용하는 아키텍처에서 데이터의 일관성 유지 차원에서 애플리케이션 측에서 개발자가 직접 개발?보장해야 하는 어려움이 있었다. MySQL에 NoSQL 요소를 플러그인해 이 부분의 개선을 가져온 것도 중요한 장점이다.

본 연재에서는 MySQL 인프라 위에서 NoSQL 특징 가운데 하나인 성능 확보를 위해 접근한 HandlerSocket Plug-in, 아직 출시되지 않았지만 MySQL에서 준비중인 Memcached Plug-in에 대해 살펴본다. 

2. HandlerSocket Plug-in for MySQL

HandlerSocket은 Percona(MySQL의 브랜치 버전, 성능이 좀 더 낫다고 볼 수 있음)와 MySQL에 각각 플러그인해 사용하는 방법이 있다. 그리고 Percona Server 5.1.52-12.3 부터 HandlerScoket이 기본 플러그인돼 있지만, 여기서는 가장 많이 사용되는 MySQL에 플러그인하는 측면에서 접근했다.

1) 아키텍처 개요

[ 그림 2] HandlerSocket Plug-in 아키텍처

HandlerSocket Plug-in 모듈이 MySQL의 SQL Layer 역할(SQL 파싱, Query Optimizing)을 대신함으로써 데이터 무결성을 보장하고 성능을 개선한 사례이다.

2) 장점

- 다양한 쿼리 패턴을 구사할 수 있다. PK/unique lookups, non-unique index lookups, range scan, LIMIT, INSERT/UPDATE/DELETE.

- 적은 메모리 사용하고 안정적이다. 다수의 동시 커넥션을 수용할 수 있다.

- 조회 시 고성능을 보장한다(750,000 QPS가 가능하다고 함).

- MySQL을 사용함으로써 영속성(persistent), 충돌 제거(crash-safe), ACID를 준수할 수 있다.

- MySQL과 독립적으로 사용돼 MySQL에 장애 발생 우려가 낮다.

3) 단점

- 분산 처리는 가능하지만 스케일 아웃이 보장되지 않는다.

- 보안 대책이 없다. 

4) 지원 클라이언트

- PHP
- 자바
- 파이썬
- Ruby
- JavaScript
- Scala
- Haskell

5) MySQL 설치

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"> &amp;gt; yum install -y libevent libevent-devl &amp;gt; vi /etc/security/limits.conf mysql soft nproc 8192 mysql hard nproc 16384 mysql soft nofile 8192 mysql hard nofile 65536 &amp;gt; wget ftp://mirror.services.wisc.edu/mirrors/mysql/Downloads/MySQL-5.5/ mysql-5.5.23.tar.gz &amp;gt; cmake -DCMAKE_INSTALL_PREFIX=/database/server/mysql-5.5.23 \ -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS=all \ -DMYSQL_DATADIR=/database/data2 \ -DMYSQL_UNIX_ADDR=/tmp/mysql.sock \ -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ -DWITH_PARTITION_STORAGE_ENGINE=1 &amp;gt; mkdir /database/data2 &amp;gt; chown -R mysql:mysql /database/data2 &amp;gt; cp support-files/my-innodb-heavy-4G.cnf /etc/mysql-5.5.23/my.cnf &amp;gt; scripts/mysql_install_db --defaults-file=/etc/mysql-5.5.23/my.cnf --basedir=/database/server/mysql-5.5.23 --datadir=/database/data2 --user=mysql --pid-file=/etc/mysql-5.5.23/mysql.pid --port=3308 --socket=/etc/mysql-5.5.23/mysql.sock &amp;gt; bin/mysqld_safe --defaults-file=/etc/mysql-5.5.23/my.cnf --datadir=/database/data2 --user=mysql &amp;amp; &amp;gt; bin/mysqladmin -u root password 'new-password' </span>

6) HandlerSocket 설치

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"> &amp;gt; tar xvfz ahiguti-HandlerSocket-Plugin-for-MySQL-1.1.0-2-g395fa55.tar.gz &amp;gt; cd ahiguti-HandlerSocket-Plugin-for-MySQL-395fa55 &amp;gt;./autogen.sh ./configure --with-mysql-source=/database/src/mysql-5.5.23 --with-mysql-bindir=/database/ server/mysql-5.5.23/bin --with-mysql-plugindir=/database/server/mysql-5.5.23/lib/plugin # --with-mysql-source : MySQL의 소스 코드 디렉토리. # --with-mysql-bindir : 설치된 MySQL의 mysql_config 커맨드가 존재하는 디렉토리 # --with-mysql-plugindir : MySQL의 plugin 디렉토리 &amp;gt; tar xvfz ahiguti-HandlerSocket-Plugin-for-MySQL-1.1.0-2-g395fa55.tar.gz &amp;gt; cd ahiguti-HandlerSocket-Plugin-for-MySQL-395fa55 &amp;gt;./autogen.sh ./configure --with-mysql-source=/database/src/mysql-5.5.23 --with-mysql-bindir=/database/ server/mysql-5.5.23/bin --with-mysql-plugindir=/database/server/mysql-5.5.23/lib/plugin # --with-mysql-source : MySQL의 소스 코드 디렉토리. # --with-mysql-bindir : 설치된 MySQL의 mysql_config 커맨드가 존재하는 디렉토리 # --with-mysql-plugindir : MySQL의 plugin 디렉토리 &amp;gt; make;sudo make install &amp;gt; vi /etc/mysql-5.5.23/my.cnf [mysqld] innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT max_connections=500 loose_handlersocket_port = 9998 loose_handlersocket_port_wr = 9999 loose_handlersocket_threads = 16 loose_handlersocket_threads_wr = 1 [mysqld_safe] open-files-limit = 65535 &amp;gt; mysql -uroot -p mysql&amp;gt;install plugin handlersocket soname 'handlersocket.so'; mysql&amp;gt; show plugins; +--------------+--------+---------+----------------+---------+ | Name | Status | Type | Library | License | +--------------+--------+---------+----------------+---------+ .............. | handlersocket| ACTIVE | DAEMON |handlersocket.so| BSD | +--------------+--------+---------+----------------+---------+ </span>

7) 테스트 테이블 확인

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"> # 테스트를 위한 테이블 생성 &amp;gt; mysql -uroot -p mysql&amp;gt;use test; mysql&amp;gt; CREATE TABLE user( -&amp;gt; `id` bigint(20) NOT NULL AUTO_INCREMENT, -&amp;gt; `last_name` varchar(256) DEFAULT NULL, -&amp;gt; `first_name` varchar(256) DEFAULT NULL, -&amp;gt; `duty` varchar(256) DEFAULT NULL, -&amp;gt; `cellphone` varchar(256) DEFAULT NULL, -&amp;gt; `housephone` varchar(256) DEFAULT NULL, -&amp;gt; `telephone` varchar(256) DEFAULT NULL, -&amp;gt; `office_fax` varchar(256) DEFAULT NULL, -&amp;gt; `home_address` varchar(256) DEFAULT NULL, -&amp;gt; `office_address` varchar(256) DEFAULT NULL, -&amp;gt; `remark` text, -&amp;gt; PRIMARY KEY (`id`), -&amp;gt; KEY `NAME_INDEX` (`first_name`,`last_name`) -&amp;gt; )ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql&amp;gt; desc user; +----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | last_name | varchar(256) | YES | | NULL | | | first_name | varchar(256) | YES | MUL | NULL | | | duty | varchar(256) | YES | | NULL | | | cellphone | varchar(256) | YES | | NULL | | | housephone | varchar(256) | YES | | NULL | | | telephone | varchar(256) | YES | | NULL | | | office_fax | varchar(256) | YES | | NULL | | | home_address | varchar(256) | YES | | NULL | | | office_address | varchar(256) | YES | | NULL | | | remark | text | YES | | NULL | | +----------------+--------------+------+-----+---------+----------------+ </span>

3. Memcached Plug-in for MySQL

Memcached는 5.6.2-labs-innodb-memcached-log 버전을 설치하며, MySQL도 5.6버전부터는 Memcached가 디폴트 플러그인 된다.

1) 아키텍처 개요

[그림 3] Memcached Plug-in 아키텍처 

HandlerSocket과 유사하게 SQL Layer에서 처리하던 SQL 파싱(parsing)과 Query 최적화 같은 연산이 제거되고, 직접 MySQL Storage Engine에 접근해 읽기/쓰기를 해 더 많은 서버 자원(CPU, 메모리 및 I/O)이 스토리지 엔진 자체 내 쿼리를 지원하는 데 쓰여 성능이 향상되는 구조다. 데이터 연동 방식은 cache-only, innodb-only, caching(cache + innodb) 등 세 가지 중 하나를 설정 가능하며, daemon_memcached_option 옵션을 설정함으로써 기존 사용하는 memcache 관련 추가적 설정이 가능하다.

2) 장점

- 기존 Memcached 를 사용하고 있다면 클라이언트 소스 변경 없이 재사용이 가능하다.

- Memcached의 비 영속적 데이터를 영속적으로 유지할 수 있게 됐다.

- MySQL을 사용함으로써 영속성(persistent), 충돌 제거(crash-safe), ACID 준수가 보장된다.

- 캐시 및 데이터베이스 간 데이터 중복을 제거함으로써 메모리 공간을 절약할 수 있다.

- 고성능 조회가 가능하다.

- Memcached를 독립적으로 운영했을 때와 비교했을 경우 데이터 동기화, 데이터 일관성 검사 작업을 제거함으로써 운영 비용이 감소된다.

3) 단점

- 현재 연구 개발 버전으로 단 한 개의 테이블만 memcache와 연동할 수 있다..

- 성능을 올리기 위해 daemon_memcached_w_batch_size를 통해 커밋 시기를 결정할 수 있는데, 이때 상호 다른 세션에서 데이터 확인이 안 되기 때문에 이를 해소하려면 isolation-level을 read uncommitted로 설정해야 데이터가 보인다.

- Character Set을 설정할 수 없다. 현재 Latin1만 지정할 수 있다.

- 보안에 대한 대응책이 없다. 

4) 지원 클라이언트

- C/C++
- PHP
- 자바
- 파이썬
- Ruby
- Perl
- 닷넷/C#
- Erlang
- Lua

5) MySQL 설치

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"> &amp;gt; yum install -y libevent libevent-devl &amp;gt; vi /etc/security/limits.conf mysql soft nproc 8192 mysql hard nproc 16384 mysql soft nofile 8192 mysql hard nofile 65536 &amp;gt; wget http://downloads.mysql.com/snapshots/pb/mysql-5.6-labs-innodb-memcached/ mysql-5.6.2-labs-innodb-memcached.tar.gz &amp;gt; tar -xzvf mysql-5.6.2-labs-innodb-memcached.tar.gz;cd mysql-5.6.2-labs-innodb-memcached &amp;gt; cmake . -DCMAKE_INSTALL_PREFIX=/database/server/mysql-5.6.2 &amp;gt; make; sudo make install &amp;gt; mkdir /database/data2 &amp;gt; chown -R mysql:mysql /database/data2 &amp;gt; cp support-files/my-innodb-heavy-4G.cnf /etc/mysql-5.6.2/my.cnf vi /etc/mysql-5.6.2/my.cnf [mysqld] innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT max_connections=500 [mysqld_safe] open-files-limit = 65535 ./scripts/mysql_install_db --defaults-file=/etc/mysql-5.6.2/my.cnf --basedir=/database/server/ mysql-5.6.2 --datadir=/database/data2 --user=mysql --pid-file=/etc/mysql-5.6.2/mysql.pid --port=3308 --socket=/etc/mysql-5.6.2/mysql.sock &amp;gt; bin/mysqld_safe --defaults-file=/etc/mysql-5.6.2/my.cnf --datadir=/database/data2 --user=mysql &amp;amp; &amp;gt; bin/mysqladmin -u root password 'new-password' </span>

6) Memcached 설치

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"> &amp;gt; cd /database/server/mysql-5.6.2 mysql -u root &amp;lt; scripts/innodb_memcached_config.sql mysql -uroot -p mysql&amp;gt; install plugin daemon_memcached soname "libmemcached.so"; mysql&amp;gt; show variables like '%memcached%'; +----------------------------------+-----------------+ | Variable_name | Value | +----------------------------------+-----------------+ | daemon_memcached_engine_lib_name | innodb_engine.so| | daemon_memcached_engine_lib_path | | | daemon_memcached_option | |#'-P 11222' 포트 변경 가능. | daemon_memcached_r_batch_size | 1048576 |# 읽기 작업 일괄 처리 크기가 임계값 | daemon_memcached_w_batch_size | 32 |# 일괄 처리 커밋 크기 임계값 +----------------------------------+-----------------+ mysql&amp;gt; use innodb_memcache; mysql&amp;gt; show tables; +---------------------------+ | Tables_in_innodb_memcache | +---------------------------+ | cache_policies | | config_options | | containers | # memcached 연동 데이터베이스 및 테이블 정보 기술됨 +---------------------------+ </span>

7) 테스트 테이블 확인

<span style="font-family: Gulim, 굴림, AppleGothic, sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">mysql -uroot ?p mysql&amp;gt; use innodb_memcache; mysql&amp;gt; use test mysql&amp;gt; select * from demo_test; # 테스트 테이블 +----+--------------+------+------+------+ | c1 | c2 | c3 | c4 | C5 | +----+--------------+------+------+------+ | AA | HELLO, HELLO | 8 | 0 | 0 | +----+--------------+------+------+------+ mysql&amp;gt; desc demo_test; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | c1 | varchar(32) | NO | PRI | | | | c2 | varchar(1024) | YES | | NULL | | | c3 | int(11) | YES | | NULL | | | c4 | bigint(20) unsigned | YES | | NULL | | | C5 | int(11) | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ </span>

4. 성능 비교

1) 클라이언트/서버 시스템 사양 및 소프트웨어 설정 값

 

1)2) Write 성능

Write(Insert)는 MySQL과 HandlerSocket은 user 테이블에 id 값을 유니크하게 보장하고, 한 레코드의 사이즈는 1024byte로 구성했다. 클라는 10,000을 설정하고 테스트를 진행했다. Memcached는 수행 방식은 HandlerSocket 방식과 같으나 테이블은 기 생성된 demo_test 테이블에 key/values 형태로 Memcached 프로토콜을 통해 테스트를 진행한 결과이다. 

 

HandlerSocket>Memcached>MySQL 순으로 Insert 성능이 진단됐고, MySQL 기준으로 볼 때 HandlerSocket은 1.6배, Memcached는 1.4배 정도의 성능 개선을 보였다. Insert 성능은 InnoDB에서 데이터를 받아 처리해야 하는 구조이기 ？문에 Insert 시 발생하는 I/O에 영향을 많이 받아 기존 MySQL과 성능 차가 그리 많이 나지는 않았다. 성능 테스트 기간에 데이터 정합성 불일치 건은 없었다. 

1)3) Read 성능

Read 테스트는 Write와 진행 방법은 같다. 조회 키로는 HahdlerSocket과 MySQL은 user 테이블의 id를 조회 조건으로 걸고 유니크하게 조회했으며, Memcached도 마찬가지로 demo_test 테이블의 c1 필드(키)를 유니크하게 바꿔가면서 테스트를 진행한 결과이다. 

 

HandlerSocket>Memcached>MySQL 순으로 조회 성능이 진단됐고 MySQL 기준으로 볼 때 평균적으로는 HandlerSocket은 3.4배, Memcached는 2.8배 정도의 성능 개선을 보였고, Thread 500개일 경우 HandlerSocket은 8.2배, Memcached는 7.5배의 성능 향상을 보였다. Read 성능은 확실히 MySQL보다는 많이 개선된 결과를 볼 수 있었다. 

5. 결론

Write의 성능은 MySQL의 InnoDB 쓰기 성능에 영향을 받기 때문에 HandlerSocket, Memcached 가 각각 1.6배와 1.4배의 큰 성능 개선을 보여주지 못했다. NoSQL 기능으로서 MySQL의 데이터 정합성에 만족해야 했다. 하지만, Read 성능은 DeNA의 개발자가 테스트한 750,000 QPS 수준은 아니지만 최대 HandlerSocket은 8.2배, Memcached는 7.5배 정도의 성능 개선을 보여줬다. 

이 같은 결과로 유추해 볼 때 하드웨어, 디스크, 메모리 DB, MySQL을 튜닝한다면 성능 개선 효과가 기대되며, 조회가 많은 서비스에서 활용하면 더 좋은 결과가 나올 것으로 판단된다. 

HandlerSocket은 MySQL의 테이블 그대로 데이터 활용이 가능한 반면, Memcached는 key/value의 행태로 데이터가 관리돼야 하는 차이는 있다. Memcached는 아직 연구 버전 단계이나 HandlerSocket은 DeNA에서 상용 환경에서 사용한 사례가 있다는 것 등으로 볼 때 필자는 HandlerSocket을 활용하는 방안이 더 매력적으로 보인다. 하지만, MySQL 서버에 영향이 적더라도 실제 수많은 테스트 케이스를 통해 안정성을 확인하고 Plugin의 내부 소스를 파악해 운영 중에 발생할 수 있는 예외 상황을 즉시 제거할 수 있는 준비가 필요하다는 점을 강조하고 싶다. 다음 연재에서는 스케이일 아웃 측면에서 NoSQL로서 MySQL의 특성을 살펴보겠다.

출처 : 한국데이터베이스진흥원

제공 : DB포탈사이트 DBguide.net