sysbench-0.5基本使用

简介

sysbench是一个基于LuaJIT的多线程基准测试工具。它最常用于数据库基准测试，但也提供了CPU，文件io，内存等的基准测试

安装

卸载mariadb-libs

yum remove mariadb-libs -y

安装mysql-community-devel

rpm -ivh mysql-community-libs-5.7.25-1.el7.x86_64.rpm  mysql-community-devel-5.7.25-1.el7.x86_64.rpm mysql-community-common-5.7.25-1.el7.x86_64.rpm

下载地址: https://dev.mysql.com/downloads/mysql

安装sysbench

下载地址 https://github.com/akopytov/sysbench/tree/0.5

yum install unzip make automake libtool pkgconfig libaio-devel -y
unzip sysbench-0.5.zip
cd sysbench-0.5/
./autogen.sh
./configure
make 
make install 

如果是二进制解压方式安装的mysql可以通过--with-mysql-includes和 --with-mysql-libs指定相关目录

验证是否安装成功

[root@localhost sysbench-master]# sysbench  --version
sysbench 1.1.0

测试CPU

--cpu-max-prime=N # 设置计算素数的最大值
 --num-threads=10  # 设置线程数
 --max-requests=1000 # 设置请求数

示例：启动10个线程，执行10个请求，每个请求计算素数到2000

[root@mgr_node2 sysbench-0.5]# sysbench --test=cpu  --cpu-max-prime=2000 --num-threads=2 --max-requests=10  run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 2  # 线程数
Random number generator seed is 0 and will be ignored 


Prime numbers limit: 2000  # 素数生成到2000

Initializing worker threads...

Threads started!


General statistics:
    total time:                          0.0018s # 运行时间
    total number of events:              10  # 事件总数
    total time taken by event execution: 0.0014s # 时间执行需要的时间
    response time:    # 响应时间
         min:                                  0.13ms  # 最小值
         avg:                                  0.14ms  # 平均值
         max:                                  0.18ms  # 最大值
         approx.  95 percentile:               0.18ms  # 95%以上的响应时间

Threads fairness:
    events (avg/stddev):           5.0000/5.00
    execution time (avg/stddev):   0.0007/0.00

文件IO基准测试

文件IO测试模拟了很多InnoDB的IO特性，测试的第一步是准备阶段，生成测试需要用的文件，生成的数据文件至少要比内存大，如果文件中的数据能完全放到内存中，则操作系统缓存大部分的数据，导致测试结果无法体现io密集型的工作负载

常用选项

--file-num=N  # 创建的文件数目，默认是128
--file-block-size=N # 测试文件的块大小
--file-total-size=SIZE # 创建的文件的总大小
--file-test-mode=STRING 
# 可选的值有:seqwr(顺序写入), seqrewr(顺序重写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机混合读写)
--file-io-mode=STRING # 文件操作模式，可选值{sync,async,mmap} 
--file-extra-flags=STRING additional flags to use on opening files {sync,dsync,direct} 
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync())
--file-fsync-all=[on|off] do fsync() after each write operation 
--file-fsync-end=[on|off] do fsync() at the end of test [on]
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} 
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) 
--file-rw-ratio=N reads/writes ratio for combined test [1.5]

准备阶段

# 准备阶段，创建数据集，文件大小5G
[root@mgr_node2 ~]# sysbench  --test=fileio --file-total-size=5G prepare

测试阶段

# IO混合随机读写基准测试
# --init-rng=on 初始化随机数发生器
[root@mgr_node2 ~]# sysbench --test=fileio --file-total-size=5G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1 # 线程数
Random number generator seed is 0 and will be ignored


Extra file open flags: 0
128 files, 40Mb each # 128个文件 每个40M
5Gb total file size # 测试的数据文件总共大小
Block size 16Kb # 块大小
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50 # 读写比例
Periodic FSYNC enabled, calling fsync() each 100 requests. # 没100个请求执行一次fsync()
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode #
Doing random r/w test  # 随机读写测试
Initializing worker threads...  

Threads started!

Operations performed:  15508 reads, 10339 writes, 33024 Other = 58871 Total
Read 242.31Mb  Written 161.55Mb  Total transferred 403.86Mb  (1.3462Mb/sec)
   86.16 Requests/sec executed
# 15508次读操作，10339次写操作，33024个其他操作，总数58871
# 每秒请求数：86.16 Requests/sec
# 吞吐量：1.3462Mb/sec

