PostgreSQL 数据库监控常用命令

--最大连接数
 select
	setting::int8 as max_conn
from
	pg_settings
where
	name = 'max_connections';
--当前连接数
 select
	count(*) as now_conn_cnt
from
	pg_stat_activity;
--各个连接用户的统计
--usename:用户名
--user_conn_cnt: 各个连接用户的统计
 select
	usename,
	count(*) as user_conn_cnt
from
	pg_stat_activity
where
	usename in (
	select
		distinct usename
	from
		pg_stat_activity)
group by
	usename
order by
	user_conn_cnt desc;
--检索数据库的所有者、大小情况
--datname：数据库名
--rolname：用户名
--"size(MB)"：数据库大小
 select
	a.datname,
	b.rolname,
	pg_database_size(a.datname)/ 1024 / 1024 as "size(MB)"
from
	pg_database a,
	pg_authid b
where
	a.datdba = b.oid
order by
	"size(MB)" desc;
--数据库名称
 select
	datname
from
	pg_database;
--表的大小top10
--relname：表名
--rolname：用户名
--"size(MB)"：表大小
 select
	t1.relname,
	t2.rolname,
	pg_relation_size(t1.oid)/ 1024 / 1024.0 as "size(MB)"
from
	pg_class t1,
	pg_authid t2
where
	relkind = 'r'
	and t1.relowner = t2.oid
order by
	"size(MB)" desc
limit 10;
--表空间以及大小情况
--spcname：表空间
--size：表空间的当前大小
 select
	spcname,
	pg_tablespace_location(oid),
	round(pg_tablespace_size(oid)/ 1024 / 1024 / 1024.0, 2)|| 'GB' as size
from
	pg_tablespace
order by
	pg_tablespace_size(oid) desc;
--垃圾数据检查
--schemaname：模式名
--relname：表名
--n_live_tup：活着的行的估计数量
--n_dead_tup：死亡行的估计数量(逻辑删除的数据，但是还没有被回收)
 select
	schemaname,
	relname,
	n_live_tup,
	n_dead_tup
from
	pg_stat_all_tables
where
	n_live_tup>0
	and n_dead_tup*1.0 / n_live_tup>0.2
	and schemaname not in ('pg_toast', 'pg_catalog')
	and n_live_tup>100000
	or n_dead_tup>100000;
--回滚与命中比率
--datname：数据库名
--rollback_ratio：回滚率
--hit_ratio：命中率
--说明：命中率越高，查询结果返回的值越快，在之前服务器内存很小的时候这个参数比较有意义，现在服务器内存都很大，这个参数没有很大的借鉴意义，并不能体现数据库性能
 select
	datname,
	xact_rollback::numeric /(
	case
		when xact_commit > 0 then xact_commit
		else 1
	end + xact_rollback ) as rollback_ratio,
	blks_hit::numeric /(
	case
		when blks_read>0 then blks_read
		else 1
	end + blks_hit ) as hit_ratio
from
	pg_stat_database;
--锁数量
--“数据库锁.txt”中是一个检索锁表的sql预计
 select
	count(*)
from
	pg_locks;
select
	waiting.locktype as waiting_locktype,
	--可锁定对象的类型：relation, extend, page, tuple,transactionid, virtualxid,object, userlock, advisory
 waiting.relation::regclass as waiting_table,
	--等待表
 waiting_stm.query as waiting_query,
	--等待查询
 waiting.mode as waiting_mode,
	--这个进程持有的或者是期望持有的锁模式
 waiting.pid as waiting_pid,
	--持有或者等待这个锁的服务器进程的进程ID ，如果锁是被一个预备事务持有的，那么为空
 other.locktype as previous_locktype,
	--当前锁的上层锁
 other.relation::regclass as previous_table,
	other_stm.query as previous_query,
	other_stm.state as previous_state,
	other.mode as Previous_mode,
	other.pid as previous_pid,
	--等待该pid完成（kill）
 other.GRANTED as previous_granted
	--如果持有锁，为真，如果等待锁，为假
	from pg_catalog.pg_locks as waiting
join pg_catalog.pg_stat_activity as waiting_stm on
	( waiting_stm.pid = waiting.pid )
