附-01 · MySQL 压测与容量规划 —— sysbench 基准测试、容量评估与参数基线

附-01 · MySQL 压测与容量规划 —— sysbench 基准测试、容量评估与参数基线

系列定位：《MySQL 数据库全栈运维实战系列》第 附-01 篇（番外），与《Oracle 数据库全栈运维实战系列（VMware 版）》同构对标。适用版本：MySQL 8.0（5.7 差异随文标注）。官方文档：MySQL 8.0 Reference Manual、sysbench 官方文档（github.com/akopytov/sysbench）。

一、为什么压测先行

很多团队上线前不压测，上线后才发现"这台 32G 虚机撑不住 5000 TPS"。容量规划的核心逻辑是：先用 sysbench 在与生产同规格的虚机上跑出这台机器的 TPS 上限，再按业务峰值 × 安全系数（2~3 倍）倒推需要多少台、多大内存、什么磁盘。没有压测数据的容量规划就是拍脑袋。

VMware 虚机环境注意事项：压测结果只在"与生产同规格、同置盘方式、同资源预留"的虚机上才有意义。压测前务必：① 固定 vCPU 数与内存（不要开热添加）；② 磁盘确认 thick eager zeroed 或 SSD 直通（thin 盘在首次写入时会做 zeroing，污染结果）；③ 关闭宿主机上其他大负载 VM；④ 压测分冷启动（重启 MySQL 后跑）和热数据（预热 Buffer Pool 后跑）两轮，分别记录。

二、sysbench 安装与三个阶段

# CentOS 7 / Rocky Linux 安装（解释：官方 yum 源或直接下二进制） curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench # 验证版本（解释：8.0 配套建议 sysbench 1.0.20+） sysbench --version # sysbench 1.0.20

sysbench 压测分三段：prepare（造数据）→run（压测）→cleanup（清数据）。

# 通用连接参数（解释：抽到变量里，三段复用） SB_HOST=127.0.0.1 SB_PORT=3306 SB_USER=sbtest SB_PWD='sbtest_pwd' SB_DB=sbtest # 先建库和账号（解释：sysbench 不会自动建） mysql -uroot -p -e "CREATE DATABASE sbtest; CREATE USER 'sbtest'@'%' IDENTIFIED BY 'sbtest_pwd'; GRANT ALL PRIVILEGES ON sbtest.* TO 'sbtest'@'%'; FLUSH PRIVILEGES;"

2.1 prepare：造数据

# 造 10 张表，每张 100 万行（解释：oltp_read_write 是经典 OLTP 模型） sysbench --mysql-host=$SB_HOST --mysql-port=$SB_PORT \ --mysql-user=$SB_USER --mysql-password=$SB_PWD \ --mysql-db=$SB_DB \ oltp_read_write \ --tables=10 --table-size=1000000 \ prepare

2.2 run：压测

# 20 并发线程，跑 60 秒（解释：先短后长，60s 看趋势，再跑 300s 拿稳定值） sysbench --mysql-host=$SB_HOST --mysql-port=$SB_PORT \ --mysql-user=$SB_USER --mysql-password=$SB_PWD \ --mysql-db=$SB_DB \ oltp_read_write \ --tables=10 --table-size=1000000 \ --threads=20 --time=60 --report-interval=10 \ run

典型输出（解释：重点看这几个数）：

SQL statistics: queries performed: read: 456000 write: 128000 other: 64000 total: 648000 transactions: 32400 (540.0 per sec.) queries: 648000 (10800.0 per sec.) ignored errors: 0 (0.0 per sec.) reconnects: 0 (0.0 per sec.) Throughput: events/s (eps): 540.00 latency (ms): min: 2.1 avg: 37.0 max: 480.5 99th percentile: 85.2 threads fairness: 0.99

2.3 cleanup：清数据

sysbench --mysql-host=$SB_HOST --mysql-port=$SB_PORT \ --mysql-user=$SB_USER --mysql-password=$SB_PWD \ --mysql-db=$SB_DB \ oltp_read_write --tables=10 \ cleanup

三、常用压测模型与场景选择