General statistics:
    total time:                          300.0009s  # 执行的时间
    total number of events:              25847  # 事件数量
    total time taken by event execution: 202.9444s 
    response time:  # 响应时间
         min:                                  0.01ms
         avg:                                  7.85ms
         max:                                573.40ms
         approx.  95 percentile:              23.83ms

Threads fairness:
    events (avg/stddev):           25847.0000/0.00
    execution time (avg/stddev):   202.9444/0.00  

清理

# 清理 
[root@mgr_node2 ~]# sysbench  --test=fileio --file-total-size=5G cleanup

主要看的数据每秒的请求数,吞吐量,95%以上事件响应时间

数据库基准测试

测试mysql常用选项：

--mysql-host    指定数据库地址
--mysql-port    指定数据库端口号
--mysql-db      指定使用的测试数据库
--mysql-user    指定使用的数据库用户
--mysql-password    指定使用的数据库密码
--mysql-table-engine 指定数据库表使用的引擎
--oltp-table-mode  执行模式，一般使用complex
--table_size    设置表的大小
--tables    设置表的数量
--threads   启动的线程
--time   设置运行时间设为0表示不限制时间
--report-interval  周期性报告，单位为秒

command:

• prepare:创建测试需要的文件，数据库等
• run：进行测试
• cleanup ：清理测试环境

示例

准备

sysbench  --test=oltp \
--mysql-table-engine=innodb  \
--oltp-table-size=1000 \
--oltp-table-mode=complex \
--oltp-tables-count=10 \
--threads=2 \
--mysql-user=hal \
--mysql-host=192.168.240.205 \
--mysql-password='hal@2019' --mysql-db=mgr \
--report-interval=10 \
--max-time=60 \
prepare

测试

sysbench  --test=oltp \
--mysql-table-engine=innodb  \
--oltp-table-size=1000 \
--oltp-table-mode=complex \
--oltp-tables-count=10 \
--threads=2 \
--mysql-user=hal \
--mysql-host=192.168.240.205 \
--mysql-password='hal@2019' --mysql-db=mgr \
--report-interval=10 \
--max-time=60 \
run

sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored


Initializing worker threads...

Threads started!

[  10s] threads: 1, tps: 17.70, reads: 248.89, writes: 70.80, response time: 257.22ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 1, tps: 23.10, reads: 323.10, writes: 92.40, response time: 237.82ms (95%), errors: 0.00, reconnects:  0.00
[  30s] threads: 1, tps: 25.29, reads: 354.61, writes: 101.54, response time: 212.06ms (95%), errors: 0.00, reconnects:  0.00
[  40s] threads: 1, tps: 26.41, reads: 368.70, writes: 105.26, response time: 233.65ms (95%), errors: 0.00, reconnects:  0.00
[  50s] threads: 1, tps: 28.19, reads: 395.78, writes: 113.17, response time: 139.29ms (95%), errors: 0.00, reconnects:  0.00
[  60s] threads: 1, tps: 30.51, reads: 427.08, writes: 122.02, response time: 132.90ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            21182
        write:                           6052
        other:                           3026
        total:                           30260  # 请求总数
    transactions:                        1513   (25.13 per sec.) # 事务数量
    read/write requests:                 27234  (452.26 per sec.)
    other operations:                    3026   (50.25 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.2170s # 运行时长
    total number of events:              1513 # 事件总数
    total time taken by event execution: 60.2051s
    response time: #响应事件
         min:                                 12.51ms
         avg:                                 39.79ms
         max:                                649.42ms
         approx.  95 percentile:             220.54ms

Threads fairness:
    events (avg/stddev):           1513.0000/0.00
    execution time (avg/stddev):   60.2051/0.00

关注的几个点

• 总的事务数量
• 每秒事务数量
• 时间统计信息(最小，平均，最大响应时间，以及95%的响应时间)
• 线程公平性统计信息(thread-fairness)，用于表示模拟负载的公平性

清理

sysbench  --test=oltp \
--mysql-table-engine=innodb  \
--oltp-table-size=1000 \
--oltp-table-mode=complex \
--oltp-tables-count=10 \
--threads=2 \
--mysql-user=hal \
--mysql-host=192.168.240.205 \
--mysql-password='hal@2019' --mysql-db=mgr \
--report-interval=10 \
--max-time=60 \
cleanup

参考文档

• 《高性能的MySQL》