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