PostgreSQL 高CPU内存使用率问题处理

高CPU使用率问题

常见原因

查询性能问题：
- 慢查询导致CPU长时间占用
- 缺少索引或索引失效
- 复杂查询（如多表连接、子查询）
- 全表扫描或大范围扫描
系统资源竞争：
- 其他进程占用大量CPU资源
- 操作系统调度问题
- 硬件资源不足
PostgreSQL 内部问题：
- 自动VACUUM或ANALYZE占用过多CPU
- 并行查询配置不合理
- 锁竞争导致的CPU自旋
- WAL写入压力过大

监控方法

系统级监控

bash

# 查看系统CPU使用情况
top

# 查看PostgreSQL进程CPU使用情况
top -p $(pgrep -f postgres)

# 查看CPU使用率按进程排序
ps aux --sort=-%cpu | grep postgres

# 查看CPU负载
uptime

# 查看每个CPU核心的使用情况
iostat -c

PostgreSQL内部监控

sql

-- 查看当前活跃查询的CPU使用情况
SELECT 
    pid, 
    usename, 
    application_name, 
    client_addr, 
    query, 
    state, 
    now() - query_start as query_duration
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_duration DESC;

-- 查看后台进程的CPU使用情况
SELECT 
    pid, 
    backend_type, 
    query, 
    state
FROM pg_stat_activity
WHERE backend_type <> 'client backend';

-- 查看自动VACUUM进程
SELECT 
    pid, 
    query, 
    state,
    relname
FROM pg_stat_activity
WHERE query LIKE '%autovacuum%';

诊断步骤

确认问题范围：
- 是单个查询还是多个查询导致
- 是周期性问题还是持续性问题
- 是所有CPU核心还是特定核心
定位消耗CPU的进程：
- 使用top或ps命令定位高CPU进程
- 查看pg_stat_activity确定具体查询
分析查询执行计划：
- 使用EXPLAIN ANALYZE分析慢查询
- 检查是否使用了合适的索引
- 查看扫描行数和成本估算
检查系统资源：
- 查看内存使用情况
- 检查磁盘I/O情况
- 查看网络连接情况

解决方案

查询优化

sql

-- 分析查询执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 添加缺失的索引
CREATE INDEX idx_users_email ON users(email);

-- 优化查询语句
-- 避免SELECT *，只查询需要的列
SELECT id, name, email FROM users WHERE email = 'test@example.com';

-- 优化JOIN语句，确保ON条件有索引
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- 确保o.user_id和u.id都有索引

系统配置优化

txt

# 调整并行查询配置
max_parallel_workers = 4
max_parallel_workers_per_gather = 2

# 调整自动VACUUM配置
autovacuum_max_workers = 2
autovacuum_naptime = 5min

# 调整work_mem参数
work_mem = 8MB

# 调整maintenance_work_mem参数
maintenance_work_mem = 512MB

硬件资源扩展

增加CPU核心数
升级CPU主频
增加系统内存
使用高性能存储

高内存使用率问题

常见原因

配置参数不合理：
- shared_buffers设置过大
- work_mem设置过大导致内存膨胀
- maintenance_work_mem设置过大
- max_connections过多导致内存占用总量过大
查询执行问题：
- 大结果集查询
- 复杂排序或哈希操作
- 临时表占用大量内存
- 游标使用不当
内存泄漏：
- PostgreSQL 内部组件内存泄漏
- 第三方扩展内存泄漏
- 长时间运行的事务占用内存
系统级问题：
- 操作系统缓存管理问题
- 其他进程占用大量内存
- 内存碎片化

监控方法

系统级监控

bash

# 查看系统内存使用情况
free -h

# 查看PostgreSQL进程内存使用情况
ps aux --sort=-%mem | grep postgres

# 查看内存使用详情
cat /proc/meminfo

# 查看PostgreSQL进程的内存映射
pmap -x $(pgrep -f "postgres -D") | head -30

PostgreSQL内部监控

sql

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看配置的最大连接数
SHOW max_connections;

-- 查看当前活跃查询的内存使用情况
SELECT 
    pid, 
    usename, 
    application_name, 
    client_addr, 
    query, 
    state,
    pg_size_pretty(pg_total_relation_size(relid)) as relation_size
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

