HandlerSocket plugin for MySQL 정리

 업무상 프로젝트에서 사용했던 MySQL 용 plugin "HandlerSocket" 을 정리하고자 한다.

 HandlerSocket 에 대한 내용은 HandlerSocket 을 처음 소개했던 "요시노리 마츠노부" 씨의 블로그 글(http://yoshinorimatsunobu.blogspot.kr/search/label/handlersocket)을 참조하자.

 

 간단하게 특징을 설명하자면,

- DeNA 에서 개발

- SQL 문 파싱단계를 없어서 데이타 처리가 빠르다.

- 여러 언어 지원(C++, Perl, PHP, Java, Ruby, Python, javascript)

- 원격(remote)에서도 TCP  소켓통신으로 데이터를 insert 할 수 있다.

- TCP 소켓 통신할때 인증 절차가 없어서, 포트 번호와 프로토콜만 알면 엉뚱한 데이터를 전송할 수 있다.

1. 다운로드

https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL 에서 소스 코드를 다운받을 수 있다.

# git clone https://github.com/DeNADev/HandlerSocket-Plugin-for-MySQL

2. 설치(HandlerSocket-Plugin-for-MySQL/docs-en/protocol.en.txt 참조)

handlersocket plugin 을 컴파일하기 위해서는 mysql 소스와 바이러리 실행파일이 필요하다. mysql 을 컴파일할 필요는 없고, 단지 소스파일이 어딘가에 있으면 된다.  mysql 사이트에서 소스코드와 설치 파일을 다운받자.

- configure 및 컴파일, install

# configure --with-mysql-source=/home/test/down/mysql-5.5.27 --with-mysql-bindir=/usr/bin/mysql --with-mysql-plugindir=/usr/lib64/mysql/plugin

# make; make install

# ls /usr/lib64/mysql/plugin/handler*

handlersocket.a

handlersocket.la

handlersocket.so

- handlersocket 플러긴 install

# mysql -uroot -p

mysql> INSTALL PLUGIN HandlerSocket SONAME 'handlersocket.so';

mysql> SHOW PLUGINS;

+---------------+----------+----------------+------------------+---------+

| Name          | Status   | Type           | Library          | License |

+---------------+----------+----------------+------------------+---------+

| binlog        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| partition     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| ARCHIVE       | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| BLACKHOLE     | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| CSV           | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| FEDERATED     | DISABLED | STORAGE ENGINE | NULL             | GPL     |

| MEMORY        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| InnoDB        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| MyISAM        | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| MRG_MYISAM    | ACTIVE   | STORAGE ENGINE | NULL             | GPL     |

| handlersocket | ACTIVE   | DAEMON         | handlersocket.so | BSD     |

+---------------+----------+----------------+------------------+---------+

* show plugins 명령어는 mysql 5.1 부터 추가되었다.

- mysql 설정 파일(/etc/my.cnf)에 추가할 내용

# handlersocket

loose_handlersocket_port = 9998

loose_handlersocket_port_wr = 9999

loose_handlersocket_threads = 16

loose_handlersocket_threads_wr = 1

handlersocket_readsize = 8192

open_files_limit = 65535

handlersocket_port : read 요청 listen 포트 번호

handlersocket_port_wr : writer 요청 listen 포트 번호

handlersocket_threads : read 요청 처리 스레드 갯수

handlersocket_threads_wr : wirte 요청 처리 스레드 갯수

handlersocket_readsize : request buffer 크기

open_files_limit : 동시 접속 수

※ HandlerSocket-Plugin-for-MySQL/docs-en/docs/en/configuration-options.en.txt (:다운받은 소스위치에서 경로)를 참조하면 여러 설정값들을 볼 수 있다.

- mysql 실행

plugin 설정을 추가하고 mysql 서버를 재시작한다. 9998, 9999 포트가 바인딩되었는지 확인한다.

3. 테스트 및 적용

HandlerSocket-Plugin-for-MySQL/docs-en/protocol.en.txt 를 참조해서 데이타 insert 를 위한 프로토콜을 볼 수 있다. 분석한 내용으로 직접 데이타를 insert 하는 예제를 작성해 보았다.

- 데이타 insert 예제 : client 디렉토리에 있는 hstest.cpp 를 분석하다보니, insert 를 하기 위한 절차는 "handler 바인딩 포트 연결" - "open index" - "insert data" 세 가지라서 간단하게 C 코드로 작성했다.

#define IP_SZ 16

bool_t is_quit;

main(int argc, char *argv[])

{

    int sd;

    char serv_ip[IP_SZ];

    u_short serv_port = 9999;

    char buf[8192];

    char recvbuf[80];

    int ret = -1;

    int i = 0;

    is_quit = FALSE;

    strcpy(serv_ip, SVR_IP);

    while (!is_quit) {

        if ((sd = my_connect(serv_ip, serv_port)) < 0) {

            printf("Can't connect to server(%s:%d)", serv_ip, serv_port);

            sd = 0;

            sleep(10);

            continue;

        }

printf("connected.. \n");

        strcpy(buf, "P");

        strcat(buf, "\t");

        strcat(buf, "1");

        strcat(buf, "\t");

        strcat(buf, "my_db");        // db name

        strcat(buf, "\t");

        strcat(buf, "t1");     // table name

        strcat(buf, "\t");        

        strcat(buf, "");        // index_name

        strcat(buf, "\t");

        strcat(buf, "name,f_name,l_name");        // column name

        strcat(buf, "\n");

printf("send.. \n");

        ret = send(sd, buf, strlen(buf), 0);

        if (ret < 0)

        {

            printf("Send Error!\n");

            is_quit = TRUE;

            continue;

        }

printf("recv.. \n");

        ret = recv(sd, recvbuf, sizeof(recvbuf), 0);

        if (ret > 0)

        {

            printf("Recv=(%s)\n", recvbuf);

        }

        else

        {

            printf("recv ret=(%d)\n", ret);

        }

        for (i = 0; i < 5; i++)

        {

            sprintf(buf, "1\t+\t3\tname\tname_first\tname_last\n");

            ret = send(sd, buf, strlen(buf), 0);

            if (ret < 0)

            printf("Send Error!\n");

            is_quit = TRUE;

            continue;

        }

printf("recv.. \n");

        ret = recv(sd, recvbuf, sizeof(recvbuf), 0);

        if (ret > 0)

        {

            printf("Recv=(%s)\n", recvbuf);

        }

        else

        {

            printf("recv ret=(%d)\n", ret);

        }

        for (i = 0; i < 5; i++)

        {

            sprintf(buf, "1\t+\t3\tname\tname_first\tname_last\n");

            ret = send(sd, buf, strlen(buf), 0);

            if (ret < 0)

            {

                printf("Send Error!\n");

                is_quit = TRUE;

                continue;

            }

            ret = recv(sd, recvbuf, sizeof(recvbuf), 0);

            if (ret > 0)

            {

                printf("Recv=(%s)\n", recvbuf);

            }

            else

            {

                printf("recv ret=(%d)\n", ret);

            }

        }

        sleep(10);

    }

    close(sd);

}

- 테이블(t1) scheme

CREATE TABLE `t1` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(45) NOT NULL DEFAULT '',

  `f_name` varchar(45) NOT NULL DEFAULT '',

  `l_name` varchar(45) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 후기

- 소스에 같이 제공되는 client 디렉토리의 hstest.pl 예제 소스를 수정해서, 10억건의 데이터를 "insert" 로 하는 스크립트와 "hsinsert" 로 insert 하는 스크립트를 따로 작성해서 완료 되는 시간을 비교해봤을때 두배이상 차이가 나지는 않았다. 물론 사용한 하드웨어와 DB 튜닝값들을 완벽하게 맞추지는 못했지만 DB SQL 문 파싱하는 단계를 없앴다고 해서 데이타 insert 속도가 월등하게 빨라지지는 않는거 같다. bulk insert 와 비교하는거 자체가 무리긴 하지만...

 

- 그리고, 가장 큰 문제라고 생각하는 점이 데이터를 구분하는 구분자가 기본적으로 탭문자('\t') 라서 특정 컬럼에 탭문자가 들어가면 그 뒤로 부터는 밀리는 사태가 발생한다. 소스코드를 확인해보니 탭문자를 하드코딩 해버려서 수정하려고 해도 애매하다.

- 항상 도구는 적용되는 환경에 맞춰서 사용해야지, "소를 잡는 칼이 될수도 있고 아니면 과일깎는 과도가 될 수도 있다"

참고 사이트

http://ronaldbradford.com/blog/mysql-handlersocket-under-ubuntu-2010-11-05/

http://dev.mysql.com/doc/refman/5.5/en/install-plugin.html