PostgreSQL 장애 체크

admin ( postgres ) 로 접속

select * from pg_stat_activity;

-> 현재 활성화되는 쿼리들

select pg_cancel_backend(인자);

-> select * from pg_stat_activity; 에서 나오는 procpid 값이 인자임.

# select 
     'select pg_cancel_backend('||procpid ||');' 
   from 
     pg_stat_activity 
   where client_addr is null;

함수찾기 :

# \df *cancel*

테이블 찾기 :

# \dt *cancel*

#1 모니터링 쿼리

select 
     relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_live_tup 
from 
     pg_stat_user_tables 
where 
     schemaname = 'public' 
order by seq_scan 
desc limit 100;

#2 모니터링 쿼리

select
	s.relname,
	s.seq_tup_read + s.idx_tup_fetch usage,
	(s.idx_tup_fetch/s.seq_tup_read::float)::decimal(18,4) index_ratio,
	s.seq_tup_read, s.idx_tup_fetch,
	s.seq_scan, s.n_live_tup,
	((io.heap_blks_hit + io.idx_blks_hit)/
        ((io.heap_blks_read + io.idx_blks_read)::float))::decimal(18,4) cache_hit_ratio
FROM pg_stat_user_tables s
INNER JOIN pg_statio_user_tables io
	ON s.relid = io.relid
WHERE s.seq_tup_read + s.idx_tup_fetch > 0
	AND s.n_live_tup > 5000 
	AND io.heap_blks_read + io.idx_blks_read  > 0
	AND s.seq_tup_read > 0
ORDER BY
	usage desc,
	cache_hit_ratio desc,
	index_ratio asc, s.seq_scan desc,
	s.n_live_tup desc

--주기적인 pg_stst_activity; 명령어
-- 아이피별 접속 카운트

    watch  "psql -U postgres -c 'select client_addr,count(*) from pg_stat_activity group by client_addr order by 2 desc;'"

--유저별 접속 카운트

watch  "psql -U postgres -c 'select usename,count(*) from pg_stat_activity group by usename order by 2 desc;'"

--Postgresl shell 에서

postgres# watch  "psql -U postgres -c 'SELECT datname,procpid,current_query FROM
> pg_stat_activity;'|grep -v IDLE|grep -v 'SELECT
> datname,procpid,current_query FROM pg_stat_activity;' | grep -v ^\( |
> grep -v 'datname  | procpid ' | grep -v -- '-----------+---------+-' "

--2번재 procpid 나오도록

watch  "psql -U postgres -c 'SELECT datname,procpid,current_query FROM pg_stat_activity;'|grep -v IDLE|
grep -v 'SELECT datname,procpid,current_query FROM pg_stat_activity;' "

참조 : http://people.planetpostgresql.org/kapple/index.php?/archives/1-PostgreSQL-Index-Tuning-Part-1.html