mysql 벤치 마크 툴 sysbench

sysbench는 CPU, memory, file I/O, mutex performance, MySQL 등 다양한 테스트가 가능 하고, 많은 레퍼런스 보유하고 있어 성능 진단에 유용하게 사용 된다.
sysbench를 이용해서 mysql 성능을 측정해보자

설치는 yum으로 간단하게 할 수 있다.

> sudo yum -y install sysbench

mysql@bench-test-utf8:/data/mytmp 11:28:09> sudo yum -y install sysbench
Loaded plugins: fastestmirror, langpacks, versionlock
Determining fastest mirrors
EPEL7                                                                                                                                    | 4.7 kB  00:00:00
base                                                                                                                                     | 3.6 kB  00:00:00
centosplus                                                                                                                               | 2.9 kB  00:00:00
extras                                                                                                                                   | 2.9 kB  00:00:00
update                                                                                                                                   | 2.9 kB  00:00:00
(1/6): EPEL7/7/x86_64/group_gz                                                                                                           |  95 kB  00:00:00
(2/6): EPEL7/7/x86_64/updateinfo                                                                                                         | 1.0 MB  00:00:00
(3/6): extras/7/x86_64/primary_db                                                                                                        | 165 kB  00:00:00
(4/6): centosplus/7/x86_64/primary_db                                                                                                    | 3.0 MB  00:00:00
(5/6): EPEL7/7/x86_64/primary_db                                                                                                         | 6.8 MB  00:00:00
(6/6): update/7/x86_64/primary_db                                                                                                        | 7.6 MB  00:00:00
Resolving Dependencies
--> Running transaction check
---> Package sysbench.x86_64 0:1.0.17-2.el7 will be installed
--> Processing Dependency: libck.so.0()(64bit) for package: sysbench-1.0.17-2.el7.x86_64
--> Processing Dependency: libluajit-5.1.so.2()(64bit) for package: sysbench-1.0.17-2.el7.x86_64
--> Running transaction check
---> Package ck.x86_64 0:0.5.2-2.el7 will be installed
---> Package luajit.x86_64 0:2.0.4-3.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================
 Package                               Arch                                Version                                     Repository                          Size
================================================================================================================================================================
Installing:
 sysbench                              x86_64                              1.0.17-2.el7                                EPEL7                              152 k
Installing for dependencies:
 ck                                    x86_64                              0.5.2-2.el7                                 EPEL7                               26 k
 luajit                                x86_64                              2.0.4-3.el7                                 EPEL7                              343 k

Transaction Summary
================================================================================================================================================================
Install  1 Package (+2 Dependent packages)

Total download size: 521 k
Installed size: 1.7 M
Downloading packages:
(1/3): ck-0.5.2-2.el7.x86_64.rpm                                                                                                         |  26 kB  00:00:00
(2/3): luajit-2.0.4-3.el7.x86_64.rpm                                                                                                     | 343 kB  00:00:00
(3/3): sysbench-1.0.17-2.el7.x86_64.rpm                                                                                                  | 152 kB  00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                           2.4 MB/s | 521 kB  00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : luajit-2.0.4-3.el7.x86_64                                                                                                                    1/3
  Installing : ck-0.5.2-2.el7.x86_64                                                                                                                        2/3
  Installing : sysbench-1.0.17-2.el7.x86_64                                                                                                                 3/3
  Verifying  : ck-0.5.2-2.el7.x86_64                                                                                                                        1/3
  Verifying  : sysbench-1.0.17-2.el7.x86_64                                                                                                                 2/3
  Verifying  : luajit-2.0.4-3.el7.x86_64                                                                                                                    3/3

Installed:
  sysbench.x86_64 0:1.0.17-2.el7

Dependency Installed:
  ck.x86_64 0:0.5.2-2.el7                                                      luajit.x86_64 0:2.0.4-3.el7

Complete!
mysql@bench-test-utf8:/data/mytmp 11:28:32>

