sysbench-0.5基本使用

r囧r小猫 2022-04-24 08:24 194阅读 0赞

# 简介 #

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][https_dev.mysql.com_downloads_mysql]

### 安装`sysbench` ###

下载地址 [https://github.com/akopytov/sysbench/tree/0.5][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 \
    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)，用于表示模拟负载的公平性

清理

参考文档

*  《高性能的MySQL》

[https_dev.mysql.com_downloads_mysql]: https://dev.mysql.com/downloads/mysql
[https_github.com_akopytov_sysbench_tree_0.5]: https://github.com/akopytov/sysbench/tree/0.5