erea

sysbench 본문

dev/server

sysbench

erea 2019. 1. 9. 00:38

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

?
1
2
3
curl -s
https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
?
1
2
3
4
5
6
7
8
9
10
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


?
1
2
3
4
5
6
7
8
9
10
11
12
13
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는 읽기 쓰기 테스트 이다.

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

?
1
2
3
4
5
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

생성된 결과

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
<p>[asd@erea sbtest]# ls -al</p>
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

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

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

'dev > server' 카테고리의 다른 글

High Availability Redis Archtecturest  (0) 2019.01.08
'dev/server' Related Articles more
Comments