欢迎来到尧图网

客户服务 关于我们

您的位置:首页 > 文旅 > 手游 > mysql性能压测

mysql性能压测

2025/2/5 12:01:10 来源:https://blog.csdn.net/gsls200808/article/details/145120132  浏览:    关键词:mysql性能压测

软件安装

安装sysbench

yum install epel-release -y
yum install sysbench
sysbench --version

创建测试库

CREATE DATABASE `demobench`
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

创建测试表(不需要,执行准备阶段的命令即可)

CREATE TABLE `sbtest1` (`id` int NOT NULL AUTO_INCREMENT,`k` int NOT NULL DEFAULT '0',`c` char(120) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',`pad` char(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',PRIMARY KEY (`id`),KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ciINSERT INTO  sbtest1
(id, k, c, `pad`)
VALUES(1, 4993, '83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330', '67847967377-48000963322-62604785301-91415491898-96926520291');

sysbench执行参数示例

sysbench

参数 10个表 每个表10000行 压测时长6000秒(100分钟)报告周期 每10秒报告1次 10个线程

准备阶段

sysbench --db-driver=mysql \--mysql-host=your_host \--mysql-port=your_port \--mysql-user=your_username \--mysql-password=your_password \--mysql-db=your_database \--tables=10 \  # Number of tables--table-size=10000 \  # Number of rows per tableoltp_read_write \prepare

执行阶段

sysbench --db-driver=mysql \--mysql-host=your_host \--mysql-port=your_port \--mysql-user=your_username \--mysql-password=your_password \--mysql-db=your_database \--time=6000 \  # 100 minutes in seconds--threads=number_of_threads \--report-interval=10 \  # Report every 10 seconds--tables=10 \  # Number of tables--table-size=10000 \  # Number of rows per tableoltp_read_write \run

清理阶段

sysbench --db-driver=mysql \--mysql-host=your_host \--mysql-port=your_port \--mysql-user=your_username \--mysql-password=your_password \--mysql-db=your_database \--tables=10 \  # Number of tablesoltp_read_write \cleanup

sysbench执行参数实战

实战

准备阶段

sysbench --db-driver=mysql \--mysql-host=192.168.11.10 \--mysql-port=13306 \--mysql-user=root \--mysql-password=root \--mysql-db=demobench \--tables=10 \--table-size=10000 \oltp_read_write \prepare

运行阶段

sysbench --db-driver=mysql \--mysql-host=192.168.11.10 \--mysql-port=13306 \--mysql-user=root \--mysql-password=root \--mysql-db=demobench \--time=6000 \--threads=10 \--report-interval=10 \--tables=10 \--table-size=10000 \oltp_read_write \run

清理阶段

sysbench --db-driver=mysql \--mysql-host=192.168.11.10 \--mysql-port=13306 \--mysql-user=root \--mysql-password=root \--mysql-db=demobench \--tables=10 \oltp_read_write \cleanup

报告解读

tps 472 qps 9441

[ 5960s ] thds: 10 tps: 493.49 qps: 9873.72 (r/w/o: 6911.77/1974.96/986.98) lat (ms,95%): 26.68 err/s: 0.00 reconn/s: 0.00
[ 5970s ] thds: 10 tps: 449.93 qps: 8997.46 (r/w/o: 6299.89/1797.71/899.86) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
[ 5980s ] thds: 10 tps: 461.38 qps: 9226.08 (r/w/o: 6456.48/1846.84/922.77) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00
[ 5990s ] thds: 10 tps: 491.41 qps: 9832.79 (r/w/o: 6882.80/1967.16/982.83) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00
[ 6000s ] thds: 10 tps: 445.78 qps: 8913.09 (r/w/o: 6240.21/1781.32/891.56) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00
SQL statistics:queries performed:read:                            39652886write:                           11329369other:                           5664687total:                           56646942transactions:                        2832338 (472.05 per sec.)queries:                             56646942 (9441.06 per sec.)ignored errors:                      11     (0.00 per sec.)reconnects:                          0      (0.00 per sec.)General statistics:total time:                          6000.0575stotal number of events:              2832338Latency (ms):min:                                    5.28avg:                                   21.18max:                                  231.8395th percentile:                       29.19sum:                             59992494.60Threads fairness:events (avg/stddev):           283233.8000/251.50execution time (avg/stddev):   5999.2495/0.01

最终的tps qps 查看

transactions: 2832338 (472.05 per sec.)

queries: 56646942 (9441.06 per sec.)

压测结果对比

阿里云99计划ECS 笔记本 服务器 压测结果对比

项目

阿里云99计划 2c2g Ecs ESSD Entry云盘 docker版 mysql8 本机压测(带工作负载)

thinpad t420 4c8g ssd docker版 mysql8 局域网压测(带工作负载)

dell t140 12c64g hdd 宿主机版 mysql8 局域网压测(带工作负载)

tps

472

38

40

qps

9441

767

819

阿里云99计划ECS服务器性能是笔记本/服务器的10倍,等服务器到期了再测试99计划的rds结果。

报错问题

低版本sysbench执行报caching_sha2_password错误

sysbench 从版本 1.0.19 开始正式支持 caching_sha2_password 认证插件
centos7默认安装的版本为1.0.17
RockyLinux8默认安装版本为1.0.20

建议升级或者编译安装新版。

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com