文章目录
- 每日一句正能量
- 1. 背景与问题
- 2. 环境与数据
- 3. 复现过程
- 4. 方案实施
- 5. 结果对比
- 6. 风险与复盘
每日一句正能量
“人间最好的相遇，不是在路上，而是在心里。”
物理的、短暂的相逢（“在路上”）是缘分；而精神的、深刻的共鸣与留存（“在心里”）才是真正的相遇。最美的关系，是一种内在的拥有。
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：
SELECTorder_id,user_id,amountFROMordersWHEREuser_id=$1ORDERBYcreate_timeDESCLIMIT20;优化前执行计划：
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. 复现过程
- pgbench导入100GB数据。
- 热点数据占总体15%。
- 持续执行高并发查询。
- 使用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参数配置不合理会引起写放大。
复盘建议：
- Shared Buffers通常设置为总内存20%~30%。
- effective_cache_size应反映数据库可利用缓存总量。
- 每次调参后必须结合EXPLAIN(ANALYZE,BUFFERS)、pg_stat_statements与iostat交叉验证。
- 建议持续观察一周业务高峰数据，再决定是否继续扩大缓存。
本文以真实调优流程为主线，围绕执行计划、监控指标、参数前后对比进行分析，可直接迁移到读密集业务场景。
转载自：https://blog.csdn.net/u014727709/article/details/164031415
欢迎 👍点赞✍评论⭐收藏，欢迎指正
