PostgreSQL 서버 세션 모니터링 및 락 확인, 세션 킬 기능

PostgreSQL에서 "pg_stat_activity"  뷰 는 데이터베이스 서버의 세션 모니터링이 가능하다. 

이 뷰를 통해 현재 실행 중인 쿼리, 세션 정보 및 다양하고 유용한 정보를 얻을 수 있다. 

PostgreSQL에서 서버 세션을 모니터링은 다음 두 개의 파라미터와 관련이 있다.

track_activities = on 
track_activity_query_size = 1024

파라미터	설명
track_activities	해당 파라미터를 설정 하면 모든 프로세스에서 실행 중인 현재 명령을 모니터링 할 수 있음 (default : on)
track_activity_query_size	현재 실행 중인 쿼리의 텍스트를 저장하기 위해 예약된 메모리양 을 지정. 이 값을 단위 없이 지정하면 바이트로 간주됨. (default : 1024 bytes)

"pg_stat_activity"에 대한 주요 컬럼과 각각의 의미에 대한 설명은 다음과 같다.

컬럼	설명
datid	데이터베이스 OID(객체 식별자)
datname	데이터베이스 이름
pid	PostgreSQL 프로세스ID
leader_pid	이 프로세스가 병렬 쿼리 작업자인 경우 병렬 그룹 리더의 프로세스 ID이고, 이 프로세스가 병렬 적용 작업자인경우 리더 전용 작업자의 프로세스ID임. NULL은 이프로세스가 병렬 그룹 리더 또는 리더 적용 작업자이거나 병렬 작업에 참여하지 않음을 나타냄.
usesysid	현재 세션을 실행하는 사용자의 OID
usename	현재 세션을 실행하는 사용자의 이름
applicaiton_name	클라이언트 응용 프로그램 이름
client_addr	클라이언트의 IP 주소
client_hostname	클라이언트 호스트 이름
client_port	클라이언트의 연결 포트 번호
backend_start	현재 세션이 시작된 시간
xact_start	현재 트랜잭션의 시작 시간
query_start	현재 실행 중인 쿼리의 시작 시간
state_change	세션의 마지막 상태가 마지막으로 변경된 시간
wait event_type	세션이 대기 중인 이벤트 유형
wait_event	세션이 대기 중인 이벤트의 이름
state	세션의 현재 상태를 나타낸다.  * active : 백엔드가 쿼리를 실행 중 * idle : 백엔드가 새 클라이언트의 명령을 기다리고 있음 * idle in transaction : 백엔드가 트랜잭션에 있지만 현재 쿼리를 실행하고 있지 않음 * idle in transaction (abort) idle in transaction : 이 상태는 idle in transaction과 비슷한 상태 (트랜잭션의 하나의 스테이트먼트에서 오류 발생한 점만 제외하면) * fastpath function call : 백엔드가 빠른 경로 함수를 실행 * disabled : 이 백엔드에서 track_activity가 비활성화 된 경우
backend_xid	현재 세션이 사용 중인 트랜잭션의 트랜잭션ID
backend_xmin	세션이 사용 중인 가장 오래된 활성 트랜잭션의 최소 트랜잭션 ID
query_id	현재 실행 중인 쿼리의 식별자.  기본적으로 쿼리 텍스트는 1024 바이트에서 잘림. 이값은 track_activity_query_size 파라미터를 통해 변경할 수 있음.
query	현재 실행 중인 쿼리 텍스트
backend_type	백엔드의 유형을 나타냄. (autovacuum launcher, autovacuum worker, logical replication launcher, parallel worker, background writer, client backend, checkpointer, archiver, startup, walreceiver, walsender, walwriter 등)

이러한 정보를 활용해 현재 PostgreSQL 데이터 서버의 활동을 모니터링 하고, 세션 및 쿼리의 성능을 디버깅하는데 도움을 얻을 수 있다.

만약 장애가 발생했는데, 쿼리가 잘려서 제대로 확인이 불가능하다면 그것 또한 패착이 될 수 있다.  

그러므로 실제 장애 상황을 분석 할 때 분석에 용이하도록 track_activty_query_size 파라미터를 조정해서 쿼리가 잘리지 않도록 설정할 수 있다. (버전에 따라 MAX 값이 다름) 

또한 장애가 발생하면 해당 세션의 state와 wait_event도 같이 봐야할 것이다. 

* wait_event와 state 컬럼은 독립적이다. 만약 백엔드의 state가 active 라면 이벤트를 대기중 일수도 있지만, 아닐 수도 있다. 

그 이유는 state가 active 이면서도 이벤트를 대기할 수 있기 때문이다. 

그런 상태라면 state가 active이고 wait_event는 Not NULL 상태 일 것이다.  이럴 때는 wait_event도 유심히 봐야 할 것이다.

 현재 실행 중인 쿼리 확인

SELECT * FROM pg_stat_activity;

만약 Lock에 의해 지연이 발생하고 있다면 다음의 쿼리를 활용해서 락을 확인할 수 있다. 

락을 대기하는 세션 확인 

SELECT pg_stat_activity.datname,
               pg_stat_activity.pid,
               pg_stat_activity.usename,
               pg_stat_activity.query,
               pg_locks.*
FROM pg_locks JOIN pg_stat_activity
ON pg_locks.pid = pg_stat_activity.pid;

락 상태를 확인함으로써 시스템에서 어떤 릴레이션에 락이 걸려 있는지, 어떤 세션이 해당 리소스를 사용하는지 등을 파악할 수 있다.  (락에 대해서도 학습이 필요하다. 추후 시간이 된다면 한번 정리해보도록 하겠다)

해당 락이 시간이 지났음에도 해결이 되지 않는다면, 최악의 상황에서는 해당 세션을 킬을 해야 하는 상황이 발생할 수도 있다. (혹은 오래 수행되어 과도한 리소스를 잡아 먹는 세션 등)

*세션 중단 및 강제 종료를 하는 것은 해당 세션이 사용 중인 트랜잭션이나 리소스에 영향을 미칠 수 있으므로 주의가 필요하다. 가능한 경우 세션을 정상적으로 종료하도록 노력하는 것이 좋다. 세션을 강제로 종료할 경우 데이터 무결성에 영향을 줄수도 있다.

세션 중단 및 킬 하기 

세션을 중단 및 킬하는 방법은 어떤 함수를 사용하는냐에 따라 달라진다.

select pg_cancel_backend ( pid integer ) ;  --## 세션 중단
select pg_terminate_backend ( pid integer )   --## 세션 강제 종료 

pg_cancel_backend () 함수는 세션을 중단 시킨다. 

pg_terminate_backend () 함수는 세션을 강제 종료 시킨다.

만약 PID를 확인하는 것이 문제가 아니라 특정 조건을 입력해서 세션을 킬할 수도 있다.

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_locks JOIN pg_stat_activity
ON pg_locks.pid = pg_stat_activity.pid
WHERE pg_locks.locktype IS NOT NULL;

물론 다음과 같이 사용하는 사람은 없을 것이다. SELECT 절을 활용해 특정 조건을 만족하는 모든 세션을 킬할 수 있는 기능을 설명하고자 하였으니, 참고 하기 바란다. 

함께 보면 좋을 내용