4.2 IO与存储参数调优

4.2 IO与存储参数调优

本节摘要：内存调好后，IO 是下一关。本节讲清楚刷脏页策略、写入策略、日志 fsync、IO 并发参数，平衡性能与安全。

IO 性能基础

数据库 IO 瓶颈常见——磁盘慢。IO 调优目标：

• 减少读 IO（内存缓存，见 4.1）。
• 优化写 IO（批量、顺序、减少 fsync）。
• 平衡性能与数据安全（崩溃不丢）。

关键：写日志是顺序 IO（快），写数据是随机 IO（慢）。数据库 WAL（Write-Ahead Logging）——先写日志再写数据，崩溃用日志恢复。

InnoDB 刷脏页

innodb_flush_neighbors：刷脏页时是否刷相邻页。

• 0：不刷相邻（SSD 用，无寻道开销）。
• 1：刷同区相邻（HDD 用，顺序写快）。
• SSD 设 0，HDD 设 1。

innodb_dirty_time_pct / innodb_max_dirty_pages_pct：脏页比例上限。

• 超过则激进刷脏页。
• 默认 75%（8.0），可调到 50-90。
• 高则缓冲池脏页多，crash recovery 慢；低则刷盘频繁。

innodb_io_capacity / innodb_io_capacity_max：告诉 InnoDB IO 能力（每秒 IO 次数）。

• SSD 设高（如 2000-10000），HDD 设低（如 200-1000）。
• 影响刷脏页速度——值高刷得快。
• 设错（如 SSD 设低）则刷脏页慢，脏页堆积。

innodb_flush_method：刷盘方式。

• fsync：传统，read/write + fsync。
• O_DSYNC：redo 用 O_SYNC。
• O_DIRECT：数据绕 OS 缓存直写（推荐，避免双缓冲）。
• O_DIRECT_NO_FSYNC：8.0，直写不 fsync（依赖文件系统）。
• Linux 推荐 O_DIRECT。

日志 fsync 策略

innodb_flush_log_at_trx_commit：redo log 刷盘策略。

• 0：每秒刷（性能高，崩溃丢 1 秒数据）。
• 1：每事务刷（默认，安全，崩溃不丢，性能低）。
• 2：每事务写 OS 缓存，每秒 fsync（折中，OS 崩溃才丢）。

sync_binlog：binlog 刷盘策略。

• 0：OS 决定（性能高，崩溃丢）。
• 1：每事务 fsync（安全，默认）。
• N：每 N 事务 fsync（折中）。

双 1 配置：innodb_flush_log_at_trx_commit=1 + sync_binlog=1——最安全，性能最低。
**双 0 配置**：都设 0——最高性能，崩溃丢数据。

权衡：

• 金融/关键数据——双 1（安全优先）。
• 性能优先可接受少量丢失——0/2（如日志、统计）。
• 折中——1+0 或 2+1。

PostgreSQL IO 参数

wal_buffers：WAL 日志缓冲。

• 默认 1/32 shared_buffers，最小 64KB。
• 大 WAL 调大（如 16-64MB）。

synchronous_commit：事务同步提交。

• on：每事务 fsync WAL（默认，安全）。
• off：异步提交（性能高，崩溃丢最近事务）。
• remote_write/local：复制相关。
• 折中：off 提性能，接受少量丢失。

wal_sync_method：WAL fsync 方式。

• fdatasync（默认 Linux）、fsync、open_sync。
• 影响不大，默认即可。

checkpoint_timeout / max_wal_size：检查点间隔。

• 大间隔——少 checkpoint，性能高，但 crash recovery 慢（WAL 多）。
• max_wal_size 调大（如 4-16GB）减少 checkpoint 频率。

checkpoint_completion_target：checkpoint 完成目标。

• 0.9（默认）——在下次 checkpoint 前 90% 时间完成，平滑 IO。
• 调高（如 0.9-1.0）更平滑。

