PostgreSQL 常用监控指标

在PostgreSQL中，监控关键性能指标可以帮助你识别和解决性能问题。以下是一些常用的监控指标：

1. 数据库大小：使用pg_database_size()函数获取当前数据库的大小。
2. 连接数：使用pg_stat_activity视图来查看当前连接数和活动连接数。
3. 查询执行时间：使用pg_stat_statements扩展来监控查询的执行时间。
4. 缓存命中率：通过pg_statio_user_tables视图可以查看缓存的命中和缺失情况。
5. 锁等待：使用pg_stat_activity视图来查看哪些查询正在等待锁。
6. 表的I/O统计：使用pg_stat_user_tables视图来查看表的I/O活动。
7. 表空间使用情况：使用pg_tablespace_size()函数查看各表空间的使用情况。
8. 超时配置：监控配置参数lock_timeout和idle_in_transaction_session_timeout来管理长事务和锁等待。

以下是一些SQL查询示例，用于检查上述指标：

-- 查询数据库大小
SELECT pg_size_pretty(pg_database_size('your_database_name'));
 
-- 查询当前连接数和活动连接数
SELECT COUNT(*) AS total_connections,
       COUNT(CASE WHEN state = 'active' THEN 1 END) AS active_connections
FROM pg_stat_activity;
 
-- 查询查询的执行时间
-- 需要启用pg_stat_statements扩展
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
 
-- 查询缓存命中率
SELECT relname, heap_blks_read, heap_blks_hit, 100.0 * heap_blks_hit / (heap_blks_hit + heap_blks_read) AS cache_hit_percent
FROM pg_statio_user_tables
ORDER BY heap_blks_read + heap_blks_hit DESC
LIMIT 10;
 
-- 查询锁等待
SELECT pid, usename, datname, query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state = 'active' AND wait_event IS NOT NULL;
 
-- 查询表的I/O统计
SELECT relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins + n_tup_upd + n_tup_del DESC
LIMIT 10;
 
-- 查询表空间使用情况
SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM pg_tablespace;
 
-- 查询超时配置
SHOW lock_timeout;
SHOW idle_in_transaction_session_timeout;

这些查询可以帮助你了解数据库的当前状态，并且可以根据需要调整配置或优化查询来提高性能。