join pg_catalog.pg_locks as other on
	( ( waiting."database" = other."database"
	and waiting.relation = other.relation )
	or waiting.transactionid = other.transactionid )
join pg_catalog.pg_stat_activity as other_stm on
	( other_stm.pid = other.pid )
where
	not waiting.GRANTED
	and waiting.pid <> other.pid;
--阻塞锁
 select
	count(*)
from
	pg_locks
where
	granted = 'f';
--慢SQL(ms)   
--usename：用户名
--datname：数据库名
--pid：后端进程ID
--query：慢sql语句
 select
	usename,
	datname,
	query,
	pid
from
	pg_stat_activity
where
	now()-xact_start>interval '5 sec'
	and query !~ '^COPY'
	and STATE <> 'idle'
order by
	xact_start;
--数据库年龄（>15亿）
--datname：数据库名
--age(datfrozenxid)：数据库年龄
--说明：随着数据库的增删改查等事务活动，表的年龄会越来越大，超过限制值时，需要执行vacuum，以此来降低数据库表的年龄。  
 select
	datname,
	age(datfrozenxid)
from
	pg_database
where
	age(datfrozenxid)>1500000000
order by
	2 desc;
--大表年龄（>9亿 and >10GB）
--relname：表名
--age(relfrozenxid)：表的年龄
--"size(GB)"：表的大小
--说明：数据表的年龄是指产生事务时的事务号离当前事务号的距离，随着数据库的增删改查等事务活动，表的年龄会越来越大，超过限制值时，需要执行vacuum，以此来降低数据库表的年龄。  
 select
	relname,
	age(relfrozenxid),
	pg_relation_size(oid)/ 1024 / 1024 / 1024.0 as "size(GB)"
from
	pg_class
where
	relkind = 'r'
	and age(relfrozenxid)>900000000
	and pg_relation_size(oid)/ 1024 / 1024 / 1024.0 > 1
order by
	3 desc;
--索引大小top10
--relname：表名
--rolname：用户名
--"size(MB)"：索引大小  
 select
	t1.relname,
	t2.rolname,
	pg_relation_size(t1.oid)/ 1024 / 1024.0 as "size(MB)"
from
	pg_class t1,
	pg_authid t2
where
	relkind = 'i'
	and t1.relowner = t2.oid
order by
	"size(MB)" desc
limit 10;
--当前空闲数
 select
	count(*)
from
	pg_stat_activity
where
	state = 'idle';
--当前活跃数
 select
	count(*)
from
	pg_stat_activity
where
	state = 'active';
--事务提交总量
 select
	sum(xact_commit) as xact_commit
from
	pg_stat_database;
--事务回滚总量
 select
	sum(xact_rollback) as xact_rollback
from
	pg_stat_database;
--物理读取块数
 select
	sum(blks_read) as blks_read
from
	pg_stat_database;
--缓存读取块数
 select
	sum(blks_hit) as blks_hit
from
	pg_stat_database;
--死锁的数量
 select
	sum(deadlocks) as deadlocks
from
	pg_stat_database;
--数据读取时间  
 select
	sum(blk_read_time) as blk_read_time
from
	pg_stat_database;
--数据库大小  
--用户名
--数据库名 
--数据库大小
 select
	b.rolname as username,
	a.datname as dbname,
	round(pg_database_size (a.datname)/ 1024 / 1024.0, 2) || 'MB' as dbsize
from
	pg_database a,
	pg_authid b
where
	a.datdba = b.oid
--数据库表个数
--模式名
--表个数
--排除分区
--分区的查询
--分区所在的模式
--分区名称
 select
	a.schemaname,
	count(a.tablename) as tablecnt
from
	pg_tables a
where
	not exists (
	select
		1
	from
		(
		select
			nmsp_child.nspname as child_schema,
			child.relname as child
		from
			pg_inherits
		join pg_class parent on
			pg_inherits.inhparent = parent.oid
		join pg_class child on
			pg_inherits.inhrelid = child.oid
		join pg_namespace nmsp_parent on
			nmsp_parent.oid = parent.relnamespace
		join pg_namespace nmsp_child on
			nmsp_child.oid = child.relnamespace ) b
	where
		b.child_schema = a.schemaname
		and b.child = a.tablename )
group by
	a.schemaname
order by
	a.schemaname