[PostgreSQL] 지금 서버에서 뭐가 돌고 뭐가 멈춰 있나 — pg_stat_activity

설치·접속: PostgreSQL 설치와 접속

부제: 갑자기 느려졌을 때 제일 먼저 여는 뷰 — 실행 중인 쿼리와 '트랜잭션 열어놓고 방치된' 세션을 잡아낸다

SELECT pid, state, wait_event_type AS wait_type, wait_event,
       date_trunc('milliseconds', now()-state_change) AS in_state,
       left(query,42) AS query
FROM pg_stat_activity
WHERE backend_type='client backend' AND pid <> pg_backend_pid()
ORDER BY state DESC;

-[ RECORD 1 ]------------------------------------------
pid        | 10144
state      | idle in transaction
wait_type  | Client
wait_event | ClientRead
in_state   | 00:00:03.039
query      | UPDATE users SET name=name WHERE id=1;
-[ RECORD 2 ]------------------------------------------
pid        | 10145
state      | active
wait_type  |
wait_event |
in_state   | 00:00:03.038
query      | SELECT count(*) FROM m5_orders CROSS JOIN

두 세션의 성격이 완전히 다르다. RECORD 2는 state=active에 wait_event가 비어 있으니 지금 CPU를 쓰며 실제로 돌고 있는 쿼리다. 문제는 RECORD 1이다. idle in transaction은 애플리케이션이 BEGIN으로 트랜잭션을 열고 UPDATE까지 해놓고는 커밋도 롤백도 안 한 채 3초째 방치 중이라는 뜻이다(wait_event=ClientRead = 클라이언트의 다음 명령을 기다리는 중). 쿼리는 안 돌지만 그 UPDATE가 잡은 락은 계속 붙들고 있어서 다른 세션을 막고 VACUUM도 방해한다. 커넥션 풀 설정 실수나 애플리케이션 버그로 흔히 생기는, 조용히 서버를 갉아먹는 상태다.

query는 그 세션이 마지막으로 실행한 SQL이라 active가 아니면 과거 쿼리라는 점, state_change로 얼마나 오래 그 상태였는지 재는 게 핵심이다.

이렇게도 쓴다

5분 넘게 도는 장기 쿼리만 골라낸다. (조합: interval 필터)

SELECT pid, now()-query_start AS runtime, query FROM pg_stat_activity
WHERE state='active' AND now()-query_start > interval '5 min'
ORDER BY runtime DESC;

 

오래 방치된 idle in transaction 세션을 잡아낸다. (조합: state 필터)

SELECT pid, now()-state_change AS idle_for, query FROM pg_stat_activity
WHERE state='idle in transaction' AND now()-state_change > interval '1 min';

 

문제 쿼리를 부드럽게 취소한다. (트랜잭션은 살려둠, 조합: pg_cancel_backend)

SELECT pg_cancel_backend(10145);

 

말을 안 들으면 세션을 강제 종료한다. (조합: pg_terminate_backend)

SELECT pg_terminate_backend(10144);

 

무엇을 기다리는지 대기 이벤트별로 집계한다. (조합: GROUP BY)

SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity
WHERE state='active' GROUP BY 1,2 ORDER BY count DESC;

 

앱·유저·클라이언트 IP까지 붙여 누가 접속했는지 본다. (조합: 접속 정보 컬럼)

SELECT pid, usename, application_name, client_addr, state FROM pg_stat_activity
WHERE backend_type='client backend';