1. 项目背景业务场景某金融风控系统的 MySQL 实例运行了两年——配置是两年前设定的——当时数据库只有 50GBQPS 只有 500。现在数据库 500GBQPS 5000——但配置一丁点没改。系统出现间歇性抖动——每 10 分钟有一次 3-5 秒的 TPS 降到接近 0。DBA 尝试调了几个参数——每次调完好像好了一点——但下次抖动又不一定是同一个原因——于是陷入了调参→抖动→再调参→再抖动的死循环。痛点生产性能调优的最大敌人不是参数不知道调什么——而是调完之后不知道有没有效果没有基线调参前后没有统一的性能对比基准——无法量化收益。改了一个参数之后 QPS 上升了——但可能是业务低峰造成的假象。一次调太多参数同时改了 5 个参数——性能变化了但不知道是哪个参数起的作用——无法形成知识。调优不考虑副作用调大 Buffer Pool 提升了命中率——但操作系统可用内存不足导致 swap——性能反而下降了。不考虑 OS 层面的适配忘了调vm.swappiness、文件句柄数、IO 调度器——参数配得再好在 OS 层面被 bottleneck 卡住也白搭。本章教你一套可复现、可回滚、可验证的性能调优方法论——建立基线 → 假设驱动 → 控制变量 → 量化验证 → 输出调优报告。2. 项目设计【场景小胖第 5 次改参数重启 MySQL——这次 TPS 又跌了】小胖“大师为什么我每次调参数——感觉像是拆东墙补西墙调大 Buffer Pool 之后读取变快了——但写入又变慢了——再调大 redo log——磁盘又满了。”大师“因为你没有遵循调优方法论。调优不是’试试这个参数’——而是’我怀疑这个参数是瓶颈——设一个对照组——跑同样的负载——看关键指标TPS、P99 延迟、CPU、IO有没有改善’。关键——一次只改一个参数——改了之后立刻验证——如果不 work——回滚到原值。”小白“那从哪开始先调什么参数”大师“调优有优先级——从收益最大、风险最小的开始。第一层是 OS 层——vm.swappiness1少用 swap、文件句柄ulimit -n 65535、IO 调度器从mq-deadline切换到noneNVMe SSD。第二层是全局参数——innodb_buffer_pool_size最大的杠杆、innodb_log_file_size次大的杠杆。第三层是按业务特征的——innodb_flush_log_at_trx_commit安全 vs 性能取舍、sync_binlog同上。最后一层是查询层面的——慢 SQL 优化、索引调整、SQL 改写——这些是最高杠杆——但需要逐条分析。”技术映射调优优先级 OS 全局参数(BP/redo) 日志刷盘参数 查询层(SQL/索引)。从最宽泛的瓶颈逐层收缩到具体。小胖“那怎么建立基线我跑 sysbench——每次结果都不一样——误差 20%。”大师“基线有四个要求——可复现、可对比、稳定态、覆盖典型负载。可复现——每次用相同的数据集大小、相同并发度、相同测试时间。可对比——测试前记录关键指标TPS、P99、CPU idle%、IO util%——测试后对比。稳定态——先预热 5 分钟——让 Buffer Pool 加载完热点——再测 10 分钟——取后半段的数据排除预热阶段的波动。覆盖典型负载——不是只测纯读或纯写——要覆盖你业务的读写比例比如 80% 读 20% 写。”3. 项目实战3.1 环境准备# 收集当前系统基线信息# OS 层面 echo OS uname-acat/proc/cpuinfo|grepmodel name|head-1free-hdf-hulimit-ncat/sys/block/sda/queue/schedulercat/proc/sys/vm/swappiness# MySQL 层面 echo MySQL mysql-uroot-pRoot123-e SELECT VERSION(); SELECT innodb_buffer_pool_size / 1024 / 1024 / 1024 AS bp_gb; SELECT innodb_log_file_size / 1024 / 1024 AS log_mb; SELECT innodb_flush_log_at_trx_commit; SELECT sync_binlog; SELECT innodb_io_capacity, innodb_io_capacity_max; SELECT max_connections; SELECT thread_cache_size; SELECT tmp_table_size / 1024 / 1024 AS tmp_mb; SELECT innodb_flush_method; SELECT innodb_buffer_pool_instances; SELECT transaction_isolation; 3.2 分步实现——调优闭环阶段一建立性能基线# 步骤目标建立调优前的性能基线——作为后续所有对比的参照# 1. 准备统一数据集sysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host127.0.0.1 --mysql-port3306\--mysql-userroot --mysql-passwordRoot123\--mysql-dbsbtest\--tables10--table-size500000\prepare# 2. 预热 5 分钟sysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host127.0.0.1 --mysql-port3306\--mysql-userroot --mysql-passwordRoot123\--mysql-dbsbtest\--tables10--table-size500000\--threads16--time300\run/dev/null21# 3. 正式基线测试——多个并发梯度echoconcurrency,tps,qps,p95_ms,cpu_pct,io_util_pctbaseline.csvforthreadsin41664128256;doecho Baseline:$threadsthreads # 记录测试前的时间START_TIME$(date%s)# 运行 sysbenchsysbench /usr/share/sysbench/oltp_read_write.lua\--mysql-host127.0.0.1 --mysql-port3306\--mysql-userroot --mysql-passwordRoot123\--mysql-dbsbtest\--tables10--table-size500000\--threads$threads--time120\--report-interval10\run/tmp/sysbench_${threads}.log# 提取指标TPS$(greptransactions:/tmp/sysbench_${threads}.log|tail-1|awk{print $3}|tr-d()QPS$(grepqueries:/tmp/sysbench_${threads}.log|tail-1|awk{print $3}|tr-d()P95$(grep95th percentile/tmp/sysbench_${threads}.log|awk{print $5})# 记录期间的 CPU 和 IO简单方法echo$threads,$TPS,$QPS,$P95baseline.csvdoneechoBaseline completed. Results:catbaseline.csv阶段二假设驱动的参数调优-- 步骤目标逐一调整关键参数并验证效果-- 假设 1Buffer Pool 太小导致命中率不足 -- 查看当前命中率SELECTROUND((1-((SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_reads)/(SELECTVARIABLE_VALUEFROMperformance_schema.global_statusWHEREVARIABLE_NAMEInnodb_buffer_pool_read_requests)))*100,2)ASbp_hit_pct;-- 如果 95% → Buffer Pool 需要调大-- 调整根据物理内存-- SET GLOBAL innodb_buffer_pool_size 4 * 1024 * 1024 * 1024; -- 4GB-- 然后重复基准测试——对比 baseline.csv-- 假设 2redo log 太小导致频繁 Checkpoint -- 查看 Checkpoint Age 信息SHOWENGINEINNODBSTATUS\G-- 搜索 LOG 段——Log sequence number - Last checkpoint at-- 如果这个差值经常接近 innodb_log_file_size × innodb_log_files_in_group-- → redo log 太小——需要调大-- 调整需要重启生效-- 在 my.cnf 中-- innodb_log_file_size 2147483648 (2GB)-- innodb_log_files_in_group 2 (共 4GB)-- 假设 3IO Capacity 不匹配磁盘性能 -- 检查磁盘 IOPS在 OS 层面-- fio --randread --nametest --size1G --rwrandread --bs4k --iodepth32 --numjobs4 --runtime30-- 假设 SSD 的 IOPS 是 50000——但 innodb_io_capacity 只有 200-- SET GLOBAL innodb_io_capacity 2000;-- SET GLOBAL innodb_io_capacity_max 4000;-- 假设 4tmp_table_size 太小导致磁盘临时表 SHOWSTATUSLIKECreated_tmp%;-- 如果 Created_tmp_disk_tables / Created_tmp_tables 0.2-- → 太多临时表写了磁盘——需要增大内存临时表限制-- SET GLOBAL tmp_table_size 256 * 1024 * 1024; -- 256MB-- SET GLOBAL max_heap_table_size 256 * 1024 * 1024;-- 假设 5连接 Thread Cache 不够 SHOWSTATUSLIKEThreads_created;-- 如果这个值一直在增长——说明线程缓存不够——频繁创建/销毁线程-- SET GLOBAL thread_cache_size 256;阶段三OS 层调优# 步骤目标调整操作系统参数——消除 OS 层面的瓶颈# 1. 减少 swap 使用保持数据库数据在内存中sudosysctl-wvm.swappiness1echovm.swappiness1|sudotee-a/etc/sysctl.conf# 2. 增加文件句柄限制sudoulimit-n65535# 永久生效/etc/security/limits.conf 添加:# mysql soft nofile 65535# mysql hard nofile 65535# 3. IO 调度器——NVMe SSD 用 noneSATA SSD 用 noop/mq-deadlineechonone|sudotee/sys/block/nvme0n1/queue/scheduler# 4. 禁用透明大页Transparent Huge Pages# THP 会导致 InnoDB 内存碎片和性能抖动echonever|sudotee/sys/kernel/mm/transparent_hugepage/enabledechonever|sudotee/sys/kernel/mm/transparent_hugepage/defrag# 5. NUMA 优化——如果服务器有多个 NUMA 节点# numactl --interleaveall /usr/sbin/mysqld # 启动时交错分配内存# 或者# innodb_numa_interleave ON # my.cnf 中启# 6. 网络队列sudosysctl-wnet.core.somaxconn65535sudosysctl-wnet.ipv4.tcp_tw_reuse1sudosysctl-wnet.ipv4.tcp_fin_timeout10阶段四火焰图定位热点函数# 步骤目标用 perf 火焰图找到 CPU 最耗时的函数# 1. 采集 perf 数据采集 60 秒sudoperf record-F99-p$(pgrep-xmysqld)-g--sleep60# 2. 生成火焰图sudoperf script|~/FlameGraph/stackcollapse-perf.pl|~/FlameGraph/flamegraph.plmysql_flamegraph.svg# 3. 分析# - 如果最宽的函数是 mutex/spin_lock —— 锁竞争激烈# - 如果最宽的函数是 buf_page_io_complete —— IO 瓶颈# - 如果最宽的函数是 rec_get_offsets —— 行格式解析页内操作多# - 如果最宽的函数是 my_strnncoll_utf8mb4 —— 字符集比较索引键比较成本高# 4. 配合 eBPF 追踪 IO 延迟sudobpftrace-ekprobe:blk_mq_make_request { start[arg0] nsecs; } kretprobe:blk_mq_make_request /start[arg0]/ { io_latency_us hist((nsecs - start[arg0]) / 1000); delete(start[arg0]); }# 查看 IO 延迟分布——如果 P99 5ms——说明磁盘响应慢阶段五输出调优报告模板# MySQL 性能调优报告 ## 1. 环境信息 - 服务器: 8C32G, NVMe SSD 1TB - MySQL: 9.6.0 Innovation - 数据量: orders_large 100 万行, sbtest 500 万行 ## 2. 基线数据调优前 | 并发 | TPS | QPS | P95(ms) | CPU | IO Util | |------|------|-------|---------|-----|---------| | 16 | 1200 | 20400 | 15.2 | 35% | 20% | | 64 | 2100 | 35700 | 42.8 | 65% | 55% | | 128 | 1800 | 30600 | 95.3 | 78% | 90% ← 瓶颈| ## 3. 调优动作 ### 动作 1: 增大 Buffer Pool 128MB → 20GB - 命中率 72% → 99.1% - TPS 64: 15% ### 动作 2: 增大 redo log 48MB → 2GB - Checkpoint Age 从 80MB → 1.5GB (更平滑) - IO Util 128: 90% → 62% ### 动作 3: innodb_io_capacity 200 → 2000 - 脏页刷新更积极——减少了激烈刷新次数 ## 4. 调优后数据 | 并发 | TPS | QPS | P95(ms) | CPU | IO Util | |------|------|-------|---------|-----|---------| | 16 | 1950 | 33150 | 8.2 | 42% | 8% | | 64 | 4200 | 71400 | 28.5 | 72% | 35% | | 128 | 5100 | 86700 | 55.1 | 85% | 62% ← 不再瓶颈| ## 5. 总结 - TPS 提升: 143% (64 并发) - P95 延迟下降: -33% - IO 使用率下降: -32% - 建议: 128 并发时 CPU 85%——已达最优——继续增加并发无益3.3 测试验证# 1. 运行基线测试bashbaseline_test.shcatbaseline.csv# 2. 修改一个参数后重新测试mysql-uroot-pRoot123-eSET GLOBAL innodb_buffer_pool_size 21474836480;# 20GB (如物理内存足够)bashbaseline_test.sh# 重新跑一轮diff(head-5baseline_before.csv)(head-5baseline_after.csv)# 3. 火焰图分析ls-lhmysql_flamegraph.svg# 预期生成 SVG 文件——用浏览器打开可交互式查看# 4. 参数回滚验证# 如果调优后发现性能下降——执行反向操作# SET GLOBAL innodb_buffer_pool_size old_value;# 证明调优动作具有可逆性4. 项目总结优点 缺点维度优点缺点/局限基线-假设-验证闭环可量化、可复现、可回滚——告别凭感觉调参需要时间跑多轮测试——每次 10-30 分钟不等OS MySQL 联合调优消除 OS 层面的隐藏瓶颈swap、THP、IO 调度器OS 调优需要 root 权限——云 RDS 环境不可行火焰图一眼找到 CPU 热点函数——直观高效需要安装 perf/bpftrace 等工具生产环境采样可能影响性能控制变量法一次只改一个参数——因果明确耗时较长——如果怀疑 10 个参数需要跑 10 轮测试调优报告团队知识沉淀——新人可直接参考历史报告报告中的数字仅对当时数据和负载有效——6 个月后可能过期适用场景系统迁移/升级从物理机迁到云——从 HDD 换到 SSD——重新建立基线。大促前性能准备双十一前一个月——根据预估并发做调优和压测。性能劣化后排查系统突然慢了——对比当前和 3 个月前的基线——定位退化点。新业务上线新业务有独特的读写比例和热点模式——独立建立基线。技术培训调优报告作为团队的性能优化案例库。不适用场景云 RDS 环境大部分 OS 参数和部分 MySQL 参数由云厂商管理——调优空间有限。临时的一次性问题如果问题是一次性的比如被某个慢 SQL 拖垮——不需要完整调优流程——直接定位并优化那条 SQL。注意事项改innodb_log_file_size需要重启 MySQL——且有数据丢失风险如果操作不当删除旧 redo log。操作前先全量备份。innodb_buffer_pool_size在线调整MySQL 5.7 支持在线 resize——但仍会短暂锁 BP——生产低峰操作。THP 关闭之后——InnoDB 需要重启才能享受完整收益因为已有的 THP 内存分配不会释放。常见踩坑经验故障案例一调innodb_buffer_pool_size到 28GB——服务器有 32GB 内存——结果 OOM。根因忘记 OS 本身需要约 2-4GB 内存——加上连接线程开销——32GB 内存 BP 设 28GB 直接 OOM。修复BP 物理内存 × 0.732GB → 22GB。故障案例二sysbench 结果波动大——连续三次测出来 TPS 分别是 1000、2000、1500。根因没预热——第一次测试 Buffer Pool 是冷的——读 IO 多——TPS 低。修复每次测试前预热 5 分钟——取预热后的稳定态数据。故障案例三改完一堆参数后 MySQL 启动失败。根因改了innodb_log_file_size但没有删除旧 redo log 文件——启动时 InnoDB 检测 redo 文件 size 不匹配。修复删除旧的#innodb_redo目录下文件——启动后 InnoDB 会自动创建新大小的 redo log。思考题为什么innodb_io_capacity设太大比如 20000反而不好InnoDB 的刷盘策略会受什么负面影响同一个基线测试——在物理机上和容器里Docker跑出的 TPS 可能相差 10-20%——主要原因是什么答案提示第 1 题——设太大导致 InnoDB 过快刷脏页——每次刷盘的 IO 量超过磁盘能承受的并发数——读请求被写请求阻塞——总体 TPS 反而下降第 2 题——容器内的 IOoverlay2/aufs有额外开销——Docker 的--volume性能优于--mount——且容器内的 CPU 调度可能受宿主机的 cgroup 限制。延伸阅读与资源Java 工程师进阶从 JVM 生产排障到OpenJDK原理NumPy 从入门到生产落地全链路实战指南科学计算/向量化Redis 8 实战精讲从 CRUD 到源码构建高可用缓存系统Redis 实战修炼与原理进阶Python 3实战精进从脚本到高并发订单引擎python入门Rquests从菜鸟脚本到企业级SDK的网络实战圣经Milvus向量数据库实战修炼从 0 到 1精通向量检索与生产落地MongoDB 实战进阶与内核修炼后端工程师的 AI 转型第一课Ollama 与私有化大模型实战10倍开发者的 Dify 魔法书从零构建全栈 AI 应用后端工程师转型AI第一课-Ollama 与私有化大模型实战大型语言模型(LLM) vLLM 高性能推理落地实战Agent开发之LlamaIndex 实战修炼与源码进阶大语言模型Transformers 实战修炼与源码剖析
