常规监控

• 连接数使用超过95%告警，最大连接数和当前连接数

  select (select count(*) from pg_stat_activity) /(select setting from pg_settings where name = 'max_connections') ::numeric >0.95;

• 存在空闲连接idle in transaction大于5秒的连，如果长期存在这样的连接说明应用有问题，并且会造成数据库出现异常

 select count(*) from pg_stat_activity where state = 'idle in transaction' and now()-query_start>'5s'::interval);

• 长事务，超过30分钟的慢sql

 select count(*) from pg_stat_activity where state <> 'idle' and (backend_xid is not null or backend_xmin is not null) and now()-xact_start > interval '1800 sec'::interval;

• 数据库占用空间

 select pg_size_pretty(pg_database_size('postgres'));
 select datname, pg_size_pretty(pg_database_size(datname)) from pg_database;

• 数据库中存在长时间阻塞的锁，阻塞超过3分钟

 select
  kl.pid as 阻塞pid,
 a.usename as blocked_user,
 ka.query as 阻塞sql,
 ka.state as 阻塞sql状态,
 now()- ka.query_start as 阻塞语句执行时长,--阻塞的语句执行时长
  bl.pid as 被阻塞pid,
 ka.usename as 被阻塞用户,
 a.query as 被阻塞sql,
 now()- a.query_start as 被阻塞语句执行时长--被阻塞语句执行时长
 from
 pg_locks bl
 join pg_stat_activity a on
 a.pid = bl.pid
 join pg_locks kl on
 kl.transactionid = bl.transactionid
 and kl.pid != bl.pid
 join pg_stat_activity ka on
 ka.pid = kl.pid
 where
 not bl.granted
  and now()- ka.query_start >'60s'::interval;--阻塞时长超过1分钟
 
 
  --阻塞pid对应的是数据库中pid，可以使用select pg_terminate_backend(pid);进行kill

• 坏元组超过10000行，且占比超过20%的表

 select current_database(),
  schemaname||'.'||relname,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup*100/ (n_live_tup )::numeric,2)||'%' AS dead_tup_ratio
  FROM
  pg_stat_all_tables
  WHERE
  n_dead_tup >= 10000 and n_live_tup>0 and (n_dead_tup)/(n_live_tup)::numeric>0.25 and schemaname not in ('pg_toast','pg_catalog','information_schema')
  ORDER BY dead_tup_ratio DESC
  LIMIT 10;
 

• wal日志生成量

 --'AD/FAFFF2A8'是5分钟前select pg_current_wal_lsn()获取的值
 select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),'AD/FAFFF2A8'));

• 表缓存命中率和索引缓存命中率，命中率低于80%说明缓存不足

• 注意，不常用的库缓存可能为0

 SELECT'index hit rate' AS name,
 (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio
 FROM pg_statio_user_indexes
 UNION ALL
 SELECT
 'table hit rate' AS name,
 sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio
 FROM pg_statio_user_tables;

• 数据库年龄监控

• 当age达到2亿会触发自动清理，如果大于5亿则告警

 select datname,age(datfrozenxid),pg_size_pretty(pg_database_size(oid)) from pg_database where age(datfrozenxid) > 500000000 order by age(datfrozenxid) desc limit 10 ;

• 表年龄（用于查看详情，默认数据库年龄会存储表年龄最大的一条）

 select sp.nspname,relname,age(relfrozenxid), pg_size_pretty(pg_table_size(cl.oid)) from pg_class cl join pg_namespace sp on cl.relnamespace = sp.oid where relkind in ('t','r') order by age(relfrozenxid) desc limit 10;
 
 -- 说明：当age到达2亿（默认）时触发自动清理，期间会大量占用系统资源。提前做好监控避免在业务高峰时发生。可在库级别操作，也可在表基本操作。

• 事务提交，事务回滚,全表扫描，索引扫描，插入记录，更新记录

 select sum(xact_commit),sum(xact_rollback),sum(tup_returned),sum(tup_fetched),sum(tup_inserted),sum(tup_updated),sum(tup_deleted),sum(conflicts),sum(deadlocks) from pg_stat_database;
 -- pg_stat_get_db_xact_commit 为stable函数，一个事务中两次调用之间只执行一次，所以需要外部多次执行。

回滚率计算：select xact_rollback/(xact_commit+xact_rollback)，如果超过10%则代表回滚率较高

• 过去5分钟内生成wal个数，可以判断当前数据库的dml频繁程度

 select count(1) from pg_catalog.pg_ls_waldir() where modification > CURRENT_TIMESTAMP - '5 minutes' :: INTERVAL ;

• wal写入速率

• lsn是一个递增的值

 SELECT CASE WHEN pg_is_in_recovery() THEN pg_last_wal_replay_lsn() ELSE pg_current_wal_lsn() END - '0/0' as wal_lsn;

高可用，逻辑复制

• 现场使用arrs或者高可用，出现异常后并不知情，可监控复制状态，以及日志堆积大小判断当前复制情况

pg10+

• 复制状态，true表示正常，false表示异常

  select active from pg_replication_slots;

• 备库applay延迟，以及堆积情况

 select
 application_name,client_addr,client_hostname,client_port,state,sync_priority,sync_state,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn)),
 pg_wal_lsn_diff(pg_current_wal_lsn(),write_lsn) write_delay,
 pg_wal_lsn_diff(pg_current_wal_lsn(),flush_lsn) flush_delay,
 pg_wal_lsn_diff(pg_current_wal_lsn(),replay_lsn) replay_dely
  from pg_stat_replication ;

