如何剖析PostgreSQL实例？CPU满负载排查及查询性能分析求助

我来帮你梳理下PostgreSQL CPU跑满时的排查思路和可用工具，这些都是日常运维里亲测好用的方法：

1. pg_stat_statements（必用的历史查询统计工具） #

这是PostgreSQL官方推荐的扩展，能精准统计所有查询的CPU耗时、调用次数、平均时长等关键指标，完美解决你区分“单条慢查询”还是“请求量过大”的需求。

启用步骤：

• 修改postgresql.conf配置：

  shared_preload_libraries = 'pg_stat_statements'
  pg_stat_statements.track = 'all'
  

• 重启PostgreSQL实例，然后创建扩展：

  CREATE EXTENSION pg_stat_statements;
  

查询高CPU占比的语句：

SELECT 
  queryid,
  LEFT(query, 200) AS query_summary, -- 截取查询前200字符方便查看
  total_time / 1000 AS total_cpu_sec, -- 总CPU耗时（秒）
  mean_time / 1000 AS avg_cpu_sec, -- 单次查询平均CPU耗时（秒）
  calls, -- 查询总调用次数
  (total_time / sum(total_time) OVER ()) * 100 AS cpu_percent -- 该查询占总CPU的比例
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

从结果里就能一眼看出：如果calls数值极大但avg_cpu_sec很小，就是请求量过大导致；如果calls少但avg_cpu_sec很高，就是单条查询执行缓慢拖垮CPU。

2. pg_top/pg_activity（实时连接监控工具） #

类似系统的top命令，能实时查看每个PostgreSQL连接的CPU占用率、当前执行的查询、连接状态等，适合快速定位正在运行的高CPU查询。

• 直接在终端运行pg_top就能启动，界面会动态刷新每个连接的CPU占比、查询内容；
• pg_activity是更可视化的版本，还能展示查询的执行计划片段，排查实时阻塞很方便。

3. auto_explain（自动记录慢查询执行计划） #

如果有些查询单次执行时间不长，但总调用量极大导致CPU跑满，或者你需要看慢查询的具体执行路径，auto_explain会自动把符合条件的查询计划写入日志。

配置方法：
修改postgresql.conf：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms' -- 记录超过500ms的查询
auto_explain.log_analyze = on -- 记录实际执行耗时
auto_explain.log_buffers = on -- 记录缓冲区使用情况
auto_explain.log_statements = 'all'

重启实例后，查看PostgreSQL日志就能看到这些查询的执行计划，帮你找到全表扫描、低效索引等CPU消耗点。

1. 先看实时负载
   先用pg_top或系统top命令，确认是单个postgres进程CPU拉满（单条慢查询），还是多个进程累加达到100%（请求量过大）。同时看连接数是否超过预期，有没有大量空闲未释放的连接。

2. 用pg_stat_statements做历史分析
   运行上面的查询语句，统计最近一段时间内的CPU消耗Top10查询，结合calls和avg_cpu_sec判断是请求量问题还是单条查询效率问题。

3. 抓取执行计划优化
   针对高CPU的查询，手动执行EXPLAIN ANALYZE [你的查询语句]，查看执行计划是否有优化空间：比如是否用到了合适的索引、是否有嵌套循环导致的重复计算、统计信息是否过时等。

4. 系统层面辅助排查
   CPU跑满不一定全是PostgreSQL的锅，可能是系统资源瓶颈：

   • 用iostat看磁盘IO利用率，如果%util接近100%，可能是IO等待导致CPU空转；
   • 用vmstat看上下文切换次数（cs列），如果数值过大，可能是并发连接过多导致CPU频繁切换上下文。

5. 检查并发配置
   查看max_connections、work_mem、shared_buffers等配置是否合理，不合理的内存配置可能导致频繁的内存交换，间接拉高CPU占用。

内容的提问来源于stack exchange，提问作者Gili