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

저작자표시비영리동일조건

• 카카오스토리
• 트위터
• 페이스북

'개발관련 > Postgresql' 카테고리의 다른 글

PostgreSQL 장애 체크  (0)	2012.12.13
PostgreSQL 해당 국가 타임존으로 시간 비교  (0)	2012.12.13
PostgreSQL 날짜 연산  (0)	2012.12.13
postgresSQL Locking 테이블 찾기  (0)	2012.12.13
postgresql 필드안에 빈줄삭제  (0)	2012.12.13
Postgres 아이피 차단/해제  (0)	2011.06.15