centos7.5下对mysql5.6基准测试



一、测试环境准备

  • 操作系统:centos7.5

  • 处理器:Intel 8核 Current Speed: 2000 MHz, Max Speed: 2000 MHz,三级缓存

  • 内存:16G

  • 磁盘:100G SSD

  • 数据库:mysql 5.6.28

  • sysbench:1.0.18


二、测试工具

1、sysbench

安装文档: https://github.com/akopytov/sysbench#installing-from-binary-packages

1
2
3
# centos7.5使用官方镜像安装
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
11
12
13
14
15
16
17
18
19
[root@10-10-135-172 benchmark]# sysbench --help

[root@10-10-135-172 benchmark]# sysbench fileio help
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)

fileio options:
--file-num=N number of files to create [128]
--file-block-size=N block size to use in all IO operations [16384]
--file-total-size=SIZE total size of files to create [2G]
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
--file-io-mode=STRING file operations mode {sync,async,mmap} [sync]
--file-async-backlog=N number of asynchronous operatons to queue per thread [128]
--file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all[=on|off] do fsync() after each write operation [off]
--file-fsync-end[=on|off] do fsync() at the end of test [on]
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]
--file-rw-ratio=N reads/writes ratio for combined test [1.5]

2、tpcc-mysql

github: https://github.com/Percona-Lab/tpcc-mysql

1
2
3
4
5
6
7
mkdir -p /var/lib/mysql/benchmark
cd /var/lib/mysql/benchmark
wget https://github.com/Percona-Lab/tpcc-mysql/archive/master.zip -O tpcc-mysql.zip
unzip -d ./ tpcc-mysql.zip
cd /var/lib/mysql/benchmark/tpcc-mysql-master/src
yum install mysql-devel
make

编译完成后,生成 tpcc_load tpcc_start:


三、测试目标

  • 处理器性能

  • 内存性能

  • 磁盘顺序读写,随机读写性能

  • mysql 吞吐量,响应时间


四、测试步骤

1、准备获取系统性能和状态的脚本

vi /root/benchmark/1-collect-system-status.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/bin/sh

mkdir -p status_data

INTERVAL=5
PREFIX=./status_data/$INTERVAL-sec-status
RUNFILE=/root/benchmark/running

mysql -uroot -p123456 -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
tfile=$(date +%F_%I)
sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
sleep $sleep
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)"
echo "$ts $loadavg" >> $PREFIX-${tfile}-status
mysql -uroot -p123456 -e 'SHOW GLOBAL STATUS' >> $PREFIX-${tfile}-status &
echo "$ts $loadavg" >> $PREFIX-${tfile}-innodbstatus
mysql -uroot -p123456 -e 'SHOW GLOBAL STATUS\G' >> $PREFIX-${tfile}-innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${tfile}-processlist
mysql -uroot -p123456 -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${tfile}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.

分析收集到的数据

vi /root/benchmark/2-system-status-analyzer.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#!/bin/bash

awk '
BEGIN {
printf "#ts date time load QPS";
fmt = " %.2f";
}
/^TS/ {
ts = substr($2, 1, index($2, ".") - 1);
load = NF -2;
diff = ts - prev_ts;
prev_ts = ts;
printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load) - 1);
}
/Queries/ {
printf fmt, ($2-Queries)/diff;
Queries=$2
}
' "$@"

用法示例

1
sh 2-system-status-analyzer.sh status_data/5-sec-status-2019-11-19_10-status

2、使用sysbench服务器性能测试

1)、cpu测试

计算20000个素数。8核cpu,分别测试 1,4,8,12 线程时的 CPU表现

1
sysbench --test=cpu --threads=1 --cpu-max-prime=20000 run

1
sysbench --test=cpu --threads=4 --cpu-max-prime=20000 run

1
sysbench --test=cpu --threads=8 --cpu-max-prime=20000 run

结论:随着线程数增加,计算能力线性增加;

1
sysbench --test=cpu --threads=12 --cpu-max-prime=20000 run

结论:当线程数超过cpu核数之后,随着线程数增加,计算能力不再增加,甚至略微下降,这是因为线程上下文切换以后的时间损耗;


2)、文件I/O测试

① 准备30G的数据

1
sysbench --test=fileio --file-total-size=30G prepare

② 运行阶段

  • seqwr:顺序写入
1
sysbench --test=fileio --file-test-mode=seqwr --file-total-size=30G --time=60 run

结论:可以看到,顺序写的过程中 CPU占用很低,但是 buff、cache、block out、in、cs(上下文切换)都很高,这是因为 linux 写磁盘用了 页缓存 和 DMA。

  • seqrewr:顺序重写
1
sysbench --test=fileio --file-test-mode=seqrewr --file-total-size=30G --time=60 run

  • seqrd:顺序读取
