sysbench对数据库进行压力测试

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:              8692Latency (ms):         min:                                  1.12         avg:                                  1.15         max:                                  2.98         95th percentile:                      1.25         sum:                               9996.64Threads 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.01Throughput:    read, MiB/s:                  0.00    written, MiB/s:               61.20General statistics:    total time:                          10.0055s    total number of events:              89250Latency (ms):         min:                                  0.01         avg:                                  0.11         max:                                 28.02         95th percentile:                      0.12         sum:                               9937.24Threads 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.38Throughput:    read, MiB/s:                  0.00    written, MiB/s:               62.79General statistics:    total time:                          10.0003s    total number of events:              91531Latency (ms):         min:                                  0.00         avg:                                  0.11         max:                                 31.45         95th percentile:                      0.10         sum:                               9933.80Threads 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.23Throughput:    read, MiB/s:                  5.30    written, MiB/s:               3.53General statistics:    total time:                          100.0007s    total number of events:              128727Latency (ms):         min:                                  0.00         avg:                                  0.78         max:                                 43.01         95th percentile:                      2.97         sum:                              99785.92Threads 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.luaoltp_common.luaoltp_delete.luaoltp_insert.luaoltp_point_select.luaoltp_read_only.luaoltp_read_write.luaoltp_update_index.luaoltp_update_non_index.luaoltp_write_only.luaselect_random_points.luaselect_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  prepareCreating 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: 8Initializing random number generator from current timeInitializing 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:              119941Latency (ms):         min:                                  0.32         avg:                                  4.00         max:                                 56.17         95th percentile:                      8.13         sum:                             479830.78Threads 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:              20941Latency (ms):         min:                                  4.92         avg:                                 22.92         max:                               5518.27         95th percentile:                     69.29         sum:                             479968.99Threads 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)

清理测试数据：

sysbench oltp_read_only  --table-size=1000000 --db-driver=mysql \ --mysql-db=test --mysql-user=root --mysql-password=123456 cleanup