凌晨 00:12，香港湾仔机房 8 楼，我正盯着监控墙：交易洪峰刚刚起势，内地到港的支付回流量比平时高了 3 倍。P95 写入延迟从 8ms 掉到了 40ms，业务方在群里问“是否需要限流”。我握着保温杯，喝了一口已经温掉的乌龙茶，决定最后再赌一把——把 InnoDB Buffer Pool 和 redo 写路径调到我在预案里做过但还没在生产上落地的极限值。

这篇文章，就是那一夜我如何从 部署、验证、到线上调参 一步步把写入性能从“勉强能用”拉到“稳到无感”的完整记录与教程。

1. 目标与约束

业务场景：跨境支付（大量小额交易），高峰期写入 TPS 突增，强一致（不能丢单）。

核心指标：

• P95/P99 写入延迟（事务提交时延）
• 单实例可承载 TPS
• Buffer Pool 命中率（> 99.5%）

硬性约束：强一致 ≥ 业务优先级（innodb_flush_log_at_trx_commit=1，sync_binlog=1），不能“刷积分式的性能美梦”。

2. 现场硬件与系统基线（香港机房）

• 机型：1 台主库 + 2 台只读/应急同步库
• CPU：AMD EPYC 7452（32C/64T）×1
• 内存：256 GB DDR4
• 系统盘：SATA SSD 480 GB（系统/日志）
• 数据盘：NVMe U.2 3.84 TB ×2，mdadm RAID1（为可靠性；写多读少，镜像更符合业务）
• 网卡：双口 10 GbE（上联至 BGP/CN2 路由，减少回国链路抖动）
• OS：CentOS 7.9（内核 3.10；保持和既有环境一致）
• 文件系统：XFS（对并发写入表现稳定）
• 时钟：Chrony 同步香港授时源 + 国内对时备份

BIOS/固件与内核小结

• 关闭 C-States 深度节能（保守），固定“高性能”电源策略
• NUMA：跨节点均衡（numactl --interleave=all 启动 mysqld）
• 关闭 THP（Transparent Huge Pages）：echo never > /sys/kernel/mm/transparent_hugepage/enabled
• I/O 调度器：NVMe 使用 none（CentOS7 上等价 noop）
• Swappiness：vm.swappiness=1，避免 MySQL 内存被动回收
• 脏页阈值：vm.dirty_ratio=10，vm.dirty_background_ratio=5

这些是给 写入延迟稳定性 打地基的活，先做，后面空间才足够。

3. 磁盘分区与挂载

# RAID1
mdadm --create /dev/md0 --level=1 --raid-devices=2 /dev/nvme0n1 /dev/nvme1n1
mkfs.xfs -f /dev/md0
mkdir -p /data/mysql
echo "/dev/md0 /data/mysql xfs noatime,nodiratime,discard 0 0" >> /etc/fstab
mount -a

noatime/nodiratime 减少元数据写；discard 让 SSD 自维护更顺滑（也可用定时 fstrim）。

4. 安装 MySQL（8.0 社区版）与目录权限

# 以官方社区仓库为例（el7）
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-7.noarch.rpm
yum install -y mysql-community-server

# 目录与权限
useradd -r -s /sbin/nologin mysql || true
chown -R mysql:mysql /data/mysql
mkdir -p /data/mysql/{data,log,tmp,run}
chown -R mysql:mysql /data/mysql

5. my.cnf：一份“能打”的起步配置（Buffer Pool 为核心）

机器 256 GB 内存，Buffer Pool 建议 70%~75% 内存，我这里定 176 GB，并切成多个实例以降低 latch 竞争。

# /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr
datadir = /data/mysql/data
tmpdir  = /data/mysql/tmp
socket  = /data/mysql/run/mysql.sock
pid-file= /data/mysql/run/mysqld.pid
log_bin = /data/mysql/log/binlog
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
server_id = 1001
relay_log_recovery = ON
binlog_expire_logs_seconds = 604800      # 7 天
sync_binlog = 1                           # 强一致

# InnoDB 基础
innodb_data_home_dir = /data/mysql/data
innodb_log_group_home_dir = /data/mysql/log
innodb_doublewrite = ON
innodb_flush_method = O_DIRECT            # 避免双缓存
innodb_flush_neighbors = 0                # SSD 必配
innodb_file_per_table = 1

# redo 日志：足够深的写入吸收池（根据压测结果微调）
innodb_log_files_in_group = 2
innodb_log_file_size = 8G                 # 总 redo 16G，适合写高峰

# Buffer Pool：本篇核心
innodb_buffer_pool_size = 176G
innodb_buffer_pool_instances = 16         # 保证每实例 ~11G，降低争用
innodb_buffer_pool_chunk_size = 128M
innodb_lru_scan_depth = 2048              # 加大清理步长

# 提交与刷盘
innodb_flush_log_at_trx_commit = 1        # 强一致
innodb_io_capacity = 20000                # 按 NVMe 实测 IOPS 定
innodb_io_capacity_max = 40000            # 峰值上限
innodb_adaptive_flushing = 1

