MySQL - 测试 - sysbench 压力测试

港控/mmm° 2023-05-30 15:56 39阅读 0赞

## 前言 ##

压力测试是针对系统的一种性能测试，但是测试数据与业务逻辑无关，更加简单直接的测试读写性能。

压力测试有4个重要测试指标：TPS、QPS、响应时间和并发量

*  QPS是每秒钟处理完的请求数量
 *  TPS是每秒钟处理完的事物数量
 *  响应时间是一次请求的平均处理时间
 *  并发量是系统能同时处理的请求数量

## 使用说明 ##

基本语法

sysbench script [options] [command]

连接信息参数

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>mysql-host</td> 
   <td>IP地址</td> 
  </tr> 
  <tr> 
   <td>msql-port</td> 
   <td>端口</td> 
  </tr> 
  <tr> 
   <td>mysql-user</td> 
   <td>用户名</td> 
  </tr> 
  <tr> 
   <td>mysql-password</td> 
   <td>密码</td> 
  </tr> 
 </tbody> 
</table>

执行参数

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>oltp-test-mode</td> 
   <td>执行模式（simple、nontrx、complex）</td> 
  </tr> 
  <tr> 
   <td>oltp-tables-count</td> 
   <td>测试表的数量</td> 
  </tr> 
  <tr> 
   <td>oltp-table-size</td> 
   <td>测试表的记录数</td> 
  </tr> 
  <tr> 
   <td>threads</td> 
   <td>并发连接数</td> 
  </tr> 
  <tr> 
   <td>time</td> 
   <td>测试执行时间（秒）</td> 
  </tr> 
  <tr> 
   <td>report-interval</td> 
   <td>生成报告单的间隔时间（秒）</td> 
  </tr> 
 </tbody> 
</table>

执行模式

<table> 
 <thead> 
  <tr> 
   <th>模式</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>simple</td> 
   <td>只测查询，不测写入</td> 
  </tr> 
  <tr> 
   <td>nontrx</td> 
   <td>测试无事务的增删改查</td> 
  </tr> 
  <tr> 
   <td>complex</td> 
   <td>有事务的增删改查</td> 
  </tr> 
 </tbody> 
</table>

\[command\] 命令

<table> 
 <thead> 
  <tr> 
   <th>命令名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>prepare</td> 
   <td>准备测试数据</td> 
  </tr> 
  <tr> 
   <td>run</td> 
   <td>执行测试</td> 
  </tr> 
  <tr> 
   <td>cleanup</td> 
   <td>清除测试数据</td> 
  </tr> 
 </tbody> 
</table>

## 环境 ##

更换Yum源

`阿里云`

# 阿里云
    wget http://mirrors.aliyun.com/repo/Centos-7.repo
    # 163
    wget http://mirrors.163.com/.help/CentOS7-Base-163.repo
    # 改名
    mv xxx  CentOS-Base.repo
    # 放到etc/yum.repos.d 目录,最好备份一下原来的文件
    cp CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo

在线安装

curl -s https://packagecloud.io/install/
    repositories/akopytov/sysbench/script.rpm.sh | sudo bash

yum -y install sysbench

本地安装

