PostgreSQL 실행 중인 쿼리 확인하기 (락 디버깅)

PostgreSQL에서 DB 성능이 좋지 못하다거나, 특정 테이블 데이터가 조회되지 않는 문제 등이 발생했을 때 현재 실행 중인 쿼리를 확인하는 방법으로 디버깅할 수 있습니다. 특히 특정 테이블에 락(Lock)이 걸린 경우도 확인할 수 있어 디버깅하는데 도움이 됩니다.

PostgreSQL 실행 중인 쿼리 확인

구문

아래의 쿼리는 PostgreSQL에서 현재 실행 중인 쿼리와 관련된 정보를 반환합니다.

SELECT datname, pid, client_addr, client_port, query_start, wait_event_type, wait_event, state, query FROM pg_stat_activity ORDER BY query_start ASC;

 

• datname : 현재 데이터베이스의 이름을 나타냅니다.
• pid : PostgreSQL 서버에서 처리를 담당하는 프로세스의 ID를 나타냅니다. 이 ID를 사용하여 특정 쿼리를 취소하거나 터미네이트할 수 있습니다.
• client_addr : 클라이언트의 IP 주소를 나타냅니다.
• client_port : 클라이언트의 포트를 나타냅니다.
• query_start : 해당 쿼리가 시작된 시각을 나타냅니다.
• wait_event_type : 프로세스가 현재 대기 중인 이벤트의 유형을 나타냅니다.
• wait_event : 프로세스가 현재 대기 중인 이벤트를 나타냅니다.
• state : 현재 쿼리의 상태를 나타냅니다. active면 쿼리가 현재 실행 중인 상태입니다. idle이면 쿼리가 대기 중인 상태로 클라이언트가 연결은 유지하되 쿼리는 보내지 않고 있는 상태입니다.
• query : 현재 실행 중인 쿼리 문자열입니다.

 

락 디버깅 예시)

테이블에 락이 걸린 경우 다른 세션에서 해당 테이블에 쿼리할 때 쿼리가 무한 대기하는 문제가 발생할 수 있습니다. 이런 경우를 간단한 예시로 알아보겠습니다.

 

아래와 같이 test_table이라는 테이블이 있습니다.

 

 

하나의 세션에서 begin으로 트랜젝션을 시작하고 해당 테이블을 업데이트하겠습니다.

BEGIN;
UPDATE test_table SET value = 'edit' WHERE id=1;

 

 

다른 세션에서 test_table 업데이트를 시도합니다. 아래와 같이 쿼리가 완료되지 않고 계속해서 돌아가는 게 확인됩니다.

UPDATE test_table SET value = 'edit2' WHERE id=1;

 

 

이제 실행 중인 쿼리를 확인하며 테이블 락을 디버깅할 수 있습니다. wait_event_type이 Lock인 쿼리가 확인됩니다. state이 active로 현재 실행 중인 것을 알 수 있습니다.

SELECT datname, pid, client_addr, client_port, query_start, wait_event_type, wait_event, state, query FROM pg_stat_activity ORDER BY query_start ASC;

 

 

만약 이처럼 문제 되는 쿼리를 발견하고 해당 쿼리를 중지하고 싶다면, 해당 쿼리의 pid를 확인하여 아래와 같이 쿼리 합니다.

SELECT pg_cancel_backend(<pid>);