# 事务与并发
innodb_thread_concurrency = 0             # 让 InnoDB 自行调度
innodb_purge_threads = 4
innodb_page_cleaners = 8
max_connections = 2000
table_open_cache = 8192
table_open_cache_instances = 16

# Binlog 压缩（跨境链路可考虑开启，CPU 充裕）
binlog_transaction_compression = ON

# 启动时预热/关闭时转存
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup  = 1

# 监控
performance_schema = ON

经验点：innodb_buffer_pool_instances 不是越多越好，要和 总大小/每实例≥1GB、CPU 核数 一起看。我在 176 GB 上用 16 个实例，减少 BP mutex 争用 明显。

6. 初始化与安全

mysqld --initialize --user=mysql --datadir=/data/mysql/data --basedir=/usr
systemctl enable mysqld
systemctl start mysqld

# 取初始密码
grep 'temporary password' /var/log/mysqld.log

# 基础安全
mysql_secure_installation

7. 基准压测：先“量体裁衣”，再动刀

7.1 磁盘 fio（写为主）

fio --name=randwrite --filename=/data/mysql/tmp/fio.test \
 --size=20G --bs=16k --iodepth=64 --rw=randwrite --direct=1 --numjobs=4 --time_based --runtime=120

看 IOPS、延迟分位；确保 RAID1/NVMe 能给出 ≥ 100k IOPS 的 16k 随机写，延迟 P99 < 5ms。

7.2 sysbench（OLTP write-heavy）

sysbench oltp_write_only \
  --table-size=5000000 --tables=16 \
  --mysql-host=127.0.0.1 --mysql-user=xxx --mysql-password=xxx \
  --mysql-db=bench --time=300 --threads=128 --rand-type=uniform prepare

sysbench oltp_write_only ... --time=300 --threads=128 run

8. Buffer Pool 优化思路（本质是“把热数据留在内存、把写路径拉直”）

• 大到装下热集：用压测或生产统计看热点表/索引总量。如果热集 ~120 GB，我直接给 176 GB，留足 margin 给 change buffer/fragment。
• 多实例减锁：16 个 BP 实例让并发分布更均匀；观察 show engine innodb status\G 的 mutex 段落和 INNODB_BUFFER_POOL_STATS。
• 写路径：增大 redo（16 GB 总量）让 group commit 更从容，配合 innodb_io_capacity(_max)，把 checkpoint 抖动拉平。
• 冷启动预热：innodb_buffer_pool_dump/load + 预热 SQL（见下）。
• SSD 友好：innodb_flush_neighbors=0、O_DIRECT、I/O 调度 none。

预热脚本（业务真实热表）

-- 热表热索引预读，避免冷启动雪崩
SET SESSION max_execution_time=600000;
SELECT COUNT(*) FROM pay_order WHERE gmt_create >= NOW() - INTERVAL 3 DAY;
SELECT /*+ SET_VAR(read_rnd_buffer_size=64M) */ id FROM pay_order FORCE INDEX(idx_status_ctime)
  WHERE status IN (1,2,3) AND gmt_create >= NOW() - INTERVAL 7 DAY LIMIT 1000000;

-- 触发热门二级索引页加载
SELECT order_no FROM pay_order_detail FORCE INDEX(idx_mch_order)
  WHERE mch_id IN (xxx, yyy) ORDER BY id DESC LIMIT 1000000;

9. 复制与可靠性（半同步、就近就地）

主从：主库在湾仔，两个半同步从库同城不同机房

参数（主库）：

rpl_semi_sync_master_enabled = ON
rpl_semi_sync_master_timeout = 1000  # 1s 等待
rpl_semi_sync_master_wait_for_slave_count = 1

参数（从库）：

rpl_semi_sync_slave_enabled = ON

策略：只等 1 个从库 ACK，既保证强一致（至少 2 份）也不把高峰 TPS 压没。链路波动时可临时降级异步，但配套“事务影子对账”兜底。

10. 联机调参：那一夜我做了什么

监控里 P95 写延迟顶到了 40ms。我按预案分三步走，每一步都在窗口期观察 3~5 分钟。

Step 1：扩大 redo、提高 IO 能力上限

把 innodb_log_file_size 从 2G → 8G（共 16G），维护窗口滚动重启。

innodb_io_capacity=20000，innodb_io_capacity_max=40000。

效果：checkpoint 更平滑，fsync 抖动下降，P95 从 40ms → 22ms。

Step 2：Buffer Pool 实例化 + 预热

innodb_buffer_pool_instances=8 → 16（176G/16 ≈ 11G/实例）。

执行预热 SQL，配合 innodb_buffer_pool_load_at_startup=1 让后续重启更稳。

效果：BP mutex 等待显著减少，命中率稳定在 99.7%+，P95 → 14ms。

Step 3：Page Cleaner 与 LRU 扫描

innodb_page_cleaners=4 → 8；innodb_lru_scan_depth=2048。

逻辑：更积极地把脏页推向 checkpoint，避免突发刷脏堵住前台。

效果：峰值期 P99 由 60ms → 28ms，P95 9~11ms 来回跳，业务侧“无感”。

11. 数据对比（真实量级，示意）

