Sysbench出图

Plot-Sysbench

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
sysbench /tmp/sysbench-master/src/lua/oltp_read_write.lua --mysql-user=root --mysql-password=mysql --mysql-port=3306 \
--mysql-socket=/data/mysql55/mysql.sock --mysql-host=localhost \
--mysql-db=sysbenchtest --tables=10 --table-size=5000000 --threads=30 \
--events=5000000 --report-interval=5 prepare
##--table-size=五百万,一个表五百万 10个表 五千万
##--threads=30 开30个线程并发prepare
Initializing worker threads...
Creating table 'sbtest6'...
Creating table 'sbtest4'...
Creating table 'sbtest5'...
Creating table 'sbtest10'...
Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest9'...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Creating table 'sbtest7'...
Inserting 5000000 records into 'sbtest4'
Inserting 5000000 records into 'sbtest5'
Inserting 5000000 records into 'sbtest6'
Inserting 5000000 records into 'sbtest8'
Inserting 5000000 records into 'sbtest3'
Inserting 5000000 records into 'sbtest9'
Inserting 5000000 records into 'sbtest10'
Inserting 5000000 records into 'sbtest7'
Inserting 5000000 records into 'sbtest1'
Inserting 5000000 records into 'sbtest2'
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest10'...

sysbenchtest库大小

1
2
[root@uz6535 mysql55]# du -sh sysbenchtest/
12G sysbenchtest/

run