IO 并发

innodb_read_io_threads / innodb_write_io_threads：IO 线程数。

• 默认 4，调到 8-16（多核 SSD）。
• 影响并发 IO 能力。

innodb_use_native_aio：异步 IO。

• 默认 ON（Linux），用 libaio 异步 IO，提并发。

PostgreSQL effective_io_concurrency：预读并发。

• SSD 设高（如 200-1000），HDD 设低（如 2-10）。

IO 调优流程

1. 看磁盘

• iostat -x——磁盘利用率、等待、队列。
• 利用率高 + 等待长——IO 瓶颈。

2. 减读 IO

• 内存缓存（4.1）——命中率 >99%。

3. 优化写 IO

• 刷脏页参数（flush_neighbors/io_capacity/dirty_pct）。
• fsync 策略（按安全需求选）。
• O_DIRECT 避免双缓冲。

4. 提并发

• IO 线程数（read/write threads）。
• 异步 IO（native_aio）。
• 预读并发（effective_io_concurrency）。

5. 平滑 IO

• checkpoint 平滑（completion_target）。
• 避免 IO 突刺（如大 checkpoint 一次大量刷盘）。

IO 监控

• iostat：磁盘利用率（%util）、等待（await）、队列（avgqu-sz）。%util 接近 100% 瓶颈。
• InnoDB：show engine innodb status——缓冲池、日志、IO 线程状态。
• PG：pg_stat_bgwriter——buffers_backend（后端写）、checkpoints（checkpoint 次数）。
• 慢 IO 告警：await > 阈值（如 20ms SSD）告警。

⚠️ 常见误读：以为"fsync 每事务最安全就好"。双 1 最安全但性能最低。按场景权衡——关键数据双 1，可接受少量丢失用 0/2 提性能。

💡 关键直觉：IO 调优减读 IO（内存缓存命中率 >99%）、优化写 IO（批量顺序减 fsync）、平衡性能安全。InnoDB 刷脏页（flush_neighbors SSD 0/HDD 1、dirty_pct 50-90、io_capacity SSD 2000-10000、flush_method O_DIRECT 避双缓冲）。fsync 策略（innodb_flush_log_at_trx_commit 0 每秒/1 每事务/2 折中，sync_binlog 0/1/N，双 1 安全双 0 性能，金融双 1 日志统计可 0/2）。PG（wal_buffers 16-64MB、synchronous_commit on/off 折中、checkpoint_timeout/max_wal_size 大间隔减 checkpoint、completion_target 0.9 平滑）。IO 并发（read/write threads 8-16、native_aio 异步、effective_io_concurrency SSD 200-1000）。流程：iostat 看磁盘→减读 IO→优化写 IO→提并发→平滑 checkpoint。监控 iostat（%util/await/avgqu-sz）、innodb status、pg_stat_bgwriter、慢 IO 告警。

本节要点回顾

• IO 基础：磁盘慢，目标减读 IO（内存缓存）、优化写 IO（批量顺序减 fsync）、平衡性能安全。WAL 先写日志后写数据崩溃恢复。
• InnoDB 刷脏页：flush_neighbors（SSD 0 无寻道/HDD 1 顺序快）、dirty_pct（50-90，高则 crash recovery 慢低则刷盘频）、io_capacity/io_capacity_max（SSD 2000-10000 HDD 200-1000，设错刷脏慢）、flush_method（O_DIRECT 绕 OS 缓存避双缓冲推荐）。
• fsync 策略：innodb_flush_log_at_trx_commit（0 每秒丢 1 秒/1 每事务安全默认/2 每事务写 OS 每秒 fsync 折中），sync_binlog（0 OS 决定/1 每事务安全默认/N 每 N 事务）。双 1 最安全性能低，双 0 最高性能丢数据。金融双 1，日志统计 0/2，折中 1+0/2+1。
• PG IO：wal_buffers（16-64MB）、synchronous_commit（on 安全/off 异步提性能丢最近）、checkpoint_timeout/max_wal_size（大间隔减 checkpoint 但 crash recovery 慢）、checkpoint_completion_target（0.9 平滑 IO）。
• IO 并发：innodb_read/write_io_threads（4 调 8-16 多核 SSD）、innodb_use_native_aio（ON libaio 异步）、PG effective_io_concurrency（SSD 200-1000 HDD 2-10）。
• 调优流程：iostat -x 看磁盘（%util/await/avgqu-sz）→减读 IO（内存缓存）→优化写 IO（刷脏/fsync/O_DIRECT）→提并发（线程/异步/预读）→平滑 IO（checkpoint completion_target）。
• 监控：iostat（%util 接近 100% 瓶颈、await >20ms SSD 慢）、show engine innodb status、pg_stat_bgwriter（buffers_backend/checkpoints）、慢 IO 告警。