1
sysbench --test=fileio --file-test-mode=seqrd --file-total-size=30G --time=60 run

  • rndrd:随机读取
1
sysbench --test=fileio --file-test-mode=rndrd --file-total-size=30G --time=60 run

结论: SSD是没有寻道时间的,但是 随机读(46.95MiB/s) 仍然比顺序读(184.46MiB/s)要慢很多

  • rndwr:随机写入
1
sysbench --test=fileio --file-test-mode=rndwr --file-total-size=30G --time=60 run

随机写(26.25MiB/s)更慢了,顺序写的速度有138.38MiB/s

  • rndrw:混合随机读/写
1
sysbench --test=fileio --file-test-mode=rndrw --file-total-size=30G --time=60 run

混合随机读写的速度更是龟速,然而我们平时业务场景中使用最多的就是这种情况,所以一定要注意,能顺序写的,一定不随机写

③ 清除数据

sysbench –test=fileio –file-total-size=30G cleanup


3)、内存测试

测试8K顺序分配

1
sysbench --test=memory --memory-access-mode=seq --memory-block-size=8K --memory-total-size=100G --threads=12 --events=10000 run

结论:可以看到 CPU 占用极高,内存写入速度为 10138.32 MiB / sec

测试8K随机分配

1
sysbench --test=memory --memory-access-mode=rnd --memory-block-size=8K --memory-total-size=100G --threads=12 --events=10000 run

结论:cpu占用极高,随机分配速度为 1381.41 MiB / sec,只有顺序分配的 1/7 左右,内存都有顺序写都有差距。


3、sysbench对mysql进行OLTP测试

1
sysbench /usr/share/sysbench/oltp_read_write.lua help

1) 、新建数据库 sbtest

1
mysqladmin create sbtest -uroot -p

2) 、运行状态收集脚本

1
sh /root/benchmark/1-collect-system-status.sh

3)、 准备1百万条数据

可以在/usr/share/sysbench/中找到需要使用的lua脚本:

1
2
3
4
5
6
7
8
# 开始插入数据
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --mysql-password=123456 --table-size=10000000 prepare

# 关键参数说明:
--threads=64 测试时使用的线程数
--time=600 测试时间
--histogram=on 在报告中是否输出关于延迟的直方图,建议开启
--table_size=10000000 数据表的大小


4)、 运行压测程序

1
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --table-size=10000000 run

  • 总事务数:820388

  • 每秒事务数:1367.22 per sec

  • 总查询数:16407760

  • 每秒查询数:27344.47 per sec

请求分布直方图

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
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
[root@10-10-135-172 benchmark]#  sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --table-size=10000000 run
sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 64
Initializing random number generator from current time


Initializing worker threads...

Threads started!