指标	调整前	调整后（稳定期）
主库写 TPS（峰值 5 分钟均）	32k	58k
P95 提交延迟	40 ms	10 ms
P99 提交延迟	120 ms	28 ms
Buffer Pool 命中率	97.8%	99.75%
fsync/sec（平峰/峰值）	4.5k / 9k	6k / 11k（更平滑）
Checkpoint Age 波动	1.8G~7.2G	6.0G~9.0G（更从容）
redo 写放大	高	中（group commit 更充分）

备注：TPS 增长不是“魔法”，而是 减少争用 + 平滑刷盘 + 扩大写缓冲深度 的合力。

12. 线上“坑点”与现场解法

重启改 redo 大小：需要干净关库并删除旧 ib_logfile*（8.0 仍遵从流程）。

解法：先 SET GLOBAL innodb_fast_shutdown=0，停库后改参数，启动让 InnoDB 重建 redo。

Buffer Pool 实例太多反而退化：实例数过大，碎片与并发调度开销会上来。

解法：用 8/12/16 做 A/B，观察 INNODB_BUFFER_POOL_STATS 的 free buffers/database pages 分布是否均衡。

THP 未关造成抖动：某次内核升级后 THP 又开了。

解法：把关闭指令写进 rc.local，并用 grub 永久参数 transparent_hugepage=never。

半同步长尾：某从库链路偶尔 200ms 抖一下，P99 被拉高。

解法：wait_for_slave_count=1 固定只等就近从库，并对远端链路做 QoS 与丢包告警。

表设计写放大：变长列过多 + 次级索引冗余，导致随机写压力偏高。

解法：热点表把冗余索引砍掉 2 个，把 VARCHAR(255) 实测降到 VARCHAR(64) 足够，Buffer Pool 命中率立竿见影。

13. 运维动作清单（可直接抄）

•  关 THP、调 swappiness/dirty ratio、NVMe 调度器 none
•  RAID1/XFS noatime，定时 fstrim
•  MySQL 8.0：innodb_buffer_pool_size=70~75% RAM，instances=8~16
•  redo 总量 ≥ 8G（写入很猛时 16G~32G 视磁盘而定）
•  innodb_flush_neighbors=0，O_DIRECT
•  innodb_io_capacity 按 fio 实测设置（峰值再给 *_max 空间）
•  innodb_buffer_pool_dump/load + 自定义预热 SQL
•  半同步只等 1 个就近从库 ACK
•  压测先行（fio + sysbench），监控看 BP 命中/redo/checkpoint

14. 关键监控与判读

• BP 命中率：<99% 就要警惕（结合缓存穿透 SQL）
• BP Latch 等待：看 performance_schema.events_waits_summary_global_by_event_name 中 wait/synch/mutex/innodb/buf_pool
• redo 写入速率：过快且 checkpoint age 大幅锯齿 → io_capacity 偏低
• Page Cleaner：检查 innodb_page_cleaners 与 lru_scan_depth 对脏页回收是否有效
• 半同步 ACK 延迟：Prometheus 上做直方图，P99>150ms 就要排链路

15. FAQ：关于“是否可以放宽一致性换吞吐”

可以把 innodb_flush_log_at_trx_commit=1 保持不变，而通过 group commit、redo 扩容、I/O 平滑 来要吞吐。

若业务允许极端异常下丢最近 1s 事务，才考虑 sync_binlog=100 这类折中。但我们的支付 不允许，所以没走这条路。

16.凌晨 02:07 的电梯间

当 P95 稳在 10ms 左右，群里不再有人问“要不要限流”，监控墙只剩规律的心跳。我把保温杯拎上电梯，电梯镜子里人有点狼狈，但心里是安定的。
写入性能不是某个魔法参数，而是系统地把每一个瓶颈都让路：从内核到文件系统，从 Buffer Pool 到 redo，再到复制链路。真正落到生产里的，不是“某篇调参宝典”，而是你在机房里一次次按图走完整个闭环。
如果你也在香港机房里和我一样守过夜，你会知道：把延迟压下去的那一刻，比咖啡更提神。

17. 附：最小可用配置模板（CentOS 7，NVMe，写密集）

[mysqld]
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
log_bin=/data/mysql/log/binlog
server_id=1001
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_format=ROW
sync_binlog=1

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=0
innodb_file_per_table=1

innodb_buffer_pool_size=176G
innodb_buffer_pool_instances=16
innodb_log_file_size=8G
innodb_log_files_in_group=2
innodb_io_capacity=20000
innodb_io_capacity_max=40000
innodb_page_cleaners=8
innodb_lru_scan_depth=2048

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
performance_schema=ON

18. 优化建议

先把 硬件/内核/文件系统 的地基打牢，再谈数据库层的优雅。

量化（fio/sysbench/PMM）是你在凌晨能否“稳住阵脚”的底气。

InnoDB Buffer Pool 的优化不是孤立参数，而是 BP 大小 × 实例数 × redo 深度 × 刷盘策略 × 复制链路 的合奏。

真正的“最佳实践”，是你能在监控异常的 5 分钟内，有步骤、有预案地把系统拉回绿色。