sysbench对数据库进行压力测试

219次阅读

共计 5576 个字符，预计需要花费 14 分钟才能阅读完成。

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 性能

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 测试
准备测试数据：

sysbench fileio –file-total-size=40G prepare

运行顺序写入测试：
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

运行顺序重写测试：
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 数据）
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

清除测试数据：
sysbench fileio –file-total-size=40G cleanup

OLTP 基准测试
OLTP 基准测试模拟了一个简单的事务处理系统的工作负载。若要对数据库性能进行测试就要使用 OLTP 相关的脚本。
在 /usr/share/sysbench/ 目录下有相关的 lua 脚本：

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 来查看。

创建测试数据：
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 命令进行测试：（只读）
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 测试，我们还可以测试数据库的写性能：

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