Latency histogram (values are in milliseconds)
value ------------- distribution ------------- count
4.329 | 64
4.407 |***** 1030
4.487 |**************** 3445
4.569 |************************** 5587
4.652 |**************************** 5867
4.737 |************************* 5250
4.823 |******************** 4190
4.910 |**************** 3476
4.999 |************** 2922
5.090 |************ 2605
5.183 |*********** 2329
5.277 |********* 1991
5.373 |********* 1813
5.470 |******** 1600
5.570 |******* 1476
5.671 |****** 1279
5.774 |****** 1178
5.879 |***** 1043
5.986 |***** 974
6.095 |**** 924
6.205 |**** 843
6.318 |**** 768
6.433 |**** 786
6.550 |**** 839
6.669 |**** 943
6.790 |***** 1012
6.913 |***** 1126
7.039 |****** 1171
7.167 |****** 1310
7.297 |****** 1305
7.430 |****** 1291
7.565 |***** 1167
7.702 |***** 1072
7.842 |***** 1066
7.985 |***** 1051
8.130 |***** 1102
8.277 |***** 1081
8.428 |***** 1120
8.581 |***** 1129
8.737 |****** 1244
8.895 |******* 1444
9.057 |******** 1692
9.222 |********* 1984
9.389 |********** 2161
9.560 |********** 2057
9.734 |********* 1944
9.910 |********* 1930
10.090 |******** 1773
10.274 |******** 1753
10.460 |******** 1698
10.651 |******** 1605
10.844 |******* 1553
11.041 |******** 1601
11.242 |******** 1636
11.446 |******** 1649
11.654 |******** 1667
11.866 |******** 1673
12.081 |******** 1733
12.301 |********* 1821
12.524 |********* 1838
12.752 |********* 1844
12.984 |********** 2031
13.219 |********* 1978
13.460 |********** 2206
13.704 |************ 2449
13.953 |************* 2681
14.207 |************* 2675
14.465 |************* 2744
14.728 |************* 2694
14.995 |************* 2667
15.268 |************ 2652
15.545 |************ 2650
15.828 |************* 2671
16.115 |************ 2644
16.408 |************* 2757
16.706 |************* 2760
17.010 |************* 2845
17.319 |************** 2933
17.633 |*************** 3217
17.954 |*************** 3105
18.280 |**************** 3294
18.612 |***************** 3587
18.950 |***************** 3704
19.295 |****************** 3845
19.645 |****************** 3763
20.002 |****************** 3743
20.366 |****************** 3852
20.736 |****************** 3763
21.112 |****************** 3891
21.496 |******************* 4076
21.886 |******************* 4108
22.284 |******************** 4191
22.689 |********************* 4400
23.101 |********************** 4702
23.521 |*********************** 4863
23.948 |************************ 5072
24.384 |************************ 5043
24.827 |************************* 5224
25.278 |************************ 5196
25.737 |************************* 5285
26.205 |************************** 5442
26.681 |************************** 5452
27.165 |*************************** 5727
27.659 |**************************** 5867
28.162 |***************************** 6097
28.673 |****************************** 6367
29.194 |****************************** 6414
29.725 |****************************** 6441
30.265 |******************************* 6512
30.815 |******************************* 6479
31.375 |******************************** 6828
31.945 |******************************** 6876
32.525 |********************************** 7128
33.116 |********************************** 7241
33.718 |*********************************** 7372
34.330 |************************************ 7548
34.954 |************************************ 7653
35.589 |************************************* 7788
36.236 |************************************* 7808
36.894 |************************************* 7910
37.565 |************************************** 8106
38.247 |*************************************** 8177
38.942 |*************************************** 8311
39.650 |**************************************** 8451
40.370 |************************************** 8058
41.104 |*************************************** 8309
41.851 |*************************************** 8260
42.611 |**************************************** 8481
43.385 |**************************************** 8493
44.173 |**************************************** 8478
44.976 |**************************************** 8394
45.793 |*************************************** 8369
46.625 |**************************************** 8398
47.472 |**************************************** 8459
48.335 |**************************************** 8455
49.213 |**************************************** 8446
50.107 |*************************************** 8245
51.018 |*************************************** 8215
51.945 |*************************************** 8215
52.889 |************************************** 8087
53.850 |************************************** 8119
54.828 |************************************** 8043
55.824 |************************************* 7959
56.839 |************************************** 8043
57.871 |************************************ 7746
58.923 |************************************ 7650
59.993 |*********************************** 7522
61.083 |*********************************** 7535
62.193 |********************************** 7250
63.323 |********************************** 7254
64.474 |********************************** 7118
65.645 |********************************* 6983
66.838 |******************************** 6868
68.053 |******************************** 6890
69.289 |******************************* 6688
70.548 |******************************* 6530
71.830 |****************************** 6437
73.135 |***************************** 6127
74.464 |****************************** 6278
75.817 |**************************** 5952
77.194 |*************************** 5767
78.597 |************************** 5581
80.025 |************************** 5522
81.479 |************************* 5374
82.959 |************************* 5217
84.467 |*********************** 4957
86.002 |*********************** 4947
87.564 |********************** 4767
89.155 |********************** 4673
90.775 |********************* 4499
92.424 |********************* 4358
94.104 |******************** 4230
95.814 |******************* 4021
97.555 |****************** 3871
99.327 |****************** 3750
101.132 |***************** 3665
102.969 |**************** 3465
104.840 |**************** 3322
106.745 |*************** 3286
108.685 |************** 3019
110.659 |************** 2963
112.670 |************* 2707
114.717 |************* 2712
116.802 |************ 2528
118.924 |*********** 2379
121.085 |*********** 2269
123.285 |********** 2138
125.525 |********** 2105
127.805 |********* 1861
130.128 |********* 1820
132.492 |******** 1735
134.899 |******* 1575
137.350 |******* 1502
139.846 |******* 1412
142.387 |****** 1302
144.974 |****** 1231
147.608 |***** 1056
150.290 |***** 1015
153.021 |**** 927
155.801 |**** 879
158.632 |**** 815
161.514 |*** 685
164.449 |*** 696
167.437 |*** 613
170.479 |*** 591
173.577 |** 499
176.731 |** 457
179.942 |** 447
183.211 |** 360
186.540 |** 352
189.929 |* 298
193.380 |* 304
196.894 |* 224
200.472 |* 208
204.114 |* 204
207.823 |* 173
211.599 |* 155
215.443 |* 113
219.358 |* 129
223.344 | 106
227.402 | 82
231.534 | 67
235.740 | 66
240.024 | 43
244.385 | 53
248.825 | 30
253.346 | 31
257.950 | 26
262.636 | 19
267.408 | 25
272.267 | 12
277.214 | 11
282.251 | 11
287.379 | 11
292.601 | 5
297.917 | 11
303.330 | 5
308.842 | 6
314.453 | 14
320.167 | 10
325.984 | 16
331.907 | 11
337.938 | 2
344.078 | 8
350.330 | 10
356.695 | 11
363.176 | 10
369.775 | 15
376.494 | 12
383.334 | 14
390.299 | 10
397.391 | 5
404.611 | 11
411.963 | 2
419.448 | 11
427.069 | 7
434.829 | 6
442.730 | 6
450.774 | 5
458.964 | 2
475.794 | 1
484.439 | 2
493.242 | 1
559.501 | 1

