作者：Digital Observer（施嘉伟）
Oracle  ACE Pro: Database
PostgreSQL  ACE Partner
11年数据库行业经验，现主要从事数据库服务工作
拥有Oracle  OCM、DB2 10.1 Fundamentals、MySQL 8.0 OCP、WebLogic 12c OCA、KCP、PCTP、PCSD、 PGCM、OCI、PolarDB技术专家、达梦师资认证、数据安全咨询高级等认证
ITPUB认证专家、PolarDB开源社区技术顾问、HaloDB技术顾问、TiDB社区技术布道师、青学会MOP技术社区专家顾问、国内某高校企业实践指导教师
公众号：Digital Observer；CSDN：施嘉伟；ITPUB：sjw1933；墨天轮：Digital Observer；PGFans：施嘉伟。

第一部分 SQL指标

1.1 会话执行情况

test=# select * from pg_stat_activity;

1.2 top sql

最耗时 SQL，单次调用最耗时 SQL TOP 5
test=# select userid::regrole, dbid, query from sys_stat_statements order by mean_exec_time desc limit 5;
总最耗时 SQL TOP 5
test=# select userid::regrole, dbid, query from sys_stat_statements order by total_exec_time desc limit 5;

1.3 日志文档
查看pg_log目录下csv文件。

第二部分 监控指标

2.1 查看数据库进程

$ ps aux | grep kingbase

2.2 检查数据库实例状态

$ ksql -p 54321 -U system test -c "select 1;"

2.3 检查数据库主备库状态

test=# select pg_is_in_recovery(); #f为主库，t为备库

2.4 主库检查备库连接情况

test=# select client_addr, pg_wal_lsn_diff(sent_lsn, write_lsn) as sent_lag, pg_wal_lsn_diff(sent_lsn, replay_lsn) as apply_lag, state, sync_state from pg_stat_replication;

2.5 磁盘容量

$ df -h

2.6 连接数

test=# select datname, usename, count(*) as connection_count from pg_stat_activity where usename is not null group by datname, usename;

2.7 查看数据库归档信息

test=# select * from pg_stat_archiver;

2.8 运行中长事务

test=# select * from pg_stat_activity where xact_start + interval '1 h' < now();

2.9 查看SQL的锁

test=# select blocked_locks.pid as blocked_pid,
blocked_activity.query as blocked_statement,
blocking_locks.pid as blocking_pid,
blocking_activity.query as blocking_statement
from pg_catalog.pg_locks as blocked_locks
join pg_catalog.pg_stat_activity as blocked_activity on blocked_activity.pid = blocked_locks.pid
join pg_catalog.pg_locks as blocking_locks on blocking_locks.locktype = blocked_locks.locktype
and blocking_locks.database is not distinct from blocked_locks.database
and blocking_locks.relation is not distinct from blocked_locks.relation
and blocking_locks.page is not distinct from blocked_locks.page
and blocking_locks.tuple is not distinct from blocked_locks.tuple
and blocking_locks.virtualxid is not distinct from blocked_locks.virtualxid
and blocking_locks.transactionid is not distinct from blocked_locks.transactionid
and blocking_locks.classid is not distinct from blocked_locks.classid
and blocking_locks.objid is not distinct from blocked_locks.objid
and blocking_locks.objsubid is not distinct from blocked_locks.objsubid
and blocking_locks.pid != blocked_locks.pid
join pg_catalog.pg_stat_activity as blocking_activity on blocking_activity.pid = blocking_locks.pid
where not blocked_locks.granted;

2.10 查看数据库的年龄

test=# select datname, age(datfrozenxid) as age from pg_database where datname not in ('template1','template0') order by age desc;

2.11 查看表的年龄

test=# select c.oid::regclass as relname,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
from pg_class c
left join pg_class t
on c.reltoastrelid = t.oid
where c.relkind in ('r', 'm')
and greatest(age(c.relfrozenxid),age(t.relfrozenxid)) > 500000
order by age desc;

第三部分 表膨胀处理

3.1 查看膨胀率

test=# select relname,
coalesce(round(n_dead_tup * 100 / (case when n_live_tup + n_dead_tup = 0 then null else n_live_tup + n_dead_tup end), 2), 0.00) as ratio
from pg_stat_all_tables
where n_dead_tup >= 10000
and coalesce(round(n_dead_tup * 100 / (case when n_live_tup + n_dead_tup = 0 then null else n_live_tup + n_dead_tup end), 2), 0.00) > 50
order by ratio desc;

3.2 手动清理

test=# vacuum full 表名;

第四部分 统计信息收集

4.1 手动收集

test=# analyze 表名;