32G 服务器 MySQL 参数调优：从内存推导到 redo log 落地

问题背景：32G 内存不是把 80% 直接分给 Buffer Pool

很多 MySQL 参数调优文章会给出一条简单规则：专用数据库服务器把 70%～80% 内存分配给 innodb_buffer_pool_size。这句话在独立物理机、实例独占内存时基本成立，但在 32G 的云主机或混合部署机器上，直接设置成 26G 往往会引出三个问题：操作系统页缓存不足、MySQL 连接与排序内存不可控、备份或 DDL 时内存峰值叠加，最终触发 OOM 或频繁 swap。

参数调优的本质不是追求某个“黄金值”，而是让内存预算、脏页比例、redo log 容量和磁盘写入能力相互匹配。下面以一台 32G 内存、4C～8C、SSD 云盘、单实例 MySQL 8.0 的 OLTP 服务器为例，推导一组可落地的初始参数，并说明上线后如何根据监控修正。

方案对比：三种常见 Buffer Pool 配置思路

第一种是保守型，将 Buffer Pool 设置为物理内存的 50%，例如 16G。它适合机器上还跑着监控 Agent、日志采集、备份脚本，或未来可能部署多个实例的场景。优点是安全边界清晰，缺点是热点数据超过 16G 后磁盘读会明显增加。

第二种是常见专用型，将 Buffer Pool 设置为 20G～24G。对于单实例、InnoDB 为主、数据量在 50G～200G 的业务，这通常是性价比最高的区间。它既能容纳热点索引和数据页，也给操作系统、连接线程、临时表和备份任务留下空间。

第三种是激进取值，例如 26G～28G。只有在确认机器为独占数据库节点、关闭不必要驻留进程、连接数受控、使用专用备份策略且 swap 使用经验证为零时才考虑。生产环境不建议把它作为初始配置。

Buffer Pool 大小如何推导

先建立内存预算，而不是先写参数：

操作系统与页缓存：至少保留 3G～4G；
MySQL 固定内存：代码段、全局字典、锁系统等通常按 1G～2G 估算；
会话内存：sort_buffer_size、join_buffer_size、read_buffer_size 等不要盲目开大，应按峰值连接数估算；
备份、在线 DDL、批量导入：至少预留 2G～4G；
剩余部分再分配给 Buffer Pool。

假设最大连接数为 300，每个连接最坏使用 20MB～40MB 会话内存，那么极端情况下需要预留约 6G～12G。当然不是所有连接同时执行大排序，但这个上限决定了不能把所有内存都分给 Buffer Pool。综合预算后，32G 机器的稳妥起点是：

ini

# my.cnf：32G 内存、单实例 InnoDB OLTP 的起步配置
innodb_buffer_pool_size = 21474836480   # 20G
innodb_buffer_pool_instances = 4        # MySQL 8 中通常按每实例数 G 划分
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

max_connections = 300
table_open_cache = 4000
open_files_limit = 65535

# 会话级缓冲不要全局开太大，避免连接数升高时放大内存
sort_buffer_size = 4194304              # 4M
join_buffer_size = 4194304              # 4M
read_buffer_size = 2097152              # 2M
read_rnd_buffer_size = 2097152          # 2M

如果机器确认只跑 MySQL，业务以短事务、主键访问为主，并且连续观察一周没有内存压力，可以把 Buffer Pool 缓慢提升到 22G 或 24G。每次调整后都要观察命中率、磁盘读、swap 和业务延迟，而不是一次改到理论极限。

Redo Log 参数如何与写入压力匹配

redo log 的作用是在崩溃恢复时重放已提交事务，并通过 WAL 机制把随机数据页写入转换为顺序日志写入。日志太小，事务刚修改的脏页很快进入“最旧日志需要被覆盖”的范围，InnoDB 会被迫激烈刷脏页；日志太大，崩溃恢复时间可能变长。

MySQL 8.0 推荐使用 innodb_redo_log_capacity 统一控制 redo 容量。对于 32G、20G Buffer Pool 的常规 OLTP 实例，可以从 4G 总容量起步；如果高峰期写入量很大、checkpoint 经常追着日志尾部走，可提升到 6G～8G。

ini