SQL statistics:
queries performed:
read: 11485432
write: 3281552
other: 1640776
total: 16407760
transactions: 820388 (1367.22 per sec.)
queries: 16407760 (27344.47 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)

General statistics:
total time: 600.0372s
total number of events: 820388

Latency (ms):
min: 4.30
avg: 46.80
max: 556.16
95th percentile: 112.67
sum: 38398062.70

Threads fairness:
events (avg/stddev): 12818.5625/107.50
execution time (avg/stddev): 599.9697/0.01

[root@10-10-135-172 benchmark]#

5)、清理数据

1
sysbench /usr/share/sysbench/oltp_read_write.lua --threads=64 --time=600 --histogram=on --mysql-host=10.10.135.172 --mysql-port=3306 --mysql-db=sbtest --mysql-user=root --table-size=10000000 cleanup


6)、分析收集到的状态

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
$ sh 2-system-status-analyzer.sh status_data/5-sec-status-2019-11-19_06-status > status.txt
$ vi status.txt
unix时间戳 时间 系统负载 QPS(数据库)
1574160560 18:49:20 18:49:20 51.53 28179.80
1574160565 18:49:25 18:49:25 51.80 26713.20
1574160570 18:49:30 18:49:30 50.86 27784.60
1574160575 18:49:35 18:49:35 51.19 26912.40
1574160580 18:49:40 18:49:40 52.30 27048.20
1574160585 18:49:45 18:49:45 52.99 26843.00
1574160590 18:49:50 18:49:50 53.87 27478.80
1574160595 18:49:55 18:49:55 54.61 28724.60
1574160600 18:50:00 18:50:00 55.68 27960.00
1574160605 18:50:05 18:50:05 55.46 25874.40
1574160610 18:50:10 18:50:10 56.07 25936.40
1574160615 18:50:15 18:50:15 52.54 25914.20
1574160620 18:50:20 18:50:20 52.66 27237.60
1574160625 18:50:25 18:50:25 52.92 27507.20
1574160630 18:50:30 18:50:30 53.89 26005.40
1574160635 18:50:35 18:50:35 55.02 26745.00
1574160640 18:50:40 18:50:40 54.86 25643.80
1574160645 18:50:45 18:50:45 55.75 24751.20
1574160650 18:50:50 18:50:50 53.69 27608.80
1574160655 18:50:55 18:50:55 52.51 26590.20
1574160660 18:51:00 18:51:00 51.83 27411.60
1574160665 18:51:05 18:51:05 49.68 28504.20
1574160670 18:51:10 18:51:10 50.27 20451.00
1574160675 18:51:15 18:51:15 46.24 1.40
1574160680 18:51:20 18:51:20 42.62 2.20
1574160685 18:51:25 18:51:25 39.21 1.40
1574160690 18:51:30 18:51:30 36.07 1.80
1574160695 18:51:35 18:51:35 33.18 2.40
1574160700 18:51:40 18:51:40 30.52 1.00
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
$ cat status_data/5-sec-status-2019-11-19_06-status | grep 'TS ' > load_status.txt
$ vi load_status.txt

