1. 先说结论为什么我把“最重要”框死在两个参数上前阵子帮朋友接手一台“已经调优过”的MySQL服务器。配置文件翻开来洋洋洒洒改了三十多个参数从max_connections到tmp_table_size从query_cache_type到key_buffer_size看着挺唬人。结果压测一跑TPS还不如装好后的默认配置磁盘IO还时不时飙红。后来我把配置逐步还原只重点动了两组参数性能反而翻身了。这事儿让我越想越觉得值得写一篇。因为很多刚接触MySQL性能调优的人一上来就被各种参数清单吓住了。什么innodb_read_io_threads、table_open_cache、sort_buffer_size每个看起来都有用每个调完都感觉“应该更好了”。但实际上真正决定一个MySQL实例能不能扛住业务压力的绝大多数情况下就那么两下子InnoDB缓冲池有多大innodb_buffer_pool_size事务日志和二进制日志按什么节奏落盘innodb_flush_log_at_trx_commit与sync_binlog的组合我把话说得这么绝对肯定有人不服。没关系这篇文章就把这两组参数的原理、配置方法、联动关系和真实调优过程全讲透。你照着检查一遍大概率会发现自己之前花大量精力折腾的几十个参数加起来都不如这俩调对了收益大。顺便交代一下文章适合谁如果你是刚开始接触MySQL调优的开发者或者要接手一个性能稀烂的存量实例这篇文章可以作为你的第一份“排查清单”。如果你已经有一定DBA经验那重点看第3章和第4章我会聊一些文档里不常写、但实际运维中很容易翻车的细节。2. 第一个关键参数innodb_buffer_pool_size——内存工作台的大小2.1 为什么Buffer Pool能一票否决性能先讲个生活化的类比。innodb_buffer_pool_size就是InnoDB的“厨房操作台”所有要处理的数据页、索引页都得先端到台面上来干活。操作台越大能同时摊开处理的食材越多厨师CPU就不用一趟趟跑去仓库磁盘取货。操作台太小哪怕仓库里堆满了货厨师也得在“取货-干活-取货-干活”之间反复折腾活活把瓶颈卡在IO上。这个类比放到数据库里就是InnoDB每次读写数据优先访问Buffer Pool命中了就直接在内存里返回结果没命中就得从磁盘把数据页读进Buffer Pool再处理。磁盘随机读的延迟是内存的几十倍往上所以Buffer Pool命中率基本决定了热点数据的访问速度。但更隐蔽的是写路径。INSERT、UPDATE、DELETE并不是直接改磁盘上的数据文件而是先在Buffer Pool里把对应的数据页改掉这些被改过的页就是“脏页”。脏页积累到一定程度后台线程才会把它们刷回磁盘。如果Buffer Pool太小脏页还没攒够批量写的量就被迫频繁刷盘写入性能直接拉胯。2.2 默认值为什么会坑你MySQL的innodb_buffer_pool_size默认值是128M。在八年前一台机器给MySQL分128MB内存还算合理。但现在随便一台云服务器都是16GB起步跑个业务库把Buffer Pool留在默认值等于开着一辆满载的卡车却只用了1/8的油箱。我见过最典型的一个案例客户说是“数据库很慢”我连上去查了三个指标就定位了——Innodb_buffer_pool_read_requests高达几千万而Innodb_buffer_pool_reads也到了几十万级别命中率算下来才刚过90%。对于OLTP业务来说Buffer Pool命中率低于99%都是不太正常的低于95%基本就是灾难级。当时那台服务器内存32GBBuffer Pool却是默认的128MB这属于典型的“硬件买了不用”。2.3 值到底怎么定不是拍脑袋按比例乘网上很多教程会告诉你“设为物理内存的70%”。这个说法大方向没错但真照着做容易出事。因为MySQL可不止Buffer Pool一块内存要吃饭每个连接都有自己的sort_buffer_size、join_buffer_size、read_buffer_size连接数一多这些会话级内存加起来非常可观。临时表落内存时要占tmp_table_size和max_heap_table_size的空间。操作系统本身要留内存做页缓存Page Cache尤其read-only场景下页缓存还能帮忙扛读。MySQL 8.0的字典缓存、锁结构、自适应哈希索引也会吃内存。所以我通常建议这样算建议值 (总内存 - 操作系统预留 - 其他进程占用) × 70%~80%举例一台16GB内存、只跑MySQL的机器操作系统预留2GB其他进程算1GB剩13GB那么Buffer Pool取9GB到10GB比较稳。一台64GB内存、上面还跑着Java应用的机器就得先减去Java的堆内存再算MySQL的份额。不过有一个细节很重要innodb_buffer_pool_size在MySQL 5.7及以上版本支持在线调整不需要重启。MySQL 5.7还能用innodb_buffer_pool_instances把它拆成多个分片来减少并发访问的锁竞争。如果你的实例还在用默认值可以先用下面这条命令在线改到目标值再观察一段时间-- 查看当前值单位是字节 SHOW VARIABLES LIKE innodb_buffer_pool_size; -- 在线调整为 8GBMySQL 5.7 支持 SET GLOBAL innodb_buffer_pool_size 8589934592;注意要持久化不然重启就丢。可以用SET PERSISTMySQL 8.0或者写进my.cnf的[mysqld]段。2.4 用命中率验证调得对不对设完值别急着走用命中率来证明问题确实解决或没解决。命中率有两个视角读命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_%; -- 命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads)正常业务下这个值应该在99%以上。如果长期低于98%要么Buffer Pool太小要么SQL写得太烂—全表扫描把整个表都读进内存命中率自然被拉低。脏页刷盘指标SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_pages_dirty;这个值如果长期占Buffer Pool的20%以上说明后台刷脏速度跟不上产生速度。这时光加Buffer Pool是不够的你还得检查刷盘策略的问题——这就引出第二个参数了。3. 第二个关键参数事务刷盘策略——双1组合与性能/安全的天平3.1 一对参数三档选择让你看懂取舍Buffer Pool解决的是“内存够不够用”的问题但内存里的数据再热最终也得落回磁盘才安全。MySQL的落盘分成两层一层是InnoDB自己的redo log重做日志另一层是binlog二进制日志主要用于复制和时间点恢复。决定这两层日志什么时候刷到磁盘的两个参数分别是innodb_flush_log_at_trx_commit控制redo log刷盘频率sync_binlog控制binlog刷盘频率先看innodb_flush_log_at_trx_commit的三个值取值行为性能数据安全性0每秒刷一次磁盘事务提交时不主动刷靠后台每秒刷盘最好最差数据库崩溃或主机断电可能丢最近1秒的事务1每次事务提交都刷盘最差但最可靠最好理论上提交即持久化2每次事务提交把日志写到操作系统缓存每秒异步刷盘中中MySQL进程崩溃不丢数据但主机断电可能丢1秒数据再看sync_binlogsync_binlog0binlog写入由操作系统决定何时落盘性能最好但最多可能丢最近一段时间的binlog。sync_binlog1每次事务提交都强制binlog刷盘最安全但开销最大。sync_binlogNN大于1每N次事务提交才刷一次盘性能和安全性折中是很多高并发场景的常用配置。3.2 “双1”到底能不能开组提交帮你摊平成本所谓的“双1”就是innodb_flush_log_at_trx_commit1加上sync_binlog1。这组合是数据安全的标准配置但很多人一听就皱眉“每次提交都刷两次盘那性能还能看吗”这里有个关键认知MySQL在双1配置下并没有让每个事务都傻乎乎地等两次独立fsync。InnoDB和binlog有组提交Group Commit机制——多个事务在提交阶段会排队把同一时间窗内的若干次刷盘合并成一次fsync。尤其是并发越高组提交的收益越明显每个事务分摊到的刷盘成本会被摊得很薄。我在压测环境里实测过一台普通的SSD云主机纯并发写入场景下双1配置和sync_binlog1000的配置相比性能差距大约在20%到40%之间。这个差距对核心业务来说完全可以接受——毕竟谁也不想为了快那零点几毫秒承担数据库崩溃丢数据、主从复制错位的风险。所以我的建议是核心业务库闭眼上双1非核心内部系统可以放宽到innodb_flush_log_at_trx_commit2sync_binlog1000。至于innodb_flush_log_at_trx_commit0这种激进配置除非你明确知道自己在干什么比如纯临时数据、可以随时重建的库否则别碰。3.3 从innodb_log_file_size到innodb_redo_log_capacity的演变这里补一个很容易被忽略但和刷盘强相关的点redo log本身的空间大小。redo log是环形写的空间太小意味着日志切换频繁每次切换都要触发一次checkpoint把脏页强制刷盘。Buffer Pool调大之后脏页产生的峰值会更高如果redo log还停留在默认的100MB级别刷盘压力会明显增大。MySQL 8.0.30及以后官方用innodb_redo_log_capacity替代了原来的innodb_log_file_size和innodb_log_files_in_group默认值也从100MB左右提高到了100MB的若干倍而且支持动态调整。建议直接给大一点# MySQL 8.0.30 推荐配置 innodb_redo_log_capacity 4G如果是MySQL 5.7或8.0早期版本那就用老参数组合把redo log总容量设置为Buffer Pool的1/8到1/4左右。很多DBA只盯着Buffer Pool加内存忘了同步检查redo log容量结果总感觉“内存加大了刷盘反而更频繁了”——其实就是日志空间没跟上。3.4 两个参数这样配实操落地方案我把常见场景的推荐写成了表格方便你直接对着抄业务类型innodb_flush_log_at_trx_commitsync_binlog说明金融/订单/核心交易11双1数据安全最高优先级一般互联网业务11性能差距可控建议顶配内部系统/报表库21000允许极端断电丢1秒数据日志分析/临时库00可接受较大数据丢失修改方式不复杂先查当前值再动态改最后写进配置文件SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE sync_binlog; SET GLOBAL innodb_flush_log_at_trx_commit 1; SET GLOBAL sync_binlog 1;但注意sync_binlog和innodb_flush_log_at_trx_commit的GLOBAL值在线改了能立刻生效但my.cnf里的持久化一定要同步改否则下次重启又回到解放前。4. 这对参数之间的联动效应以及被忽略的配套项4.1 只调一个参数性能反而更烂的场景前面说的是两个参数各自的职责但实际调优中它们经常是联动的。最典型的反面案例是只调大Buffer Pool不动刷盘策略结果性能不升反降。听起来反直觉但原理很好理解。Buffer Pool调大之后脏页的“蓄水池”变大了能攒下更多未刷盘的修改页。这本是好事因为后台刷盘可以更从容地批量写。但如果你把redo log容量调太小或者刷盘参数配置过于激进比如innodb_flush_log_at_trx_commit0后台线程会频繁触发checkpoint大量脏页集中刷盘磁盘IO瞬间被拉满。此时你去看iostatutil基本都是100%查询再快也被写拖死。所以我的调优顺序永远是固定的先看Buffer Pool够不够再看redo log容量够不够最后才调整刷盘频率。任何一步跳过都可能踩到“单点调优”的坑。4.2 别忽视innodb_flush_method这个兼容伴侣innodb_flush_method不在“2个最重要参数”里但它和两个主参数的关系紧密到值得一提尤其在Linux环境下。MySQL 8.0默认用的是fsync方式数据文件和日志文件都通过操作系统的Page Cache再写入磁盘。这样写其实有一层缓存兜底但问题也很明显存在双写缓冲的浪费刷盘路径更长。对性能敏感的生产库绝大多数DBA会建议用O_DIRECTinnodb_flush_method O_DIRECTO_DIRECT的意思是数据文件的读写绕过操作系统Page Cache由InnoDB自己管理缓存。这里的“缓存”指的就是Buffer Pool——所以你看到没它和innodb_buffer_pool_size是配套的既然你打算用Buffer Pool扛大部分IO那就应该让数据文件读写走O_DIRECT别再让OS Page Cache多插一脚。有个常见误区是很多人以为O_DIRECT“会丢数据”其实它丢的只是Page Cache这一层不在事务提交时强制刷盘的保护逻辑内。真正负责“提交即持久化”的还是innodb_flush_log_at_trx_commit两个参数的职责是分开的。4.3 还有哪些参数值得看但我不会天天动它标题说“只有2个最重要”不是否定其他参数的存在意义。只是说在排查性能问题时它们的优先级远没有前两个高。下面这些属于“值得确认但不值得反复折腾”的配套项参数作用我什么时候会动它max_connections最大连接数连接被拒、Too many connections报错时innodb_io_capacity/innodb_io_capacity_max后台刷脏的IO上限磁盘是SSD时把它从默认200调高到800-2000transaction_isolation事务隔离级别业务默认REPEATABLE READ没问题别乱降long_query_time慢查询阈值排查慢SQL时设为1秒binlog_formatbinlog格式有主从复制时保持默认ROW即可这些参数的特点是调了能带来5%-10%的边际优化但调错了可能引发连锁问题。而innodb_buffer_pool_size和刷盘策略这俩调对了是质的飞跃调错了也是质的翻车。这就是我说“最重要”的真正含义——影响面最大收益最明显。5. 一次真实调优案例从连接池告警到TPS翻身5.1 现场情况和第一轮排查几个月前有个做游戏运营的朋友找我说他家一个MySQL实例最近老是被连接池告警轰炸业务高峰期应用端的“获取连接超时”频繁出现。我登上去一看规格8核16GB的云主机单实例MySQL上面同时还有两个轻量Java服务在跑。第一件事跑几个全局状态SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE sync_binlog; SHOW VARIABLES LIKE innodb_redo_log_capacity; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads; SHOW GLOBAL STATUS LIKE Threads_connected;结果如下innodb_buffer_pool_size 128MB默认值innodb_flush_log_at_trx_commit 1sync_binlog 1innodb_redo_log_capacity 100MB8.0.30之前实例命中率粗算只有91%左右Threads_connected在100上下浮动而机器的16GB内存闲着将近10GB。这台服务器的配置水平属于典型的“买了高配却跑着乞丐设置”。5.2 调整过程先内存再日志后验证我没有一次性把所有参数全改掉那样出了问题没法定位。调整顺序是第一步Buffer Pool从128MB调到8GB。SET GLOBAL innodb_buffer_pool_size 8589934592;在线调整生效后用SHOW ENGINE INNODB STATUS确认没有报错。调大Buffer Pool时要留意MySQL需要把新空间的内存页初始化期间会有短暂的内存分配操作但过程中服务不中断。实际上这一步的收益立竿见影——只过了不到5分钟磁盘读的次数就明显下降了。第二步redo log容量从100MB调到2GB。因为这台实例当时MySQL版本较低用的是老参数innodb_log_file_size 2G innodb_log_files_in_group 2这里插一句在线改innodb_log_file_size在MySQL 5.7和8.0早期版本必须重启才能生效。所以我把配置写进my.cnf然后挑了个凌晨的低峰窗口重启了一次。如果当时是8.0.30直接用innodb_redo_log_capacity就能在线改省掉重启。第三步刷盘策略保持双1不动。是你没看错我在这台实例上没降刷盘要求。因为调整完前两个参数后业务高峰期的TPS已不再被IO拖后腿双1的额外开销已经被组提交机制摊得很低。为了挽回那一点点性能去牺牲数据安全不划算。5.3 结果和复盘为什么我只动了这几个地方调优后的数据对比指标调整前高峰调整后高峰Buffer Pool命中率91%99.6%磁盘读IOPS80002000不到获取连接超时告警频繁0应用侧TPS3000左右7500左右整个过程中我唯一修改的就是Buffer Pool和redo log容量刷盘策略保持双1。这个案例恰好印证了文章标题这台实例真正缺的不是什么花哨的调优技巧而是那两个最核心的“缸”——内存工作台太小事务日志空间太小再多其他参数也白搭。5.4 这个案例里隐藏的两个坑复盘时我发现两个值得提醒的细节第一在线调Buffer Pool时不要在业务高峰期一次性从128MB直接拉到12GB。虽然官方支持在线调整但大额度的空间扩展会触发内部结构重建可能导致短暂性能抖动。稳妥做法是分两次比如先到4GB观察半小时再拉到8GB。第二检查一下innodb_buffer_pool_instances。默认情况下Buffer Pool小于1GB时只有一个实例大于1GB时默认拆成8个。这次调整后8GB的Buffer Pool配8个实例并发访问的锁争用也小了。如果你的实例还在用单实例大池子可以试试给拆开。6. 最后再分享一个小技巧调完参数别急着下结论我在实际运维里养成的习惯是调完任何关键参数后至少观察一到两周再下结论。因为MySQL的性能表现受业务波动影响很大今天的高峰和下周的高峰可能差出一倍。我见过有人把Buffer Pool调大后第二天看到命中率还不到98%就以为调错了结果第五天业务量上来后命中率自己就稳到99.5%了——纯粹是被周末低峰期的数据误导了。如果要给一个快速自查动作那就是每次改完参数在同一个业务周期点比如每天同一时间记录三个值Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads、Innodb_buffer_pool_pages_dirty。连续记录三天看趋势而不是看某一个瞬间。另外如果你的实例还在MySQL 8.0.30之前升级的时候记得重新评估redo log容量。因为新版本默认的innodb_redo_log_capacity会自动适应负载但旧版本升级后仍会沿用老的日志文件配置。我踩过一次坑升级后忘了检查结果日志空间还是2GB而Buffer Pool已经调到16GB脏页峰一来checkpoint频繁到磁盘IO被打满。后来把innodb_redo_log_capacity调到8GB才彻底消停。说到底MySQL性能调优从来不是参数越多越厉害而是把影响面最大的那几个变量吃透。Buffer Pool决定你的数据能不能在内存里转起来刷盘策略决定你的写入能不能在安全和性能之间找到平衡点。这两组参数拨正了剩下的调优才有意义。否则就像一辆轮胎都没气的车你花再多心思去调座椅和后视镜也跑不快。
MySQL调优最重要的两个参数：缓冲池与刷盘策略
1. 先说结论为什么我把“最重要”框死在两个参数上前阵子帮朋友接手一台“已经调优过”的MySQL服务器。配置文件翻开来洋洋洒洒改了三十多个参数从max_connections到tmp_table_size从query_cache_type到key_buffer_size看着挺唬人。结果压测一跑TPS还不如装好后的默认配置磁盘IO还时不时飙红。后来我把配置逐步还原只重点动了两组参数性能反而翻身了。这事儿让我越想越觉得值得写一篇。因为很多刚接触MySQL性能调优的人一上来就被各种参数清单吓住了。什么innodb_read_io_threads、table_open_cache、sort_buffer_size每个看起来都有用每个调完都感觉“应该更好了”。但实际上真正决定一个MySQL实例能不能扛住业务压力的绝大多数情况下就那么两下子InnoDB缓冲池有多大innodb_buffer_pool_size事务日志和二进制日志按什么节奏落盘innodb_flush_log_at_trx_commit与sync_binlog的组合我把话说得这么绝对肯定有人不服。没关系这篇文章就把这两组参数的原理、配置方法、联动关系和真实调优过程全讲透。你照着检查一遍大概率会发现自己之前花大量精力折腾的几十个参数加起来都不如这俩调对了收益大。顺便交代一下文章适合谁如果你是刚开始接触MySQL调优的开发者或者要接手一个性能稀烂的存量实例这篇文章可以作为你的第一份“排查清单”。如果你已经有一定DBA经验那重点看第3章和第4章我会聊一些文档里不常写、但实际运维中很容易翻车的细节。2. 第一个关键参数innodb_buffer_pool_size——内存工作台的大小2.1 为什么Buffer Pool能一票否决性能先讲个生活化的类比。innodb_buffer_pool_size就是InnoDB的“厨房操作台”所有要处理的数据页、索引页都得先端到台面上来干活。操作台越大能同时摊开处理的食材越多厨师CPU就不用一趟趟跑去仓库磁盘取货。操作台太小哪怕仓库里堆满了货厨师也得在“取货-干活-取货-干活”之间反复折腾活活把瓶颈卡在IO上。这个类比放到数据库里就是InnoDB每次读写数据优先访问Buffer Pool命中了就直接在内存里返回结果没命中就得从磁盘把数据页读进Buffer Pool再处理。磁盘随机读的延迟是内存的几十倍往上所以Buffer Pool命中率基本决定了热点数据的访问速度。但更隐蔽的是写路径。INSERT、UPDATE、DELETE并不是直接改磁盘上的数据文件而是先在Buffer Pool里把对应的数据页改掉这些被改过的页就是“脏页”。脏页积累到一定程度后台线程才会把它们刷回磁盘。如果Buffer Pool太小脏页还没攒够批量写的量就被迫频繁刷盘写入性能直接拉胯。2.2 默认值为什么会坑你MySQL的innodb_buffer_pool_size默认值是128M。在八年前一台机器给MySQL分128MB内存还算合理。但现在随便一台云服务器都是16GB起步跑个业务库把Buffer Pool留在默认值等于开着一辆满载的卡车却只用了1/8的油箱。我见过最典型的一个案例客户说是“数据库很慢”我连上去查了三个指标就定位了——Innodb_buffer_pool_read_requests高达几千万而Innodb_buffer_pool_reads也到了几十万级别命中率算下来才刚过90%。对于OLTP业务来说Buffer Pool命中率低于99%都是不太正常的低于95%基本就是灾难级。当时那台服务器内存32GBBuffer Pool却是默认的128MB这属于典型的“硬件买了不用”。2.3 值到底怎么定不是拍脑袋按比例乘网上很多教程会告诉你“设为物理内存的70%”。这个说法大方向没错但真照着做容易出事。因为MySQL可不止Buffer Pool一块内存要吃饭每个连接都有自己的sort_buffer_size、join_buffer_size、read_buffer_size连接数一多这些会话级内存加起来非常可观。临时表落内存时要占tmp_table_size和max_heap_table_size的空间。操作系统本身要留内存做页缓存Page Cache尤其read-only场景下页缓存还能帮忙扛读。MySQL 8.0的字典缓存、锁结构、自适应哈希索引也会吃内存。所以我通常建议这样算建议值 (总内存 - 操作系统预留 - 其他进程占用) × 70%~80%举例一台16GB内存、只跑MySQL的机器操作系统预留2GB其他进程算1GB剩13GB那么Buffer Pool取9GB到10GB比较稳。一台64GB内存、上面还跑着Java应用的机器就得先减去Java的堆内存再算MySQL的份额。不过有一个细节很重要innodb_buffer_pool_size在MySQL 5.7及以上版本支持在线调整不需要重启。MySQL 5.7还能用innodb_buffer_pool_instances把它拆成多个分片来减少并发访问的锁竞争。如果你的实例还在用默认值可以先用下面这条命令在线改到目标值再观察一段时间-- 查看当前值单位是字节 SHOW VARIABLES LIKE innodb_buffer_pool_size; -- 在线调整为 8GBMySQL 5.7 支持 SET GLOBAL innodb_buffer_pool_size 8589934592;注意要持久化不然重启就丢。可以用SET PERSISTMySQL 8.0或者写进my.cnf的[mysqld]段。2.4 用命中率验证调得对不对设完值别急着走用命中率来证明问题确实解决或没解决。命中率有两个视角读命中率SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_%; -- 命中率 Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests Innodb_buffer_pool_reads)正常业务下这个值应该在99%以上。如果长期低于98%要么Buffer Pool太小要么SQL写得太烂—全表扫描把整个表都读进内存命中率自然被拉低。脏页刷盘指标SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_pages_dirty;这个值如果长期占Buffer Pool的20%以上说明后台刷脏速度跟不上产生速度。这时光加Buffer Pool是不够的你还得检查刷盘策略的问题——这就引出第二个参数了。3. 第二个关键参数事务刷盘策略——双1组合与性能/安全的天平3.1 一对参数三档选择让你看懂取舍Buffer Pool解决的是“内存够不够用”的问题但内存里的数据再热最终也得落回磁盘才安全。MySQL的落盘分成两层一层是InnoDB自己的redo log重做日志另一层是binlog二进制日志主要用于复制和时间点恢复。决定这两层日志什么时候刷到磁盘的两个参数分别是innodb_flush_log_at_trx_commit控制redo log刷盘频率sync_binlog控制binlog刷盘频率先看innodb_flush_log_at_trx_commit的三个值取值行为性能数据安全性0每秒刷一次磁盘事务提交时不主动刷靠后台每秒刷盘最好最差数据库崩溃或主机断电可能丢最近1秒的事务1每次事务提交都刷盘最差但最可靠最好理论上提交即持久化2每次事务提交把日志写到操作系统缓存每秒异步刷盘中中MySQL进程崩溃不丢数据但主机断电可能丢1秒数据再看sync_binlogsync_binlog0binlog写入由操作系统决定何时落盘性能最好但最多可能丢最近一段时间的binlog。sync_binlog1每次事务提交都强制binlog刷盘最安全但开销最大。sync_binlogNN大于1每N次事务提交才刷一次盘性能和安全性折中是很多高并发场景的常用配置。3.2 “双1”到底能不能开组提交帮你摊平成本所谓的“双1”就是innodb_flush_log_at_trx_commit1加上sync_binlog1。这组合是数据安全的标准配置但很多人一听就皱眉“每次提交都刷两次盘那性能还能看吗”这里有个关键认知MySQL在双1配置下并没有让每个事务都傻乎乎地等两次独立fsync。InnoDB和binlog有组提交Group Commit机制——多个事务在提交阶段会排队把同一时间窗内的若干次刷盘合并成一次fsync。尤其是并发越高组提交的收益越明显每个事务分摊到的刷盘成本会被摊得很薄。我在压测环境里实测过一台普通的SSD云主机纯并发写入场景下双1配置和sync_binlog1000的配置相比性能差距大约在20%到40%之间。这个差距对核心业务来说完全可以接受——毕竟谁也不想为了快那零点几毫秒承担数据库崩溃丢数据、主从复制错位的风险。所以我的建议是核心业务库闭眼上双1非核心内部系统可以放宽到innodb_flush_log_at_trx_commit2sync_binlog1000。至于innodb_flush_log_at_trx_commit0这种激进配置除非你明确知道自己在干什么比如纯临时数据、可以随时重建的库否则别碰。3.3 从innodb_log_file_size到innodb_redo_log_capacity的演变这里补一个很容易被忽略但和刷盘强相关的点redo log本身的空间大小。redo log是环形写的空间太小意味着日志切换频繁每次切换都要触发一次checkpoint把脏页强制刷盘。Buffer Pool调大之后脏页产生的峰值会更高如果redo log还停留在默认的100MB级别刷盘压力会明显增大。MySQL 8.0.30及以后官方用innodb_redo_log_capacity替代了原来的innodb_log_file_size和innodb_log_files_in_group默认值也从100MB左右提高到了100MB的若干倍而且支持动态调整。建议直接给大一点# MySQL 8.0.30 推荐配置 innodb_redo_log_capacity 4G如果是MySQL 5.7或8.0早期版本那就用老参数组合把redo log总容量设置为Buffer Pool的1/8到1/4左右。很多DBA只盯着Buffer Pool加内存忘了同步检查redo log容量结果总感觉“内存加大了刷盘反而更频繁了”——其实就是日志空间没跟上。3.4 两个参数这样配实操落地方案我把常见场景的推荐写成了表格方便你直接对着抄业务类型innodb_flush_log_at_trx_commitsync_binlog说明金融/订单/核心交易11双1数据安全最高优先级一般互联网业务11性能差距可控建议顶配内部系统/报表库21000允许极端断电丢1秒数据日志分析/临时库00可接受较大数据丢失修改方式不复杂先查当前值再动态改最后写进配置文件SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE sync_binlog; SET GLOBAL innodb_flush_log_at_trx_commit 1; SET GLOBAL sync_binlog 1;但注意sync_binlog和innodb_flush_log_at_trx_commit的GLOBAL值在线改了能立刻生效但my.cnf里的持久化一定要同步改否则下次重启又回到解放前。4. 这对参数之间的联动效应以及被忽略的配套项4.1 只调一个参数性能反而更烂的场景前面说的是两个参数各自的职责但实际调优中它们经常是联动的。最典型的反面案例是只调大Buffer Pool不动刷盘策略结果性能不升反降。听起来反直觉但原理很好理解。Buffer Pool调大之后脏页的“蓄水池”变大了能攒下更多未刷盘的修改页。这本是好事因为后台刷盘可以更从容地批量写。但如果你把redo log容量调太小或者刷盘参数配置过于激进比如innodb_flush_log_at_trx_commit0后台线程会频繁触发checkpoint大量脏页集中刷盘磁盘IO瞬间被拉满。此时你去看iostatutil基本都是100%查询再快也被写拖死。所以我的调优顺序永远是固定的先看Buffer Pool够不够再看redo log容量够不够最后才调整刷盘频率。任何一步跳过都可能踩到“单点调优”的坑。4.2 别忽视innodb_flush_method这个兼容伴侣innodb_flush_method不在“2个最重要参数”里但它和两个主参数的关系紧密到值得一提尤其在Linux环境下。MySQL 8.0默认用的是fsync方式数据文件和日志文件都通过操作系统的Page Cache再写入磁盘。这样写其实有一层缓存兜底但问题也很明显存在双写缓冲的浪费刷盘路径更长。对性能敏感的生产库绝大多数DBA会建议用O_DIRECTinnodb_flush_method O_DIRECTO_DIRECT的意思是数据文件的读写绕过操作系统Page Cache由InnoDB自己管理缓存。这里的“缓存”指的就是Buffer Pool——所以你看到没它和innodb_buffer_pool_size是配套的既然你打算用Buffer Pool扛大部分IO那就应该让数据文件读写走O_DIRECT别再让OS Page Cache多插一脚。有个常见误区是很多人以为O_DIRECT“会丢数据”其实它丢的只是Page Cache这一层不在事务提交时强制刷盘的保护逻辑内。真正负责“提交即持久化”的还是innodb_flush_log_at_trx_commit两个参数的职责是分开的。4.3 还有哪些参数值得看但我不会天天动它标题说“只有2个最重要”不是否定其他参数的存在意义。只是说在排查性能问题时它们的优先级远没有前两个高。下面这些属于“值得确认但不值得反复折腾”的配套项参数作用我什么时候会动它max_connections最大连接数连接被拒、Too many connections报错时innodb_io_capacity/innodb_io_capacity_max后台刷脏的IO上限磁盘是SSD时把它从默认200调高到800-2000transaction_isolation事务隔离级别业务默认REPEATABLE READ没问题别乱降long_query_time慢查询阈值排查慢SQL时设为1秒binlog_formatbinlog格式有主从复制时保持默认ROW即可这些参数的特点是调了能带来5%-10%的边际优化但调错了可能引发连锁问题。而innodb_buffer_pool_size和刷盘策略这俩调对了是质的飞跃调错了也是质的翻车。这就是我说“最重要”的真正含义——影响面最大收益最明显。5. 一次真实调优案例从连接池告警到TPS翻身5.1 现场情况和第一轮排查几个月前有个做游戏运营的朋友找我说他家一个MySQL实例最近老是被连接池告警轰炸业务高峰期应用端的“获取连接超时”频繁出现。我登上去一看规格8核16GB的云主机单实例MySQL上面同时还有两个轻量Java服务在跑。第一件事跑几个全局状态SHOW VARIABLES LIKE innodb_buffer_pool_size; SHOW VARIABLES LIKE innodb_flush_log_at_trx_commit; SHOW VARIABLES LIKE sync_binlog; SHOW VARIABLES LIKE innodb_redo_log_capacity; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_read_requests; SHOW GLOBAL STATUS LIKE Innodb_buffer_pool_reads; SHOW GLOBAL STATUS LIKE Threads_connected;结果如下innodb_buffer_pool_size 128MB默认值innodb_flush_log_at_trx_commit 1sync_binlog 1innodb_redo_log_capacity 100MB8.0.30之前实例命中率粗算只有91%左右Threads_connected在100上下浮动而机器的16GB内存闲着将近10GB。这台服务器的配置水平属于典型的“买了高配却跑着乞丐设置”。5.2 调整过程先内存再日志后验证我没有一次性把所有参数全改掉那样出了问题没法定位。调整顺序是第一步Buffer Pool从128MB调到8GB。SET GLOBAL innodb_buffer_pool_size 8589934592;在线调整生效后用SHOW ENGINE INNODB STATUS确认没有报错。调大Buffer Pool时要留意MySQL需要把新空间的内存页初始化期间会有短暂的内存分配操作但过程中服务不中断。实际上这一步的收益立竿见影——只过了不到5分钟磁盘读的次数就明显下降了。第二步redo log容量从100MB调到2GB。因为这台实例当时MySQL版本较低用的是老参数innodb_log_file_size 2G innodb_log_files_in_group 2这里插一句在线改innodb_log_file_size在MySQL 5.7和8.0早期版本必须重启才能生效。所以我把配置写进my.cnf然后挑了个凌晨的低峰窗口重启了一次。如果当时是8.0.30直接用innodb_redo_log_capacity就能在线改省掉重启。第三步刷盘策略保持双1不动。是你没看错我在这台实例上没降刷盘要求。因为调整完前两个参数后业务高峰期的TPS已不再被IO拖后腿双1的额外开销已经被组提交机制摊得很低。为了挽回那一点点性能去牺牲数据安全不划算。5.3 结果和复盘为什么我只动了这几个地方调优后的数据对比指标调整前高峰调整后高峰Buffer Pool命中率91%99.6%磁盘读IOPS80002000不到获取连接超时告警频繁0应用侧TPS3000左右7500左右整个过程中我唯一修改的就是Buffer Pool和redo log容量刷盘策略保持双1。这个案例恰好印证了文章标题这台实例真正缺的不是什么花哨的调优技巧而是那两个最核心的“缸”——内存工作台太小事务日志空间太小再多其他参数也白搭。5.4 这个案例里隐藏的两个坑复盘时我发现两个值得提醒的细节第一在线调Buffer Pool时不要在业务高峰期一次性从128MB直接拉到12GB。虽然官方支持在线调整但大额度的空间扩展会触发内部结构重建可能导致短暂性能抖动。稳妥做法是分两次比如先到4GB观察半小时再拉到8GB。第二检查一下innodb_buffer_pool_instances。默认情况下Buffer Pool小于1GB时只有一个实例大于1GB时默认拆成8个。这次调整后8GB的Buffer Pool配8个实例并发访问的锁争用也小了。如果你的实例还在用单实例大池子可以试试给拆开。6. 最后再分享一个小技巧调完参数别急着下结论我在实际运维里养成的习惯是调完任何关键参数后至少观察一到两周再下结论。因为MySQL的性能表现受业务波动影响很大今天的高峰和下周的高峰可能差出一倍。我见过有人把Buffer Pool调大后第二天看到命中率还不到98%就以为调错了结果第五天业务量上来后命中率自己就稳到99.5%了——纯粹是被周末低峰期的数据误导了。如果要给一个快速自查动作那就是每次改完参数在同一个业务周期点比如每天同一时间记录三个值Innodb_buffer_pool_read_requests、Innodb_buffer_pool_reads、Innodb_buffer_pool_pages_dirty。连续记录三天看趋势而不是看某一个瞬间。另外如果你的实例还在MySQL 8.0.30之前升级的时候记得重新评估redo log容量。因为新版本默认的innodb_redo_log_capacity会自动适应负载但旧版本升级后仍会沿用老的日志文件配置。我踩过一次坑升级后忘了检查结果日志空间还是2GB而Buffer Pool已经调到16GB脏页峰一来checkpoint频繁到磁盘IO被打满。后来把innodb_redo_log_capacity调到8GB才彻底消停。说到底MySQL性能调优从来不是参数越多越厉害而是把影响面最大的那几个变量吃透。Buffer Pool决定你的数据能不能在内存里转起来刷盘策略决定你的写入能不能在安全和性能之间找到平衡点。这两组参数拨正了剩下的调优才有意义。否则就像一辆轮胎都没气的车你花再多心思去调座椅和后视镜也跑不快。
