MySQL： 7 对生产环境中的数据库进行360度无死角压测

╰+攻爆jí腚メ 2022-10-18 11:30 159阅读 0赞

### 1.压测环境 ###

压测的服务器为2核4G的Linux客户机。

这里将根据CPU、内存、磁盘IO、网络宽带等因素去测试该机器到底可以抗每秒几千甚至每秒几万的并发请求。

### 2.压测工具sysbench的安装与使用 ###

**2.1 关于sysbench**

如果要进行数据的压测，推荐使用sysbench。

它可以自动在数据库里构造出来大量的数据，并且可以模拟几千个线程并发的访问你的数据库，模拟使用各种各样的SQL语句来访问你的数据库，包括模拟出来各种事务提交到你的数据库里去，甚至可以模拟出几十万的TPS去压测你的数据库。

**2.2 在linux上安装sysbench工具**

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

如果上面可以看到sysbench的版本号，就说明安装成功了。

**2.3 安装过程中的问题**

（1）在执行 "sudo yum -y install sysbench" 命令的时候报错 “Transaction check error:”。

完整的报错记录如下：

Downloading packages:
    Running transaction check
    Running transaction test
    
    
    Transaction check error:
      file /usr/share/mysql/charsets/README from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/Index.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/armscii8.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/ascii.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp1250.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp1251.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp1256.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp1257.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp850.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp852.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/cp866.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/dec8.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/geostd8.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/greek.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/hebrew.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/hp8.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/keybcs2.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/koi8r.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/koi8u.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/latin1.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/latin2.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/latin5.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/latin7.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/macce.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/macroman.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/charsets/swe7.xml from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/czech/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/danish/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/dutch/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/english/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/estonian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/french/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/german/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/greek/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/hungarian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/italian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/japanese/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/korean/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/norwegian-ny/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/norwegian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/polish/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/portuguese/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/romanian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/russian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/serbian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/slovak/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/spanish/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/swedish/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
      file /usr/share/mysql/ukrainian/errmsg.sys from install of mariadb-libs-1:5.5.68-1.el7.x86_64 conflicts with file from package MySQL-server-5.5.52-1.el6.x86_64
    
    错误概要
    -------------

**问题分析：**CentOS的VPS中默认安装过[MariaDB][]，而在初始化sysbench时会安装mysql相关组件。如上图中所示，报错提示的是：Mariadb和MySQL的包冲突了。

**解决方法**

删除其中存在且造成冲突的包，上面冲突打印中的`MySQL-server-5.5.56-1.el7.x86_64`。

rpm -e MySQL-server-5.5.52-1.el6.x86_64

然后再次安装`sysbench`，顺利完成！

**2.4 安装MariaDB**

（1）移除存在冲突的包，不然MariaDB会安装失败。

rpm -e MySQL-client-5.5.52-1.el6.x86_64

（2）首先需要安装mariadb-server

yum install -y mariadb-server

（3）启动服务

systemctl start mariadb.service

（4）添加到开机启动

systemctl enable mariadb.service

（5）进行一些安全设置，以及修改数据库管理员密码

mysql_secure_installation

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70][]

（5）mysql给root开启远程访问权限

use mysql；
    select User,authentication_string,Host from user；
    
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';
    flush privileges;
    
    exit;

（6）连接成功：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1][]

### 3.基于sysbench构造测试表和测试数据 ###

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable prepare
    sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

上面我们构造了一个sysbench命令，给他加入了很多的参数，现在我们来解释一下这些参数，大概的意思如下：

1.  \--db-driver=mysql : 说的是他基于mysql的驱动去连接mysql数据库，如果是oracle或者sqlserver，那就是其他的数据库的驱动了
2.  \--time=300：指连续访问300秒
3.  \--threads=10：这是就是说用10个线程模拟并发访问
4.  \--report-interval=1：指每隔1秒输出一下压测情况
5.  \--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test\_user --mysql-password=test\_user: 这一大串，是说连接到哪台机器的哪个端口上的MySQL库，它的用户名和密码是什么
6.  \--mysql-db=test\_db --tables=20 --table\_size=1000000：这一串的意思，是说在test\_db这个库里，构造20个测试表，每个测试表里构造100万条测试数据，测试表的名字会是类似于sbtest1，sbtest2这个样子的
7.  oltp\_read\_write：这个是说，执行oltp数据库的读写测试
8.  \--db-ps-mode=disable：这个就是禁止ps模式
9.  最后的 prepare：意思是参照这个命令的设置去构造出来我们需要的数据库里的数据，它会自动创建20个测试表，每个表里创建100万条数据。