TS 1574160295.047916883 18:44:55 18:44:55 up 1 day, 2:22, 5 users, load average: 52.13, 24.94, 9.96
TS 1574160300.059352736 18:45:00 18:45:00 up 1 day, 2:22, 5 users, load average: 53.08, 25.59, 10.25
TS 1574160305.042101201 18:45:05 18:45:05 up 1 day, 2:22, 5 users, load average: 50.91, 25.60, 10.33
TS 1574160310.052530257 18:45:10 18:45:10 up 1 day, 2:22, 5 users, load average: 51.72, 26.19, 10.60
TS 1574160315.046490819 18:45:15 18:45:15 up 1 day, 2:22, 5 users, load average: 52.78, 26.83, 10.89
TS 1574160320.008893450 18:45:20 18:45:20 up 1 day, 2:22, 5 users, load average: 53.68, 27.45, 11.18
TS 1574160325.203247981 18:45:25 18:45:25 up 1 day, 2:22, 5 users, load average: 54.67, 28.09, 11.47
TS 1574160330.118228229 18:45:30 18:45:30 up 1 day, 2:22, 5 users, load average: 55.57, 28.72, 11.77
TS 1574160335.016869072 18:45:35 18:45:35 up 1 day, 2:22, 5 users, load average: 55.61, 29.17, 12.00
TS 1574160340.042057908 18:45:40 18:45:40 up 1 day, 2:22, 5 users, load average: 54.84, 29.45, 12.19
TS 1574160345.056853963 18:45:45 18:45:45 up 1 day, 2:23, 5 users, load average: 55.65, 30.04, 12.47
TS 1574160350.059495691 18:45:50 18:45:50 up 1 day, 2:23, 5 users, load average: 55.84, 30.50, 12.72
TS 1574160355.238934528 18:45:55 18:45:55 up 1 day, 2:23, 5 users, load average: 56.65, 31.09, 13.00
TS 1574160360.115909774 18:46:00 18:46:00 up 1 day, 2:23, 5 users, load average: 56.76, 31.54, 13.24
TS 1574160365.067470620 18:46:05 18:46:05 up 1 day, 2:23, 5 users, load average: 57.34, 32.08, 13.52
TS 1574160370.111400548 18:46:10 18:46:10 up 1 day, 2:23, 5 users, load average: 55.39, 32.09, 13.62
TS 1574160375.074730081 18:46:15 18:46:15 up 1 day, 2:23, 5 users, load average: 53.76, 32.14, 13.74
TS 1574160380.210612654 18:46:20 18:46:20 up 1 day, 2:23, 5 users, load average: 54.34, 32.62, 13.99
TS 1574160385.102457806 18:46:25 18:46:25 up 1 day, 2:23, 5 users, load average: 50.87, 32.26, 13.97
TS 1574160390.117591568 18:46:30 18:46:30 up 1 day, 2:23, 5 users, load average: 50.48, 32.49, 14.14
TS 1574160395.076076309 18:46:35 18:46:35 up 1 day, 2:23, 5 users, load average: 51.40, 32.98, 14.40
TS 1574160400.191211227 18:46:40 18:46:40 up 1 day, 2:23, 5 users, load average: 52.09, 33.43, 14.65
TS 1574160405.153420741 18:46:45 18:46:45 up 1 day, 2:24, 5 users, load average: 49.20, 33.14, 14.65
TS 1574160410.013108072 18:46:50 18:46:50 up 1 day, 2:24, 5 users, load average: 49.58, 33.49, 14.87
TS 1574160415.084485731 18:46:55 18:46:55 up 1 day, 2:24, 5 users, load average: 48.82, 33.59, 15.00
TS 1574160420.011562959 18:47:00 18:47:00 up 1 day, 2:24, 5 users, load average: 47.55, 33.58, 15.10
TS 1574160425.018923271 18:47:05 18:47:05 up 1 day, 2:24, 5 users, load average: 48.95, 34.11, 15.37

4、tpcc-mysql对mysql进行测试

  1. Build binaries
    • cd src ; make ( you should have mysql_config available in $PATH)
  2. Load data
    • create database mysqladmin create tpcc1000
    • create tables mysql tpcc1000 < create_table.sql
    • create indexes and FK ( this step can be done after loading data) mysql tpcc1000 < add_fkey_idx.sql
    • populate data
      • simple step tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "" -w 1000 |hostname:port| |dbname| |user| |password| |WAREHOUSES| ref. tpcc_load –help for all options
      • load data in parallel check load.sh script
  3. Start benchmark
    • ./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w1000 -c32 -r10 -l10800
    • |hostname| |port| |dbname| |user| |WAREHOUSES| |CONNECTIONS| |WARMUP TIME| |BENCHMARK TIME|
    • ref. tpcc_start –help for all options

1)、 创建数据库和表结构

1
2
3
4
5
cd tpcc-mysql-master

mysqladmin create tpcc1000 -uroot -p123456
mysql -uroot -p123456 tpcc1000 < create_table.sql
mysql -uroot -p123456 tpcc1000 < add_fkey_idx.sql

2)、加载数据

1
2
3
./tpcc_load -h10.10.135.172 -P3306 -d tpcc1000 -u root -p 123456 -w 10
# 1 代表1个仓库大小,10代表 10个仓库大小
# sh load.sh tpcc1000 1000

加载完后查看数据量

1
2
3
4
5
6
7
8
9
select count(*) from tpcc1000.customer;
select count(*) from tpcc1000.district;
select count(*) from tpcc1000.history;
select count(*) from tpcc1000.item;
select count(*) from tpcc1000.new_orders;
select count(*) from tpcc1000.order_line;
select count(*) from tpcc1000.orders;
select count(*) from tpcc1000.stock;
select count(*) from tpcc1000.warehouse;


3)、运行状态收集脚本

1
sh /root/benchmark/1-collect-system-status.sh

