6.1 Connection/Transaction/Lock

Connection이 많고 lock이 많다는 사실만으로 장애는 아닙니다. 사용 가능한 connection이 줄어드는지, transaction이 끝나지 않는지, 실제로 서로를 block하는 session이 있는지를 순서대로 확인합니다.

1. Connection 포화

sum by (instance) (
  pg_stat_database_numbackends{datname!~"template.*"}
)

현재 한도는 PostgreSQL에서 확인합니다.

SELECT current_setting('max_connections')::int AS max_connections,
       current_setting('superuser_reserved_connections')::int AS reserved;

전체 backend가 한도에 가깝지 않아도 application pool 하나가 고갈되기도 합니다. 다음 데이터를 함께 봅니다.

• application별 connection 수
• pool active/idle/pending
• connection acquire 시간과 timeout
• PostgreSQL connection 생성 rate
• session state 분포

SELECT application_name,
       state,
       count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY application_name, state
ORDER BY application_name, state;

2. 오래된 transaction

idle session과 idle in transaction은 다릅니다. 후자는 transaction snapshot과 lock을 유지해 vacuum을 방해할 수 있습니다.

SELECT pid,
       usename,
       application_name,
       state,
       now() - xact_start AS xact_age,
       now() - state_change AS state_age,
       wait_event_type,
       wait_event,
       left(query, 200) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Exporter의 long_running_transactions collector는 기본 비활성입니다. 활성화하면 가장 오래된 transaction age를 지속적으로 관찰할 수 있지만, metric 이름과 동작은 배포 version의 /metrics에서 확인합니다.

3. Lock 개수와 blocking 구분

pg_locks_count는 database와 mode별 lock 개수를 제공합니다.

sum by (instance, datname, mode) (pg_locks_count)

Lock 개수 증가는 workload 증가 결과일 수 있습니다. 실제 block 여부는 granted = false와 pg_blocking_pids()로 확인합니다.

SELECT a.pid AS blocked_pid,
       a.usename AS blocked_user,
       now() - a.query_start AS blocked_for,
       a.wait_event_type,
       a.wait_event,
       pg_blocking_pids(a.pid) AS blocking_pids,
       left(a.query, 200) AS blocked_query
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

Blocker 상세를 연결합니다.

SELECT blocked.pid AS blocked_pid,
       blocker.pid AS blocker_pid,
       now() - blocker.xact_start AS blocker_xact_age,
       blocker.state AS blocker_state,
       left(blocker.query, 200) AS blocker_query
FROM pg_stat_activity AS blocked
CROSS JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid)
JOIN pg_stat_activity AS blocker ON blocker.pid = b.pid;

4. Deadlock

Deadlock은 PostgreSQL이 deadlock_timeout 이후 감지해 transaction 하나를 취소하므로 순간 상태를 놓치기 쉽습니다. Counter 증가와 log를 연결합니다.

increase(pg_stat_database_deadlocks[10m]) > 0

Log에서 SQLSTATE 40P01과 관련 statement를 찾습니다. Deadlock은 lock 순서가 일관되지 않은 application logic 문제인 경우가 많으며 deadlock_timeout을 늘려 해결하지 않습니다.

완화 순서

1. 사용자 영향과 affected database를 확인합니다.
2. Blocked session과 root blocker를 찾습니다.
3. Blocker transaction이 정상 작업인지 확인합니다.
4. 안전할 때 pg_cancel_backend()를 먼저 시도합니다.
5. Cancel로 끝나지 않고 영향이 클 때 pg_terminate_backend()를 검토합니다.
6. Application의 transaction 경계와 lock 획득 순서를 수정합니다.

Session 종료는 쓰기 transaction rollback과 사용자 오류를 만들 수 있으므로 PID만 보고 자동 실행하지 않습니다.

참고: PostgreSQL pg_stat_activity, PostgreSQL lock monitoring