db에 부하 테스트 할 수 있는 tool 인 sysbench 를 이용하여 
mariadb thread pool 사용 / 미사용 시 어떤 설정이 더 성능이 좋은 지 테스트한 결과입니다.
test 환경 상 cpu 1개인 서버에서 test 했으며 서버 환경에 따라 결과가 다를 수 있습니다.
본 테스트에서는 thread pool 사용이 동일 시간 대비 더 많은 트랜잭션을 수행한 것으로 나왔습니다.

 

-- sysbench 설치

 
wget https://github.com/akopytov/sysbench/archive/master.zip
=> sysbench 다운로드 후 unzip
 
[ip-172-31-13-44:root]/engn001/sysbench/sysbench-master] ls
autogen.sh  configure.ac  install-sh   missing        rpm      src
ChangeLog   COPYING       m4           mkinstalldirs  scripts  tests
config      debian        Makefile.am  README.md      snap     third_party
 
[TESTSERVER]/engn001/sysbench/sysbench-master] ./autogen.sh
[TESTSERVER]/engn001/sysbench/sysbench-master] ./configure --prefix=/engn001/testuser/sysbench/sysbench-master --with-mysql-includes=/usr/include/mysql --with-mysql-libs=/usr/lib64/mysql
[TESTSERVER]/engn001/sysbench/sysbench-master] make
[TESTSERVER]/engn001/sysbench/sysbench-master] make install
# configure 옵션 설명
./configure --prefix={sysbench Home 위치} --with-mysql-includes={MySQL include 디렉토리 위치} --with-mysql-libs={MySQL lib 디렉토리 위치}
 
[TESTSERVER]/engn001/sysbench/sysbench-master] ./bin/sysbench --version
sysbench 1.1.0
=> 정상 설치 확인

 

-- sysbench 를 통해 테스트 환경 준비

# 준비
/engn001/testuser/sysbench/sysbench-master/bin/sysbench --mysql-host=localhost --mysql-port=3310  --mysql-socket=/engn001/testuser/NEWDB/mysql/mysql.sock --mysql-user=sysbench --mysql-password=test --mysql-db=sysbench --threads=10 --table-size=5000000 --tables=10 /engn001/testuser/sysbench/sysbench-master/share/sysbench/oltp_read_write.lua prepare
 
=> 각각  5백만 건의 데이터를 갖고 있는 테이블 10개 생성
 
# 성능테스트 실행
/engn001/sysbench/sysbench-master/bin --mysql-host=localhost --mysql-port=3310 --mysql-user=sysbench --mysql-password=test --mysql-db=sysbench --threads=10 --table-size=5000000 --tables=10 /engn001/testuser/sysbench/sysbench-master/share/sysbench/oltp_read_write.lua run
 
=> 위에서 생성한 10개 테이블에 대해 10개의 thread로 read / write 트랜잭션 수행
 
# 테스트 데이터 삭제
/engn001/sysbench/sysbench-master/bin --mysql-host=localhost --mysql-port=3310 --mysql-user=sysbench --mysql-password=test --mysql-db=sysbench --threads=10 --table-size=5000000 --tables=10 /engn001/testuser/sysbench/sysbench-master/share/sysbench/oltp_read_write.lua cleanup
 

-- test 시나리오

 
1) thread pool 사용   / 사용 thread 개수 10개 , 20개 , 40개  ( thread pool 의 thread 미리 생성필요)
 
mysql> show variables like 'thread_handling';
+-----------------+-----------------+
| Variable_name   | Value           |
+-----------------+-----------------+
| thread_handling | pool-of-threads |
+-----------------+-----------------+
 
=> pool of threads : thread pool 사용
 
mysql> show variables like 'thread_pool_max_threads';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| thread_pool_max_threads | 65536    |
+-------------------------+-------+

 

