《RESAR 性能工程实战》第 3 篇：容量场景实战 —— MySQL 索引优化与 sysbench 梯度压测

《RESAR 性能工程实战》第 3 篇容量场景实战 —— MySQL 索引优化与 sysbench 梯度压测系列目录全部源码与原始实验日志GitCode 仓库 https://gitcode.com/cpyaxjq/resar-perf-in-action ① 开篇一小时四台 ECS 搭起完整性能实验场 ② 基准场景wrk 压测与软中断证据链 ③ 容量场景MySQL 索引优化与 sysbench 梯度压测 ④ 稳定性与异常Redis 混沌工程四连击 ⑤ 性能结论生产配置建议系列导航第 1 篇 性能工程总览 · 第 2 篇 基准与环境 · 本篇 容量场景与数据库优化 · 下篇预告见文末关键词容量场景 / 最大 TPS / MySQL 索引 / sysbench 梯度压测 / 拐点判断 / 配置审计一、前言性能测试要对结果负责很多团队做性能测试最终的产出只是一张「TPS 1000、响应时间 200ms」的截图然后报告里写一句「系统性能良好」。这远远不够。在 RESAR 性能工程体系课程第 23–25 讲里我们反复强调一个核心命题性能测试必须对结果负责。所谓「负责」至少包含三层含义能不能扛住生产—— 这需要一个「容量场景」来回答得到系统的最大 TPS以及它在什么并发下开始「变脸」。慢的根因是什么—— 不能只报「慢」而要给出从现象到证据的完整链路EXPLAIN、统计、复验。上线前该改什么—— 不能只报 TPS必须给出可落地的配置/索引建议否则这份报告对生产没有任何决策价值。本篇就是一次完整示范在华为云 8C16G 的 MySQL 8.0 上我们既做了慢查询的索引优化证据链又做了sysbench 梯度容量压测最后还做了一轮配置审计——把「只报数」变成「对结果负责」。二、容量场景方法论我们到底在测什么容量场景Capacity Test的本质是回答一个问题在可控 SLA如 P95 50ms约束下系统到底能扛多少吞吐方法论分四步铺底数据造足量的、贴近真实的业务数据本文 100 万行订单 40 万行基准表。梯度加压从低并发到高并发逐步拉满线程数4 / 16 / 64观察 TPS、延迟、资源占用。拐点判断找到「TPS 增速放缓 延迟恶化 资源逼近饱和」的临界点——这就是容量上限。优化闭环对发现的瓶颈缺索引、低配参数做优化并复验给出生产建议。容量场景不是「压到挂」的破坏性测试而是为了定位拐点、量化上限、指导调优。这一点务必和生产「稳定性/破坏性」测试区分开。三、环境与铺底数据项规格云主机华为云 ECS8 vCPU / 16 GB 内存OSUbuntu 24.04数据库MySQL 8.0压测工具sysbench 1.0.20业务库perfdbt_order100 万行user_id无索引、t_product524288 行基准库sbtest4 张表 × 10 万行--tables4 --table-size100000账号root 本地免密压测用户perf业务表结构造数脚本有意省略了user_id上的索引用来复现「生产常见慢查询」CREATETABLEt_order(idintNOTNULLAUTO_INCREMENT,user_idintDEFAULTNULL,amountdecimal(10,2)DEFAULTNULL,statustinyintDEFAULTNULL,PRIMARYKEY(id))ENGINEInnoDBAUTO_INCREMENT1048561DEFAULTCHARSETutf8mb4;数据量核实exp_mysql_probe.log原始回显mysql SELECT COUNT(*) FROM perfdb.t_order; 1000000 mysql SELECT COUNT(*) FROM sbtest.sbtest1; 100000注意t_order有且仅有一个主键iduser_id上没有二级索引。这条「看似无害」的缺失正是后面慢查询的根因。四、索引优化证据链从「现象」到「复验」性能工程最有说服力的不是结论而是证据链。我们用三步把「慢」讲清楚。4.1 现象20 次聚合查询要 2.4 秒用一个贴近业务的聚合查询按用户统计订单数与金额循环 20 次计时$time(foriin$(seq120);do\mysql perfdb-N-eSELECT COUNT(*),SUM(amount) FROM t_order WHERE user_id$((RANDOM%100000))/dev/null;\done)real 0m2.432s user 0m0.043s sys 0m0.056s单次约 120ms20 次 2.4 秒——对一个「点查 聚合」来说明显偏慢。4.2 EXPLAIN根因是百万行全表扫描exp_mysql_1.log原始回显EXPLAIN ... \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_order partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 998412 filtered: 10.00 Extra: Using where关键证据type: ALL——全表扫描possible_keys: NULL/key: NULL—— 优化器根本没索引可用rows: 998412—— 预计扫描近100 万行表总共 100 万行等于全扫Extra: Using where—— 在扫描完后再逐行过滤。结论每次查询都把整张 100 万行的表扫一遍。user_id缺失索引 贴着生产最常见的「慢查询」模板。4.3 优化在线加索引只用了 3.1 秒$ mysql perfdb-eALTER TABLE t_order ADD INDEX idx_uid(user_id)---exit0elapsed3.1s ---MySQL 8.0 的在线 DDLInstant / Inplace 算法让加索引不锁表、仅 3.1 秒即可完成业务几乎无感。4.4 复验typeref、rows14、整体快 28 倍加完索引后再 EXPLAINexp_mysql_2.log*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_order partitions: NULL type: ref possible_keys: idx_uid key: idx_uid key_len: 5 ref: const rows: 14 filtered: 100.00 Extra: NULLtype: ref—— 从全扫升级为索引查找key: idx_uid—— 命中我们刚加的索引rows: 14—— 扫描行数从 998412 降到14降了 7 万倍filtered: 100.00/Extra: NULL—— 无需回表后二次过滤。再跑一遍同样的 20 次计时$time(foriin$(seq120);do\mysql perfdb-N-eSELECT COUNT(*),SUM(amount) FROM t_order WHERE user_id$((RANDOM%100000))/dev/null;\done)real 0m0.086s user 0m0.036s sys 0m0.044s2.432s → 0.086s加速 28.3 倍2.432 / 0.086 ≈ 28.3。证据链闭环现象慢2.4s→ EXPLAIN全表扫rows≈100 万→ 优化加idx_uid在线 3.1s→ 复验ref、rows14、28×。这就是性能工程该有的「讲清楚」。图t_order100万行user_id 索引优化前后——扫描行数从 998,412 降到 14查询耗时从 2.43 秒降到 0.086 秒加速 28.3 倍五、sysbench 梯度容量完整统计块 表格容量场景的核心动作梯度加压。我们用oltp_read_write读写混合固定 45 秒线程数取 4 / 16 / 64每档都同步采集mpstat/vmstat/iostat。安全说明以下命令中密码以******脱敏。5.1 线程4低并发$ sysbench oltp_read_write --mysql-host127.0.0.1 --mysql-userperf\--mysql-password****** --mysql-dbsbtest\--tables4--table-size100000--threads4--time45--report-interval15run中间采样[ 15s ] thds: 4 tps: 549.85 qps: 11001.32 (r/w/o: 7701.42/2199.93/1099.97) lat (ms,95%): 10.27 [ 30s ] thds: 4 tps: 557.40 qps: 11148.42 (r/w/o: 7803.75/2229.87/1114.80) lat (ms,95%): 10.27 [ 45s ] thds: 4 tps: 558.93 qps: 11178.33 (r/w/o: 7825.00/2235.47/1117.87) lat (ms,95%): 10.09汇总块SQL statistics: queries performed: read: 349958 write: 99988 other: 49994 total: 499940 transactions: 24997 (555.39 per sec.) queries: 499940 (11107.77 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 45.0078s total number of events: 24997 Latency (ms): min: 3.52 avg: 7.20 max: 46.78 95th percentile: 10.27 sum: 179984.145.2 线程16中并发汇总块节选关键行[ 15s ] thds: 16 tps: 1568.48 qps: 31389.78 lat (ms,95%): 13.70 [ 30s ] thds: 16 tps: 1550.33 qps: 30998.69 lat (ms,95%): 13.70 [ 45s ] thds: 16 tps: 1558.40 qps: 31176.21 lat (ms,95%): 13.70 transactions: 70175 (1559.07 per sec.) queries: 1403500 (31181.49 per sec.) ignored errors: 0 (0.00 per sec.) Latency (ms): min: 4.20 avg: 10.26 max: 53.04 95th percentile: 13.705.3 线程64高并发汇总块节选关键行[ 15s ] thds: 64 tps: 2699.81 qps: 54068.69 lat (ms,95%): 36.24 [ 30s ] thds: 64 tps: 2683.29 qps: 53666.75 lat (ms,95%): 36.89 [ 45s ] thds: 64 tps: 2672.08 qps: 53434.12 lat (ms,95%): 36.24 transactions: 120893 (2683.86 per sec.) queries: 2417860 (53677.30 per sec.) ignored errors: 0 (0.00 per sec.) Latency (ms): min: 5.39 avg: 23.83 max: 86.31 95th percentile: 36.245.4 三档汇总表线程TPSQPSP95(ms)avg(ms)CPU busy%mpstat(usr/sys/soft/idle)iowait%4555.3911,107.7710.277.2033.3%10.22 / 4.29 / 1.54 / 66.7017.24161,559.0731,181.4913.7010.2661.8%33.40 / 12.77 / 4.81 / 38.2310.80642,683.8653,677.3036.2423.8392.4%60.95 / 21.32 / 7.50 / 7.632.59每线程吞吐4 ≈ 139 TPS/线程、16 ≈ 97、64 ≈ 42。并发越高单线程效率越低——这是典型的多线程争用锁、上下文切换、CPU 调度信号而非「线程越多越便宜」。图sysbench oltp_read_write 三档梯度——TPS蓝线在 4→16 线程近线性增长16→64 增速放缓P95 延迟红虚线与 CPU 占用率紫点线在 64 线程急剧恶化黄色区域为拐点区间六、拐点分析容量上限在哪把三档数据画成「趋势」来看4 → 16 线程TPS 从 555 涨到 1559180%几乎线性CPU 仅 33% → 62%P95 从 10.3ms 微升到 13.7ms33%。这一段是「健康的扩容红利区」。16 → 64 线程TPS 从 1559 涨到 2684仅 72%但 CPU 从 62% 飙升到92%P95 从 13.7ms 恶化到36.2ms2.6 倍avg 延迟从 10.3ms 翻倍到 23.8ms。拐点判断三要素在这里同时亮灯TPS 增速明显放缓180% → 72%P95 延迟急剧恶化2.6 倍CPU 逼近饱和92%8 vCPU 已无余量。由此判定真实拐点约在 16–32 线程之间。超过 64 线程CPU 必然打满、上下文切换与锁竞争加剧延迟会继续恶化而 TPS 几乎不再增长甚至回落。一个有趣的旁证——iowait 随并发变化低并发4 线程iowait17.24%CPU 还有大量空闲idle 66.7%此时瓶颈在磁盘 IOCPU 在等 IO高并发64 线程iowait降到 2.59%CPU idle 只剩 7.6%——IO 等待被 CPU 并行「掩盖」了瓶颈从 IO 转移到了CPU 计算/调度。exp_mysql_3a_mon.log中 4 线程的vmstat实测wa 列17与 mpstat iowait 吻合procs -----------memory---------- ---swap-- -----io---- -system-- -------cpu------- r b swpd free buff cache si so bi bo in cs us sy id wa st gu 0 2 0 12739416 99780 1895632 0 0 0 28834 40255 73646 10 6 66 17 0 0 0 1 0 12736528 99784 1900600 0 0 0 28636 40363 74124 10 6 66 17 0 0而 64 线程时vmstat的 wa 已降到 3、CPU 跑满r b swpd free buff cache si so bi bo in cs us sy id wa st gu 56 2 0 12208584 99796 2189480 0 0 0 42994 37669 160061 59 28 10 3 0 0 48 2 0 12160908 99796 2237196 0 0 0 43294 36532 160778 59 28 9 3 0 0结论拐点不是拍脑袋而是 TPS 曲线 延迟曲线 资源曲线三者的交叉点。本报告给出的 16–32 线程拐点是「在 P95 仍可接受 ~20ms前提下的最大收益区间」。七、最大能力纯点查能跑多快读写混合之外我们还单独测了「纯点查」oltp_point_select线程64、30 秒看 MySQL 在最理想读路径下的天花板exp_mysql_4.logSQL statistics: queries performed: read: 2548405 write: 0 other: 0 total: 2548405 transactions: 2548405 (84882.01 per sec.) queries: 2548405 (84882.01 per sec.) ignored errors: 0 (0.00 per sec.) Latency (ms): min: 0.06 avg: 0.75 max: 19.61 95th percentile: 1.82最大点查 QPS 84,882P95 仅 1.82msavg 0.75ms。对比同线程读写混合 QPS 53,677纯读约为读写混合的 1.58 倍——印证「写redo/undo/刷盘才是读写混合场景的主要成本」。读路径命中内存、无写开销的吞吐能力是评估「缓存命中率提升空间」的重要基线。八、配置审计与生产建议对结果负责的关键一步压完一轮如果不看配置等于白压。我们对运行参数做了快照exp_mysql_5.logVariable_name Value innodb_buffer_pool_size 134217728 innodb_flush_log_at_trx_commit 1 innodb_io_capacity 200 max_connections 151Variable_name Value Threads_cached 8 Threads_connected 1 Threads_created 127 Threads_running 28.1 两个「默认低配陷阱」参数当前值问题生产建议innodb_buffer_pool_size128 MB134217728仅占 16G 内存的0.8%100 万行订单 40 万基准表远放不进 buffer pool大量随机读被迫落盘设为物理内存的 50–70%即8–10 GBinnodb_io_capacity200默认值远落后于云盘SSD/云硬盘实际 IOPS 能力脏页刷写节奏偏保守云盘建议2000按盘实测 IOPS 调证据联动正是 buffer pool 太小导致第 4 档低并发iowait高达17%——CPU 明明空闲 66%却在等磁盘把数据读进那可怜的 128MB 缓存。把 buffer pool 调大到 8–10G 后热点数据常驻内存iowait 会显著下降低并发吞吐与高并发拐点都会上移。8.2 其他参数评价innodb_flush_log_at_trx_commit 1最安全每次事务提交都刷盘生产推荐保留若对丢数据零容忍又追求更高写吞吐可在「主从 业务可接受」前提下评估改 2但本文不建议动。max_connections 151压测中Threads_connected仅 1、running2、created127连接数远未触顶当前默认够用无需调大盲目调大反而增加内存与上下文切换开销。线程状态cached8说明连接池复用正常。这一步才是「对结果负责」的真正落点不只报 TPS还告诉生产「该改什么、改成多少、为什么」。九、踩坑速查表可直接收藏场景现象/证据动作聚合/点查慢EXPLAIN typeALL, rows≈全表在 WHERE/JOIN/ORDER BY 列加二级索引优先在线 DDL加索引怕锁表MySQL 8.0 在线 DDLALTER ... ADD INDEX通常秒级完成本文 3.1s业务无感低并发 iowait 高mpstat iowait 17%、CPU idle 却 66%多半是 buffer pool 太小调大innodb_buffer_pool_size高并发 TPS 不涨CPU 92%、P95 翻倍已过拐点优化 SQL/索引/锁或扩容 CPU每线程吞吐递减4≈139 → 64≈42 TPS/线程多线程争用别无限加线程读写混合慢于点查点查 84k QPS vs 读写 53k QPS写redo/刷盘是主成本评估提交策略与 IO 能力只报 TPS 不报建议报告无配置审计补齐 buffer pool / io_capacity 等生产级建议十、总结与下篇预告本篇用一条完整的证据链示范了「容量场景 数据库优化」如何落地索引优化user_id缺索引导致百万行全扫rows 998412加idx_uid仅 3.1s查询从 2.432s 降到 0.086s28.3 倍加速。证据链现象 → EXPLAIN → 优化 → 复验。容量拐点读写混合 TPS 在 4/16/64 线程分别为 558 / 1559 / 2684CPU 33% / 62% / 92%P95 10.3 / 13.7 / 36.2ms。拐点约 16–32 线程超过后 TPS 收益骤减、延迟恶化。最大能力纯点查 64 线程达84,882 QPS读写混合的 1.58 倍。配置审计buffer_pool 128MB仅占 0.8%、io_capacity 200是默认低配陷阱建议调到 8–10G / 2000并联动低并发 17% iowait 证据。核心一句话性能测试要对结果负责——拿到最大 TPS 只是起点给出「为什么慢、怎么优化、生产怎么配」才是交付。下篇预告第 4 篇当容量拐点已现如何下钻到 MySQL 内部瓶颈我们将用perf/pt-query-digest/ InnoDB 指标做火焰图与慢日志下钻并结合本篇的 buffer pool 调优做「调优前后对比实验」验证 8G buffer pool 能否把拐点推到 32 线程以上。本文实验数据均来自真实环境实操AI 辅助整理成文。