性能视图

前言

如果说PostgreSQL是一个精密的瑞士手表，那么性能视图就是手表上的透明表盘，让我们能够清晰地看到内部的齿轮如何运转。这些视图提供了数据库运行时的详细信息，就像医生通过X光查看人体内部一样，数据库管理员可以通过性能视图洞察数据库的健康状况，找出性能瓶颈，优化系统运行。

查看性能视图

PostgreSQL提供了丰富的性能视图，以pg_stat_为前缀。我们可以通过以下命令查看所有可用的性能视图：

SELECT relname 
FROM pg_class 
WHERE relname LIKE 'pg_stat_%' 
ORDER BY relname;

常见性能视图

数据库级别视图

pg_stat_database

pg_stat_database视图提供了每个数据库级别的统计信息，是数据库整体性能监控的基础。

SELECT datname, numbackends, xact_commit, xact_rollback, 
       blks_read, blks_hit, temp_bytes, deadlocks
FROM pg_stat_database;

关键指标解析：

• numbackends：当前连接数
• xact_commit/xact_rollback：提交/回滚的事务数
• blks_read/blks_hit：磁盘块读取次数与缓存命中次数，用于计算缓存命中率
• temp_bytes：排序和哈希操作使用的临时文件大小
• deadlocks：死锁次数

应用场景：

• 监控数据库连接数是否超过阈值
• 分析事务提交和回滚比例，评估应用稳定性
• 计算缓存命中率：blks_hit / (blks_hit + blks_read)
• 检查临时文件使用量，评估内存配置是否合理
• 监控死锁情况，优化事务隔离级别

pg_stat_database_conflicts

此视图记录了数据库冲突统计，对于高并发系统尤为重要。

SELECT datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock
FROM pg_stat_database_conflicts;

关键指标解析：

• confl_tablespace：表空间冲突次数
• confl_lock：锁冲突次数
• confl_snapshot：快照冲突次数
• confl_bufferpin：缓冲区固定冲突次数
• confl_deadlock：死锁冲突次数

应用场景：

• 分析高并发环境下的锁竞争情况
• 评估快照隔离级别的效果
• 优化事务设计，减少冲突

表级别视图

pg_stat_user_tables

pg_stat_user_tables视图提供了用户表的统计信息，是表性能分析的基础。

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, 
       n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup
FROM pg_stat_user_tables;

关键指标解析：

• seq_scan/seq_tup_read：顺序扫描次数和读取的元组数
• idx_scan/idx_tup_fetch：索引扫描次数和获取的元组数
• n_tup_ins/n_tup_upd/n_tup_del：插入/更新/删除的元组数
• n_tup_hot_upd：热更新元组数（无需更新索引指针的更新）
• n_live_tup/n_dead_tup：存活/死亡元组数，用于评估VACUUM效果

应用场景：

• 识别频繁访问的表，优化热点数据
• 分析表扫描方式，评估索引使用效率
• 监控表更新频率，规划维护窗口
• 评估VACUUM效果，调整autovacuum参数

pg_stat_user_indexes

此视图提供了用户表的索引统计信息，对于索引优化至关重要。

SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes;

关键指标解析：

• idx_scan：索引扫描次数
• idx_tup_read：索引读取的元组数
• idx_tup_fetch：通过索引获取的表元组数

应用场景：

• 识别低效或未使用的索引
• 分析索引选择策略，优化查询性能
• 评估索引维护成本，平衡查询与写入性能

查询级别视图

pg_stat_statements

pg_stat_statements扩展提供了语句级别的统计信息，是SQL性能分析的核心工具。

-- 首先需要安装扩展
CREATE EXTENSION pg_stat_statements;

-- 查看查询统计
SELECT query, calls, total_time, rows, shared_blks_hit, shared_blks_read, 
       shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read,
       temp_blks_read, temp_blks_written, blk_read_time, blk_write_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

关键指标解析：

• calls：执行次数
• total_time：总执行时间（毫秒）
• rows：返回或影响的行数
• shared_blks_hit/shared_blks_read：共享缓冲区命中/读取次数
• shared_blks_dirtied/shared_blks_written：共享缓冲区脏化/写入次数
• local_blks_hit/local_blks_read：本地缓冲区命中/读取次数
• temp_blks_read/temp_blks_written：临时缓冲区读取/写入次数
• blk_read_time/blk_write_time：块读写时间（毫秒）

应用场景：

