近期有碰到 MYSQL 的性能测试需求,因此网上收集了 MYSQL 的测试方法,并使用 sysbench 完成了几次这次,这里分享下这次的总结(大多是参考资料里贴来的)。
相较于全链路测试,基准测试是在特定时间点通过性能测试设定一个性能基准线。当系统软硬件环境变动后,可重新执行基准测试,以评估这些变化对性能的具体影响。
而针对数据库的基准测试,可以评估当前配置(含硬件、操作系统、数据库设置等)下的性能表现,确定性能阈值,并根据实际需求调整配置。这为数据库系统的硬件采购、架构设计及升级提供了关键数据支持。可以理解为针对数据库基础性能的一种压力测试,但这个测试不涉及业务逻辑,测试数据由工具生成,增删改查是固定脚本。
性能基准线需要关注的指标:
系统负载
·CPU:%user、%idle、%sys、%iowait # 最通用的几个指标·IO:tps、await、svctm、%util
·内存:free(free、shared、buffers、cached)、used,以及 swap
内存的利用率越多越好,所以我们主要关注空闲、使用、还有 swap ,si so
当前是否使用 swap 是否频繁使用 swap 也需要关注
MYSQL 运行指标
tps、rt、lock、hit ratio、waits
rt = response time
lock = row lock、table lock
hit ratio = cache/buffer hit ratio
waits = Innodb_buffer_pool_wait_free / Innodb_log_waits / Table_locks_waited / Innodb_row_lock_current_waits / Innodb_row_lock_waits
支持数据库基准测试的工具很多,当前选择了 sysbench
prepare:执行准备工作。例如,在磁盘上创建必要的测试文件以进行 fileio 测试,或者在测试数据库上新建 100 万行数据以执行数据库基准测试。
run:使用 testname 参数指定的压测脚本执行对应的测试。
cleanup:在测试结束后删除临时数据或文件。
编译必需依赖
yum -y install make automake libtool pkgconfig libaio-devel
编译 MySQL 必需依赖, 在 RHEL/CentoS 5 上替换为 mysql-devel
yum -y install mariadb-devel openssl-devel
编译 PostgreSQL 必需依赖
yum -y install postgresql-devel
安装 sysbench
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench 命令语法如下:
sysbench [options]… [testname] [command]
常规选项
–threads=N:指定线程数,默认值为1,相当于sysbendh 0.5及之前版本中的–num-threads=N选项。
–events=N:指定总的请求数,默认值为0,表示不限制请求数,相当于sysbench 0.5及之前版本中的–max-requests选项。
–time=N:指定压测时长,默认值为10s,相当于sysbench 0.5及之前版本中的–max-time=N选项。
–forced-shutdown=STRING:有效值为off、N、N%(默认值为off)。off表示不启用强制关机功能;N表示在–time选项指定的时间到期后,再过N秒强制关机;N%表示在–time选项指定的时间到期后,再过–time*N%时间强制关机。
–thread-stack-size=SIZE:指定每个线程的堆栈大小,默认值为64KB。
–rate=N:限定事务速率(tps),默认值为0,表示不限制,相当于sysbench 0.5及之前版本中的–tx-rate=N选项。
–report-interval=N:指定中间统计结果报告的间隔时间,默认值为0,表示关闭中间统计结果报告输出。
–report-checkpoints=[LIST,…]:用逗号分隔的一组列表值,这些值在执行sysbench压测时被依次读取,表示执行多少秒就打印一次统计报告(例如–report- checkpoints=10,20,30,表示当执行10s、20s、30s时分别打印一次统计报告。注意,该数值是指从执行sysbench开始到现在的时间),默认值为空,表示在–time选项指定的时间到期后才打印统计报告。
–debug[=on|off]:是否打印调试信息,默认值为off。
–help[=on|off]:是否打印帮助信息,默认值为off。
–version[=on|off]:是否打印版本信息,默认值为off。
伪随机数创建选项
–rand-type=STRING:随机数分布类型,可选项包括uniform、gaussian、special、pareto,默认值为special。
–rand-spec-iter=N:随机数生成的迭代次数,默认值为12次。
–rand-spec-pct=N:对特定随机数分布来说被视为“特殊”值的百分比,默认值为1。
–rand-spec-res=N:对特定随机数分布来说“特殊”值的百分比,默认值为75。
–rand-seed=N:随机数发生器的种子。当该选项设置为0时,表示使用当前时间作为RNG种子。
–rand-pareto-h=N:用于指定pareto随机分布的h参数,默认值为0.2。
日志选项
–verbosity=N:日志打印的详细程度,5表示打印debug级别以上的日志,0表示只打印critical级别以上的日志。默认值为3。
–percentile=N:在延迟时间统计中选择哪个百分位数,可选范围为(1~100),默认值为95。如果设置为0,则表示禁用延迟时间统计功能。
–histogram[=on|off]:是否打印延迟时间直方图报告,默认值为off。
常规数据库选项
–db-driver=STRING:指定数据库驱动程序(即指定数据库类型),当前版本支持MySQL和PostgreSQL。
–db-ps-mode=STRING:prepare命令使用模式,有效值为auto和disable,默认值为auto,在高并发压力下建议使用disable。
–db-debug[=on|off]:是否打印数据库的调试信息,默认值为off。
MySQL 选项
–mysql-host=MySQL服务器主机,默认值为localhost。
–mysql-port=MySQL服务器端口号,默认值为3306。
–mysql-socket= MySQL服务器Socket文件目录。
–mysql-user=连接MySQL服务器的用户名,默认值为sbtest。
–mysql-password=连接MySQL服务器的密码。
–mysql-db=连接MySQL服务器的数据库名,默认值为sbtest。
–mysql-ssl[=on|off]:连接MySQL服务器是否使用SSL,默认值为off。
–mysql-ssl-cipher=连接MySQL服务器使用SSL时的Cipher。
–mysql-compression[=on|off]:连接MySQL服务器是否使用压缩,默认值为off。
–mysql-debug[=on|off]:连接MySQL服务器是否跟踪所有的客户端库调用,默认值为off。
–mysql-ignore-errors=是否忽略MySQL返回的错误,默认值为[1213,1020,1205]。
–mysql-dry-run[=on|off]:是否空跑,只是调用MySQL客户端API,但是不真正执行。
pgsql 选项
–pgsql-host= PostgreSQL服务器主机,默认值为localhost。
–pgsql-port= PostgreSQL服务器端口,默认值为5432。
–pgsql-user=连接MySQL服务器的用户名,默认值为sbtest。
–pgsql-password=连接MySQL服务器的密码。
–pgsql-db=连接MySQL服务器的数据库名,默认值为sbtest。
其他选项
通过使用如下命令来查看额外的关于测试名称(事务模型)命令选项,只需要任意指定一个测试名称即可。
# 指定oltp_read_write测试名称来查看额外的帮助选项
[root@localhost ]# sysbench oltp_read_write help
……
oltp_read_write options:
–distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1]
–sum_ranges=N Number of SELECT SUM() queries per transaction [1]
–skip_trx[=on|off] Don’t start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
……
各选项解释如下
–distinct_ranges=N:指定在每个事务中SELECT DISTINCT查询的执行次数,默认值为1。
-sum_ranges=N:指定在每个事务中SELECT SUM()查询的执行次数,默认值为1。
–skip_trx[=on|off]:指定在AUTOCOMMIT(自动提交)模式下是否需要跳过启动显式事务(使用START语句显式启动一个事务),默认值为off。
–secondary[=on|off]:指定是否需要使用一个二级索引来代替主键索引,默认值为off。
–create_secondary[=on|off]:指定除主键之外,是否还需要创建一个二级索引,默认值为on。
–index_updates=N:指定在每个事务中使用索引执行UPDATE语句的次数,默认值为1。
–range_size=N:指定在每个事务中范围SELECT查询的条件值,默认值为100。
–auto_inc[=on|off]:指定是否需要使用自增列的自增值作为主键值,如果不使用自增值,则使用sysbench自动生成的ID值作为主键值,默认值为on。
–delete_inserts=N:指定在每个事务中DELETE/INSERT组合语句的数量,默认值为1。
–tables=N:指定并行压测的表数量。
–mysql_storage_engine=STRING:指定表的存储引擎,默认值为InnoDB。
–non_index_updates=N:指定在每个事务中不使用索引执行UPDATE语句的次数,默认值为1。
–table_size=N:指定每个表的数据总量,默认值为10 000。
–pgsql_variant=STRING:当用PostgreSQL驱动程序运行时使用此PostgreSQL变体。目前唯一支持的变体是“redshift”。启用后,将自动禁用create_secondary,并将–delete_inserts选项设置为0。
–simple_ranges=N:指定在每个事务中简单范围SELECT查询(这里指的是BETWEEN范围查询)的次数,默认值为1。
–order_ranges=N:指定在每个事务中SELECT ORDER BY查询的次数,默认值为1。
–range_selects[=on|off]:指定是否需要打开或关闭所有的范围SELECT查询,默认值为on。
–point_selects=N:指定在每个事务中单行SELECT查询的次数,默认值为10。
备注项:
在执行 oltp_read_write 测试时,从 MySQL 的 general_log 中抓取的每个事务的语句数量也证实了,在默认的配置下一个事务中的语句数量为 18 条
testname
testname 用于指定 sysbench 的基准测试名称。基准测试包括:
备注:在实际执行时,对于 Lua 新格式脚本,可以只写脚本名称(不写.lua 后缀),如 oltp_read_only,不再需要像 sysbench 0.5 及之前版本那样使用–test 选项来指定。
该项输出结果包括 sysbench 发起的读/写/其他/总计 SQL 查询数量、总计事务数及每秒事务数、总计请求数及每秒请求数、总计错误数及每秒错误数、总计重连接数及每秒重连接数。
SQL statistics:
queries performed:
read: 49084
write: 9513
other: 11523
total: 70120
transactions: 3506 (350.33 per sec.)
queries: 70120 (7006.63 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
该项输出结果包括总计执行的时间、所有的事件数量(这里对应的是发起的 MySQL 事务数)。
General statistics:
total time: 10.0062s
total number of events: 3506
该项输出结果包括延迟时间最低值、平均值、最高值、第 95% 位值、总计值。
Latency (ms):
min: 4.56
avg: 11.41
max: 39.24
95th percentile: 19.65
sum: 39997.58
该项输出结果包括每个压测线程的平均事件数及标准差、每个事务的平均执行时间及标准差。
Threads fairness:
events (avg/stddev): 876.5000/5.22
execution time (avg/stddev): 9.9994/0.00
如下为只读场景示例,当前支持 mysql 压测的场景有:
造数
sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=admin1 --mysql-password=68667109!@#1 --mysql-db=sbtest --table_size=2500 --tables=25 --time=60 oltp_read_only prepare
参数解释:
sysbench
:这是命令行工具的名称,用于启动 Sysbench 测试。--db-driver=mysql
:指定数据库驱动为 MySQL。Sysbench 支持多种数据库,通过这个参数指定要测试的数据库类型。--mysql-host=localhost
:指定 MySQL 数据库服务器的主机地址。这里使用的是一个 IP 地址,表明测试将在远程服务器上执行。--mysql-port=3306
:指定 MySQL 数据库服务器的端口号。3306 是 MySQL 的默认端口。--mysql-user=admin1
:指定用于连接 MySQL 服务器的用户名。--mysql-password=68667109!@#1
:指定用于连接 MySQL 服务器的用户密码。在实际使用中,密码应妥善保管,避免在命令行中明文显示。--mysql-db=sbtest
:指定要测试的数据库名称。在这个例子中,数据库名为 sbtest
。--table_size=2500
:指定每个测试表中的数据行数。这里每个表将包含 2500 行数据。--tables=25
:指定要创建的测试表的数量。在这个例子中,将创建 25 个表。--time=60
:指定测试运行的时间(秒)。在这个例子中,测试将运行 60 秒。oltp_read_only
:指定要执行的测试类型。oltp_read_only
表示执行只读的事务处理(OLTP)测试,这种测试主要评估数据库的读取性能。prepare
:这个参数指示 Sysbench 执行测试前的准备工作,即创建所需的数据库和表,并填充数据。这通常是测试过程的第一步。压测
sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=admin1 --mysql-password=68667109!@#1 --mysql-db=sbtest --percentile=80 --max-requests=0 --threads=11 --time=60 oltp_read_only run
参数说明:
sysbench
:命令行工具的名称,用于启动 Sysbench 测试。--db-driver=mysql
:指定数据库驱动为 MySQL。--mysql-host=localhost
:指定 MySQL 数据库服务器的主机地址。--mysql-port=3306
:指定 MySQL 数据库服务器的端口号。--mysql-user=admin1
:指定连接 MySQL 服务器的用户名。--mysql-password=68667109!@#1
:指定连接 MySQL 服务器的用户密码。注意,出于安全考虑,通常不建议在命令行中明文显示密码。--mysql-db=sbtest
:指定要测试的数据库名称。--percentile=80
:指定在计算延迟统计时考虑的百分位数。这里设置为 80%,意味着会报告 80% 的请求在多少时间内完成,这有助于了解系统的性能瓶颈。--max-requests=0
:指定测试期间要执行的最大请求数。设置为 0 表示没有限制,测试将持续到指定的时间(--time
参数)结束。--threads=11
:指定用于测试的并发线程数。这里设置为 11,意味着将使用 11 个并发线程来模拟用户活动。--time=60
:指定测试运行的时间(秒)。oltp_read_only
:指定要执行的测试类型为只读事务处理测试。run
:这个参数指示 Sysbench 执行测试的实际运行阶段。在 prepare
阶段之后,你需要使用 run
参数来启动实际的性能测试。清理
sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=admin1 --mysql-password=68667109!@#1 --mysql-db=sbtest --table_size=2500 --tables=25 --time=60 oltp_read_only cleanup
参数说明
sysbench
:命令行工具的名称,用于启动 Sysbench 测试。--db-driver=mysql
:指定数据库驱动为 MySQL,表明我们将要对 MySQL 数据库进行操作。--mysql-host=localhost
:指定 MySQL 数据库服务器的主机地址,即测试所连接的数据库服务器的 IP 地址。--mysql-port=3306
:指定 MySQL 数据库服务器的端口号,MySQL 的默认端口是 3306。--mysql-user=admin1
:指定用于连接 MySQL 服务器的用户名。--mysql-password=68667109!@#1
:指定用于连接 MySQL 服务器的用户密码。请注意,出于安全考虑,不建议在命令行中明文显示密码。--mysql-db=sbtest
:指定要操作的数据库名称,这里假设是为测试创建的名为 sbtest
的数据库。--table_size=2500
和 --tables=25
:这两个参数在 cleanup
阶段实际上是不需要的,因为清理过程会删除整个数据库或指定的表,而不会关心表的大小或数量。但是,它们通常与 prepare
命令一起使用,用于创建和填充测试表。--time=60
:在 cleanup
阶段,这个参数是不需要的,因为清理过程不会持续特定的时间。它通常与 run
命令一起使用,指定测试运行的时间。oltp_read_only
:指定测试类型为只读事务处理(OLTP)测试。在 cleanup
阶段,这个参数主要用于识别之前为测试创建的特定表或数据库结构。cleanup
:这个参数指示 Sysbench 执行清理操作,删除为测试创建的数据库或表。
通过上图可以分析得出如下结论:
1) 60 秒内共执行读请求 28756 次,更新及其他类型的请求 4108 次,QPS:544.42;
2) 60s 内共执行事务操作 2054,TPS:34.03,成功率 100%;
3) 每个事务执行最小时间 272.88ms,最大时间 850.33ms,平均执行时间 322.46ms。
以上测试结果运行环境基本配置信息:
1) 虚拟机 1 颗 CPU、2G 内存,10G 存储空间;
2) MySQL 数据库参数未优化、调整。
一、会因为 mysql8.0 密码加密方式导致报错
FATAL: error 2059: Authentication plugin 'caching_sha2_password' cannot be loaded
ALTER USER 'admin1'@'%' IDENTIFIED WITH mysql_native_password BY '68667109!@#1'
flush privileges;
mysql 性能解析系列教程:https://www.bilibili.com/video/BV1U64y1d7EE/?p=37
通过 sysbench 工具实现 MySQL 数据库的性能测试:https://blog.csdn.net/hongjian006/article/details/142760189
MySQL 的性能基线收集及压力测试:https://blog.51cto.com/yijiu/1566615
MySQL 性能测试(完整版):https://developer.aliyun.com/article/1509755
MySQL 数据库性能基准测试之 sysbench 之 1—概念与工具分类及 sysbench 介绍:https://zhuanlan.zhihu.com/p/687978862
sysbench 安装:https://blog.csdn.net/m0_61066945/article/details/138163575
OLTP 负载测试:https://help.aliyun.com/zh/polardb/polardb-for-mysql/oltp-performance-test
lua 脚本说明:https://blog.csdn.net/zhou920786312/article/details/125095810
github:https://github.com/akopytov/sysbench#sysbench