MySQL 压测与结果分析

文章目录

- 说明
- 1. 安装部署
- - 1.1 二进制包
  - 1.2 源码包
- 2. 服务器性能测试
- - 2.1 CPU
  - 2.2 内存
  - 2.3 磁盘
- 3. MySQL 基准测试
- - 3.1 参数解析
  - 3.2 压测命令
  - 3.3 输出解读
  - 3.4 结果分析

说明

Sysbench 是一个开源的多线程基准测试工具，也是目前使用最多的 MySQL 压力测试工具。本篇文章将介绍如何使用它完成数据库压力测试。

1. 安装部署

1.1 二进制包

使用下方命令，一键完成二机制安装。

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

使用该方式安装，压测脚本默认在 /usr/share/sysbench/ 目录下。

1.2 源码包

源码下载地址：Sysbench Github

# 安装依赖环境
yum -y install make automake libtool pkgconfig libaio-devel# 支持 MySQL 安装下方依赖
yum -y install mysql-devel openssl-devel# 支持 PostgreSQL 需要安装下方依赖
yum -y install postgresql-devel

# 源码安装
wget https://github.com/akopytov/sysbench/archive/refs/tags/1.0.20.tar.gz# 解压
tar -zxvf sysbench-1.0.20.tar.gz
cd sysbench-1.0.20
./autogen.sh
./configure
make -j
make install

源码安装完成后，压测脚本默认在 /usr/local/share/sysbench 目录下。

2. 服务器性能测试

Sysbench 不仅可以测试数据库的性能，还可以测试服务器的性能。其中包含服务器的 CPU、内存和磁盘 I/O 性能。

2.1 CPU

sysbench cpu --cpu-max-prime=20000 --threads=32 run

测试算法：sysbench 是通过计算 --cpu-max-prime 范围内的质数（prime number）数量来衡量 CPU 的计算能力。

–cpu-max-prime：质数生成数量的上限。
–threads：并发线程的数量。
–time：运行时长，默认为运行 10 秒。
–events：若为 100 则表示计算 100 event 后自动结束，默认不限制。

看到这里，想必大家都明白了，比较两台服务器的计算性能，可以根据单位时间内，生成 event 的数量来衡量高低。

# 测试 cpu 性能，8 个并发线程运行 15 秒，质数生成上限是 2w
sysbench cpu --time=15 --cpu-max-prime=20000 --threads=8 run

输出中，重点关注 events per second 的结果。值越大，代表 CPU 计算能力越强。

CPU speed:events per second:  1480.62

2.2 内存

sysbench memory --memory-block-size=1M --memory-total-size=100G --num-threads=1 run

内存测试，支持的选项如下。

–memory-block-size：内存块的大小。默认为 1KB。推荐为 1M。
–memory-total-size：要传输的数据总大小。默认为 100G。
–memory-scope：内存访问的范围，可指定 Global 或 local。默认为 local。
–memory-hugetlb：是否从 HugeTLB 池中分配内存，默认为 off。
–memory-oper：内存操作类型，可指定 write、read 或 none。默认为 write。
–memory-access-mode：内存访问模式。可指定为 seq（顺序访问）或 rnd（随机访问）默认为 seq。

通过该工具，可以测出内存的写入或读取速率，用该速率衡量内存的性能。

# 测试命令，每次操作 1M，共 100G
sysbench memory --memory-block-size=1M --memory-total-size=100G --num-threads=1 run

输出中，重点关注以下部分。14263.70 MiB/sec 表示内存中顺序写入的速率。

102400.00 MiB transferred (14263.70 MiB/sec)

2.3 磁盘

磁盘的 I/O 测试，需要三个步骤：准备文件、测试、删除文件。以下是相关参数。

–file-num：测试需要创建的文件数。默认为 128.
–file-block-size：操作块的大小。默认 16KB。
–file-total-size：需要创建文件的总大小。默认为 2GB。
–file-test-mode：测试模式，可指定为 seqwr（顺序写）、seqrewr（顺序重写）、seqrd（顺序读）、rndrd（随机读）、rndwr（随机写）或 rndrw（随机读写）。
–file-async-backlog：每个线程异步 I/O 队列长度，默认为 128。
–file-extra-flags：打开文件时的指定标志，可指定为 sync、dsync 或 direct 默认为空，既没有指定。
–file-sync-freq：指定持久化操作的频率，默认为 100。
–file-sync-all：每次写入都执行持久化，默认为 on。
–file-sync-end：在测试结束时执行持久化操作，默认为 on。
–file-rw-ratio：混合测试中读写的比例。

# 准备文件
sysbench fileio --file-num=1 --file-total-size=5G --file-test-mode=rndrw prepare# 执行测试，测试模式是 随机读写
sysbench fileio --file-num=1 --file-total-size=5G --file-test-mode=rndrw run# 清理文件
sysbench fileio --file-num=1 --file-total-size=5G --file-test-mode=rndrw cleanup

输出中，reads/s 加上 writes/s 就是 IOPS。read, MiB/s 加上 written, MiB/s 就是常说的吞吐量。

File operations:reads/s:                      131.98writes/s:                     87.95fsyncs/s:                     2.30Throughput:read, MiB/s:                  2.06written, MiB/s:               1.37

3. MySQL 基准测试

对于新上线的业务而言，如果有新的服务器，新的环境，那么资源承载压力是否与预计匹配，需要用数据说话。

3.1 参数解析

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='xxxxx' --mysql-db=sbtest --tables=30 --table-size=10000 --threads=30 prepare