# redo log 与刷盘策略
innodb_redo_log_capacity = 4294967296  # 总 redo 容量 4G
innodb_flush_log_at_trx_commit = 1     # 每次提交刷 redo，保证持久性
innodb_flush_method = O_DIRECT         # 数据文件绕过 OS 页缓存，redo 仍由 MySQL 管理
innodb_io_capacity = 2000              # 按 SSD 实际能力设置
innodb_io_capacity_max = 4000
innodb_max_dirty_pages_pct = 75        # 脏页比例软上限
innodb_max_dirty_pages_pct_lwm = 10    # 低水位提前刷脏，避免突刺
sync_binlog = 1                        # 与双 1 搭配，保证主库事务安全
log_bin = ON
binlog_format = ROW

这里最容易被误解的是 innodb_flush_log_at_trx_commit=1。它确实会增加 fsync 成本，但这是主库默认且推荐的持久性设置。只有能接受实例宕机时丢失约 1 秒事务的场景，才考虑改成 2；把它改成 0 来换取性能通常不适合支付、订单等核心业务。

用可运行 SQL 建表并观察参数效果

下面建立一张订单明细表，并写入可重复执行的测试数据。索引设计遵循“高频过滤字段在前、主键尽量短而有序”的原则，避免在大字段上建立无用索引。

sql

-- 创建测试库
CREATE DATABASE IF NOT EXISTS tune_demo
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE tune_demo;

-- 订单明细表：自增主键适合 InnoDB 聚簇索引的顺序插入
CREATE TABLE IF NOT EXISTS order_item (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  tenant_id INT UNSIGNED NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  sku_id BIGINT UNSIGNED NOT NULL,
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status TINYINT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
    ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_tenant_status_created (tenant_id, status, created_at),
  KEY idx_user_created (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 写入 10 万行测试数据，递归 CTE 在 MySQL 8.0 可直接执行
INSERT INTO order_item (
  tenant_id, order_no, user_id, sku_id,
  quantity, amount, status, created_at
)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
  1000 + (n % 8) AS tenant_id,
  LPAD(n, 20, '0') AS order_no,
  100000 + (n * 7 % 90000) AS user_id,
  200000 + (n * 13 % 80000) AS sku_id,
  1 + (n % 5) AS quantity,
  ROUND(10 + (n * 1.37 % 900), 2) AS amount,
  1 + (n % 3) AS status,
  DATE_SUB(NOW(3), INTERVAL n MINUTE) AS created_at
FROM seq;

-- 典型分页统计：应使用联合索引 idx_tenant_status_created
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM order_item
WHERE tenant_id = 1003
  AND status = 2
  AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY status;

-- 查看执行计划，确认是否命中索引、是否出现临时表或文件排序
EXPLAIN ANALYZE
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total_amount
FROM order_item
WHERE tenant_id = 1003
  AND status = 2
  AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY status;

如果 EXPLAIN ANALYZE 中出现全表扫描，先检查查询条件是否符合联合索引最左前缀，而不是立刻增大 Buffer Pool。缓存只能减少磁盘访问，不能把错误的访问路径变成高效索引。

上线后用状态指标验证，而不是只看命中率

Buffer Pool 命中率很容易产生误导。在一次冷启动或批量扫描后，命中率可能短期偏低，但业务并不慢；相反，命中率 99.9% 也可能因为少量脏页刷新突刺导致 P99 抖动。更应该结合下面指标判断：

sql

-- Buffer Pool 使用情况：关注脏页比例、年轻页和扫描驱逐
SHOW ENGINE INNODB STATUS\G

SELECT
  VARIABLE_NAME,
  VARIABLE_VALUE
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_reads',
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_buffer_pool_pages_data',
  'Innodb_buffer_pool_pages_free',
  'Innodb_buffer_pool_wait_free',
  'Innodb_redo_log_checkpoint_lsn',
  'Innodb_redo_log_current_lsn'
);

-- MySQL 8.0.30+ 可查看 redo 空间占用与可用比例
SELECT
  event_name,
  current_alloc,
  current_max_alloc
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name LIKE '%redo%';

-- 查看实际生效的关键参数
SELECT variable_name, variable_value
FROM performance_schema.global_variables
WHERE variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_buffer_pool_instances',
  'innodb_redo_log_capacity',
  'innodb_io_capacity',
  'innodb_io_capacity_max',
  'innodb_flush_log_at_trx_commit',
  'sync_binlog'
);