-- 查看临时文件使用情况
SELECT 
    datname, 
    temp_files, 
    temp_bytes, 
    pg_size_pretty(temp_bytes) as temp_size
FROM pg_stat_database;

-- 查看缓冲区使用情况
SELECT 
    name, 
    setting, 
    unit,
    short_desc
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size');

诊断步骤

确认内存使用情况：
- 使用free命令查看系统内存使用
- 检查PostgreSQL进程的内存占用
- 查看是否有swap使用
分析配置参数：
- 检查shared_buffers、work_mem等参数设置
- 计算总内存占用：shared_buffers + (work_mem * max_connections) + maintenance_work_mem + 操作系统内存
查看当前查询：
- 检查是否有长时间运行的查询
- 查看是否有大结果集查询
- 查看是否有复杂排序或哈希操作
检查临时文件：
- 查看临时文件数量和大小
- 临时文件过大可能表示work_mem设置不足

解决方案

配置参数优化

txt

# 调整shared_buffers参数（建议为系统内存的25%）
shared_buffers = 4GB

# 调整work_mem参数（根据系统内存和max_connections计算）
# 公式：(系统内存 * 0.25) / max_connections
work_mem = 8MB

# 调整maintenance_work_mem参数（建议为系统内存的5%-10%）
maintenance_work_mem = 1GB

# 调整max_connections参数（根据系统内存和work_mem计算）
max_connections = 200

# 调整effective_cache_size参数（建议为系统内存的50%-75%）
effective_cache_size = 12GB

查询优化

sql

-- 优化大结果集查询，使用LIMIT限制结果集
SELECT * FROM large_table LIMIT 100;

-- 优化排序操作，确保有合适的索引
SELECT * FROM users ORDER BY created_at DESC LIMIT 100;
-- 确保created_at列有索引

-- 优化JOIN操作，避免笛卡尔积
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'completed';
-- 确保o.status列有索引

-- 使用CTE或临时表优化复杂查询
WITH temp_data AS (
    SELECT * FROM large_table WHERE condition = 'value'
) SELECT * FROM temp_data WHERE another_condition = 'another_value';

系统级优化

增加系统内存
调整操作系统内存管理参数
关闭不必要的服务和进程
考虑使用连接池减少连接数
定期重启PostgreSQL释放内存（仅作为临时解决方案）

高CPU内存使用率的关联分析

常见关联模式

查询导致的资源风暴：
- 单个慢查询同时导致高CPU和高内存
- 特征：CPU使用率和内存使用率同时飙升
- 解决方案：优化查询，添加索引
自动VACUUM影响：
- 自动VACUUM导致CPU使用率升高，同时可能占用大量内存
- 特征：周期性出现，与表大小相关
- 解决方案：调整自动VACUUM参数
连接数过多：
- 大量连接导致内存使用率升高，同时可能导致CPU上下文切换频繁
- 特征：连接数接近或达到max_connections
- 解决方案：使用连接池，降低max_connections
WAL写入压力：
- WAL写入导致CPU使用率升高，同时可能占用内存用于WAL缓冲区
- 特征：WAL文件生成速率高，磁盘I/O高
- 解决方案：调整WAL相关参数

综合诊断方法

bash

# 同时监控CPU、内存、磁盘I/O和网络
vmstat 1

# 监控PostgreSQL的统计信息
iostat -x 1

# 监控PostgreSQL的WAL写入情况
dstat -td --disk-util --io

# 查看PostgreSQL的锁情况
psql -c "SELECT * FROM pg_locks WHERE granted = false;"

版本差异处理

版本	高CPU内存问题特点	解决方案差异
PostgreSQL 12及以下	并行查询支持有限，可能导致CPU使用率过高	限制并行查询参数
PostgreSQL 13+	并行查询优化，自动VACUUM增强	调整并行查询和自动VACUUM参数
PostgreSQL 14+	内存管理优化，支持更多内存配置选项	使用新的内存配置参数
PostgreSQL 15+	改进的查询优化器，减少不必要的CPU和内存使用	利用优化器改进，调整统计信息收集

预防措施

监控与告警

