🔗 接上一篇《PostgreSQL锁问题排查》，今天我们深入数据库的“内脏系统”——内存、磁盘I/O与临时文件，找出那些让系统变慢的“隐形杀手”。

你是否发现：

• 查询偶尔卡顿，但找不到慢SQL？
• 磁盘I/O使用率飙升？
• temp_file 持续增长？

这些问题，往往源于资源瓶颈。PostgreSQL的性能不仅取决于SQL本身，更依赖于底层资源的健康状态。今天，我就教你用几条关键SQL，精准定位性能瓶颈。

---

🧠 核心指标：三大“生命体征”

PostgreSQL的性能健康，主要看三个核心指标：

指标	作用	健康标准
缓冲池命中率	内存缓存效率	> 99%
I/O延迟	磁盘读写速度	< 10ms
临时文件使用	是否频繁落盘	越少越好

我们逐个击破。

---

1️⃣ 缓冲池命中率 —— 内存“吸氧效率”

PostgreSQL通过 shared_buffers 缓存数据页，命中率越高，磁盘I/O越少。

SELECT
    SUM(blks_hit) * 100.0 / (SUM(blks_hit) + SUM(blks_read)) AS buffer_hit_ratio
FROM
    pg_stat_database
WHERE
    datname = current_database();

✅ 解读：

• blks_hit：从内存缓存中读取的块数
• blks_read：从磁盘读取的块数
• 计算公式：命中率 = hit / (hit + read)

📊 健康标准：

• ✅ > 99%：优秀，内存充足
• ⚠️ 95% ~ 99%：可接受，关注趋势
• ❌ < 95%：危险！需优化或增加 shared_buffers

💡 优化建议：

• 增加 shared_buffers（通常设为物理内存的 25%）
• 优化SQL，减少全表扫描

---

2️⃣ I/O延迟分析 —— 磁盘“血液循环”

高I/O延迟是性能卡顿的常见原因。

SELECT
    datname AS database,
    blks_read,
    ROUND((blk_read_time / 1000.0)::NUMERIC, 2) AS read_time_s,
    ROUND((blk_write_time / 1000.0)::NUMERIC, 2) AS write_time_s,
CASE WHEN blks_read > 0
THEN ROUND((blk_read_time::NUMERIC / blks_read), 3)
ELSE 0
END AS avg_read_latency_ms
FROM
    pg_stat_database
WHERE
    datname NOT LIKE 'template%'
AND datname IS NOT NULL AND blk_read_time IS NOT NULL 
ORDER BY
    blk_read_time DESC;

✅ 解读：

• blk_read_time：花在磁盘读取上的总时间（毫秒）
• avg_read_latency_ms：平均每次读取的延迟
• 关注 read_time_s 高的数据库

🚨 警报阈值：

• 平均读延迟 > 10ms → 磁盘性能瓶颈
• 写延迟高 → 检查WAL写入或存储性能

💡 排查方向：

• 使用SSD替代HDD
• 优化WAL配置（wal_writer_delay, commit_delay）
• 检查存储系统负载

---

3️⃣ 临时文件监控 —— “内存溢出”预警

当排序、哈希操作超出内存限制时，PostgreSQL会使用临时文件（temp files），导致性能骤降。

SELECT
    temp_files,
    pg_size_pretty(temp_bytes) AS temp_size
FROM
    pg_stat_database
WHERE
    datname = current_database();

✅ 解读：

• temp_files：自启动以来创建的临时文件数
• temp_bytes：临时文件总大小
• 使用 pg_size_pretty() 格式化为可读单位

🚨 危险信号：

• temp_files 持续快速增长 → 说明大量查询在落盘
• 尤其是 ORDER BY、GROUP BY、JOIN 操作

💡 优化建议：

• 增加 work_mem：为每个操作提供更多内存
• 优化SQL：避免大结果集排序
• 添加索引：减少排序需求

---

📊 综合诊断：表级资源消耗

找出最“吃资源”的表：

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    (n_dead_tup::float / n_live_tup) AS dead_ratio,
    pg_size_pretty(pg_relation_size(schemaname||'.'||relname)) AS size
FROM pg_stat_user_tables
WHERE n_live_tup > 0
ORDER BY dead_ratio DESC;

✅ 关键指标：

• dead_ratio：死元组比例，> 0.2 需立即 VACUUM
• last_autovacuum：检查自动清理是否正常运行
• size：表大小，大表是优化重点

🛠️ 处理建议：

• 高 dead_ratio → 手动 VACUUM 或调整 autovacuum 参数
• 大表无索引 → 评估是否需要分区或归档

---

📣 总结

性能瓶颈定位，核心是“数据驱动”：

• 🩺 缓冲池命中率 < 99%？→ 加内存 or 优化SQL
• ⚡ I/O延迟 > 10ms？→ 检查磁盘 or 存储
• 📦 临时文件 太多？→ 调大 work_mem or 加索引
• 🧟 死元组 积压？→ 立即 VACUUM

🔗 下期预告：

下一篇《PostgreSQL表空间与膨胀分析》，我们将深入存储层，解决“越用越大”的膨胀问题！

📌 点赞 + 关注，掌握PostgreSQL性能调优核心技能！

👉 让你的数据库跑得更快、更稳！

强烈推荐，使用AI自动诊断

看完是不是觉得要记下好多的SQL，排查步骤又繁琐，不要担心，在 AI 的时代，让大模型来替我们排查分析数据库问题，推荐一款开源好用的MCP Server 工具：SmartDB_MCP ，它不仅能让AI与多种数据库“畅聊无阻”，还能像瑞士军刀一样，提供从SQL优化到数据库健康检测分析的一站式解决方案。
github地址 ： https://github.com/wenb1n-dev/SmartDB_MCP
博文地址：SmartDB：AI与数据库的“翻译官”，开启无缝交互新时代！