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_ci

INSERT 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 table
  oltp_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 table
  oltp_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 tables
  oltp_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:                            39652886
        write:                           11329369
        other:                           5664687
        total:                           56646942
    transactions:                        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.0575s
    total number of events:              2832338

Latency (ms):
         min:                                    5.28
         avg:                                   21.18
         max:                                  231.83
         95th percentile:                       29.19
         sum:                             59992494.60

Threads fairness:
    events (avg/stddev):           283233.8000/251.50
    execution 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

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

原文地址：https://blog.csdn.net/gsls200808/article/details/145120132

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！