4)、执行测试

1
2
3
4
5
6
7
8
9
10
# -w 指定仓库数量
# -c 指定并发连接数
# -r 指定开始测试前进行warmup的时间,进行预热后,测试效果更好
# -l 指定测试持续时间
# -i 指定生成报告间隔时长
# -f 指定生成的报告文件名

# 真实测试场景中,建议预热时间不小于5分钟,持续压测时长不小于30分钟,否则测试数据可能不具参考意义
# 耗时:1800, 即 30分钟
./tpcc_start -h10.10.135.172 -P3306 -dtpcc1000 -uroot -p123456 -w10 -c32 -r300 -l1800 > tpcc_output.txt

top

vmstat 1

执行结果


5)、分析收集到的状态数据

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
$ sh 2-system-status-analyzer.sh status_data/5-sec-status-2019-11-19_06-status > status.txt
$ vi status.txt
unix时间戳 时间 系统负载 QPS(数据库)
1574177360 2019-11-19 23:29:20 22.00 37156.60
1574177365 2019-11-19 23:29:25 20.24 36821.80
1574177370 2019-11-19 23:29:30 21.26 36879.80
1574177375 2019-11-19 23:29:35 19.56 37217.60
1574177380 2019-11-19 23:29:40 20.63 37646.80
1574177385 2019-11-19 23:29:45 21.62 35293.20
1574177390 2019-11-19 23:29:50 19.89 36457.80
1574177395 2019-11-19 23:29:55 18.30 37464.00
1574177400 2019-11-19 23:30:00 19.72 36722.60
1574177405 2019-11-19 23:30:05 20.78 37913.60
1574177410 2019-11-19 23:30:10 19.12 36294.00
1574177415 2019-11-19 23:30:15 17.58 35279.20
1574177420 2019-11-19 23:30:20 18.98 39051.20
1574177425 2019-11-19 23:30:25 20.10 37700.20
1574177430 2019-11-19 23:30:30 18.49 37632.20
1574177435 2019-11-19 23:30:35 17.01 38068.00
1574177440 2019-11-19 23:30:40 15.65 38218.40
1574177445 2019-11-19 23:30:45 14.48 34143.40
1574177450 2019-11-19 23:30:50 13.40 7794.60
1574177455 2019-11-19 23:30:55 12.40 7744.80
1574177460 2019-11-19 23:31:00 11.41 613.40
1574177465 2019-11-19 23:31:05 10.50 2.20
1574177470 2019-11-19 23:31:10 9.66 1.40
1574177475 2019-11-19 23:31:15 8.88 1.80
1574177480 2019-11-19 23:31:20 8.17 1.80
1574177485 2019-11-19 23:31:25 7.52 1.80
1574177490 2019-11-19 23:31:30 6.92 2.00
1574177495 2019-11-19 23:31:35 6.36 2.00
1574177500 2019-11-19 23:31:40 5.85 1.60

vi tpcc_output.txt

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
***************************************
*** ###easy### TPC-C Load Generator ***
***************************************
option h with value '10.10.135.172'
option P with value '3306'
option d with value 'tpcc1000'
option u with value 'root'
option p with value '123456'
option w with value '10'
option c with value '32'
option r with value '300'
option l with value '1800'
<Parameters>
[server]: 10.10.135.172 -- 主机
[port]: 3306 -- 端口
[DBname]: tpcc1000 -- 压测的数据库
[user]: root -- 账号
[pass]: 123456 -- 密码
[warehouse]: 10 -- 仓库数
[connection]: 32 -- 并发线程数
[rampup]: 300 (sec.) -- 数据预热时长
[measure]: 1800 (sec.) -- 压测时长

RAMP-UP TIME.(300 sec.) --预热结束

MEASURING START. --开始压测

