资源使用分析

资源使用分析是PostgreSQL性能优化的核心环节，通过监控和分析CPU、内存、磁盘I/O、网络等系统资源的使用情况，DBA可以及时发现资源瓶颈，为性能优化提供依据。有效的资源分析需要结合操作系统层面和数据库层面的监控数据，形成完整的资源使用视图。

资源使用分析基础

监控原则

多维度监控：同时关注操作系统和数据库层面的指标
基线建立：在系统正常运行时建立资源使用基线
异常检测：设置合理的告警阈值，及时发现异常
趋势分析：关注资源使用的长期趋势，进行容量规划
关联分析：将资源使用与SQL执行、事务等关联分析

版本差异注意

不同PostgreSQL版本在资源监控方面存在差异，主要体现在：

PostgreSQL 14+：增强了wait_event类型，提供更细粒度的等待事件
PostgreSQL 13+：改进了pg_stat_statements的统计信息
PostgreSQL 12+：引入了并行VACUUM，减少了锁持有时间
PostgreSQL 10+：引入了pg_stat_wal_receiver视图，用于监控复制

CPU使用分析

监控指标

指标名称	说明	监控来源	告警阈值
CPU使用率	系统总体CPU使用率	操作系统（top、vmstat、sar）	持续>90%
PostgreSQL进程CPU	单个PostgreSQL进程CPU使用率	操作系统（top、ps）	单个进程>80%
等待事件分布	进程等待事件类型统计	pg_stat_activity.wait_event_type	锁等待占比>30%
活跃连接数	当前活跃连接数量	pg_stat_activity	接近max_connections

实时监控

sql

-- 查看活跃连接的CPU使用和等待事件
SELECT pid, usename, datname, state, wait_event_type, wait_event, query 
FROM pg_stat_activity 
WHERE state != 'idle';

-- 查看CPU消耗最高的SQL（需要pg_stat_statements）
SELECT queryid, query, calls, total_exec_time, mean_exec_time 
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

bash

# 操作系统层面监控
# 实时查看PostgreSQL进程CPU
top -p $(pgrep -d ',' postgres)

# 查看CPU使用趋势
sar -u 1 10

常见问题与解决方案

问题1：CPU使用率持续过高

症状：系统CPU使用率持续>90%，数据库响应缓慢

可能原因：

复杂查询消耗大量CPU
缺少索引导致全表扫描
锁竞争导致CPU空闲但响应慢
配置不当（如work_mem过大）

解决方案：

sql

-- 优化CPU消耗最高的SQL，添加索引
-- 调整work_mem参数
ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

问题2：CPU突发峰值

症状：CPU使用率间歇性突发到100%

可能原因：

定时任务（如报表生成）
大量并发连接
自动VACUUM操作

解决方案：

调整定时任务执行时间
使用连接池限制并发
优化VACUUM配置

内存使用分析

监控指标

指标名称	说明	监控来源	告警阈值
系统内存使用率	系统总体内存使用情况	操作系统（free、vmstat）	持续>90%
PostgreSQL进程内存	单个PostgreSQL进程内存	操作系统（ps、pmap）	进程内存>预期值
shared_buffers命中率	共享缓冲区缓存命中率	pg_stat_database.blks_hit/(blks_read+blks_hit)	<95%
临时文件使用	超过work_mem产生的临时文件	pg_stat_database.temp_files/temp_bytes	temp_bytes持续增长

实时监控

sql

-- 查看shared_buffers使用情况
SELECT 
  current_setting('shared_buffers') AS shared_buffers,
  round(blks_hit * 100.0 / (blks_read + blks_hit), 2) AS cache_hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- 查看临时文件使用
SELECT datname, temp_files, temp_bytes, pg_size_pretty(temp_bytes) 
FROM pg_stat_database 
ORDER BY temp_bytes DESC;

bash

# 查看系统内存
free -h

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

常见问题与解决方案

问题1：shared_buffers命中率低

症状：shared_buffers命中率<95%

可能原因：

shared_buffers设置过小
大量随机读写
频繁的表扫描

解决方案：

sql

-- 调整shared_buffers（通常设置为系统内存的25%-40%）
ALTER SYSTEM SET shared_buffers = '8GB';
SELECT pg_reload_conf();

