sysbench-MySql性能测试工具

  1. 下载安装包 https://github.com/akopytov/sysbench
Bash
shell> wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
shell> unzip sysbench-1.0.zip
shell> cd sysbench-1.0
  1. 安装依赖库
Bash
shell> yum install automake libtool -y
  1. 开始安装
Bash
shell> ./autogen.sh
shell> ./configure
#ERROR: cannot find MySQL libraries. If you want to compile with MySQL support 没找到mysql库 需要用参数指定下 --with-mysql-includes和--with-mysql-libs
shell> ./configure --with-mysql-includes=/alidata/server/mysql5.7/include/ --with-mysql-libs=/alidata/server/mysql5.7/lib/
shell> make
  1. 执行下命令:
Bash
shell> sysbench --help
#sysbench: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
#问题原因:sysbench无法找到mysql的库文件,可能是环境变量LD_LIBRARY_PATH没有设置,设置后即可解决该问题:
shell> export LD_LIBRARY_PATH=/alidata/server/mysql5.7/lib/lib
shell> sysbench --version
sysbench 1.0
  1. 创建测试数据库sbtest
Bash
shell> mysqladmin create sbtest -uroot -p
Enter password:
  1. 测试准备: 20个并发连接,20张表 每个表填充10W条数据 最大请求时间120s
Bash
#-test=tests/db/oltp.lua 表示调用 tests/db/oltp.lua 脚本进行 oltp 模式测试
#--oltp_tables_count=10 表示会生成 10 个测试表
#--oltp-table-size=100000 表示每个测试表填充数据量为 100000 
#--rand-init=on 表示每个测试表都是用随机数据来填充的
#-num-threads=8 表示发起 8个并发连接
#--oltp-read-only=off 表示不要进行只读测试,也就是会采用读写混合模式测试
#--report-interval=10 表示每10秒输出一次测试进度报告
#--rand-type=uniform 表示随机类型为固定模式,其他几个可选随机模式:uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
#--max-time=120 表示最大执行时长为 120秒
#--max-requests=0 表示总请求数为 0,因为上面已经定义了总执行时长,所以总请求数可以设定为 0;也可以只设定总请求数,不设定最大执行时长
#--percentile=99 表示设定采样比例,默认是 95%,即丢弃1%的长请求,在剩余的99%里取最大值

shell> sysbench --test=oltp --oltp_tables_count=10 --oltp-table-size=100000 --mysql-user=root --mysql-password=123456 --num-threads=20 --max-time=120 --max-requests=0 --oltp-test-mode=complex prepare
sysbench 1.0:  multi-threaded system evaluation benchmark

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating secondary indexes on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating secondary indexes on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating secondary indexes on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating secondary indexes on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating secondary indexes on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating secondary indexes on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating secondary indexes on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating secondary indexes on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating secondary indexes on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating secondary indexes on 'sbtest10'...

7.开始测试

Bash
shell> sysbench --test=oltp --oltp_tables_count=10 --oltp-table-size=100000 --mysql-user=root --mysql-password=123456 --num-threads=20 --max-time=120 --max-requests=0 --oltp-test-mode=complex run >> /tmp/log/sysbench_oltpx_20161121.log

#执行结束后查看测试报告
shell> less /tmp/log/sysbench_oltpx_20161121.log
sysbench 1.0:  multi-threaded system evaluation benchmark

#报告内容如下:
Running the test with following options:
Number of threads: 20
Initializing random number generator from current time


Initializing worker threads...

Threads started!

OLTP test statistics:
    queries performed:
        read:                            935592 --读总数
        write:                           267295 --写总数
        other:                           133650 --其他操作(CURD之外的操作,例如COMMIT)
        total:                           1336537 --全部总数
    transactions:                        66822  (556.77 per sec.) --总事务数(每秒事务数)
    read/write requests:                 1202887 (10022.55 per sec.) --读写总数(每秒读写次数)
    other operations:                    133650 (1113.58 per sec.)  --其他操作总数(每秒其他操作次数)
    ignored errors:                      6      (0.05 per sec.)  --总忽略错误总数(每秒忽略错误次数)
    reconnects:                          0      (0.00 per sec.) --重连总数(每秒重连次数)

General statistics: --常规统计
    total time:                          120.0180s --总耗时
    total number of events:              66822 --共发生多少事务数
    total time taken by event execution: 2399.7900s  --所有事务耗时相加(不考虑并行因素)
    response time:
         min:                                  2.76ms --最小耗时
         avg:                                 35.91ms --平均耗时
         max:                               1435.19ms --最长耗时
         approx.  95 percentile:              84.22ms --超过95%平均耗时

Threads fairness: --并发统计
    events (avg/stddev):           3341.1000/37.54 --总处理事件数/标准偏差
    execution time (avg/stddev):   119.9895/0.02
--总执行时间/标准偏差

发表评论