设置CPU使用率告警：当CPU使用率超过80%持续5分钟时告警
设置内存使用率告警：当内存使用率超过90%时告警
设置慢查询告警：当查询执行时间超过10秒时告警
设置连接数告警：当连接数超过max_connections的80%时告警

定期维护

定期分析查询性能：每周使用pg_stat_statements分析慢查询
定期优化索引：每月检查并优化索引
定期更新统计信息：每周运行ANALYZE命令
定期检查配置参数：每月根据系统负载调整配置

性能测试

新功能上线前进行性能测试：使用pgbench或实际业务数据进行测试
定期进行基准测试：每季度进行一次基准测试，比较性能变化
测试配置变更影响：在测试环境验证配置变更的影响

常见问题（FAQ）

Q1: PostgreSQL 进程CPU使用率100%怎么办？

A1: 处理步骤：

使用top命令定位消耗CPU的PostgreSQL进程
使用SELECT * FROM pg_stat_activity WHERE pid = <pid>;查看具体查询
使用EXPLAIN ANALYZE分析查询执行计划
根据分析结果优化查询或添加索引
如果是系统进程（如autovacuum），调整相关配置参数

Q2: PostgreSQL 内存使用率过高导致系统swap怎么办？

A2: 处理步骤：

检查shared_buffers、work_mem和max_connections参数设置
计算总内存占用：shared_buffers + (work_mem * max_connections) + maintenance_work_mem
降低work_mem或max_connections参数
考虑增加系统内存
调整操作系统的swap设置

Q3: 如何区分是查询问题还是配置问题导致的高CPU？

A3: 区分方法：

查询问题：
- 特定查询导致CPU飙升
- 查询执行时间长
- 缺少索引或索引失效
配置问题：
- 系统整体CPU使用率高
- 多个查询同时受影响
- 配置参数与系统资源不匹配

Q4: 自动VACUUM导致CPU使用率过高怎么办？

A4: 解决方案：

调整autovacuum_max_workers参数，减少同时运行的VACUUM进程数
调整autovacuum_naptime参数，增加VACUUM进程的休眠时间
针对特定表调整VACUUM参数：ALTER TABLE table_name SET (autovacuum_vacuum_cost_delay = 20);
在低峰期手动运行VACUUM

Q5: 如何监控PostgreSQL的内存使用情况？

A5: 监控方法：

使用ps aux --sort=-%mem | grep postgres查看进程内存使用
使用pg_settings查看内存相关参数
使用pg_stat_activity查看当前查询的内存使用
使用pg_stat_database查看临时文件使用情况
使用第三方监控工具如Prometheus + Grafana

Q6: 如何优化大量连接导致的内存问题？

A6: 解决方案：

使用连接池（如PgBouncer）减少实际连接数
降低max_connections参数
调整work_mem参数，降低每个连接的内存消耗
优化应用程序，减少空闲连接

Q7: 如何处理PostgreSQL的内存泄漏问题？

A7: 处理方法：

确认是否为内存泄漏：监控内存使用趋势
检查PostgreSQL版本，升级到修复了内存泄漏的版本
检查第三方扩展，禁用可能导致内存泄漏的扩展
定期重启PostgreSQL释放内存
向PostgreSQL社区报告内存泄漏问题

Q8: 如何优化复杂查询的CPU和内存使用？

A8: 优化方法：

使用EXPLAIN ANALYZE分析查询执行计划
添加合适的索引
优化查询结构，避免不必要的JOIN和子查询
使用CTE或临时表拆分复杂查询
限制结果集大小，使用LIMIT
调整work_mem参数，为复杂查询提供足够内存

Q9: 如何处理周期性高CPU内存使用率问题？

A9: 处理方法：

分析周期性规律，确定是否与特定任务相关
检查是否与自动VACUUM、备份或报表任务冲突
调整相关任务的执行时间，避免同时运行
优化相关任务的性能

Q10: 如何评估是否需要硬件升级？

A10: 评估方法：

监控CPU和内存使用率趋势
检查是否频繁出现性能问题
进行基准测试，比较与预期性能的差距
考虑业务增长需求
计算硬件升级的ROI

案例分析

案例1：慢查询导致高CPU使用率

问题现象：

CPU使用率突然飙升到90%以上
应用响应缓慢
数据库连接数增加

诊断过程：