-- thread 10개 

 
Running the test with following options:
Number of threads: 10
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            79170
        write:                           22620
        other:                           11310
        total:                           113100
    transactions:                        5655   (188.17 per sec.)
    queries:                             113100 (3763.39 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      188.1696
    time elapsed:                        30.0527s
    total number of events:              5655
 
Latency (ms):
         min:                                   31.06
         avg:                                   53.10
         max:                                  165.04
         95th percentile:                       71.83
         sum:                               300293.05
 
Threads fairness:
    events (avg/stddev):           565.5000/2.87
    execution time (avg/stddev):   30.0293/0.01
 
 
--  thread 20개
 
Running the test with following options:
Number of threads: 20
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            77602
        write:                           22172
        other:                           11086
        total:                           110860
    transactions:                        5543   (184.35 per sec.)
    queries:                             110860 (3686.91 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      184.3455
    time elapsed:                        30.0685s
    total number of events:              5543
 
Latency (ms):
         min:                                   44.84
         avg:                                  108.36
         max:                                  209.92
         95th percentile:                      150.29
         sum:                               600645.89
 
Threads fairness:
    events (avg/stddev):           277.1500/2.99
    execution time (avg/stddev):   30.0323/0.02
 
 
-- thread 40개 
 
 
Running the test with following options:
Number of threads: 40
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            76076
        write:                           21736
        other:                           10868
        total:                           108680
    transactions:                        5434   (179.74 per sec.)
    queries:                             108680 (3594.78 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      179.7390
    time elapsed:                        30.2327s
    total number of events:              5434
 
Latency (ms):
         min:                                   90.79
         avg:                                  221.75
         max:                                  436.03
         95th percentile:                      303.33
         sum:                              1205005.96
 
Threads fairness:
    events (avg/stddev):           135.8500/1.92
    execution time (avg/stddev):   30.1251/0.07
 
 
 

2) thread pool 미사용 / thread 개수 10개, 20개,40개  

 
mysql> show variables like 'thread_handling';
+-----------------+---------------------------+
| Variable_name   | Value                     |
+-----------------+---------------------------+
| thread_handling | one-thread-per-connection |
+-----------------+---------------------------+
1 row in set (0.00 sec)
 
=> thread pool 미사용 설정, connection 붙은 수 만큼 thread 생성됨
 
 
-- thread 10개 
 
 
Running the test with following options:
Number of threads: 10
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            88830
        write:                           25380
        other:                           12690
        total:                           126900
    transactions:                        6345   (211.31 per sec.)
    queries:                             126900 (4226.13 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      211.3065
    time elapsed:                        30.0275s
    total number of events:              6345
 
Latency (ms):
         min:                                   24.60
         avg:                                   47.30
         max:                                   75.17
         95th percentile:                       56.84
         sum:                               300102.37
 
Threads fairness:
    events (avg/stddev):           634.5000/1.36
    execution time (avg/stddev):   30.0102/0.01
 
 
 
 
-- thread 20개
 
 
Running the test with following options:
Number of threads: 20
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            84868
        write:                           24248
        other:                           12124
        total:                           121240
    transactions:                        6062   (201.72 per sec.)
    queries:                             121240 (4034.35 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      201.7177
    time elapsed:                        30.0519s
    total number of events:              6062
 
Latency (ms):
         min:                                   52.44
         avg:                                   99.04
         max:                                  156.34
         95th percentile:                      112.67
         sum:                               600368.62
 
Threads fairness:
    events (avg/stddev):           303.1000/1.34
    execution time (avg/stddev):   30.0184/0.02
 
 
-- thread 40개
 
 
Running the test with following options:
Number of threads: 40
Initializing random number generator from current time
 
 
Initializing worker threads...
 
Threads started!
 
SQL statistics:
    queries performed:
        read:                            81760
        write:                           23360
        other:                           11680
        total:                           116800
    transactions:                        5840   (193.92 per sec.)
    queries:                             116800 (3878.40 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
Throughput:
    events/s (eps):                      193.9198
    time elapsed:                        30.1155s
    total number of events:              5840
 
Latency (ms):
         min:                                  112.60
         avg:                                  205.92
         max:                                  327.84
         95th percentile:                      231.53
         sum:                              1202592.04
 
Threads fairness:
    events (avg/stddev):           146.0000/1.02
    execution time (avg/stddev):   30.0648/0.04