MySQL性能压测工具SysBench详解（非常详细）

吾爱主题阅读：185 2023-08-30 10:16:00 评论：0

概述

背景

最近需要给不同的客户部署数据库，各自的预算不一样，购买的服务器配置也不一样。那么我们就需要对其数据库的支撑能力进行一定测试，以保证满足业务真是的需要

数据库性能指标

指标	英文含义	说明
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