*  下载压缩文件
    
    [https://][https][codeload.github.com/akopytov/sysbench/zip/1.0][https]
 *  安装依赖包
    
        yum install -y automake libtool
        yum install -y mysql-devel
 *  执行安装
    
        #cd sysbench
        ./autogen.sh 
        ./configure 
        make
        make install
 *  验证是否安装
    
        sysbench --version

## 使用 ##

创建 schema `名字是规定好的，不能随便创建`

create schema sbtest;

如果是编译安装的话修改下目录

# 在线安装的路径
    sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua
    # 离线安装
    sysbench  /home/sysbench-1.0/tests/include/oltp_legacy/oltp.lua

准备测试库

sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-tables-count=10 --oltp-table-size=100000 prepare

执行测试

`建议测试24小时以上，这里只测试5分钟`

sysbench  /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --threads=10 --time=300 --report-interval=10 run >> /home/vagrant/mysysbench.log

清理数据

sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-tables-count=10 cleanup

返回结果

sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
    
    Running the test with following options:
    Number of threads: 32
    Report intermediate results every 10 second(s)
    Initializing random number generator from current time
    
    
    Initializing worker threads...
    
    Threads started!
    
    [ 10s ] thds: 32 tps: 266.21 qps: 5355.17 (r/w/o: 3753.19/1066.56/535.43) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
    [ 20s ] thds: 32 tps: 279.40 qps: 5590.16 (r/w/o: 3914.18/1116.99/559.00) lat (ms,95%): 127.81 err/s: 0.00 reconn/s: 0.00
    [ 30s ] thds: 32 tps: 273.60 qps: 5470.54 (r/w/o: 3829.26/1094.09/547.19) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
    [ 40s ] thds: 32 tps: 258.20 qps: 5169.85 (r/w/o: 3620.43/1033.01/516.40) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
    [ 50s ] thds: 32 tps: 281.80 qps: 5627.48 (r/w/o: 3936.89/1127.20/563.40) lat (ms,95%): 127.81 err/s: 0.00 reconn/s: 0.00
    [ 60s ] thds: 32 tps: 279.50 qps: 5595.15 (r/w/o: 3917.53/1118.41/559.20) lat (ms,95%): 127.81 err/s: 0.00 reconn/s: 0.00
    [ 70s ] thds: 32 tps: 278.30 qps: 5565.72 (r/w/o: 3895.85/1113.38/556.49) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
    [ 80s ] thds: 32 tps: 262.40 qps: 5254.69 (r/w/o: 3679.89/1049.90/524.90) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
    [ 90s ] thds: 32 tps: 277.50 qps: 5541.91 (r/w/o: 3877.80/1109.20/554.90) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
    [ 100s ] thds: 32 tps: 276.20 qps: 5522.59 (r/w/o: 3864.99/1105.50/552.10) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
    [ 110s ] thds: 32 tps: 282.40 qps: 5643.40 (r/w/o: 3949.30/1128.90/565.20) lat (ms,95%): 123.28 err/s: 0.00 reconn/s: 0.00
    [ 120s ] thds: 32 tps: 275.30 qps: 5514.88 (r/w/o: 3862.76/1101.62/550.51) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
    SQL statistics:
        queries performed:
            read:                            461174   #读总数
            write:                           131764    #写总数（update、insert、delete 语句数量）
            other:                           65882     #其他操作总数(SELECT、INSERT、UPDATE、DELETE 之外的操作，例如 COMMIT 等)
            total:                           658820     #全部总数
        transactions:                        32941  (274.10 per sec.)     #TPS，总事务数（每秒事务数）
        queries:                             658820 (5482.05 per sec.)     #QPS，读写总数(每秒读写次数)
        ignored errors:                      0      (0.00 per sec.)   #忽略的错误数
        reconnects:                          0      (0.00 per sec.)
    
    General statistics:
        total time:                          120.1753s   #--time 指定的压测时间；
        total number of events:              32941   #总发生事件数，一般与 transactions 相同；
    
    Latency (ms):
             min:                                   11.51   #最小耗时
             avg:                                  116.63   #平均耗时
             max:                                  515.65   #最大耗时
             95th percentile:                      144.97  # 95%平均耗时
             sum:                              3841840.74   #总响应时间
    
    Threads fairness:
        events (avg/stddev):           1029.4062/5.26
        execution time (avg/stddev):   120.0575/0.04

## 总结 ##

*  测试建议24小时以上
 *  数据表建议不低于 10个，单表数据量不低于 **500万行**，如果是配置了SSD或者PCIESSD的话，则建议单表数据量最少不低于1亿行

[https]: https://codeload.github.com/akopytov/sysbench/zip/1.0