• 识别最耗时的查询，优化性能瓶颈
• 分析查询执行计划，调整索引和SQL结构
• 监控资源使用情况，优化数据库配置
• 评估查询优化效果

pg_stat_activity

pg_stat_activity视图提供了当前活动的会话信息，是实时监控的重要工具。

SELECT pid, usename, application_name, client_addr, state, query, 
       backend_start, xact_start, query_start, state_change
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start DESC;

关键指标解析：

• pid：进程ID
• usename：用户名
• application_name：应用程序名称
• client_addr：客户端地址
• state：会话状态（active, idle, idle in transaction等）
• query：当前执行的查询
• backend_start：后端启动时间
• xact_start：事务开始时间
• query_start：查询开始时间
• state_change：状态变更时间

应用场景：

• 监控当前活动会话，发现异常连接
• 识别长时间运行的查询，防止资源锁定
• 分析事务使用模式，优化事务隔离级别
• 追踪特定用户或应用程序的活动

系统级别视图

pg_stat_bgwriter

pg_stat_bgwriter视图提供了后台写入器的统计信息，对于理解数据库I/O行为很重要。

SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, 
       checkpoint_sync_time, buffers_checkpoint, buffers_clean, 
       buffers_backend, buffers_backend_fsync
FROM pg_stat_bgwriter;

关键指标解析：

• checkpoints_timed/checkpoints_req：定时/请求的检查点数量
• checkpoint_write_time/checkpoint_sync_time：检查点写入/同步时间（毫秒）
• buffers_checkpoint：检查点写入的缓冲区数量
• buffers_clean：后台清理器写入的缓冲区数量
• buffers_backend：后端直接写入的缓冲区数量
• buffers_backend_fsync：后端直接同步的缓冲区数量

应用场景：

• 分析检查点频率和耗时，优化检查点配置
• 评估I/O压力，调整写入策略
• 监控后台写入器效率，优化缓冲区管理

pg_statio_user_tables

此视图提供了表级别的I/O统计信息，是I/O性能分析的重要工具。

SELECT schemaname, relname, heap_blks_read, heap_blks_hit, 
       idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit,
       tidx_blks_read, tidx_blks_hit
FROM pg_statio_user_tables;

关键指标解析：

• heap_blks_read/heap_blks_hit：堆读取/命中次数
• idx_blks_read/idx_blks_hit：索引读取/命中次数
• toast_blks_read/toast_blks_hit：TOAST读取/命中次数
• tidx_blks_read/tidx_blks_hit：TOAST索引读取/命中次数

应用场景：

• 分析表和索引的I/O效率，优化缓存配置
• 识别频繁访问的数据，优化内存分配
• 评估索引效果，调整索引策略

性能视图使用技巧

定期收集数据

性能视图提供的是实时数据，为了进行趋势分析，可以定期收集数据：

-- 创建表存储历史数据
CREATE TABLE performance_history (
    timestamp timestamp,
    metric_name text,
    metric_value numeric
);

-- 定期收集关键指标
INSERT INTO performance_history
SELECT now(), 'database_connections', numbackends
FROM pg_stat_database WHERE datname = current_database();

INSERT INTO performance_history
SELECT now(), 'cache_hit_ratio', 
       round(blks_hit * 100.0 / (blks_hit + blks_read), 2)
FROM pg_stat_database WHERE datname = current_database();

设置警报阈值

基于性能视图数据设置警报阈值，及时发现性能问题：

-- 检查缓存命中率低于95%的数据库
SELECT datname, blks_hit, blks_read,
       round(blks_hit * 100.0 / (blks_hit + blks_read), 2) as hit_ratio
FROM pg_stat_database
WHERE blks_hit + blks_read > 0
  AND round(blks_hit * 100.0 / (blks_hit + blks_read), 2) < 95;

-- 检查死锁情况
SELECT datname, deadlocks
FROM pg_stat_database
WHERE deadlocks > 0;

结合使用多个视图

综合利用多个性能视图，全面分析系统性能：

-- 分析慢查询的I/O使用情况
SELECT s.query, s.total_time, s.calls, s.rows,
       t.heap_blks_read, t.heap_blks_hit,
       i.idx_blks_read, i.idx_blks_hit
FROM pg_stat_statements s
JOIN pg_statio_user_tables t ON s.relname = t.relname
JOIN pg_statio_user_indexes i ON s.relname = i.relname
WHERE s.total_time > 1000  -- 超过1秒的查询
ORDER BY s.total_time DESC;