问题2：临时文件过多

症状：pg_stat_database.temp_files持续增长

可能原因：

work_mem设置过小
大量排序或哈希操作
复杂查询

解决方案：

sql

-- 调整work_mem参数
ALTER SYSTEM SET work_mem = '32MB';
SELECT pg_reload_conf();

-- 优化查询，减少排序操作

磁盘I/O分析

监控指标

指标名称	说明	监控来源	告警阈值
磁盘利用率	磁盘读写操作使用率	操作系统（iostat、sar）	持续>90%
磁盘吞吐量	磁盘读写数据量	操作系统（iostat、sar）	接近磁盘最大吞吐量
磁盘IOPS	每秒I/O操作次数	操作系统（iostat、sar）	接近磁盘最大IOPS
平均响应时间	I/O操作平均响应时间	操作系统（iostat、sar）	>50ms
缓存命中率	数据库缓冲区缓存命中率	pg_stat_database.blks_hit/(blks_read+blks_hit)	<95%

实时监控

sql

-- 查看数据库I/O统计
SELECT datname, blks_read, blks_hit, 
       round(blks_hit * 100.0 / (blks_read + blks_hit), 2) AS cache_hit_ratio 
FROM pg_stat_database;

-- 查看表级I/O统计
SELECT relname, schemaname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch,
       heap_blks_read, heap_blks_hit, 
       round(heap_blks_hit * 100.0 / (heap_blks_read + heap_blks_hit), 2) AS table_cache_hit
FROM pg_stat_user_tables 
ORDER BY heap_blks_read DESC 
LIMIT 10;

bash

# 查看磁盘I/O情况
iostat -x 1

# 查看进程级I/O
iotop -o

常见问题与解决方案

问题1：磁盘I/O利用率高

症状：磁盘I/O利用率持续>90%

可能原因：

全表扫描过多
写入频繁（大量INSERT/UPDATE/DELETE）
shared_buffers设置过小
表膨胀

解决方案：

sql

-- 添加合适索引，减少全表扫描
CREATE INDEX idx_table_column ON schema.table(column);

-- 调整shared_buffers
ALTER SYSTEM SET shared_buffers = '8GB';
SELECT pg_reload_conf();

-- 执行VACUUM清理膨胀
VACUUM ANALYZE schema.table;

问题2：写入延迟高

症状：写入操作响应时间长

可能原因：

WAL写入瓶颈
磁盘性能差
大量并发写入

解决方案：

sql

-- 调整WAL相关参数
ALTER SYSTEM SET wal_buffers = '16MB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
SELECT pg_reload_conf();

网络流量分析

监控指标

指标名称	说明	监控来源	告警阈值
网络吞吐量	网络接口读写数据量	操作系统（ifstat、sar）	接近带宽上限
连接数	PostgreSQL连接数量	pg_stat_activity、操作系统（netstat、ss）	接近max_connections
复制延迟	主从复制延迟	pg_stat_replication、pg_stat_wal_receiver	>1MB或>10秒

实时监控

sql

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

-- 查看连接来源分布
SELECT client_addr, count(*) FROM pg_stat_activity GROUP BY client_addr;

-- 查看复制延迟（主库）
SELECT application_name, client_addr, state, sync_state, 
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_lag_bytes
FROM pg_stat_replication;

-- 查看复制延迟（从库）
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS replay_lag_bytes,
       pg_last_xact_replay_timestamp();

bash

# 查看网络流量
ifstat 1

# 查看PostgreSQL连接数
ss -an | grep 5432 | wc -l

常见问题与解决方案

问题1：网络连接数过多

症状：连接数接近max_connections

可能原因：

应用程序连接泄漏
缺少连接池
短连接频繁创建销毁

解决方案：

使用连接池（如Pgbouncer、Pgpool-II）
优化应用程序，确保连接正确关闭
调整max_connections参数

问题2：复制延迟大

症状：主从复制延迟持续增长

可能原因：

从库性能差
主库写入量大
网络带宽不足
从库故障

解决方案：

优化从库硬件配置
增加网络带宽
检查从库日志，排除故障
考虑使用级联复制