# 压测读库（解释：从库/只读节点用 read_only 模型） sysbench ... oltp_read_only --threads=50 --time=300 run # 压测写能力（解释：主库写上限） sysbench ... oltp_write_only --threads=32 --time=300 run

四、并发梯度：找到拐点

# 梯度压测（解释：依次跑 10/20/50/100/200 并发，记录 TPS 与 P99） for t in 10 20 50 100 200; do echo "=== threads=$t ===" sysbench ... oltp_read_write --threads=$t --time=60 run 2>&1 | grep -E "transactions:|99th" done

典型拐点曲线：

结论：这台虚机在 50 并发附近 TPS 见顶（约 620），P99 < 100ms。业务峰值按 200 TPS 算，单机够；按 1000 TPS 算，要 2 主从读写分离。

五、容量评估方法论

5.1 内存怎么算

32G 虚机：BP=20G，max_connections=500，预留 OS+连接约 8G，刚好。

5.2 磁盘怎么算

总磁盘 = 数据 + 索引 + redo + binlog(7天) + 备份空间 + 30% 余量

• 数据：sysbench 跑出单行大小 × 预估行数。
• binlog：按峰值 TPS × 平均 row 大小 × 86400 × 7 天估。
• 备份：至少保留 2 份全备 + 当天增量，建议数据盘的 1.5 倍。

5.3 IOPS 怎么算

# 压测时同时盯主机 IO（解释：iostat 看 %util 和 await） iostat -xz 2 5 # 如果 await > 20ms 且 %util 接近 100%，说明磁盘是瓶颈，要换 SSD/加盘

六、参数基线（8.0 生产推荐 my.cnf）

[mysqld] # ===== 基础 ===== user = mysql port = 3306 datadir = /data/mysql socket = /data/mysql/mysql.sock character-set-server = utf8mb4 collation-server = utf8mb4_0900_ai_ci # ===== 内存（按物理内存 60~70%）===== innodb_buffer_pool_size = 20G # 32G 虚机示例 innodb_buffer_pool_instances = 8 innodb_log_buffer_size = 64M # ===== redo（8.0.30+ 统一参数）===== innodb_redo_log_capacity = 2G innodb_flush_log_at_trx_commit = 1 # 双 1，不动 innodb_flush_method = O_DIRECT # ===== binlog ===== log_bin = /data/mysql-bin/mysql-bin binlog_format = ROW binlog_expire_logs_seconds = 604800 # 7 天 sync_binlog = 1 # 双 1，不动 # ===== 连接 ===== max_connections = 500 wait_timeout = 60 interactive_timeout = 300 # ===== 临时表/排序 ===== tmp_table_size = 128M max_heap_table_size = 128M sort_buffer_size = 2M join_buffer_size = 2M # ===== IO（SSD 直通盘）===== innodb_io_capacity = 2000 innodb_io_capacity_max = 4000 # ===== 慢日志 ===== slow_query_log = 1 long_query_time = 1 slow_query_log_file = /data/mysql-slow/slow.log

类比 Oracle：Oracle 容量规划用 ORION 压磁盘 IO、Swingbench 压业务负载；MySQL 生态对应就是 sysbench（业务负载）+ fio（裸盘 IO）。Oracle 有 Automatic Memory Management，MySQL 内存参数要自己按上面公式算，没有自动 SGA 调优。

七、上线后基线对比

压测不是一次性动作。上线后每周把生产监控曲线和压测基线对比：

八、踩坑记录（实测汇总）

九、本文要点速记

1. 压测先行：同规格虚机上跑 sysbench，拿到单机 TPS 上限再做容量规划。
2. 三段式：prepare → run → cleanup，别忘 cleanup。
3. 找拐点：梯度加并发，TPS 见顶 + P99 飙升处就是最优并发。
4. 内存公式：Buffer Pool 60~70% 物理内存，连接内存按 max_connections×per-thread 算。
5. 双 1 不动：压测时别为了好看把 flush_log/sync_binlog 关了，那不是生产配置。
6. 上线后对比基线：每周看生产指标 vs 压测基线，偏差超过 20% 要查原因。

十、参考资料（官方正式文档）

本文章属于《MySQL 数据库全栈运维实战系列》番外篇。欢迎收藏、交流。