Postger数据库 性能问题排查

当使用PostgreSQL时遇到了性能问题，可以监控数据库活动进行诊断和优化。那么，就需要知道通过哪些数据库的表可以监测到有用信息。

1. pg_stat_activity 表
   作用：实时监控所有数据库连接的状态和活动，包括正在执行的 SQL、空闲连接、事务状态等。

关键字段	说明
pid	进程 ID，唯一标识一个数据库连接。
usename	执行当前操作的用户名（如 postgres）。
datname	当前连接的数据库名。
client_addr	客户端的 IP 地址（远程连接时显示）。
query_start	当前查询开始执行的时间戳。
state	连接状态：active: 正在执行查询 idle: 空闲 idle in transaction: 事务中空闲
query	当前正在执行的 SQL 语句，或最后一次执行的语句（若状态为 idle）。
wait_event_type	进程等待的事件类型（如 Lock、IO）。
wait_event	具体的等待事件名称（如 RowLock、BufferPin）。

2. pg_locks 表
   作用：显示当前数据库中的锁信息，用于诊断锁冲突和阻塞问题。

示例：
SELECT * FROM pg_locks WHERE granted = false;

关键字段	说明
locktype	锁类型：1、relation: 表级锁2 、row: 行级锁 3、transactionid: 事务 ID 锁
relation	被锁对象的 OID（对应 pg_class.oid）。
pid	持有或等待锁的进程 ID。
mode	锁模式： AccessShareLock（共享读锁） ExclusiveLock（排他写锁）
granted	是否已获得锁（true 表示已获得，false 表示等待中）

3. pg_stat_database 表
   作用：统计数据库级别的活动（如事务数、回滚率、缓存命中率）。

示例： 查看各数据库的缓存命中率
SELECT datname, 
  (blks_hit * 100.0 / (blks_hit + blks_read + 1))::numeric(5,2) AS cache_hit_rate 
FROM pg_stat_database;

关键字段	说明
datname 数据库名。
xact_commit	已提交的事务数。
xact_rollback	已回滚的事务数。
blks_hit	缓存命中的磁盘块数。
blks_read	从磁盘读取的块数。
tup_returned	返回的行数（所有查询）。

4. pg_class 表
   作用：存储所有表和索引的元数据信息（如物理存储大小、行数等）。

示例：查看所有表及其行数
SELECT relname, reltuples 
FROM pg_class 
WHERE relkind = 'r';

关键字段	说明
oid	对象的唯一标识符。
relname	表或索引的名称。
relnamespace	所属命名空间的 OID（对应 pg_namespace.oid，通常指 schema）。
relkind	对象类型 ： r: 普通表 i: 索引 v: 视图
reltuples	表中预估的行数（由 ANALYZE 更新）。
relpages	表占用的磁盘页数（1 页 = 8 KB）。

5. pg_stat_user_tables 表
   作用：统计用户表的访问情况（如扫描次数、增删改操作）。

示例： 找出全表扫描频繁的表
SELECT relname, seq_scan, seq_tup_read 
FROM pg_stat_user_tables 
ORDER BY seq_scan DESC;

关键字段	说明
relname	表名。
seq_scan	全表扫描次数。
seq_tup_read	全表扫描读取的行数。
idx_scan	索引扫描次数。
n_tup_ins	插入的行数。
n_tup_upd	更新的行数。
n_tup_del	删除的行数。

6. pg_index 表
   作用：存储索引的元数据信息（如索引类型、唯一性约束等）。

-- 查看某表的所有索引
SELECT c.relname AS index_name 
FROM pg_index i 
JOIN pg_class c ON i.indexrelid = c.oid 
WHERE i.indrelid = 'your_table'::regclass;

关键字段	说明
indexrelid	索引的 OID（对应 pg_class.oid）。
indrelid	索引所属表的 OID。
indisunique	是否为唯一索引（t 表示唯一）。
indisprimary	是否为主键索引（t 表示主键）。
indkey	索引列的序号数组（如 indkey = ‘1 3’ 表示索引包含表的第1和第3列）。