• arrs复制延迟，以及堆积情况

  select slot_name, plugin, slot_type,
 temporary, active, active_pid,
 pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) from
 pg_replication_slots ;

pg10以下

• 备库applay延迟，复制堆积超过1GB

 select
 application_name,client_addr,client_hostname,client_port,state,sync_priority,
 sync_state,pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(),replay_location)),
 pg_xlog_location_diff(pg_current_xlog_location(),write_location) write_delay,
 pg_xlog_location_diff(pg_current_xlog_location(),flush_location) flush_delay,
 pg_xlog_location_diff(pg_current_xlog_location(),replay_location) replay_dely
  from pg_stat_replication;
  select slot_name, plugin, slot_type,
  active, active_pid,
 pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn)) from
 pg_replication_slots;

• 主从流复制延时时间 （从库执行）

 SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END
 10 版本及以后
 SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END;
 -- 主从复制延迟字节 (主库执行)
 select greatest(0,pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) from pg_stat_replication ; where client_addr = '从库

慢SQL监控

• 数据库需要安装插件pg_stat_statements

• 在数据库中当前频繁执行的SQL TOP5

 SELECT C.rolname,
  b.datname,
  A.total_time / A.calls per_call_time,
  A.*
 FROM
  pg_stat_statements A,
  pg_database b,
  pg_authid C
 WHERE
  A.userid = C.oid
  AND A.dbid = b.oid
 ORDER BY
  A.total_time DESC
  LIMIT 5

• 最耗时 SQL，单次调用最耗时 SQL TOP5

 select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 5;

• 最耗共享内存 SQL

 select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 5;

• 总最耗IO SQL TOP5

 select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 5;

• 最耗IO SQL，单次调用最耗IO SQL TOP5

 select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 5;

• QPS

  with
  a as(select sum(calls)s from pg_stat_statements),
  b as(select sum(calls)s from pg_stat_statements,pg_sleep(1))
  select b.s-a.s --QPS
  from a,b;

• 长期（30天）未更新统计信息的表，坏元组比例超过10%就应该触发更新统计信息(警告)

 
 SELECT
  to_char((now() - last_analyze), 'DD'), to_char((now() - last_autoanalyze),'DD' ) ,
 current_database(),
  schemaname||'.'||relname,n_live_tup,n_dead_tup,
  round(n_dead_tup*100/n_live_tup::numeric,2)||'%' as dead_tup_ratio
 FROM
  pg_stat_all_tables
 WHERE
  schemaname not in( 'pg_toast','pg_catalog','information_schema' )
 AND (last_analyze IS null OR to_char((now() - last_analyze), 'DD') :: NUMERIC > 30)
 AND (last_autovacuum IS null OR to_char((now() - last_autovacuum),'DD') :: NUMERIC > 30)
 AND (last_autoanalyze IS null OR to_char((now() - last_autoanalyze),'DD' ) :: NUMERIC > 30)
 and n_dead_tup >= 10000 and n_live_tup>0 and (n_dead_tup)/(n_live_tup)::numeric > 0.1;