判断标准可以这样落地：

Innodb_buffer_pool_wait_free 持续增长，说明空闲页等待变多，可能是 Buffer Pool 偏小或后台刷盘跟不上；
脏页比例长期贴近 innodb_max_dirty_pages_pct，同时业务写入延迟周期性升高，应检查 redo 容量、IO 能力和慢查询；
current_lsn - checkpoint_lsn 长期接近 redo 总容量，说明 checkpoint 推进压力大，可逐步增大 redo；
操作系统层面使用 vmstat 1、iostat -xz 1 观察 si/so、磁盘 await 和 %util，数据库指标必须与主机指标一起看。

参数调整顺序与灰度方法

不要在同一时间修改十几个变量。推荐顺序是：先确认数据规模、峰值连接和磁盘类型；再设置 Buffer Pool；然后配置 redo 与双 1；最后根据压测结果微调 IO 容量和脏页阈值。每轮只改一类参数，保留回滚配置，并至少覆盖一个业务高峰。

在线修改 Buffer Pool 大小时，MySQL 8.0 可以动态执行，但调整过程本身有内存拷贝和业务影响，建议在低峰操作：

sql

-- 低峰动态调整，单位为字节；执行后观察错误日志与业务延迟
SET GLOBAL innodb_buffer_pool_size = 23622320128; -- 22G

-- 调整 redo 容量后，通过状态变量确认生效
SET GLOBAL innodb_redo_log_capacity = 6442450640; -- 约 6G

SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_resize_status';

动态调整适合修正参数，但不替代配置文件固化。验证稳定后，应同步写入 my.cnf，避免数据库重启后回退。

避坑总结：生产环境最常见的 5 个错误

第一，照抄 80% 规则导致 OOM。 现象是夜间备份或批量任务执行时机器内存突然打满，系统日志出现 oom-killer，MySQL 被重启。修正方法是先做内存预算，把连接峰值、排序缓冲、备份和 DDL 峰值计入，32G 单实例建议从 20G～22G 起步，而不是直接给 26G 以上。

第二，把会话缓冲全局调得很大。 有些业务看到排序临时表就把 sort_buffer_size 设成 64M 或 128M。300 个连接同时复杂查询时，理论内存放大非常可观。更合理的做法是全局保持 2M～4M，对确有必要的会话使用 SET SESSION sort_buffer_size 临时放大，同时通过索引和 SQL 改写消除文件排序。

第三，redo log 太小导致每秒刷盘尖刺。 现象是业务 P99 延迟周期性升高，磁盘写延迟抖动，脏页比例长期高位。不要只提高 innodb_io_capacity，应先查看 LSN 与 checkpoint 距离，将 redo 总容量从 4G 按 2G 步长提升，并用真实高峰流量验证恢复时间。

第四，为了性能关闭双 1。 将 innodb_flush_log_at_trx_commit 和 sync_binlog 都改成 0，压测 QPS 可能好看，但宕机后存在事务丢失或主从数据不一致风险。核心主库应保持双 1；报表库、可重建从库再按 RPO 要求评估放宽。

第五，只调参数不查 SQL 与索引。 当慢查询扫描几十万行时，把 Buffer Pool 从 20G 调到 24G 只能缓解磁盘读，无法减少锁持有时间和临时表开销。应先用 EXPLAIN ANALYZE 检查访问路径，确认联合索引顺序，再处理缓存容量。

最终结论是：32G 内存下，Buffer Pool 与 redo log 的合理初始值通常是 20G～22G 与 4G～6G，但真正可靠的参数必须由内存预算、写入峰值、索引质量和监控数据共同决定。参数文件只是起点，持续观察并小步修正，才是 MySQL 参数调优的实战方法。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

32G 服务器 MySQL 参数调优：从内存推导到 redo log 落地 ​

问题背景：32G 内存不是把 80% 直接分给 Buffer Pool ​

方案对比：三种常见 Buffer Pool 配置思路 ​

Buffer Pool 大小如何推导 ​

Redo Log 参数如何与写入压力匹配 ​

用可运行 SQL 建表并观察参数效果 ​

上线后用状态指标验证，而不是只看命中率 ​

参数调整顺序与灰度方法 ​

避坑总结：生产环境最常见的 5 个错误 ​