1
2
3
4
5
6
sysbench \
/tmp/sysbench-master/src/lua/oltp_read_write.lua \
--mysql-user=root --mysql-password=mysql --mysql-port=3306 \
--mysql-socket=/data/mysql55/mysql.sock --mysql-host=localhost \
--mysql-db=sysbenchtest --tables=10 --table-size=5000000 \
--threads=30 --report-interval=5 --time=300 run > binlog_off.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
[ 5s ] thds: 30 tps: 73.76 qps: 1538.67 (r/w/o: 1084.35/300.82/153.51) lat (ms,95%): 733.00 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 30 tps: 75.80 qps: 1519.27 (r/w/o: 1062.85/304.81/151.61) lat (ms,95%): 590.56 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 30 tps: 58.39 qps: 1183.57 (r/w/o: 831.24/235.55/116.78) lat (ms,95%): 926.33 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 30 tps: 48.21 qps: 961.19 (r/w/o: 672.13/192.64/96.42) lat (ms,95%): 1129.24 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 30 tps: 43.57 qps: 863.96 (r/w/o: 606.66/170.15/87.14) lat (ms,95%): 1109.09 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 30 tps: 21.70 qps: 418.24 (r/w/o: 291.07/83.77/43.41) lat (ms,95%): 2585.31 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 30 tps: 19.99 qps: 420.45 (r/w/o: 299.09/81.37/39.99) lat (ms,95%): 3267.19 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 30 tps: 26.41 qps: 511.99 (r/w/o: 352.53/106.64/52.82) lat (ms,95%): 2198.52 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 30 tps: 30.20 qps: 608.26 (r/w/o: 427.04/120.81/60.41) lat (ms,95%): 2279.14 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 30 tps: 20.58 qps: 438.48 (r/w/o: 308.03/89.29/41.15) lat (ms,95%): 1973.38 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 30 tps: 26.03 qps: 486.74 (r/w/o: 338.97/95.71/52.06) lat (ms,95%): 2279.14 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 30 tps: 46.98 qps: 955.28 (r/w/o: 670.77/190.54/93.97) lat (ms,95%): 1170.65 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 30 tps: 40.62 qps: 803.33 (r/w/o: 562.03/160.06/81.23) lat (ms,95%): 1533.66 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 30 tps: 40.49 qps: 814.29 (r/w/o: 569.19/164.13/80.97) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 30 tps: 38.90 qps: 779.99 (r/w/o: 550.83/151.36/77.80) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 30 tps: 30.30 qps: 617.64 (r/w/o: 428.84/128.19/60.61) lat (ms,95%): 2009.23 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 30 tps: 21.46 qps: 418.45 (r/w/o: 292.27/83.25/42.93) lat (ms,95%): 2449.36 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 30 tps: 37.61 qps: 755.12 (r/w/o: 527.08/152.82/75.21) lat (ms,95%): 1771.29 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 30 tps: 32.54 qps: 651.63 (r/w/o: 457.56/128.98/65.08) lat (ms,95%): 1869.60 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 30 tps: 42.28 qps: 826.39 (r/w/o: 577.77/164.27/84.36) lat (ms,95%): 1453.01 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 30 tps: 30.84 qps: 653.14 (r/w/o: 460.72/130.55/61.87) lat (ms,95%): 2198.52 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 30 tps: 49.66 qps: 975.39 (r/w/o: 684.23/191.85/99.31) lat (ms,95%): 1149.76 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 30 tps: 39.18 qps: 779.72 (r/w/o: 540.44/160.93/78.35) lat (ms,95%): 1376.60 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 30 tps: 19.13 qps: 370.96 (r/w/o: 258.79/74.11/38.05) lat (ms,95%): 3095.38 err/s: 0.00 reconn/s: 0.00
[ 125s ] thds: 30 tps: 21.44 qps: 460.58 (r/w/o: 325.15/92.36/43.07) lat (ms,95%): 3267.19 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 30 tps: 29.84 qps: 581.09 (r/w/o: 404.44/116.97/59.68) lat (ms,95%): 3040.14 err/s: 0.00 reconn/s: 0.00
[ 135s ] thds: 30 tps: 23.97 qps: 467.19 (r/w/o: 328.38/90.86/47.95) lat (ms,95%): 3326.55 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 30 tps: 25.41 qps: 516.94 (r/w/o: 363.70/102.43/50.81) lat (ms,95%): 2449.36 err/s: 0.00 reconn/s: 0.00
[ 145s ] thds: 30 tps: 26.00 qps: 518.58 (r/w/o: 363.19/103.40/52.00) lat (ms,95%): 2778.39 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 30 tps: 22.56 qps: 447.75 (r/w/o: 314.40/88.25/45.11) lat (ms,95%): 2539.17 err/s: 0.00 reconn/s: 0.00
[ 155s ] thds: 30 tps: 23.66 qps: 485.03 (r/w/o: 333.80/103.92/47.31) lat (ms,95%): 2405.65 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 30 tps: 22.00 qps: 427.88 (r/w/o: 305.20/78.68/44.00) lat (ms,95%): 2932.60 err/s: 0.00 reconn/s: 0.00
[ 165s ] thds: 30 tps: 25.84 qps: 521.81 (r/w/o: 362.14/108.00/51.68) lat (ms,95%): 2632.28 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 30 tps: 40.39 qps: 818.20 (r/w/o: 575.66/161.76/80.78) lat (ms,95%): 2082.91 err/s: 0.00 reconn/s: 0.00
[ 175s ] thds: 30 tps: 35.98 qps: 697.47 (r/w/o: 485.77/139.73/71.97) lat (ms,95%): 2045.74 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 30 tps: 18.50 qps: 391.91 (r/w/o: 277.32/77.59/37.00) lat (ms,95%): 2680.11 err/s: 0.00 reconn/s: 0.00
[ 185s ] thds: 30 tps: 26.36 qps: 520.54 (r/w/o: 363.80/104.03/52.72) lat (ms,95%): 1973.38 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 30 tps: 32.40 qps: 641.60 (r/w/o: 447.40/129.40/64.80) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 195s ] thds: 30 tps: 32.60 qps: 668.65 (r/w/o: 465.04/138.41/65.21) lat (ms,95%): 2405.65 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 30 tps: 23.99 qps: 469.08 (r/w/o: 328.12/92.98/47.99) lat (ms,95%): 2985.89 err/s: 0.00 reconn/s: 0.00
[ 205s ] thds: 30 tps: 35.41 qps: 715.31 (r/w/o: 506.48/138.02/70.81) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 30 tps: 28.97 qps: 571.75 (r/w/o: 396.95/116.87/57.93) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 215s ] thds: 30 tps: 27.81 qps: 538.34 (r/w/o: 374.30/108.43/55.61) lat (ms,95%): 2539.17 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 30 tps: 41.66 qps: 855.75 (r/w/o: 605.81/166.62/83.31) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
[ 225s ] thds: 30 tps: 43.58 qps: 844.54 (r/w/o: 584.88/172.51/87.15) lat (ms,95%): 2120.76 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 30 tps: 29.80 qps: 613.46 (r/w/o: 433.04/120.81/59.61) lat (ms,95%): 1533.66 err/s: 0.00 reconn/s: 0.00
[ 235s ] thds: 30 tps: 35.47 qps: 716.43 (r/w/o: 503.19/142.29/70.95) lat (ms,95%): 2198.52 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 30 tps: 35.54 qps: 702.84 (r/w/o: 487.17/144.58/71.09) lat (ms,95%): 1506.29 err/s: 0.00 reconn/s: 0.00
[ 245s ] thds: 30 tps: 28.79 qps: 581.14 (r/w/o: 410.41/113.15/57.57) lat (ms,95%): 1938.16 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 30 tps: 19.20 qps: 393.79 (r/w/o: 272.59/82.80/38.40) lat (ms,95%): 3151.62 err/s: 0.00 reconn/s: 0.00
[ 255s ] thds: 30 tps: 31.60 qps: 608.62 (r/w/o: 427.62/117.80/63.20) lat (ms,95%): 2493.86 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 30 tps: 29.00 qps: 588.06 (r/w/o: 411.64/118.41/58.01) lat (ms,95%): 2159.29 err/s: 0.00 reconn/s: 0.00
[ 265s ] thds: 30 tps: 34.99 qps: 705.06 (r/w/o: 494.30/140.77/69.99) lat (ms,95%): 1836.24 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 30 tps: 16.41 qps: 333.18 (r/w/o: 229.72/70.64/32.82) lat (ms,95%): 2778.39 err/s: 0.00 reconn/s: 0.00
[ 275s ] thds: 30 tps: 32.99 qps: 670.70 (r/w/o: 472.39/132.34/65.97) lat (ms,95%): 3040.14 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 30 tps: 38.99 qps: 755.84 (r/w/o: 527.09/150.77/77.98) lat (ms,95%): 1771.29 err/s: 0.00 reconn/s: 0.00
[ 285s ] thds: 30 tps: 36.41 qps: 720.35 (r/w/o: 503.91/143.63/72.82) lat (ms,95%): 1561.52 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 30 tps: 35.80 qps: 740.01 (r/w/o: 523.00/145.40/71.60) lat (ms,95%): 1678.14 err/s: 0.00 reconn/s: 0.00
[ 295s ] thds: 30 tps: 32.94 qps: 662.28 (r/w/o: 466.61/129.78/65.89) lat (ms,95%): 1973.38 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 30 tps: 32.27 qps: 639.95 (r/w/o: 439.33/136.09/64.54) lat (ms,95%): 1869.60 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 139958
write: 39988
other: 19994
total: 199940
transactions: 9997 (33.18 per sec.)
queries: 199940 (663.60 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 301.2969s
total number of events: 9997
Latency (ms):
min: 13.53
avg: 901.57
max: 5107.53
95th percentile: 2238.47
sum: 9012972.28
Threads fairness:
events (avg/stddev): 333.2333/6.55
execution time (avg/stddev): 300.4324/0.32

shell处理一下,文件名 最好为测试的条件 比如binlog_off binlog_on thread_30 thread90

1
2
3
4
5
6
7
8
grep "^\[" binlog_off.txt |awk -F'(' '{print $1}'|sed -e "s/\[\ /\{'time':'/g" -e "s/\ \]//g" -e "s/:\ /':'/g" -e "s/\ /','/g" -e "s/,'$/\}/g"
{'time':'5s','thds':'30','tps':'14.95','qps':'391.01'}
{'time':'10s','thds':'30','tps':'27.33','qps':'549.58'}
{'time':'15s','thds':'30','tps':'29.01','qps':'585.07'}
{'time':'20s','thds':'30','tps':'27.51','qps':'528.04'}
{'time':'25s','thds':'30','tps':'24.80','qps':'503.08'}
{'time':'30s','thds':'30','tps':'34.22','qps':'688.40'}

处理成字典的样子,然后用python的 eval函数就能转换成字典了.我这里只取了输出结果的tps qps thds部分数据

出图用charts库,我用的jupyter notebook直接执行的

测试机跑完文件那下来,统一命名测试机跑出来的结果为.txt,处理完的为.out

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
import charts
import subprocess
import os
class MyCharts:
def __init__(self,file_list,qpsortps,title,interval=None):
self.file_list=file_list
self.qpsortps=qpsortps
self.interval=interval
self.title=title
self.options = {
'title':{'text':self.title},
'xAxis':{
'labels':{
'step':1
},
},
'yAxis':{
'title':{'text':self.qpsortps},
},
'chart': {
'type': 'line',
'zoomType': 'x',
'panning': 'true',
'panKey': 'shift'
},
}
def getOne(self,file):
with open(file,'r') as data:
dicList=[]
for line in data:
dic=eval(line.strip())
if self.interval:
if int(dic['time'][:-1])%self.interval == 0:
dicList.append(dic)
else:
dicList.append(dic)
timeList,qpsortpsList=[],[]
for i in dicList:
timeList.append(int(i['time'][:-1]))
if self.qpsortps == 'qps':
qpsortpsList.append(float(i['qps']))
else:
qpsortpsList.append(float(i['tps']))
qpsortpsData=list(zip(timeList,qpsortpsList))
temp = file[file.rindex('/')+1:file.rindex('.')]
return {'data':qpsortpsData,'name':temp}
def getMulti(self):
s1List = []
for file in self.file_list:
s1List.append(self.getOne(file))
return s1List
def etl(file_list):
for file in file_list:
file_txt='/Users/TiM/sysbench_file/'+file+'.txt'
file_out='/Users/TiM/sysbench_file/'+file+'.out'
if not os.path.exists(file_out):
cmd='''grep "^\[" %s \
|awk -F'(' '{print $1}'|sed -e "s/\[\ /\{'time':'/g" -e \
"s/\ \]//g" -e "s/:\ /':'/g" -e "s/\ /','/g" -e "s/,'$/\}/g" \
> %s''' % (file_txt,file_out)
subprocess.call(cmd,shell=True)
etl(['oltp_point_select','oltp_read_only','oltp_read_write'])
file_list = [
'/Users/TiM/sysbench_file/oltp_read_write.out',
'/Users/TiM/sysbench_file/oltp_point_select.out',
'/Users/TiM/sysbench_file/oltp_read_only.out'
]
char_1=MyCharts(file_list,'qps','16G 4core Benchmark')
res=char_1.getMulti()
charts.plot(res,options=char_1.options,show='inline',)

image

Powered by Hexo and Hexo-theme-hiker

Copyright © 2013 - 2018 Fan() All Rights Reserved.

访客数 : | 访问量 :

#