7. pg_stat_statements
   作用：统计所有 SQL 的执行情况（需启用扩展），用于分析高频查询、慢查询和资源消耗。

-- 查看耗时最高的前 10 条 SQL
SELECT query, calls, total_time, rows 
FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

关键字段	说明
queryid	SQL 的唯一标识符（相同 SQL 归一化后相同）。
query	归一化后的 SQL 文本（去除了参数和空格）。
calls 该 SQL	被调用的总次数。
total_time	该 SQL 的总执行时间（毫秒）。
rows	该 SQL 返回或影响的总行数。
shared_blks_hit	从共享缓冲区命中读取的块数（缓存命中率高则性能好）。

1. 查看所有正在执行的 SQL

-- 数据库性能问题
SELECT
  pid,                -- 进程ID
  usename,            -- 执行SQL的用户
  datname,            -- 数据库名
  client_addr,        -- 客户端IP地址
  query_start,        -- 查询开始时间
  state,              -- 状态（active: 正在执行, idle: 空闲）
  query               -- 当前执行的SQL
FROM pg_stat_activity
WHERE state = 'active'  -- 过滤出活跃的查询
  AND backend_type = 'client backend'  -- 排除后台进程
  AND query <> '<IDLE>';               -- 排除空闲连接

2.筛选长时间运行的查询

SELECT
  pid,
  now() - query_start AS duration,  -- 查询已执行的时间
  query,
  wait_event_type,                  -- 等待事件类型（如Lock、IO）
  wait_event                        -- 具体的等待事件
FROM pg_stat_activity
WHERE state = 'active'
  AND query_start IS NOT NULL
ORDER BY duration DESC;             -- 按执行时间倒序排列

3. 检查锁等待

SELECT 
  blocked.pid AS blocked_pid,       -- 被阻塞的进程ID
  blocked.query AS blocked_query,   -- 被阻塞的SQL
  blocking.pid AS blocking_pid,     -- 阻塞者的进程ID
  blocking.query AS blocking_query, -- 阻塞者的SQL
  blocked.wait_event_type,          -- 等待类型（如Lock）
  blocked.wait_event                -- 等待事件（如transactionid）
FROM pg_stat_activity blocked
JOIN pg_locks l1 ON l1.pid = blocked.pid
JOIN pg_locks l2 ON l2.locktype = l1.locktype
  AND l2.DATABASE IS NOT DISTINCT FROM l1.DATABASE
  AND l2.relation IS NOT DISTINCT FROM l1.relation
  AND l2.page IS NOT DISTINCT FROM l1.page
  AND l2.tuple IS NOT DISTINCT FROM l1.tuple
  AND l2.virtualxid IS NOT DISTINCT FROM l1.virtualxid
  AND l2.transactionid IS NOT DISTINCT FROM l1.transactionid
  AND l2.classid IS NOT DISTINCT FROM l1.classid
  AND l2.objid IS NOT DISTINCT FROM l1.objid
  AND l2.objsubid IS NOT DISTINCT FROM l1.objsubid
  AND l2.pid <> l1.pid
JOIN pg_stat_activity blocking ON blocking.pid = l2.pid
WHERE NOT blocked.granted;          -- 仅显示未获得锁的进程

关键字段说明

• pid 进程ID，用于唯一标识一个数据库连接
• state 状态：active（执行中）、idle（空闲）、idle in transaction（事务中空闲）
• query 当前正在执行的 SQL 或最后一次执行的 SQL（若状态为 idle）
• wait_event_type 进程等待的事件类型（如 Lock、IO、Latch）
• wait_event 具体的等待事件名称（如 transactionid、RowLock）

注意事项

1、权限：需要具有 pg_read_all_stats 权限的用户（如超级用户）才能查看所有进程。

2、后台进程：pg_stat_activity 包含后台进程（如自动清理），可通过 backend_type 过滤。

3、谨慎终止进程：pg_terminate_backend 可能导致事务回滚或数据不一致，需确认后再操作。

4、版本差异：不同 PostgreSQL 版本的系统表字段可能略有差异，建议查阅对应版本文档。