刷脏节奏的调优哲学

IO 参数里最有嚼头的是刷脏节奏——它体现了数据库工程里最经典的三角权衡：崩溃恢复时间（日志要勤刷）、写入吞吐（脏页要攒批刷）、IO 平滑（不能集中爆发）。三个目标天然冲突：日志刷得勤则恢复快但每次提交都付 IO 代价；脏页攒得多则批量效率高但一旦集中刷写会形成 IO 尖峰拖垮在线查询；平滑策略又增加了调度复杂度。参数调优就是在这个三角里按业务选点：交易类系统选"恢复优先"（提交即刷日志、脏页水位设低），日志类批量系统选"吞吐优先"（攒批刷写、放宽恢复时间承诺），混合负载用分级策略（热数据勤刷、冷数据攒批）。观察三角是否失衡的指标：日志写入等待时间（提交变慢的信号）、刷脏队列深度（攒批过度的信号）、IO 利用率的方差（尖峰的信号）。这套权衡思维比任何具体参数值都值钱——数据库厂商每隔几年调整默认值，但三角本身三十年未变，理解它你就获得了跨版本、跨产品的迁移能力。

预读与检测点的隐形争用

IO 参数还有两个隐形角色值得单独点名：预读与检测点。预读的双刃：顺序扫描时提前批量读后续页能大幅提速，但预读过量会污染缓冲池（把冷数据顶掉热数据）并浪费 IO 带宽——大扫描任务频繁的系统要检查预读策略（按需预读还是激进预读），并考虑给批量任务单独的缓冲策略或独立实例；预读不足则表现为"明明顺序读却一页一页要"，IO 队列深度上不去。检测点的协调：检测点（全量脏页刷盘的周期性动作）与业务写入共享 IO 带宽，检测点周期过长则崩溃恢复久、过短则频繁冲击业务——参数之外，更有效的手段是平滑检测点（把刷写摊到周期内）与错峰（避开业务高峰调度）。两个隐形角色的共同点：它们不在"性能参数清单"的显眼位置，出问题时却能把整个 IO 子系统的表现拖下水——IO 调优的最后一课，就是把这些看不见的机制看见。

IO 调优的验收基准线

IO 章收官给一组验收基准线——参数调完后，这些数字应该落在的健康区间。写路径：日志写入的提交延迟应在亚毫秒到低毫秒级（配合掉电保护的缓存盘），刷脏活动平稳（IO 利用率方差不出现规律性尖峰），脏页水位在配置区间内波动而非顶格。读路径：缓冲命中率维持高位（结构性的、分解后仍高），物理读速率与业务增长同斜率（而非突然翻倍），大扫描任务的 IO 配额受控（不挤压在线流量）。异常侧：IO 等待在等待事件聚合中占比回到与硬件能力匹配的低位，无超时与重试的日志噪音。这组基准线的用法：调优前记录、调优后对照、纳入日常监控——IO 调优不是一次性动作而是维持态，基准线就是维持态的仪表读数。最后一句叮嘱：所有 IO 调优的最终裁判是业务时延的分布（P99 与尾部），中间指标的健康只是手段——别在手段上达成完美却在目的上失焦。