### 4. 对数据库进行360度的全方位测试 ###

（1）测试数据库的综合读写TPS，使用的是oltp\_read\_write模式（命令最后是run，不是prepare，意思是运行压测）:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=root --mysql-db=test_db --tables=20 --table_size=1000000 oltp_read_write --db-ps-mode=disable run
    sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

综合读写TPS压测结果：

[ 204s ] thds: 10 tps: 113.51 qps: 2325.86 (r/w/o: 1633.88/464.97/227.01) lat (ms,95%): 253.35 err/s: 0.00 reconn/s: 0.00
    [ 205s ] thds: 10 tps: 77.38 qps: 1541.49 (r/w/o: 1082.26/304.48/154.75) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
    [ 206s ] thds: 10 tps: 38.00 qps: 730.94 (r/w/o: 507.96/146.99/75.99) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00
    [ 207s ] thds: 10 tps: 125.96 qps: 2523.16 (r/w/o: 1759.41/511.83/251.92) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
    [ 208s ] thds: 10 tps: 68.68 qps: 1402.55 (r/w/o: 993.43/271.75/137.37) lat (ms,95%): 427.07 err/s: 0.00 reconn/s: 0.00
    [ 209s ] thds: 10 tps: 106.50 qps: 2108.93 (r/w/o: 1457.87/438.06/213.00) lat (ms,95%): 369.77 err/s: 0.00 reconn/s: 0.00
    [ 210s ] thds: 10 tps: 114.01 qps: 2298.24 (r/w/o: 1624.17/446.05/228.02) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
    [ 211s ] thds: 10 tps: 91.52 qps: 1760.73 (r/w/o: 1217.59/360.10/183.04) lat (ms,95%): 484.44 err/s: 0.00 reconn/s: 0.00
    [ 212s ] thds: 10 tps: 71.40 qps: 1505.33 (r/w/o: 1068.92/293.63/142.79) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00
    [ 213s ] thds: 10 tps: 153.89 qps: 3037.81 (r/w/o: 2123.47/606.56/307.78) lat (ms,95%): 211.60 err/s: 0.00 reconn/s: 0.00

（2）测试数据库的只读性能，使用的是oltp\_read\_only模式：

只读性能压测结果：

[ 25s ] thds: 10 tps: 342.93 qps: 5487.80 (r/w/o: 4801.95/0.00/685.85) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
    [ 26s ] thds: 10 tps: 259.06 qps: 4152.92 (r/w/o: 3634.81/0.00/518.11) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00
    [ 27s ] thds: 10 tps: 286.90 qps: 4599.37 (r/w/o: 4025.57/0.00/573.80) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
    [ 28s ] thds: 10 tps: 177.98 qps: 2840.74 (r/w/o: 2484.77/0.00/355.97) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00
    [ 29s ] thds: 10 tps: 166.00 qps: 2625.03 (r/w/o: 2293.03/0.00/332.00) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00
    [ 30s ] thds: 10 tps: 275.75 qps: 4409.00 (r/w/o: 3857.50/0.00/551.50) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
    [ 31s ] thds: 10 tps: 227.99 qps: 3681.02 (r/w/o: 3225.04/0.00/455.98) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
    [ 32s ] thds: 10 tps: 263.07 qps: 4191.17 (r/w/o: 3665.02/0.00/526.15) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
    [ 33s ] thds: 10 tps: 255.03 qps: 4073.57 (r/w/o: 3563.50/0.00/510.07) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
    [ 34s ] thds: 10 tps: 220.21 qps: 3538.44 (r/w/o: 3098.01/0.00/440.43) lat (ms,95%): 303.33 err/s: 0.00 reconn/s: 0.00

（3）测试数据库的删除性能，使用的是oltp\_delete模式：

数据库的删除性能压测结果：

