sysbench对数据库进行压力测试
sysbench是一款压力测试工具,可以测试系统的硬件性能,也可以用来对数据库进行基准测试。安装方式:https://github.com/akopytov/sysbench/blob/master/README.md#general-command-line-options此处安装的是sysbench 1.0.6版本,和网上大部分的0.4,0....
sysbench是一款压力测试工具,可以测试系统的硬件性能,也可以用来对数据库进行基准测试。
安装方式:https://github.com/akopytov/sysbench/blob/master/README.md#general-command-line-options
此处安装的是sysbench 1.0.6版本,和网上大部分的0.4,0.5版本的命令不太一样。
sysbench支持三种测试方案:
1、使用自带的测试模块,如对cpu,I/O,memory等的测试。
2、使用自带的lua脚本进行测试,如果使用快速安装的方式,默认的脚本路径为:/usr/share/sysbench
3、使用自定义的lua脚本。
获取帮助信息:
如果对相关模块或者脚本的参数不明确的,可以使用 sysbench testname help来查看,testname含模块名称和脚本名称。
sysbench经典测试命令:
-
prepare
: 创建测试数据或者相关的测试文件,为测试做准备工作。如数据库的测试数据准备等。 -
run
: 开始执行测试。 -
cleanup
: 移除测试数据 -
help
: 帮助命令
测试CPU性能
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
sysbench cpu --cpu-max-prime=10000 run
#显示测试结果
General statistics:
total
time
: 10.0003s
total number of events: 8692
Latency (ms):
min: 1.12
avg: 1.15
max: 2.98
95th percentile: 1.25
sum
: 9996.64
Threads fairness:
events (avg
/stddev
): 8692.0000
/0
.00
execution
time
(avg
/stddev
): 9.9966
/0
.00
|
I/O测试
准备测试数据:
1
|
sysbench fileio --
file
-total-size=40G 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
|
sysbench fileio --
file
-total-size=40G --
file
-
test
-mode=seqwr run
#测试结果
File operations:
reads
/s
: 0.00
writes
/s
: 3916.87
fsyncs
/s
: 5001.01
Throughput:
read
, MiB
/s
: 0.00
written, MiB
/s
: 61.20
General statistics:
total
time
: 10.0055s
total number of events: 89250
Latency (ms):
min: 0.01
avg: 0.11
max: 28.02
95th percentile: 0.12
sum
: 9937.24
Threads fairness:
events (avg
/stddev
): 89250.0000
/0
.00
execution
time
(avg
/stddev
): 9.9372
/0
.00
|
运行顺序重写测试:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
sysbench fileio --
file
-total-size=40G --
file
-
test
-mode=seqrewr run
#测试结果:
File operations:
reads
/s
: 0.00
writes
/s
: 4018.65
fsyncs
/s
: 5131.38
Throughput:
read
, MiB
/s
: 0.00
written, MiB
/s
: 62.79
General statistics:
total
time
: 10.0003s
total number of events: 91531
Latency (ms):
min: 0.00
avg: 0.11
max: 31.45
95th percentile: 0.10
sum
: 9933.80
Threads fairness:
events (avg
/stddev
): 91531.0000
/0
.00
execution
time
(avg
/stddev
): 9.9338
/0
.00
|
混合随机读/写:(如果执行出错,需要重新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
|
sysbench fileio --
file
-total-size=40G --
file
-
test
-mode=rndrw --max-
time
=100 --max-requests=0 run
#测试结果:
File operations:
reads
/s
: 338.98
writes
/s
: 225.99
fsyncs
/s
: 722.23
Throughput:
read
, MiB
/s
: 5.30
written, MiB
/s
: 3.53
General statistics:
total
time
: 100.0007s
total number of events: 128727
Latency (ms):
min: 0.00
avg: 0.78
max: 43.01
95th percentile: 2.97
sum
: 99785.92
Threads fairness:
events (avg
/stddev
): 128727.0000
/0
.00
execution
time
(avg
/stddev
): 99.7859
/0
.00
|
清除测试数据:
1
|
sysbench fileio --
file
-total-size=40G cleanup
|
OLTP基准测试
OLTP基准测试模拟了一个简单的事务处理系统的工作负载。若要对数据库性能进行测试就要使用OLTP相关的脚本。
在/usr/share/sysbench/目录下有相关的lua脚本:
1
2
3
4
5
6
7
8
9
10
11
12
|
bulk_insert.lua
oltp_common.lua
oltp_delete.lua
oltp_insert.lua
oltp_point_select.lua
oltp_read_only.lua
oltp_read_write.lua
oltp_update_index.lua
oltp_update_non_index.lua
oltp_write_only.lua
select_random_points.lua
select_random_ranges.lua
|
对于未知的测试参数,可以通过 sysbench scripts-name help 来查看。
创建测试数据:
1
2
3
4
5
6
7
|
sysbench oltp_common --table-size=1000000 --db-driver=mysql \
--mysql-db=
test
--mysql-user=root --mysql-password=123456 prepare
Creating table
'sbtest1'
...
Inserting 1000000 records into
'sbtest1'
Creating a secondary index on
'sbtest1'
...
|
执行run命令进行测试:(只读)
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
|
sysbench oltp_read_only --table-size=1000000 --db-driver=mysql \
--mysql-db=
test
--mysql-user=root --mysql-password=123456 --
time
=60 --max-requests=0 --threads=8 run
#测试结果
Running the
test
with following options:
Number of threads: 8
Initializing random number generator from current
time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read
: 1679174
write: 0
other: 239882
total: 1919056
transactions: 119941 (1998.81 per sec.)
queries: 1919056 (31980.95 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total
time
: 60.0047s
total number of events: 119941
Latency (ms):
min: 0.32
avg: 4.00
max: 56.17
95th percentile: 8.13
sum
: 479830.78
Threads fairness:
events (avg
/stddev
): 14992.6250
/116
.75
execution
time
(avg
/stddev
): 59.9788
/0
.00
|
除了使用read_only测试,我们还可以测试数据库的写性能:
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
|
sysbench oltp_write_only --table-size=1000000 --db-driver=mysql \
--mysql-db=
test
--mysql-user=root --mysql-password=123456 --
time
=60 --max-requests=0 --threads=8 run
#测试结果
SQL statistics:
queries performed:
read
: 0
write: 83764
other: 41882
total: 125646
transactions: 20941 (348.93 per sec.)
queries: 125646 (2093.56 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total
time
: 60.0120s
total number of events: 20941
Latency (ms):
min: 4.92
avg: 22.92
max: 5518.27
95th percentile: 69.29
sum
: 479968.99
Threads fairness:
events (avg
/stddev
): 2617.6250
/17
.87
execution
time
(avg
/stddev
): 59.9961
/0
.00
|
上面的结果中包含了很多的信息,其中重点需要我们关注的信息是:
1、总的事务数 (total number of events)
2、每秒事务数
3、时间统计信息(最小,平均,最大响应时间,以及95%百分比响应时间)
4、线程公平性统计信息,表示负载的公平性。(thread-fairness)
清理测试数据:
1
2
|
sysbench oltp_read_only --table-size=1000000 --db-driver=mysql \
--mysql-db=
test
--mysql-user=root --mysql-password=123456 cleanup
|
更多推荐
所有评论(0)