IO 参数的三个"别碰"

IO 章的最后给三个"别碰"警告——这些参数的诱惑大、风险也大。别碰一，异步提交配无保护缓存盘：性能提升立竿见影，掉电丢数据的窗口也在同步打开——除非业务明确接受丢失（日志类、可重建数据），交易库碰它是玩火。别碰二，双一配置的激进放宽（日志刷盘与脏页刷盘全放开）：吞吐报表好看，崩溃恢复时间以小时计——放宽前先与业务确认恢复时间目标，把承诺改在前面。别碰三，批量调大 IO 队列深度到设备极限：基准好看，高并发下的延迟排队恶化——队列深度要按延迟目标反推，不是按吞吐峰值正推。三个"别碰"的共同结构：它们都在用"耐久性或延迟分位数"换"吞吐均值"——报表只显示均值时，代价就隐形了。IO 参数的成熟心法：先问业务用什么买（丢多少、等多久），再决定卖不卖。

收尾再给一个测试动作：把三个"别碰"参数的当前值截屏存进台账——不是为了改，是为了在故障排查时能立刻回答"我们没有碰过这些"；四分钟的动作换将来排查时的一锤定音，这是参数治理里最便宜的保险。另外每年对照一次厂商的安全公告，别碰清单也会随版本演进，去年的安全区可能是今年的雷区。

IO 疑难杂症的三个案例片段

给三个真实风格的疑难片段，扩展 IO 调优的视野。片段一，"每逢整点慢五分钟"：定时任务的批量写入与检测点重合，双重 IO 压力叠加——解法是错峰（任务移到检测点后）加平滑（任务内部限速），时序对齐的监控图是破案关键。片段二，"备份后一周内逐渐变慢"：备份冲刷了缓存，之后一周缓存缓慢重建，期间命中率低谷——解法不是调参数而是调预期与优先级（备份后预热关键表、或接受一周的爬坡），认识到"这是缓存生命周期"而非故障，本身就让处理冷静了一半。片段三，"新存储上线反而抖动"：新介质的队列深度最优值与旧参数不匹配，旧的激进配置在新硬件上引发排队抖动——硬件升级后 IO 参数要重新校准（第 6 章的呼应），"参数是硬件的函数"这条公式在这里显形。三个片段的共同教学点：IO 问题的时间模式（何时发生、与什么对齐）比空间模式（哪个指标高）更有诊断力——排查 IO 时先画时间轴，再画指标图，顺序不要反。

补一个混合负载的编排细节：读写分离与资源队列配合使用时，报表流量走从库且限流，在线流量走主库且保底——两层的隔离（路由层加资源层）叠出来的稳态，比任何单层的参数微调都更能扛住"报表与交易抢跑道"的经典场景；参数章的这条延伸把第 3 章的架构手段引了进来，也再次印证：越往深层调优，越要回头借力上层的设计。

再补一条经验值的校准方法：IO 相关的经验值（比如队列深度、批量大小）的有效性高度依赖硬件代际——三年前的最优值在今天的介质上可能偏差一倍，所以这类参数的台账里要记录"在什么硬件上验证的"，硬件换代即触发复测；把参数当成硬件的函数来管理，是 IO 层与其他层最大的管理差异，也是最容易被忽略的一条。

最后补一个观测细节：把"IO 等待的分布形态"纳入日常——等待时长直方图（而非平均值）能区分"均匀的慢"（介质或配置问题）与"长尾的偶发慢"（争用或突发问题），两者的处理方向完全不同；直方图这个视角在 CPU、锁、网络各层同样适用，是全教程反复出现的观测升级——从平均值的时代搬到分布的时代，是性能观测的成人礼。