[ 35s ] thds: 10 tps: 411.81 qps: 411.81 (r/w/o: 0.00/388.70/23.10) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00
    [ 36s ] thds: 10 tps: 290.90 qps: 290.90 (r/w/o: 0.00/273.91/16.99) lat (ms,95%): 262.64 err/s: 0.00 reconn/s: 0.00
    [ 37s ] thds: 10 tps: 486.77 qps: 486.77 (r/w/o: 0.00/454.78/31.98) lat (ms,95%): 114.72 err/s: 0.00 reconn/s: 0.00
    [ 38s ] thds: 10 tps: 296.26 qps: 296.26 (r/w/o: 0.00/282.24/14.01) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
    [ 39s ] thds: 10 tps: 735.73 qps: 735.73 (r/w/o: 0.00/696.74/38.99) lat (ms,95%): 102.97 err/s: 0.00 reconn/s: 0.00
    [ 40s ] thds: 10 tps: 659.23 qps: 659.23 (r/w/o: 0.00/609.21/50.02) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
    [ 41s ] thds: 10 tps: 623.76 qps: 623.76 (r/w/o: 0.00/579.77/43.98) lat (ms,95%): 94.10 err/s: 0.00 reconn/s: 0.00
    [ 42s ] thds: 10 tps: 516.24 qps: 516.24 (r/w/o: 0.00/488.23/28.01) lat (ms,95%): 130.13 err/s: 0.00 reconn/s: 0.00
    [ 43s ] thds: 10 tps: 578.80 qps: 578.80 (r/w/o: 0.00/531.81/46.98) lat (ms,95%): 99.33 err/s: 0.00 reconn/s: 0.00
    [ 44s ] thds: 10 tps: 285.07 qps: 285.07 (r/w/o: 0.00/264.06/21.00) lat (ms,95%): 94.10 err/s: 0.00 reconn/s: 0.00

（4）测试数据库的更新索引字段的性能，使用的是oltp\_update\_index模式：

数据库的更新索引字段的压测结果：