설치가 완료 되면 /usr/share/sysbench 경로에 벤치 마크 예제들이 생성 된다.
oltp_read_write.lua 파일을 이용해서 mysql DB의 oltp 성능을 측정해보자.

진행은 다음 순서로 한다.
1. prepare : 초기 기반 테이블 및 데이터을 설치
2. run : 수행 후 분석 결과 확인
3. cleanup : 설치한 모든 내용 제거

옵션은 다음과 같다.
--max-requests=[쿼리 요청 개수] 
--max-time=[테스트 시간]
--mysql-host=[호스트명] 
--mysql-db=[테스트 데이터베이스명] 
--mysql-user=[DB USER] 
--mysql-password=[DB USER 비밀번호] 
--mysql-table-engine=[테이블 엔진 종류] 
--mysql-socket=[MySQL 소켓 파일 경로] 
--mysql-port=[MySQL-포트-번호] 
--oltp-test-mode=[simple|complex|nontrx] 
--oltp-read-only=[on|off] 
--num-threads=[동시 스레드 개수]
--table_size=[데이터 건수]
--tables=[테이블 개수]

다음과 같이 실행 시간 기반으로 요청 수에 관계 없이 240초 동안 실행하고 결과를 출력한다.

sysbench --time=240 --max-requests=0 /usr/share/sysbench/oltp_read_write.lua \
--table_size=10000000 --tables=30 --report-interval=1 \
--mysql-host=localhost --mysql-port=3306 \
--db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \
--mysql-db=sysbench --mysql-socket=/tmp/mysql.sock --num-threads=110 \
prepare

sysbench --time=240 --max-requests=0 /usr/share/sysbench/oltp_read_write.lua \
--table_size=10000000000 --tables=15 --report-interval=1 \
--mysql-host=localhost --mysql-port=3306 \
--db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \
--mysql-db=sysbench --mysql-socket=/tmp/mysql.sock --num-threads=110 \
run

SQL statistics:
    queries performed:
        read:                            8899310
        write:                           641866
        other:                           3172110
        total:                           12713286
    transactions:                        635658 (2647.55 per sec.)
    queries:                             12713286 (52951.61 per sec.)
    ignored errors:                      7      (0.03 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          240.0904s
    total number of events:              635658

Latency (ms):
         min:                                    1.78
         avg:                                   41.53
         max:                                  379.03
         95th percentile:                      106.75
         sum:                             26396240.17

Threads fairness:
    events (avg/stddev):           5778.7091/210.35
    execution time (avg/stddev):   239.9658/0.03

본 테스트 시스템에서는 threads가 130이 넘으면 에러가 발생하고, table_size가 1000000000000 이상이면 동작하지 않으므로 적절한 값으로 조절하여 수행 하면 된다.

다음 테스트는 time 240으로 table_size 와 threads에 변화에 따른 tps 수치 변화를 표로 만들어 보았다.

	threads=50	threads=120
table_size=1000000	transactions:  466736 (1944.34 per sec.) queries:      9358012 (38983.83 per sec.)	transactions: 402868 (1677.81 per sec.) queries:    8057378 (33556.21 per sec.)
table_size=10000000000	transactions: 639694 (2664.91 per sec.) queries:   12793880 (53298.20 per sec.)	transactions: 614487 (2559.47 per sec.) queries:    12289812 (51189.78 per sec.)

threads 값 보다는 table_size가 tps에 더 많은 영향을 미치고 오히려 threads가 높으면 Latency가 높아서 성능이 더 떨어지는 상황이 발생 하였다.

테스트시 수행되는 SQL의 패턴은 다음과 같다.

BEGIN; SELECT c FROM sbtest WHERE id=N; SELECT c FROM sbtest WHERE id BETWEEN N AND M; SELECT SUM(K) FROM sbtest WHERE id BETWEEN N and M; SELECT c FROM sbtest WHERE id between N and M ORDER BY c; SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c; UPDATE sbtest SET k=k+1 WHERE id=N; UPDATE sbtest SET c=N WHERE id=M; DELETE FROM sbtest WHERE id=N; INSERT INTO sbtest VALUES (...); COMMIT;