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