使用top命令发现多个PostgreSQL进程CPU使用率接近100%
使用pg_stat_activity查看当前查询，发现大量相同的慢查询
使用EXPLAIN ANALYZE分析查询，发现缺少索引导致全表扫描

解决方案：

添加缺失的索引：CREATE INDEX idx_orders_customer_id ON orders(customer_id);
优化查询语句，减少返回列数
设置慢查询日志，定期分析慢查询

优化效果：

CPU使用率降至正常水平（20%以下）
查询执行时间从10秒降至0.1秒
应用响应恢复正常

案例2：连接数过多导致内存不足

问题现象：

内存使用率持续超过95%
系统开始使用swap
PostgreSQL响应缓慢

诊断过程：

使用free -h查看内存使用情况，发现内存不足
使用ps aux --sort=-%mem | grep postgres查看PostgreSQL进程内存占用
使用SELECT count(*) FROM pg_stat_activity;查看连接数，发现接近max_connections
检查应用程序，发现连接池配置不当

解决方案：

调整max_connections从500降至200
调整work_mem从16MB降至8MB
优化应用程序的连接池配置
增加系统内存从16GB到32GB

优化效果：

内存使用率降至60%以下
不再使用swap
PostgreSQL响应恢复正常

PostgreSQL 高CPU内存使用率问题处理 ​

高CPU使用率问题 ​

常见原因 ​

监控方法 ​

系统级监控 ​

PostgreSQL内部监控 ​

诊断步骤 ​

解决方案 ​

查询优化 ​

系统配置优化 ​

硬件资源扩展 ​

高内存使用率问题 ​

常见原因 ​

监控方法 ​

系统级监控 ​

PostgreSQL内部监控 ​

诊断步骤 ​

解决方案 ​

配置参数优化 ​

查询优化 ​

系统级优化 ​

高CPU内存使用率的关联分析 ​

常见关联模式 ​

综合诊断方法 ​

版本差异处理 ​

预防措施 ​

监控与告警 ​

定期维护 ​

性能测试 ​

常见问题（FAQ） ​

Q1: PostgreSQL 进程CPU使用率100%怎么办？ ​

Q2: PostgreSQL 内存使用率过高导致系统swap怎么办？ ​

Q3: 如何区分是查询问题还是配置问题导致的高CPU？ ​

Q4: 自动VACUUM导致CPU使用率过高怎么办？ ​

Q5: 如何监控PostgreSQL的内存使用情况？ ​

Q6: 如何优化大量连接导致的内存问题？ ​

Q7: 如何处理PostgreSQL的内存泄漏问题？ ​

Q8: 如何优化复杂查询的CPU和内存使用？ ​

Q9: 如何处理周期性高CPU内存使用率问题？ ​

Q10: 如何评估是否需要硬件升级？ ​

案例分析 ​

案例1：慢查询导致高CPU使用率 ​

案例2：连接数过多导致内存不足 ​

PostgreSQL 高CPU内存使用率问题处理

高CPU使用率问题

常见原因

监控方法

系统级监控

PostgreSQL内部监控

诊断步骤

解决方案

查询优化

系统配置优化

硬件资源扩展

高内存使用率问题

常见原因

监控方法

系统级监控

PostgreSQL内部监控

诊断步骤

解决方案

配置参数优化

查询优化

系统级优化

高CPU内存使用率的关联分析

常见关联模式

综合诊断方法

版本差异处理

预防措施

监控与告警

定期维护

性能测试

常见问题（FAQ）

Q1: PostgreSQL 进程CPU使用率100%怎么办？

Q2: PostgreSQL 内存使用率过高导致系统swap怎么办？

Q3: 如何区分是查询问题还是配置问题导致的高CPU？

Q4: 自动VACUUM导致CPU使用率过高怎么办？

Q5: 如何监控PostgreSQL的内存使用情况？

Q6: 如何优化大量连接导致的内存问题？

Q7: 如何处理PostgreSQL的内存泄漏问题？

Q8: 如何优化复杂查询的CPU和内存使用？

Q9: 如何处理周期性高CPU内存使用率问题？

Q10: 如何评估是否需要硬件升级？

案例分析

案例1：慢查询导致高CPU使用率

案例2：连接数过多导致内存不足