监控 I/O

1. 判断表示顺序扫描还是索引扫描

select schemaname, relname,seq_scan, idx_scan, cast(idx_scan as numeric) /(idx_scan + seq_scan) as idx_scan_pct
from pg_stat_user_tables where (idx_scan + seq_scan) > 0
order by idx_scan_pct desc

顺序扫描通常意味着较差的性能，如果大部分表都是顺序扫描，意味着数据库缺少索引或者索引没有没使用。缺少索引可以新建索引，索引没有被使用有可能是因为数据库统计信息不及时。

2. 判断HOT的频率
select relname, n_tup_upd, n_tup_hot_upd, cast(n_tup_hot_upd as numeric)/(n_tup_upd + n_tup_hot_upd) as hot_pct
from pg_stat_user_tables 
where (n_tup_upd + n_tup_hot_upd) > 0
order by hot_pct desc

通常情况下，所有update都应该通过HOT, 这样Update能得到高性能。 如果HOT比例低，Update的性能不佳，数据库的性能就会不佳。

3. 查看表的insert, update 和delete的频率 

select relname, n_tup_ins, n_tup_upd, n_tup_del, cast(n_tup_ins as numeric)/(n_tup_ins + n_tup_upd + n_tup_del) as ins_pct,
                                                 cast(n_tup_upd as numeric)/(n_tup_ins + n_tup_upd + n_tup_del) as upd_pct,
                                                 cast(n_tup_del as numeric) /( n_tup_ins + n_tup_upd + n_tup_del) as del_pct
from pg_stat_user_tables where (n_tup_ins + n_tup_upd + n_tup_del) > 0

4 .  查看锁的依赖关系
select locktype,virtualtransaction, transactionid, nspname,relname, mode, granted, 
cast(date_trunc('second',query_start) as timestamp) as query_start,
substr(query,1,25) as query
from pg_locks
left outer join pg_class on pg_locks.relation = pg_class.oid
left outer join pg_namespace on pg_namespace.oid = pg_class.relnamespace, 
pg_stat_activity
where not pg_locks.pid = pg_backend_pid()
and pg_locks.pid = pg_stat_activity.pid;

5.  查看HOT频率和update频率的对比关系

select relname, cast(n_tup_hot_upd as numeric)/(n_tup_upd + n_tup_hot_upd) as hot_pct,  
                cast(n_tup_upd as numeric)/(n_tup_ins + n_tup_upd + n_tup_del) as upd_pct
from pg_stat_user_tables 
where (n_tup_upd + n_tup_hot_upd) > 0
and (n_tup_ins + n_tup_upd + n_tup_del) > 0
order by upd_pct desc

如果更新频率高但是HOT比例低，update有可能是数据库的瓶颈，应该调查

6.  监控物理I/O

61.  表的缓存查询与分析

select relname, cast(heap_blks_hit as numeric)/(heap_blks_hit + heap_blks_read) as hit_pct, heap_blks_hit, heap_blks_read
from pg_statio_user_tables
where (heap_blks_hit + heap_blks_read) > 0
order by hit_pct desc

heap(堆) 是指shared_buffers， 以8k为最小的存储单位。经常使用的表的数据，heap读取的比例越高越好，反之数据库的性能可能有问题。 

6.2 索引的缓存查询与分析

6.2.1 查询pg_statio_user_tables

select relname, cast(idx_blks_hit as numeric)/(idx_blks_hit + idx_blks_read) hit_pct, idx_blks_hit, idx_blks_read
from pg_statio_user_tables
where (idx_blks_hit + idx_blks_read) > 0
order by hit_pct desc6

6.2.2 查询pg_statio_user_indexes

select relname, cast(idx_blks_hit as numeric)/(idx_blks_hit + idx_blks_read) as idx_hit_pct, idx_blks_hit, idx_blks_read
from pg_statio_user_indexes
where (idx_blks_hit + idx_blks_read) > 0
order by idx_hit_pct desc

经常使用的索引应该是尽可能多的从缓存读取。如果缓存大小不够，可是增大缓 -  shared_buffers。"show shared_buffers"显示当前数据库的缓存大小。

6.3  索引的平均返回行数

select relname, cast(idx_tup_read as numeric)/idx_scan as avg_tuples, idx_scan, idx_tup_read
from pg_stat_user_indexes
where idx_scan > 0

如果avg_tuples略大于1，说明偶发的死行被扫描。avg_tuples可以说明索引范围扫描被使用的频率。pg_stat_user_indexes可以判断索引的使用情况。鉴于索引的开销，如果索引几乎不使用，则应该被删除。索引的开销包括，insert/update/delete操作更新索引，索引的存储，rebuild索引。

6.4 监控缓存，后台进程及检查点

select 
     (100 * checkpoints_req)/(checkpoints_timed + checkpoints_req) as avg_ckpoint_req_pct,
     pg_size_pretty(cast(buffers_checkpoint * block_size as numeric)/(checkpoints_timed + checkpoints_req)) as avg_ckpoint_write,
     pg_size_pretty(block_size * (buffers_checkpoint + buffers_clean + buffers_backend)) as total_write,
     100 * buffers_checkpoint/(buffers_checkpoint + buffers_clean + buffers_backend) as checkpoint_pct,
     100 * buffers_backend /(buffers_checkpoint + buffers_clean + buffers_backend) as backend_pct
from pg_stat_bgwriter, (select cast(current_setting('block_size') as integer) as block_size) as BS