MySQL性能压测工具SysBench详解（非常详细）
概述
背景
最近需要给不同的客户部署数据库，各自的预算不一样，购买的服务器配置也不一样。那么我们就需要对其数据库的支撑能力进行一定测试，以保证满足业务真是的需要
数据库性能指标
| 指标 | 英文含义 | 说明 |
|---|---|---|
| QPS | Query Per Second | 数据库每秒执行的SQL数，包含insert、select、update、delete等。 |
| TPS | Transaction Per Second | 数据库每秒执行的事务数，每个事务中包含18条SQL语句。 |
sysbench简介
sysbench 支持以下几种测试模式：
1、CPU 运算性能
2、磁盘 IO 性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX 线程性能–互斥基准测试
6、数据库性能(OLTP 基准测试)
安装
二进制方式
RHEL/CentOS:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
Debian/Ubuntu
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bash
sudo apt -y install sysbench
优化内核，提升SysBench Client
执行如下命令配置SysBench Client，使内核可以使用所有的CPU处理数据包（默认设置为使用2个CPU），同时减少CPU之间的上下文切换。
sudo sh -c 'for x in /sys/class/net/eth0/queues/rx-*; do echo ffffffff>$x/rps_cpus; done'
sudo sh -c "echo 32768 > /proc/sys/net/core/rps_sock_flow_entries"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-0/rps_flow_cnt"
sudo sh -c "echo 4096 > /sys/class/net/eth0/queues/rx-1/rps_flow_cnt"
其中：ffffffff表示使用32个CPU（1个f表示4个CPU）。请根据实际配置修改，例如ECS为8核CPU，则输入ff。
快速体验
以下命令可以输出各个检测项的帮助文档
# IO
sysbench --test=fileio help
# CPU
sysbench --test=cpu help
# 内存
sysbench --test=memory help
# 线程
sysbench --test=threads help
# 互斥性能
sysbench --test=mutex help
# 事务处理
sysbench --test=oltp help
格式
通用格式：
sysbench [options]... [testname] [command]
| 参数 | 说明 | 可选值 |
|---|---|---|
| testname | 测试项 | 可以是 fileio, memory, cpu, threads, mutex 等测试项，也可以是工具或者自定义的lua脚本。PS：没有这一项，直接执行sysbench，cli会等待输入。 |
| command | 命令 | prepare：执行测试之前的预备操作，如 创建文件，填充数据等；run：运行测试；cleanup：测试结束后清空数据； |
| help | 帮助 | 展示测试的使用方式信息 |
也可以使用sysbench --help 展示所有使用手册。
内置lua脚本
在/usr/share/sysbench目录下有常用操作的lua脚本，要自定义脚本实现特定功能时，可参考这些脚本。
bulk_insert.lua
oltp_common.lua
oltp_delete.lua
oltp_insert.lua
oltp_point_select.lua
oltp_read_only.lua
oltp_read_write.lua
oltp_update_index.lua
oltp_update_non_index.lua
oltp_write_only.lua
select_random_points.lua
select_random_ranges.lua
使用
文件IO
磁盘IO性能测试，主要查看请求数(request)和总体的吞吐量(total)。
| 参数 | 说明 | 默认 |
|---|---|---|
| –file-num=N | 创建文件的数量 | 默认值：128。 |
| –file-block-size=N | 每次IO操作的block大小 | 默认值：16K。 |
| –file-total-size=SIZE | 所有文件大小总和 | 2G |
| –file-test-mode=STRING | 测试模式： | |
| seqwr(顺序写), seqrewr(顺序读写), seqrd(顺序读), rndrd(随机读), rndwr(随机写), rndrw(随机读写)。 | ||
| –file-io-mode=STRING | 文件操作模式：sync(同步),async(异步),mmap(快速map映射) | sync |
| –file-async-backlog=N | 每个线程排队的异步操作数 | 128 |
| –file-extra-flags=[LIST,…] | 使用额外的标志符来打开文件{sync,dsync,direct} | 默认值空 |
| –file-fsync-freq=N | 在完成N次请求之后，执行fsync()，0表示不使用fsync。 | |
| fsync主要是同步磁盘文件，因为可能有系统和磁盘缓冲的关系 | 100 | |
| –file-fsync-all[=on、off] | 每次写操作后执行fsync() | |
| 每次执行写操作后就执行一次fsync | off | |
| –file-fsync-end[=on、off] | 测试结束后执行fsync() | on |
| –file-fsync-mode=STRING | 使用fsync或fdatasync方法进行同步 | |
| 文件同步函数的选择，同样是和API相关的参数，由于多个操作系统对于fdatasync支持不同，因此不建议使用fdatasync。 | fsync | |
| –file-merged-requests=N | 尽可能的合并N个IO请求数，0表示不合并 | 0 |
| –file-rw-ratio=N | 测试时候的读写比例 | 1.5(即3:2) |
预备测试
sysbench --test=fileio --file-num=2 --file-block-size=1024 --file-total-size=1G prepare
执行测试
1、测试顺序读写，请求10000次，如果不能在30秒内完成，测试结束
sysbench fileio
--file-num=2
--file-block-size=1024
--file-total-size=1G
--file-test-mode=seqrewr
--time=30
--events=10000
--threads=4
--file-fsync-freq=200
--file-extra-flags=direct run
2、测试随机读写，请求10000次，如果不能在30秒内完成，测试结束
sysbench --test=fileio
--file-num=2
--file-block-size=1024
--file-total-size=1G
--file-test-mode=rndrw
--time=30
--events=10000
--threads=4
--file-fsync-freq=200
--file-extra-flags=direct run
3、测试随机读，请求10000次，如果不能在30秒内完成，测试结束
sysbench --test=fileio
--file-num=2
--file-block-size=1024
--file-total-size=1G
--file-test-mode=rndrd
--time=30
--events=10000
--threads=4
--file-fsync-freq=200
--file-extra-flags=direct run
清理文件
sysbench --test=fileio --num-threads=4 --file-total-size=1G --file-test-mode=rndrw cleanup
结果说明
### 版本说明
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
### 线程数
Number of threads: 4
Initializing random number generator from current time
Extra file open flags: directio
### 文件数、单个文件大小
2 files, 5GiB each
### 文件总大小
10GiB total file size
### 单次最大I/O操作block 10k
Block size 10KiB
### I/O读取请求次数
Number of IO requests: 10000
### 读写测试比例 3/2：1.5
Read/Write ratio for combined random IO test: 1.50
### 200次请求会执行记录数据
Periodic FSYNC enabled, calling fsync() each 200 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
### 文件操作
File operations:
reads/s: 1411.72
writes/s: 941.22
fsyncs/s: 25.19
### 吞吐量
Throughput:
read, MiB/s: 13.79
written, MiB/s: 9.19
### 总统计
General statistics:
total time: 4.2069s
total number of events: 10000
### fileio 磁盘测试，在4秒范围内进行随机读写这个行为一共进行了10000次，实际执行时间为4.2069秒
### 潜在数据
Latency (ms):
min: 0.04
avg: 1.68
max: 45.76
95th percentile: 3.96
sum: 16810.76
### 这部分数据应该统计的是线程真正执行的时间，总共16810.76ms, 单次执行最少时间为0.04ms,
### 最多时间为45.76ms, 平均时间为1.68ms, 95%次的执行时间在3.96ms左右;
Threads fairness:
events (avg/stddev): 2500.0000/26.24
execution time (avg/stddev): 4.2027/0.00
### 归纳总结，线程执行时间为4.2027s, 执行平均次数为2500次,上下差为：26.24
CPU测试
选项
sysbench的cpu测试是在指定时间内，循环进行素数计算。
素数（也叫质数）就是从1开始的自然数中，无法被整除的数，比如2、3、5、7、11、13、17等。编程公式：对正整数n，如果用2到根号n之间的所有整数去除，均无法整除，则n为素数。
| 选项 | 说明 | 默认值 |
|---|---|---|
| –cpu-max-prime | 素数生成数量的上限 | 默认值为 10000 |
| –threads | 线程数 | 默认值为 1 |
| –time | 运行时长，单位秒 | 默认值为10 ，如果时间还有剩就再进行一轮素数计算，直到时间耗尽。每完成一轮就叫一个 event 。相同时间，比较的是谁完成的event多 |
| –events | event上限次数 | 默认值为0，则表示不限event次数。相同event次数，比较的是谁用时更少 |
测试
sysbench cpu --cpu-max-prime=20000 --threads=2 run
结果
[root@Reseach sysbench-test]# sysbench cpu --cpu-max-prime=20000 --threads=2 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 2 // 指定线程数为2
Initializing random number generator from current time
Prime numbers limit: 20000 // 每个线程产生的素数上限均为2万个
Initializing worker threads...
Threads started!
CPU speed:
events per second: 1955.47 // 所有线程每秒完成了650.74次event
General statistics:
total time: 10.0006s // 共耗时10秒
total number of events: 19559 // 10秒内所有线程一共完成了6510次event
Latency (ms):
min: 0.87 // 完成1次event的最少耗时3.03秒
avg: 1.02 // 所有event的平均耗时3.07毫秒
max: 1.71 // 完成1次event的最多耗时3.27毫秒
95th percentile: 1.67 // 95%次event在3.13秒毫秒内完成
sum: 19995.36 // 每个线程耗时10秒，2个线程叠加耗时就是20秒
Threads fairness:
events (avg/stddev): 9779.5000/6.50 // 平均每个线程完成3255次event，标准差为44
execution time (avg/stddev): 9.9977/0.00 // 每个线程平均耗时10秒，标准差为0
如果有2台服务器进行CPU性能对比，当素数上限和线程数一致时：
- 相同时间，比较event
- 相同event，比较时间
- 时间和event都相同，比较stddev(标准差)
线程性能测试
选项
| 选项 | 说明 | 默认值 |
|---|---|---|
| –thread-yields=N | 每个请求产生多少个线程 | 1000 |
| –thread-locks=N | 每个线程的锁的数量 | 8 |
测试
sysbench --test=threads --num-threads=4 --thread-yields=12 --thread-locks=2 run
结果
General statistics:
total time: 10.0001s
total number of events: 2357161
Latency (ms):
min: 0.00
avg: 0.02
max: 18.44
95th percentile: 0.05
sum: 39356.63
Threads fairness:
events (avg/stddev): 589290.2500/577.33
execution time (avg/stddev): 9.8392/0.00
互斥性能测试
Mutex请求的性能与CPU主频及物理CPU个数有关
选项
| 选项 | 说明 | 默认值 |
|---|---|---|
| –mutex-num=N | 互斥数组的总大小 | 4096 |
| –mutex-locks=N | 每个线程要执行的互斥锁数 | 50000 |
| –mutex-loops=N | 在互斥锁之外执行的空循环数 | 10000 |
测试
sysbench mutex --mutex-num=2048 --mutex-locks=5000 --mutex-loops=5000 run
结果
General statistics:
total time: 0.0075s
total number of events: 1
Latency (ms):
min: 7.48
avg: 7.48
max: 7.48
95th percentile: 7.43
sum: 7.48
Threads fairness:
events (avg/stddev): 1.0000/0.00
execution time (avg/stddev): 0.0075/0.00
内存性能测试
选项
| 选项 | 说明 | 默认值 |
|---|---|---|
| –memory-block-size=SIZE | 测试时内存块大小。 | 1K |
| –memory-total-size=SIZE | 传输数据的总大小。 | 100G |
| –memory-scope=STRING | 内存访问范围{global,local} | global |
| –memory-hugetlb=[on、off] | 从HugeTLB池内存分配 | off |
| –memory-oper=STRING | 内存操作类型。{read, write, none} | write |
| –memory-access-mode=STRING | 存储器存取方式{seq,rnd} | seq |
测试
sysbench --test=memory
--threads=2
--events=10000
--memory-total-size=1G
--memory-block-size=8K
--memory-oper=read run
sysbench --test=memory
--threads=2
--events=100000
--memory-total-size=50G
--memory-block-size=8K
--memory-oper=write run
数据库OLTP
选项说明
| 选项 | 描述 | 默认值 |
|---|---|---|
| –threads | 工作线程数 | 1 |
| –events | 总的请求数，默认为0 表示无限制 | 0 |
| –time | 执行时间（秒），0表示无限制 | 0 |
| –warmup-time | 预热时间，可以实现在 CPU/database/page/caches 预热之后再进行统计，这样得到的数据指标更准确 | 0 |
| –rate | TPS，事务数。0表示无限制 | 0 |
| –thread-init-timeout | 工作线程初始化的等待时间（秒） | 30 |
| –thread-stack-size | 每一线程栈大小 | 32k |
| –report-interval | 多少秒输出一次报告，0表示禁止立即报告 | 0 |
| –debug | 调试模式 | off |
| –validate | 在可能的情况下对测试结果进行验证 | off |
| –help | 打印有关常规语法或指定测试的帮助，然后退出 | off |
| –verbosity | 日志级别，0-致命错误，5-调试信息 | 4 |
| –percentile | sysbench衡量所有已处理请求的执行时间，以显示统计信息，如最小、平均和最大执行时间。 | 95 |
| –luajit-cmd | 执行LuaJIT控制命令 |
随机数生成算法
| 选项 | 描述 | 默认值 |
|---|---|---|
| –rand-type | 默认情况下使用随机数分布{uniform，gaussian，special，pareto，zipfian}。基准脚本可以选择使用默认分布，也可以明确指定，即覆盖默认分布。 | special |
| –rand-seed | 随机数生成器的种子。当为0时，当前时间用作RNG种子。 | 0 |
| –rand-spec-iter | 特殊分布的迭代次数 | 12 |
| –rand-spec-pct | “特殊”值将属于特殊分布的整个范围的百分比 | 1 |
| –rand-spec-res | 用于特殊分布的“特殊”值百分比 | 75 |
| –rand-pareto-h | 帕累托分布的形状参数 | 0.2 |
| –rand-zipfian-exp | zipfian分布的形状参数 | 0.8 |
支持的lua脚本
| 选项 | 说明 | |
|---|---|---|
| oltp_read_only | 只读测试 | |
| oltp_read_write | 读写测试 | |
| oltp_insert | 简单插入测试 | |
| bulk_insert | 批量插入测试 | |
| oltp_delete | delete删除测试 | |
| oltp_update_index | 带索引的更新测试 | |
| oltp_update_non_index | 不带索引的更新测试 | |
| oltp_point_select | 等值查询测试 | |
| select_random_points | 随机等值查询测试 | |
| select_random_ranges | 随机范围查询测试 |
脚本参数
| 选项 | 说明 | 默认值 |
|---|---|---|
| –auto_inc[=on/off] | 使用 AUTO_INCREMENT 列作为主键（对于 MySQL），或者它在其他 DBMS 中的替代项。禁用时，使用客户端生成的 ID | [on] |
| –create_secondary[=on/off] | 除了 PRIMARY KEY 创建二级索引 | [on] |
| –delete_inserts=N | 每个事务的 DELETE/INSERT 组合数 | [1] |
| –distinct_ranges=N | 每个事务的 SELECT DISTINCT 查询数 | [1] |
| –index_updates=N | 每个事务的 UPDATE 索引查询数 | [1] |
| –mysql_storage_engine=STRING | 存储引擎，如果使用 MySQL | [innodb] |
| –non_index_updates=N | 每个事务的 UPDATE 非索引查询数 | [1] |
| –order_ranges=N | 每个事务的 SELECT ORDER BY 查询数 | [1] |
| –pgsql_variant=STRING | 使用 PostgreSQL 驱动程序运行时使用此 PostgreSQL 变体。当前唯一支持的变体是“redshift”。启用后，create_secondary 自动禁用，delete_inserts 设置为 0 | |
| –point_selects=N | 每个事务的点 SELECT 查询数 | [10] |
| –range_selects[=on/off] | 启用/禁用所有范围 SELECT 查询 | [on] |
| –range_size=N | 范围 SELECT 查询的范围大小 | [100] |
| –secondary[=on/off] | 使用二级索引代替 PRIMARY KEY | [off] |
| –simple_ranges=N | 每个事务的简单范围 SELECT 查询数 | [1] |
| –skip_trx[=on/off] | 不要启动显式事务并在 AUTOCOMMIT 模式下执行所有查询 | [off] |
| –sum_ranges=N | 每个事务的 SELECT SUM() 查询数 | [1] |
| –table_size=N | 每个表的行数 | [10000] |
| –tables=N | 表的个数 | [1] |
测试
OLTP读写混合场景
| SQL类型 | 比例 | SQL语句 |
|---|---|---|
| point_selects | 10 | SELECT c FROM sbtest100 WHERE id=? |
| simple_ranges | 1 | SELECT c FROM sbtest100 WHERE id BETWEEN ? AND ? |
| sum_ranges | 1 | SELECT SUM(k) FROM sbtest100 WHERE id BETWEEN ? AND ? |
| order_ranges | 1 | SELECT c FROM sbtest100 WHERE id BETWEEN ? AND ? ORDER BY c |
| distinct_ranges | 1 | SELECT DISTINCT c FROM sbtest100 WHERE id BETWEEN ? AND ? ORDER BY c |
| index_updates | 1 | UPDATE sbtest100 SET k=k+1 WHERE id=? |
| non_index_updates | 1 | UPDATE sbtest100 SET c=? WHERE id=? |
| deletes | DELETE FROM sbtest100 WHERE id=? | |
| inserts_ignore | 1 | INSERT IGNORE INTO sbtest100 (id, k, c, pad) VALUES (?, ?, ?, ?) |
表结构
执行以下sysbench命令可以创建，sysbench的内置表。
sysbench --db-driver=mysql
--mysql-host=127.0.0.1
--mysql-port=33106
--mysql-user=root
--mysql-password=Abc123456
--mysql-db=test_db
--table_size=25000
--tables=250
--events=0
--time=600
oltp_read_write prepare
可以生成如下表结构
mysql> show create table sbtest168;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sbtest168 | CREATE TABLE `sbtest168` (
`id` int NOT NULL AUTO_INCREMENT,
`k` int NOT NULL DEFAULT '0',
`c` char(120) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
`pad` char(60) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_168` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=25001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
只写
##准备数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_write_only run
##运行 workload
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
--events=0
--time=600
--threads=192
--percentile=95
--report-interval=1
oltp_write_only run
##清理数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_write_only cleanup
只读（point select）
##准备数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_only prepare
##运行 workload
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
--events=0
--time=600
--threads=512
--percentile=95
--range_selects=0
--skip-trx=1
--report-interval=1
oltp_read_only run
##清理数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_only cleanup
只读（range select）
##准备数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_only prepare
##运行 workload
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
--events=0
--time=600
--threads=512
--percentile=95
--skip-trx=1
--report-interval=1
oltp_read_only run
##清理数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_only cleanup
混合读写（point select）
##准备数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_write run
##运行 workload
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
--events=0
--time=600
--range_selects=0
--threads=XXX
--percentile=95
--report-interval=1
oltp_read_write run
##清理数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_write cleanup
混合读写（range select）
##准备数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_write run
##运行 workload
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
--events=0
--time=600
--threads=XXX
--percentile=95
--report-interval=1
oltp_read_write run
##清理数据
sysbench --db-driver=mysql
--mysql-host=XXX
--mysql-port=XXX
--mysql-user=XXX
--mysql-password=XXX
--mysql-db=XXX
--table_size=XXX
--tables=XXX
oltp_read_write cleanup
本文内容到此结束了，
如有收获欢迎点赞?收藏?关注✔️，您的鼓励是我最大的动力。
如有错误❌疑问?欢迎各位指出。
保持热爱，奔赴下一场山海。???
参考文档：