最近在测试和使用trove，准备在公司上线数据库相关的服务。trove跑通之后，创建了几个mysql实例，然后想用sysbench简单的坐下性能测试。
但是在网上一搜，发现好的文档都过时了。因为现在sysbench的版本不再是0.5.x的版本了，而是升级到了1.0.x，升级之后，sysbench的使用方法发生了很多的变化，参数也变了。这里简单记录下新版sysbench的使用方法。
安装sysbench
我使用的是centos 7作为发起测试的客户系统。在centos 7下，直接通过yum就可以安装sysbench。命令如下：
1
|
$ yum install -y sysbench
|
安装完成之后，可以执行下面的命令，查看使用方法和参数：
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
|
$ sysbench --help
Usage:
sysbench [options]... [testname] [command]
Commands implemented by most tests: prepare run cleanup help
General options:
--threads=N number of threads to use [1]
--events=N limit for total number of events [0]
--time=N limit for total execution time in seconds [10]
--forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
--thread-stack-size=SIZE size of stack per thread [64K]
--rate=N average transactions rate. 0 for unlimited rate [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
--report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
--debug[=on|off] print more debugging info [off]
--validate[=on|off] perform validation checks where possible [off]
--help[=on|off] print help and exit [off]
--version[=on|off] print version and exit [off]
--config-file=FILENAME File containing command line options
--tx-rate=N deprecated alias for --rate [0]
--max-requests=N deprecated alias for --events [0]
--max-time=N deprecated alias for --time [0]
--num-threads=N deprecated alias for --threads [1]
Pseudo-Random Numbers Generator options:
--rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
--rand-spec-iter=N number of iterations used for numbers generation [12]
--rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
--rand-spec-res=N percentage of 'special' values to use (for special distribution) [75]
--rand-seed=N seed for random number generator. When 0, the current time is used as a RNG seed. [0]
--rand-pareto-h=N parameter h for pareto distibution [0.2]
Log options:
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3]
--percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95]
--histogram[=on|off] print latency histogram in report [off]
General database options:
--db-driver=STRING specifies database driver to use ('help' to get list of available drivers)
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]
--db-debug[=on|off] print database-specific debug information [off]
Compiled-in database drivers:
mysql - MySQL driver
pgsql - PostgreSQL driver
mysql options:
--mysql-host=[LIST,...] MySQL server host [localhost]
--mysql-port=[LIST,...] MySQL server port [3306]
--mysql-socket=[LIST,...] MySQL socket
--mysql-user=STRING MySQL user [sbtest]
--mysql-password=STRING MySQL password []
--mysql-db=STRING MySQL database name [sbtest]
--mysql-ssl[=on|off] use SSL connections, if available in the client library [off]
--mysql-ssl-cipher=STRING use specific cipher for SSL connections []
--mysql-compression[=on|off] use compression, if available in the client library [off]
--mysql-debug[=on|off] trace all client library calls [off]
--mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205]
--mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off]
pgsql options:
--pgsql-host=STRING PostgreSQL server host [localhost]
--pgsql-port=N PostgreSQL server port [5432]
--pgsql-user=STRING PostgreSQL user [sbtest]
--pgsql-password=STRING PostgreSQL password []
--pgsql-db=STRING PostgreSQL database name [sbtest]
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
See 'sysbench <testname> help' for a list of options for each test.
|
如果你熟悉0.5.x版本的sysbench，你会发现sysbench的用法还是发生了挺多的变化，另外sysbench还内嵌对于lua的支持。相关的测试都需要指定具体的lua脚本才行。下面用具体的例子简要说明下其用法。
使用方法
安装完成之后，如果你的数据库已经配置好了，就可以开始测试了。测试时需要指定测试的类型、数据库名称、数据库用户名和密码，数据库表的数目及单个表的大小。具体如下：
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
|
$ cat oltp-bench.sh
#!/bin/bash
if [ $# -ne 1 ]; then
echo "usage $0 <host>"
exit
fi
SYSBENCH="sysbench /usr/share/sysbench/oltp_read_write.lua \ ## 测试用例
--db-driver=mysql \
--mysql-user=test \ ## 数据库用户名
--mysql-host=$1 \ ## 数据库地址
--mysql-password=test123 \ ## 数据库密码
--mysql-db=perf \ ## 数据库名称
--tables=10 \ ## 测试表的数量
--table-size=200000 \ ## 每个表的记录数量
--report-interval=10 \ ## 测试过程中多长时间显示一次统计结果
--threads=32 \ ## 启动的线程数
--time=120" ## 总的测试时间
$SYSBENCH prepare ## 准备测试数据
$SYSBENCH run ## 执行测试
$SYSBENCH cleanup ## 清理测试用的表格和数据
$ ./oltp-bench.sh 101.0.0.41
sysbench 1.0.5 (using system LuaJIT 2.0.4)
Initializing worker threads...
Creating table 'sbtest4'...
Creating table 'sbtest2'...
Creating table 'sbtest6'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest1'...
Creating table 'sbtest9'...
Creating table 'sbtest10'...
Inserting 200000 records into 'sbtest6'
Inserting 200000 records into 'sbtest9'
Inserting 200000 records into 'sbtest7'
Inserting 200000 records into 'sbtest2'
Inserting 200000 records into 'sbtest5'
Inserting 200000 records into 'sbtest3'
Inserting 200000 records into 'sbtest8'
Inserting 200000 records into 'sbtest4'
Inserting 200000 records into 'sbtest10'
Inserting 200000 records into 'sbtest1'
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest4'...
sysbench 1.0.5 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 140.86 qps: 2869.27 (r/w/o: 2016.49/567.85/284.93) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 32 tps: 148.20 qps: 2947.79 (r/w/o: 2061.39/590.00/296.40) lat (ms,95%): 427.07 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 32 tps: 153.80 qps: 3083.63 (r/w/o: 2158.82/617.21/307.60) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 32 tps: 148.00 qps: 2975.20 (r/w/o: 2080.20/599.00/296.00) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 32 tps: 151.50 qps: 3018.25 (r/w/o: 2114.24/601.01/303.01) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 32 tps: 145.50 qps: 2923.27 (r/w/o: 2043.78/588.49/291.00) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 32 tps: 150.70 qps: 3014.66 (r/w/o: 2109.77/603.49/301.40) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 32 tps: 151.00 qps: 3017.75 (r/w/o: 2113.96/601.79/301.99) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 32 tps: 149.31 qps: 2957.93 (r/w/o: 2067.89/593.03/297.01) lat (ms,95%): 383.33 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 32 tps: 160.90 qps: 3240.79 (r/w/o: 2274.89/642.50/323.40) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 32 tps: 159.59 qps: 3199.49 (r/w/o: 2234.42/645.88/319.19) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 32 tps: 162.00 qps: 3240.00 (r/w/o: 2268.00/648.00/324.00) lat (ms,95%): 331.91 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 255444
write: 72984
other: 36492
total: 364920
transactions: 18246 (151.89 per sec.)
queries: 364920 (3037.81 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.1239s
total number of events: 18246
Latency (ms):
min: 71.89
avg: 210.65
max: 746.05
95th percentile: 363.18
sum: 3843430.91
Threads fairness:
events (avg/stddev): 570.1875/5.55
execution time (avg/stddev): 120.1072/0.02
sysbench 1.0.5 (using system LuaJIT 2.0.4)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...
|
上面的测试结果中，通常我们最关注的是两个值：
- TPS: 每秒钟处理的事务数量
- QPS: 每个钟处理的查询数量
上面的测试中，TPS是151.89，QPS是3037.81
次阅读