sysbench

sysbench

CPU, I/O OLTP(Online transaction processing,) .

Sysbench 2004 0.4.12 2016 LUA .( v1.0.25)

1.X 6

Usage

mysql vs mariadb

mariadb vs pgsql

하드웨어 성능측정

cloud vs baremetal

Galera Cluster Test

설정값에 따른 성능 측정

Installation

curl -s
https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

Properties
--threads=N //
--events=N //
--time=N //
--warmup-time=N //
--rate=N //
--report-interval=N //
--rand-type=STRING //{uniform, gaussian, special, pareto,
zipfian} to use by default [special] sysbench --help
--db-ps-mode=STRING // prepare statement

Execution
-rwxr-xr-x 1 root root 1452 7 4 05:07 bulk_insert.lua
-rwxr-xr-x 1 root root 14369 7 4 05:07 oltp_common.lua
-rwxr-xr-x 1 root root 1290 7 4 05:07 oltp_delete.lua
-rwxr-xr-x 1 root root 2415 7 4 05:07 oltp_insert.lua
-rwxr-xr-x 1 root root 1265 7 4 05:07 oltp_point_select.lua
-rwxr-xr-x 1 root root 1649 7 4 05:07 oltp_read_only.lua
-rwxr-xr-x 1 root root 1824 7 4 05:07 oltp_read_write.lua
-rwxr-xr-x 1 root root 1118 7 4 05:07 oltp_update_index.lua
-rwxr-xr-x 1 root root 1127 7 4 05:07 oltp_update_non_index.lua
-rwxr-xr-x 1 root root 1440 7 4 05:07 oltp_write_only.lua
-rwxr-xr-x 1 root root 1919 7 4 05:07 select_random_points.lua
-rwxr-xr-x 1 root root 2118 7 4 05:07 select_random_ranges.lua

각각 용도에 맞게 lua sciprt를 실행하면 된다.

read_only는 말그대로 읽기전용 테스트 read_write는 읽기 쓰기 테스트 이다.

여기서는 읽기전용 테스트로 진행

prepare
sysbench /usr/share/sysbench/oltp_read_only.lua --threads=16
--mysql-socket=/home/db/data/mysql.sock --mysql-user=root
--mysql-password=<pass>--mysql-port=3306 --tables=10 --table-size=1000000
prepare

생성된 결과

[asd@erea sbtest]# ls -al
2457772
drwx------ 2 mysql mysql 4096 12 3 11:03 .
drwxrwxrwx 15 mysql mysql 4096 12 3 09:58 ..
-rw-rw---- 1 mysql mysql 61 12 3 09:58 db.opt
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest1.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest1.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest10.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest10.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:01 sbtest2.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:02 sbtest2.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest3.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest3.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest4.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest4.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest5.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest5.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest6.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest6.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:03 sbtest7.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest7.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:01 sbtest8.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:02 sbtest8.ibd
-rw-rw---- 1 mysql mysql 8632 12 3 11:02 sbtest9.frm
-rw-rw---- 1 mysql mysql 251658240 12 3 11:03 sbtest9.ibd
[root@erea sbtest]# du -sh
2.4G
run
sysbench /usr/share/sysbench/oltp_read_only.lua --threads=16
--mysql-socket=/home/db/data/mysql.sock --mysql-user=root
--mysql-password=<pass> --mysql-port=3306 --tables=10 --table-size=1000000
prepare


result
Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 6121080
write: 0
other: 1224216
total: 7345296
transactions: 612108 (2040.29 per sec.)
queries: 7345296 (24483.47 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0081s
total number of events: 612108
Latency (ms):
min: 0.53
avg: 7.84
max: 119.49
95th percentile: 14.46
sum: 4799088.32
Threads fairness:
events (avg/stddev): 38256.7500/109.47
execution time (avg/stddev): 299.9430/0.00


clean
sysbench /usr/share/sysbench/oltp_read_only.lua --threads=16 --events=0
--time=300 --mysql-user=root --mysql-password=<pass> --mysql-port=3306
--mysql-socket=/home/db/data/mysql.sock --tables=10 --table-size=1000000
--range_selects=off --db-ps-mode=disable --report-interval=1 cleanup



write: 0
other: 250204
total: 1501224
transactions: 125102 (416.85 per sec.)
queries: 1501224 (5002.25 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.1076s
total number of events: 125102
Latency (ms):
min: 23.09
avg: 38.37
max: 919.10
95th percentile: 48.34
sum: 4800181.00
Threads fairness:
events (avg/stddev): 7818.8750/687.23
execution time (avg/stddev): 300.0113/0.02
localhost(mariadb)
mariadb-result
Running the test with following options:
Number of threads: 16
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 6121080
write: 0
other: 1224216
total: 7345296
transactions: 612108 (2040.29 per sec.)
queries: 7345296 (24483.47 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0081s
total number of events: 612108
Latency (ms):
min: 0.53
avg: 7.84
max: 119.49
95th percentile: 14.46
sum: 4799088.32
Threads fairness:
events (avg/stddev): 38256.7500/109.47
execution time (avg/stddev): 299.9430/0.00
localhost(pgsql 9.4)
pgsql-result
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 3295210
write: 0
other: 659042
total: 3954252
transactions: 329521 (1098.26 per sec.)
queries: 3954252 (13179.14 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0361s
total number of events: 329521
Latency (ms):
min: 1.54
avg: 14.57
max: 402.52
95th percentile: 21.11
sum: 4799500.02
Threads fairness:
events (avg/stddev): 20595.0625/95.41
execution time (avg/stddev): 299.9688/0.01
localhost(pgsql 11.1)
pgsql-result(11.1)
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 3254640
write: 0
other: 650928
total: 3905568
transactions: 325464 (1084.77 per sec.)
queries: 3905568 (13017.23 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0280s
total number of events: 325464
Latency (ms):
min: 1.67
avg: 14.75
max: 397.47
95th percentile: 21.50
sum: 4799432.55
Threads fairness:
events (avg/stddev): 20341.5000/111.49
execution time (avg/stddev): 299.9645/0.01

Reference

https://github.com/akopytov/sysbench

https://severalnines.com/blog/how-benchmark-performance-mysql-mariadb-using-sysbench

Conclusion

각 기종별 하드웨어별 성능테스트를 하는데 정확한 결과를 낼수 있다.

또한 여러모로 구성에 따라 유용하게 쓸수 있고 쉽게 적용할 수 있기 때문에 좋은 오픈소스라고 볼수 있다.