-- 每10秒输出一次压测数据
-- 以逗号分隔,共9列
-- 第1列,第N次10秒
-- 第2列,New-Order在本轮取样时间内成功执行事务的次数(即吞吐量)
-- 第3列,New-Order在本轮取样时间内,95%事务 在响应时间内(ms)完成
-- 第4列,New-Order在本轮取样时间内,99%事务 在响应时间内完成
-- 第5列,New-Order在本轮取样时间内,本轮测试最大响应时间(ms)
-- 第6列,Payment 有效事务数(吞吐量) | 最大响应时间(ms)
-- 第7列,Order-Status 有效事务数(吞吐量) | 最大响应时间(ms)
-- 第8列,Delivery 有效事务数(吞吐量) | 最大响应时间(ms)
-- 第9列,Stock-Level 有效事务数(吞吐量) | 最大响应时间(ms)
10, trx: 6322, 95%: 58.880, 99%: 80.384, max_rt: 465.298, 6319|311.356, 632|147.998, 633|518.363, 632|840.708
20, trx: 6402, 95%: 57.161, 99%: 74.165, max_rt: 108.881, 6399|102.155, 640|54.569, 640|187.683, 641|125.100
30, trx: 6215, 95%: 58.616, 99%: 83.674, max_rt: 240.631, 6224|149.658, 623|135.217, 622|332.418, 621|168.763
40, trx: 6365, 95%: 57.109, 99%: 76.190, max_rt: 136.020, 6362|140.110, 637|35.815, 637|142.510, 636|154.135
50, trx: 6426, 95%: 56.973, 99%: 76.281, max_rt: 131.508, 6427|171.652, 641|45.733, 640|145.872, 643|156.595
60, trx: 6198, 95%: 58.476, 99%: 80.384, max_rt: 229.300, 6197|238.621, 620|37.124, 621|269.572, 619|261.585
70, trx: 6418, 95%: 57.366, 99%: 76.648, max_rt: 116.301, 6421|115.735, 643|42.165, 643|172.855, 643|151.629
80, trx: 6406, 95%: 57.815, 99%: 76.877, max_rt: 115.657, 6396|99.119, 639|39.815, 640|152.090, 640|159.341
90, trx: 5841, 95%: 62.047, 99%: 87.229, max_rt: 290.416, 5836|291.379, 584|42.550, 585|301.866, 584|142.273
100, trx: 6168, 95%: 56.362, 99%: 76.717, max_rt: 125.903, 6183|115.971, 618|47.143, 619|158.824, 618|162.145
110, trx: 6249, 95%: 57.573, 99%: 78.200, max_rt: 138.199, 6232|126.211, 624|40.234, 623|155.532, 624|152.938
120, trx: 6107, 95%: 58.511, 99%: 78.694, max_rt: 121.301, 6125|146.152, 611|37.128, 608|156.622, 609|126.521
130, trx: 6076, 95%: 58.424, 99%: 84.227, max_rt: 346.854, 6074|266.228, 608|48.112, 608|361.008, 610|140.032
140, trx: 6365, 95%: 55.992, 99%: 75.599, max_rt: 115.579, 6367|110.699, 637|47.943, 637|156.809, 634|126.359
....
1760, trx: 6386, 95%: 56.599, 99%: 74.588, max_rt: 115.095, 6386|103.424, 638|44.586, 640|143.890, 637|139.005
1770, trx: 6148, 95%: 59.039, 99%: 76.510, max_rt: 124.202, 6149|99.945, 615|37.548, 612|152.861, 615|157.265
1780, trx: 5831, 95%: 59.786, 99%: 82.061, max_rt: 291.887, 5826|335.447, 583|47.130, 585|365.992, 583|156.420
1790, trx: 6161, 95%: 57.676, 99%: 78.294, max_rt: 130.952, 6162|132.039, 616|46.349, 616|149.911, 615|173.092
1800, trx: 6164, 95%: 56.295, 99%: 74.098, max_rt: 117.632, 6169|131.690, 616|39.105, 616|202.839, 618|126.449

STOPPING THREADS................................ -- 结束压测

# 成功(success,简写sc)次数,延迟(late,简写lt)次数,重试(retry,简写rt)次数,失败(failure,简写fl)次数
<Raw Results> -- 第一次统计结果
[0] sc:35421 lt:1093057 rt:0 fl:0 avg_rt: 30.0 (5) -- New-Order,新订单业务
[1] sc:457714 lt:670742 rt:0 fl:0 avg_rt: 15.6 (5) -- Payment,支付业务统计
[2] sc:96544 lt:16304 rt:0 fl:0 avg_rt: 3.6 (5) -- Order-Status,订单状态业务统计
[3] sc:87922 lt:24927 rt:0 fl:0 avg_rt: 72.5 (80) -- Delivery,发货业务统计
[4] sc:1565 lt:111282 rt:0 fl:0 avg_rt: 62.8 (20) -- Stock-Level,库存业务统计
in 1800 sec.

<Raw Results2(sum ver.)> -- 第二次统计结果,其他同上
[0] sc:35421 lt:1093057 rt:0 fl:0
[1] sc:457717 lt:670764 rt:0 fl:0
[2] sc:96544 lt:16304 rt:0 fl:0
[3] sc:87922 lt:24927 rt:0 fl:0
[4] sc:1565 lt:111283 rt:0 fl:0