• 未使用的索引

 SELECT
 schemaname || '.' || relname AS table,
 indexrelname AS index,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
 idx_scan as index_scans
 FROM pg_stat_user_indexes ui
 JOIN pg_index i ON ui.indexrelid = i.indexrelid
 WHERE NOT indisunique
 AND idx_scan < 5
 AND pg_relation_size(relid) > 5 * 8192
 ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
 pg_relation_size(i.indexrelid) DESC;
 
--修改后：

 SELECT
 schemaname || '.' || relname AS table,
 indexrelname AS index,
 pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,pg_size_pretty(pg_relation_size(relid)),
 idx_scan as index_scans
 FROM pg_stat_user_indexes ui
 JOIN pg_index i ON ui.indexrelid = i.indexrelid
 WHERE NOT indisunique
 AND idx_scan < 5
 AND pg_relation_size(relid) > 5 * 8192*1000
 ORDER BY pg_relation_size(i.indexrelid) desc limit 5;

• 表未创建主键

 SELECT
 current_database (),ns.nspname,obj.relname
 FROM
 pg_class obj,pg_namespace ns
 WHERE
 obj.relnamespace = ns.oid
 AND obj.relkind = 'r'
 AND ns.nspname NOT IN ('pg_toast','pg_catalog','information_schema')
 AND obj.oid NOT IN (SELECTobj.oid FROMpg_class obj,pg_index idxWHEREobj.oid = idx.indrelidAND idx.indisprimary = 'true')
 limit 10;

• 数据库中存在触发器

 SELECT
 current_database (),
 ns.nspname,
 rel.relname,
 tri.tgname
 FROM
 pg_class rel,
 pg_namespace ns,
 pg_trigger tri
 WHERE
 ns.nspname NOT IN ('pg_toast','pg_catalog','information_schema')
 AND rel.oid = tri.tgrelid
 AND rel.relnamespace = ns.oid
 AND rel.relhastriggers = true and tgisinternal= = false;

• 上班期间存在备份进程

 select st.application_name,st.datname,st.query_start,st.client_addr,client_hostname,query from pg_stat_activity st where state='active' and application_name='pg_dump';

• 上班期间存在创建索引进程

 select st.application_name,st.datname,st.query_start,st.client_addr,client_hostname
 from pg_stat_activity st where state='active' and (st.query ilike 'create index%' or st.query ilike 'create uniuqe index%');

• 上班期间重建索引

 select st.application_name,st.datname,st.query_start,st.client_addr,client_hostname from pg_stat_activity st where state='active' and query ilike 'reindex%';
 

• vacuum full进程

 select st.application_name,st.datname,st.query_start,st.client_addr,client_hostname,query from pg_stat_activity st where state='active' and query ilike 'vacuum full%';

• 重复索引

 select allindx.schemaname,allindx.relname,allindx.indexrelname,ind.indexdef FROM pg_stat_all_indexes allindx,pg_indexes ind where allindx.relname=ind.tablename and allindx.indexrelname=ind.indexname and allindx.indexrelid :: VARCHAR
 IN (SELECT regexp_split_to_table(indexs, $$,$$)
 FROM(SELECT string_agg (idx :: VARCHAR, $$,$$) AS indexs
 FROM(
 SELECT indexrelid :: oid AS idx,
 (indrelid :: TEXT || $$BB$$ || indclass :: TEXT || $$BB$$ || indkey :: TEXT || $$BB$$ || COALESCE (indexprs :: TEXT, $$BB$$) || $$BB$$ || COALESCE (indpred :: TEXT, $$BB$$)) AS val FROM pg_index ) sub
 GROUP BYval HAVINGCOUNT (*) > 1
 ORDER BY SUM (pg_relation_size(idx)) desc
 ) arr) and allindx.schemaname not in ('pg_toast','pg_catalog');