6.3 体能训练：服务器参数调优

6.3 体能训练：服务器参数调优

本节摘要：参数调优的优先级是：内存给足、连接管好、日志不拖后腿。本节给出一台 16GB 专用服务器的参考配置与每项的判断依据，并示范用状态变量验证调优效果。

参考配置与依据

[mysqld]
# 内存：专用机器给到 50% 到 70%
innodb_buffer_pool_size = 10G

# 连接：按应用真实并发设，不是越大越好
max_connections = 500
thread_cache_size = 64

# 日志：控制单个文件大小，避免重启大文件初始化
innodb_redo_log_capacity = 2G
max_binlog_size = 512M
binlog_expire_logs_seconds = 604800

# 临时表：列表页排序聚合常用
tmp_table_size = 256M
max_heap_table_size = 256M
​

为什么是这些

buffer_pool_size 是第一杠杆。 第 3 章讲过命中率检查，内存装得下热点数据，绝大多数读都不落盘。它从 2G 调到 10G 带来的提升，通常超过其他所有参数的总和。

max_connections 不是安全垫。 每个连接消耗内存，几千个空闲连接能把机器拖垮。应用侧连接池（典型几十个连接）加上合理的 max_connections，比开一个大口子健康得多。

tmp_table_size 治排序溢出。 验证方法：

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables 与 Created_tmp_files 持续增长
-- 说明内存临时表不够，聚合排序正在落盘
​

调优的方法论

一次只动一个参数，观察一周再动下一个；每个参数都配一条验证用的状态指标。没有度量就调参数，等于不量血压就开降压药。

图：参数优先级与验证指标

⚠️ 常见坑：网上抄来的"万能优化配置"把 sort_buffer_size 调到几百 MB。这类会话级参数是每连接一份，500 个连接就是几十 GB 的承诺，OOM 就这么来的。

压测：调优前的体测

不压测就调参等于不体测就开训练计划。轻量压测用自带的 mysqlslap 就能起步：

# 模拟 50 并发、每连接 200 次查询的混合负载
mysqlslap --concurrency=50 --iterations=3 --number-of-queries=200 \
  --create-schema=clinic --query="SELECT * FROM clinic_order WHERE user_id=1001" \
  -utrainee -p
​

专业些的用 sysbench：准备一张百万行的测试表，分别跑点查、范围查、混合读写三个剧本，各记录吞吐与延迟百分位。调优前后各跑一轮同样剧本，提升多少一目了然。盯指标时看 P95 与 P99 而不是平均值——平均 10 毫秒 P99 两秒的系统，用户的感受是"时快时慢"。

一次完整的调优回合

把方法论落成一个可照抄的回合。回合前记录基线：压测三个剧本的指标，加 SHOW GLOBAL STATUS 里十来个关键计数器的快照。回合中只动一个参数，比如把 innodb_redo_log_capacity 从默认 100M 提到 2G，重启生效。回合后复测同剧本，对比基线，同时看两个专属验证指标——写入类剧本的吞吐与 checkpoint 频率。有效就保留，无效就回滚，一个回合一到两天。这套纪律看似慢，实际是最快的路：无度量的调优里，人们会把巧合当成疗效、把副作用当成玄学，最后参数表变成没人敢动的祖传配方。

-- 基线快照与复测快照用同一条取数
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
​

参数之外的两个杠杆

参数调优的天花板由两件事决定，值得在投入调参前先检查。一是硬件配置：机械盘换 SSD 对随机 IO 的提升是数量级的，任何参数都调不出来；内存从 8G 到 32G 让缓冲池装下全量热数据，命中率问题的根治方案。二是负载结构：一条每秒执行几百次的烂 SQL 拖垮的系统，把参数调出花也没用，先回第 4 章动手术。参数是第三杠杆，排在硬件与 SQL 之后——认清这个次序，能省下大量在错误层面使劲的时间。

三个高频争议参数的裁决

调优讨论里反复出现三个争议参数，给一份裁决参考。sort_buffer_size：会话级排序内存，默认两三 MB，调大只对少量巨大排序有益，且每连接一份，大量小排序场景调大纯亏，保持默认、真有大排序再会话级临时调。innodb_flush_log_at_trx_commit 与 sync_binlog：这对安全与性能的双子参数，金融场景双一（都是 1）不动摇，可容忍秒级丢失的业务调成二加百（2 与 100）换取明显吞吐，一半一半的组合是最差的中间态。max_connections 与连接池的关系再强调一次：数据库侧的连接上限是保险丝不是加速器，真正的并发控制永远在应用连接池侧完成：

-- 会话级临时调大排序缓冲跑完大报表再退出
SET SESSION sort_buffer_size = 64 * 1024 * 1024;
​

最后交代调优的边界：参数调不出来的三类病，认得出它们才能不浪费时间。病一是数据量病，三亿行的表配任何参数都是三亿行，归档与分区才是药。病二是 SQL 病，一条烂查询拖垮的系统，参数只能延缓症状，回第 4 章动手术。病三是架构病，单机写入顶到天花板时，读写分离、分库、换专用组件是唯一出路。三类病各有各的科室，参数调优室的门口应该挂一块牌子：只接内存不足、日志配置、连接管理这三类内伤病，其余转诊。

本节要点回顾

• 优先级：缓冲池 > 日志容量 > 连接管理 > 临时表，最后才是细枝末节
• 每项必有验证指标，用 SHOW GLOBAL STATUS 说话
• 会话级参数乘以连接数才是真实内存承诺