PostgreSQL 장애 체크
개발관련/Postgresql2012. 12. 13. 10:45
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 |