oltp_read_write：测试模型，对应的是 /usr/local/share/sysbench/oltp_read_write.lua 脚本。
–mysql-xxx：这部分属于连接参数，见文知意。
–mysql-db：压测使用的数据库，需要提前创建好。
–tables：压测使用表的数量。
–table-size：单表大小，默认 10000 行。
–threads：并发工作线程数。
–time：压测多长时间。
–report-interval：每 x 秒输出一次结果，默认不输出。

3.2 压测命令

MySQL 数据库压测需要执行四个步骤，分别是造数、预热、压测、清理环境。

# 造数
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='xxxxx' --mysql-db=sbtest --tables=30 --table-size=10000 --threads=30 prepare

# 预热
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='xxxxx' --mysql-db=sbtest --tables=30 --table-size=10000 --threads=30 prewarm

# 压测 30 秒
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='xxxxx' --mysql-db=sbtest --tables=30 --table-size=10000 --threads=30 --time=30 --report-interval=10 run

# 清理
sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='xxxxx' --mysql-db=sbtest --tables=30 --table-size=10000 --threads=30 cleanup

3.3 输出解读

压测输出结果如下。

Threads started!
# thds：线程数 tps:每秒事务数 
# qps:每秒操作数 r 读 w 写 o 其他操作 
# lat 表示延迟 95% 查询等于或小于该值，单位毫秒 
# err/s 每秒错误数 reconn/s  每秒重试数
[ 10s ] thds: 30 tps: 1014.35 qps: 20334.80 (r/w/o: 14239.59/4063.50/2031.70) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 30 tps: 946.60 qps: 18930.09 (r/w/o: 13251.17/3785.82/1893.11) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 30 tps: 1043.73 qps: 20885.39 (r/w/o: 14616.88/4180.94/2087.57) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
# 统计信息
SQL statistics:queries performed:read:                            421274 # 读操作的数量write:                           120364 # 写操作的数量other:                           60182 # 其他操作的数量total:                           601820 # 总请求数量transactions:                        30091  (945.30 per sec.) # 总事务数量（每秒事务数）queries:                             601820 (18906.09 per sec.) # 总请求数（每秒请求数）ignored errors:                      0      (0.00 per sec.) # 总错误数（每秒错误数）reconnects:                          0      (0.00 per sec.) # 重试数（每秒重试数）# 全局信息
General statistics:total time:                          31.8294s # 压测使用的时间total number of events:              30091 # 压测使用的 event 数量，在 oltp_read_write 脚本中，一个 event 就是一个失误# 耗时
Latency (ms):min:                                    4.89 # 最小耗时avg:                                   31.38 # 平均耗时max:                                 1926.37 # 最大耗时95th percentile:                       52.89 # 95% 执行耗时sum:                               944296.08 # 总耗时
# 线程信息
Threads fairness:events (avg/stddev):           1003.0333/15.84 # 平均每个线程，执行事件的数量# stddev 是标准差，值越小，代表结果越稳定execution time (avg/stddev):   31.4765/0.36 # 平均每个线程执行的时间

输出中，重点关注的指标是 QPS 和 TPS 反映了数据库系统的吞吐量，值越大越好。95th percentile 表示请求耗时，值越小越好。在一定范围内，并发线程越大，TPS 和 QPS 也会越大。需要结合服务器监控，调整线程数，最终得到一个合理的结果。

3.4 结果分析

如 3.3 小节，sysbench 输出的结果只适用于阅读，如果想画统计图，让结果更有说服力的时候，就需要加工数据。–report-interval 参数每段时间输出结果，可以用程序进行解析，然后将数据保存为 CSV 便于分析画图。

[ 10s ] thds: 30 tps: 1014.35 qps: 20334.80 (r/w/o: 14239.59/4063.50/2031.70) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00

使用 vi result_collect.py 复制代码粘贴进去。

# -*- coding: utf-8 -*-
import sysprint('=' * 30)
print('times,thds,tps,qps,r,w,o,lat,err,reconn')
for line in sys.stdin:if line[0] == '[':times = int(str(line[line.rfind('[') + 1:line.find(']')]).replace('s', '').strip())thds = int(str(line[line.find('thds:') + 5: line.find('tps:')]).strip())tps = float(str(line[line.find('tps:') + 5: line.find('qps:')]).strip())qps = float(str(line[line.find('qps:') + 5: line.find('(r/w/o:')]).strip())rwo = str(line[line.find('(r/w/o:') + 8: line.find(' lat') - 1]).split('/')r = float(rwo[0])w = float(rwo[1])o = float(rwo[2])lat = float(str(line[line.find('95%):') + 5: line.find('err/s:')]).strip())err = float(str(line[line.find('err/s:') + 6:line.find('reconn/s:')]).strip())reconn = float(str(line[line.find('econn/s:') + 8: -1]).strip())res = [times, thds, tps, qps, r, w, o, lat, err, reconn]print(','.join([str(i) for i in res]))

执行压测时，使用下方命令。

sysbench oltp_read_write --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password='xxxx' --mysql-db=sbtest --tables=30 --table-size=10000 --threads=30 --time=30 --report-interval=10  run | python result_collect.py

得到的结果：

times, thds, tps, qps, r, w, o, lat, err, reconn
10, 30, 1052.02, 21083.05, 14763.14, 4212.87, 2107.04, 52.89, 0.0, 0.0
20, 30, 1067.54, 21349.05, 14945.12, 4268.85, 2135.07, 50.11, 0.0, 0.0
30, 30, 958.0, 19175.78, 13419.48, 3840.3, 1916.0, 64.47, 0.0, 0.0