---

每日一句正能量

“人间最好的相遇，不是在路上，而是在心里。”
物理的、短暂的相逢（“在路上”）是缘分；而精神的、深刻的共鸣与留存（“在心里”）才是真正的相遇。最美的关系，是一种内在的拥有。

1. 背景与问题

某读密集PostgreSQL系统，数据库服务器128GB内存，白天QPS持续在15000左右。虽然CPU利用率仅35%，但磁盘随机读持续偏高，SQL响应时间波动明显。排查发现Shared Buffers设置过小，OS Page Cache利用率不足，导致热点数据频繁重新加载。

2. 环境与数据

• PostgreSQL 16
• Linux x86_64
• 内存128GB
• NVMe SSD
• Shared Buffers：8GB（优化前）→32GB（优化后）
• work_mem：4MB→16MB
• effective_cache_size：64GB→96GB

核心SQL：

SELECT order_id,user_id,amount
FROM orders
WHERE user_id=$1
ORDER BY create_time DESC
LIMIT 20;

优化前执行计划：

Index Scan using idx_orders_user
Buffers: shared hit=820 read=73
Execution Time: 18.6 ms

监控指标：

指标	优化前	优化后
Shared Buffer命中率	92.1%	98.7%
磁盘随机读IOPS	5200	1650
平均SQL响应(ms)	18.6	11.0
Checkpoint写入峰值(MB/s)	430	270

3. 复现过程

1. pgbench导入100GB数据。
2. 热点数据占总体15%。
3. 持续执行高并发查询。
4. 使用pg_stat_statements、EXPLAIN(ANALYZE,BUFFERS)、iostat、vmstat采集数据。

4. 方案实施

参数调整：

shared_buffers=32GB
effective_cache_size=96GB
work_mem=16MB
maintenance_work_mem=2GB
random_page_cost=1.1
effective_io_concurrency=256

执行计划优化后：

Index Scan using idx_orders_user
Buffers: shared hit=895 read=6
Execution Time: 11.0 ms

重点监控：

• Shared Buffer Hit Ratio
• OS Page Cache命中率
• Dirty Page比例
• Checkpoint耗时
• SQL TopN

5. 结果对比

优化后热点数据基本保留在Shared Buffers中，而冷数据更多依赖OS Page Cache，二者形成分层缓存。Shared Buffers负责事务一致性和数据库页管理，操作系统缓存负责减少物理IO，两者并非互斥，而是协同工作。命中率提升后，磁盘随机读下降约68%，P99响应时间下降约38%，CPU利用率基本保持稳定。

6. 风险与复盘

风险：

• Shared Buffers配置过大可能压缩OS缓存空间。
• work_mem过大会导致并发内存放大。
• Checkpoint参数配置不合理会引起写放大。

复盘建议：

1. Shared Buffers通常设置为总内存20%~30%。
2. effective_cache_size应反映数据库可利用缓存总量。
3. 每次调参后必须结合EXPLAIN(ANALYZE,BUFFERS)、pg_stat_statements与iostat交叉验证。
4. 建议持续观察一周业务高峰数据，再决定是否继续扩大缓存。

本文以真实调优流程为主线，围绕执行计划、监控指标、参数前后对比进行分析，可直接迁移到读密集业务场景。

---

转载自：https://blog.csdn.net/u014727709/article/details/164031415
欢迎 👍点赞✍评论⭐收藏，欢迎指正