[PostgreSQL Admin] Monitoring

모니터링

 

Postgresql 은 시스템 카탈로그를 통해서 제공 합니다. 대부분 테이블이거나 뷰인데, 이는 결국 데이터베이스의 모니터링하는데 큰 도움이 됩니다.

 

SELECT datname, procpid, usename, client_addr, client_port, waiting, current_query FROM pg_stat_activity

 

위 쿼리는 많은 정보를 보여줍니다. 클라이언트 주소(client_addr), 클라이언트 포트(client_port), 현재 쿼리(current_query), 데이터베이스(datname), 프로세스 아이디(procpid) 입니다. current_query 는 반드시 쿼리문이 오지는 않습니다. 예를들면 클라이언트의 프로세스가 IDLE 상태라면 '' 이라고 나옵니다. 뭔가 실행중이라면 쿼리문이 적혀있게 됩니다.

 

SELECT datname, procpid, usename, client_addr, client_port, waiting, current_query FROM pg_stat_activity WHERE current_query != ''

 

위의 쿼리는 현재 활성화 되어 있는 쿼리문들을 보여줍니다.

 

SELECT current_timestamp - query_start AS runtime, datname, usename, current_query FROM pg_stat_activity WHERE current_query != '' ORDER BY 1 DESC;

 

위의 쿼리는 실행시간이 오래된 순으로 정렬해서 보여줍니다.

 

SELECT datname,usename,current_query FROM pg_stat_activity WHERE waiting = true;
SELECT datname,usename,current_query FROM pg_stat_activity WHERE waiting;
SELECT current_timestamp - query_start AS runtime, datname,usename,current_query FROM pg_stat_activity WHERE waiting;

 

위의 쿼리는 클라이언트가 처리하는 상태가 waiting 인 것을 보여줍니다. waiting 상태는 시스템이 프로세스에 대해서 lock 걸거나 Postgresql 내부에서 lock 을 걸었을때 발생합니다.

 

SELECT w.current_query as waiting_query, w.procpid as w_pid, w.usename as w_user, l.current_query as locking_query, l.procpid as l_pid, l.usename as l_user, t.schemaname || '.' || t.relname as tablename from pg_stat_activity w join pg_locks l1 on w.procpid = l1.pid and not l1.granted join pg_locks l2 on l1.relation = l2.relation and l2.granted join pg_stat_activity l on l2.pid = l.procpid join pg_stat_user_tables t on l1.relation = t.relid where w.waiting;

 

위 쿼리는 락(lock)이 걸린 쿼리는 무엇인지를 알려줍니다.

 

"Idle in transaction" 상태는 트랜잭션을 시작하고 난후 커밋(commit)을 하지 않고 접속을 끊거나 어떤 이유로 인해서 커밋이 안되었을때 주로 발생합니다. 이를 그대로 두면 리소스를 계속 잡아먹기 때문에 시스템 전체에 영향을 줄 수 있습니다. 이를 찾고 쿼리 프로세스를 종료 시키는 쿼리는 다음과 같습니다.

 

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query = ' in transaction' AND current_timestamp - query_start > '10 min';

 

10 이상 Idle in transaction 상태의 쿼리를 찾아 pg_terminate_backend를 이용해 프로세스를 종료 시켜줍니다.

 

출처 : http://histlinux.egloos.com/viewer/1227718

by. 백준