<Constraint Check> (all must be [OK]) -- 下面所有业务逻辑结果都必须为 OK 才行
[transaction percentage]
Payment: 43.48% (>=43.0%) [OK] -- 支付成功次数(上述统计结果中sc + lt)必须大于43.0%,否则结果为NG,而不是OK
Order-Status: 4.35% (>= 4.0%) [OK] --订单状态,其他同上
Delivery: 4.35% (>= 4.0%) [OK] -- 发货,其他同上
Stock-Level: 4.35% (>= 4.0%) [OK] -- 库存,其他同上
[response time (at least 90% passed)] -- 响应耗时指标必须超过90%通过才行
New-Order: 3.14% [NG] * -- 标准5ms,只有3.14% 响应时间合格
Payment: 40.56% [NG] * -- 标准5ms,
Order-Status: 85.55% [NG] * -- 标准5ms,
Delivery: 77.91% [NG] * -- 标准80ms,
Stock-Level: 1.39% [NG] * -- 标准20ms,

<TpmC>
37615.934 TpmC -- TpmC结果值{每分钟事务数,该值是第一次统计结果中的新订单事务数除以总耗时分钟数,例如本例中是:(35421+1093057)/30 ≈ 37615.93..};1000tpmC相当于2000个事务;

tpcc-mysql的业务逻辑及其相关的几个表作用如下:

  • New-Order:新订单,一次完整的订单事务,几乎涉及到全部表
  • Payment:支付,主要对应 orders、history 表
  • Order-Status:订单状态,主要对应 orders、order_line 表
  • Delivery:发货,主要对应 order_line 表
  • Stock-Level:库存,主要对应 stock 表

其它表说明:

  • 客户:主要对应 customer 表
  • 地区:主要对应 district 表
  • 商品:主要对应 item 表
  • 仓库:主要对应 warehouse 表

6)、清理数据

1
drop database tpcc1000;

7)、使用gunplot绘图

① 安装 gunplot

1
2
3
4
yum install -y gnuplot

gnuplot -V
# gnuplot 4.6 patchlevel 2

帮助文档: http://gnuplot.info/docs_4.6/gnuplot.pdf

② 准备分析脚本和绘图脚本

gunplot-analyze-tpcc.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#!/bin/bash
TIMESLOT=1

if [ -n "$2" ]
then
TIMESLOT=$2
echo "Defined $2"
fi

cat $1 | grep -v HY000 | grep -v payment | grep -v neword | \
awk -v timeslot=$TIMESLOT '\
BEGIN { FS="[,():|]"; s=0; cntr=0; aggr=0 } \
/MEASURING START/ {s=1} \
/STOPPING THREADS/ {s=0} \
/0/ { \
if (s==1) { cntr++; aggr+=$2; } \
if (cntr==timeslot ) { printf ("%d %3f %d\n",$1,$5,$3) ; cntr=0; aggr=0 } \
}'

解释: 把 140, trx: 6365, 95%: 55.992, 99%: 75.599, max_rt: 115.579, 6367|110.699, 637|47.943, 637|156.809, 634|126.359中的 “[,():|]” 字符去掉以后,就剩下了140 trx 6365 95% 55.992 99% 75.599 max_rt 115.579 6367 110.699 637 47.943 637 156.809 634 126.359。$1就是140, $5就是55.992,打印的语法就跟c语言一样

帮助文档:man awk

③ 对 tpcc-mysql 测试生成的数据文件进行提取

1
sh gunplot-analyze-tpcc.sh tpcc_output.txt > tpcc_output_transfer.txt

④ 使用tpcc-graph-build.sh脚本生成图表

  • 画95%的事务响应时间图:gunplot-graph-build-rt.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#!/bin/bash
### goto user homedir and remove previous file
rm -f '$2'
gnuplot << EOP
### set data source file
datafile = '$1'
### set graph type and size
set terminal jpeg size 720,640
### set titles
set grid x y
set xlabel "Time (sec)"
set ylabel "Response Times(sec)"
### set output filename
set output '$2'
### build graph
# plot datafile using 1:2 title "95%-rt" with lines,datafile using 1:3 title "max_rt" with lines axes x1y1
plot datafile using 1:2 title "95%-rt" with lines axes x1y1
EOP
  • 画间隔时间内完成的事务数的图:gunplot-graph-build-trx.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#!/bin/bash
### goto user homedir and remove previous file
rm -f '$2'
gnuplot << EOP
### set data source file
datafile = '$1'
### set graph type and size
set terminal jpeg size 720,640
### set titles
set grid x y
set xlabel "Time (sec)"
set ylabel "Transaction Nums"
### set output filename
set output '$2'
### build graph
# plot datafile using 1:2 title "95%-rt" with lines,datafile using 1:3 title "max_rt" with lines axes x1y1
plot datafile using 1:3 title "Transaction Nums" with lines axes x1y1
EOP
1
sh gunplot-graph-build-rt.sh tpcc_output_transfer.txt tpcc_output.jpg

1
sh gunplot-graph-build-trx.sh tpcc_output_transfer.txt tpcc_output.jpg


五、mysql性能调优

可以看到tpcc-mysql基准测试中 rt项全部不合格,所以需要对mysql进行性能调优