[ 21s ] thds: 10 tps: 279.91 qps: 279.91 (r/w/o: 0.00/264.91/15.00) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
    [ 22s ] thds: 10 tps: 249.16 qps: 249.16 (r/w/o: 0.00/233.15/16.01) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
    [ 23s ] thds: 10 tps: 397.99 qps: 397.99 (r/w/o: 0.00/358.99/39.00) lat (ms,95%): 90.78 err/s: 0.00 reconn/s: 0.00
    [ 24s ] thds: 10 tps: 216.94 qps: 216.94 (r/w/o: 0.00/202.94/14.00) lat (ms,95%): 282.25 err/s: 0.00 reconn/s: 0.00
    [ 25s ] thds: 10 tps: 209.05 qps: 209.05 (r/w/o: 0.00/196.05/13.00) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00
    [ 26s ] thds: 10 tps: 214.97 qps: 214.97 (r/w/o: 0.00/202.97/12.00) lat (ms,95%): 267.41 err/s: 0.00 reconn/s: 0.00
    [ 27s ] thds: 10 tps: 293.09 qps: 293.09 (r/w/o: 0.00/273.08/20.01) lat (ms,95%): 137.35 err/s: 0.00 reconn/s: 0.00
    [ 28s ] thds: 10 tps: 354.64 qps: 354.64 (r/w/o: 0.00/330.67/23.98) lat (ms,95%): 110.66 err/s: 0.00 reconn/s: 0.00
    [ 29s ] thds: 10 tps: 511.33 qps: 511.33 (r/w/o: 0.00/473.30/38.02) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
    [ 30s ] thds: 10 tps: 233.96 qps: 233.96 (r/w/o: 0.00/210.96/23.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00

（5）测试数据库的更新非索引字段的性能，使用的是oltp\_update\_non\_index模式：

数据库的更新非索引字段的压测结果：

[ 16s ] thds: 10 tps: 1697.43 qps: 1697.43 (r/w/o: 0.00/1565.55/131.88) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00
    [ 17s ] thds: 10 tps: 2129.98 qps: 2129.98 (r/w/o: 0.00/1999.92/130.06) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00
    [ 18s ] thds: 10 tps: 1717.73 qps: 1717.73 (r/w/o: 0.00/1575.67/142.06) lat (ms,95%): 21.89 err/s: 0.00 reconn/s: 0.00
    [ 19s ] thds: 10 tps: 919.98 qps: 919.98 (r/w/o: 0.00/851.98/68.00) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00
    [ 20s ] thds: 10 tps: 1779.23 qps: 1779.23 (r/w/o: 0.00/1639.13/140.10) lat (ms,95%): 21.50 err/s: 0.00 reconn/s: 0.00
    [ 21s ] thds: 10 tps: 1345.20 qps: 1345.20 (r/w/o: 0.00/1253.26/91.95) lat (ms,95%): 28.67 err/s: 0.00 reconn/s: 0.00
    [ 22s ] thds: 10 tps: 1700.74 qps: 1700.74 (r/w/o: 0.00/1573.69/127.06) lat (ms,95%): 21.11 err/s: 0.00 reconn/s: 0.00
    [ 23s ] thds: 10 tps: 1431.55 qps: 1431.55 (r/w/o: 0.00/1302.77/128.78) lat (ms,95%): 33.12 err/s: 0.00 reconn/s: 0.00
    [ 24s ] thds: 10 tps: 2764.02 qps: 2764.02 (r/w/o: 0.00/2552.71/211.31) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
    [ 25s ] thds: 10 tps: 608.70 qps: 608.70 (r/w/o: 0.00/554.73/53.97) lat (ms,95%): 104.84 err/s: 0.00 reconn/s: 0.00

（6）测试数据库的插入性能，使用的是oltp\_insert\_only模式：

数据库的插入性能的压测结果：

[ 15s ] thds: 10 tps: 873.01 qps: 873.01 (r/w/o: 0.00/873.01/0.00) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
    [ 16s ] thds: 10 tps: 401.94 qps: 401.94 (r/w/o: 0.00/401.94/0.00) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
    [ 17s ] thds: 10 tps: 1008.22 qps: 1008.22 (r/w/o: 0.00/1008.22/0.00) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
    [ 18s ] thds: 10 tps: 694.04 qps: 694.04 (r/w/o: 0.00/694.04/0.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
    [ 19s ] thds: 10 tps: 1078.94 qps: 1078.94 (r/w/o: 0.00/1078.94/0.00) lat (ms,95%): 48.34 err/s: 0.00 reconn/s: 0.00
    [ 20s ] thds: 10 tps: 791.34 qps: 791.34 (r/w/o: 0.00/791.34/0.00) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
    [ 21s ] thds: 10 tps: 804.79 qps: 804.79 (r/w/o: 0.00/804.79/0.00) lat (ms,95%): 81.48 err/s: 0.00 reconn/s: 0.00
    [ 22s ] thds: 10 tps: 635.64 qps: 635.64 (r/w/o: 0.00/635.64/0.00) lat (ms,95%): 84.47 err/s: 0.00 reconn/s: 0.00
    [ 23s ] thds: 10 tps: 944.86 qps: 944.86 (r/w/o: 0.00/944.86/0.00) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00
    [ 24s ] thds: 10 tps: 171.98 qps: 171.98 (r/w/o: 0.00/171.98/0.00) lat (ms,95%): 356.70 err/s: 0.00 reconn/s: 0.00

（7）测试数据库的写入性能，使用的是oltp\_write\_only模式：

数据库的写入性能的压测结果：

[ 13s ] thds: 10 tps: 66.04 qps: 416.24 (r/w/o: 0.00/272.16/144.08) lat (ms,95%): 467.30 err/s: 0.00 reconn/s: 0.00
    [ 14s ] thds: 10 tps: 190.96 qps: 1145.79 (r/w/o: 0.00/721.87/423.92) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
    [ 15s ] thds: 10 tps: 110.98 qps: 636.86 (r/w/o: 0.00/399.91/236.95) lat (ms,95%): 132.49 err/s: 0.00 reconn/s: 0.00
    [ 16s ] thds: 10 tps: 82.95 qps: 498.72 (r/w/o: 0.00/322.82/175.90) lat (ms,95%): 960.30 err/s: 0.00 reconn/s: 0.00
    [ 17s ] thds: 10 tps: 53.05 qps: 326.31 (r/w/o: 0.00/203.19/123.12) lat (ms,95%): 682.06 err/s: 0.00 reconn/s: 0.00
    [ 18s ] thds: 10 tps: 149.20 qps: 887.26 (r/w/o: 0.00/557.02/330.24) lat (ms,95%): 511.33 err/s: 0.00 reconn/s: 0.00
    [ 19s ] thds: 10 tps: 94.51 qps: 574.11 (r/w/o: 0.00/360.96/213.16) lat (ms,95%): 344.08 err/s: 0.00 reconn/s: 0.00
    [ 20s ] thds: 10 tps: 158.92 qps: 954.54 (r/w/o: 0.00/593.71/360.83) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
    [ 21s ] thds: 10 tps: 99.02 qps: 586.13 (r/w/o: 0.00/372.08/214.05) lat (ms,95%): 926.33 err/s: 0.00 reconn/s: 0.00
    [ 22s ] thds: 10 tps: 109.95 qps: 687.68 (r/w/o: 0.00/441.79/245.88) lat (ms,95%): 475.79 err/s: 0.00 reconn/s: 0.00

（7）执行cleanup命令，清理数据

cleanup 会清除掉sysbench建来压测的20张表以及表数据。

### 5.压测结果分析 ###

按照上面的命令，是让他每隔1秒都会输出一次压测报告的，此时他每隔一秒会输出类似下面的一段东西：

[ 22s ] thds: 10 tps: 109.95 qps: 687.68 (r/w/o: 0.00/441.79/245.88) lat (ms,95%): 475.79 err/s: 0.00 reconn/s: 0.00

这一段内容是第22s输出的一段压测统计报告，然后是其他的一些统计字段：

> thds: 10，这个意思是有10个线程在压测
> 
> tps: 109.96，这个意思是每秒执行了109.96个事务
> 
> qps: 687.68，这个意思是每秒可以执行687.68个请求。（r/w/o：0.00/441.79/245.88），这个意思是说，在每秒687.68个请求中，有0.00个读请求，有441.79个写请求，有245.88个其他的请求，这是对QPS进行了拆解。
> 
> lat（ms, 95%）：475.79，这个意思是说，95%的请求的延迟都在475.79毫秒以下
> 
> err/s：0.00 reconn/s：0.00，这两个的意思是，每秒有0个请求是失败的，发生了0次网络重连。

压测结果会根据每个人的机器的性能不同有很大的差距。性能高的机器并发压测，用100个线程跑可能达到每秒的TPS有上千个；性能低的，可能压测出来的TPS才二三十个，QPS才几百个。

在完成压测之后，最后会显示一个**总的压测报告**，解释如下：

SQL statistics:
        queries performed:
            read:             0  // 这是说在压测期间没有执行过读请求
            write:            138128 // 这是说在压测期间执行了13万多次的写请求
            other:            69064  // 这是说在压测期间执行了6万多次的其他请求
            total:            207192 // 这是说一共执行了20万多次的请求
        transactions:         34532  (115.09 per sec.) // 这是说一共执行了3万多个事务，每秒执行115多个事务
        queries:              207192 (690.53 per sec.) // 这是说一共执行了20万多次的请求，每秒执行690+请求
        ignored errors:       0      (0.00 per sec.)
        reconnects:           0      (0.00 per sec.)
    
    // 下面是说，一共执行了300s的压测，执行了3万+的事务
    General statistics:
        total time:                          300.0446s
        total number of events:              34532
    
    Latency (ms):
             min:                       0.63 // 请求中延迟最小的是0.63ms
             avg:                      86.88 // 所有请求平均延迟是86.88ms
             max:                    2699.73 // 延迟最大的请求是2699.73ms
             95th percentile:         325.98 // 95%的请求延迟都在325.98ms以内
             sum:                 3000118.57 // 总的延迟耗时
    
    Threads fairness:
        events (avg/stddev):           3453.2000/83.49
        execution time (avg/stddev):   300.0119/0.02

[MariaDB]: https://cloud.tencent.com/product/tdsql?from=10680
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70]: /images/20221005/c68069dc3830402e9073853a9be732ae.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjQwNTY3MA_size_16_color_FFFFFF_t_70 1]: /images/20221005/81f2616993464f209301307b09e7e126.png