활성 쿼리 모니터링: pg_stat_activity를 사용한 성능 튜닝

데이터베이스가 갑자기 느려지면 PostgreSQL이 현재 무엇을 하고 있는지 알아야 합니다. pg_stat_activity는 활성 쿼리, 유휴 세션, 잠금 대기 및 열린 트랜잭션을 보여주므로 느린 쿼리와 차단된 쿼리를 구분할 수 있습니다.

장애 상황에서 사용하되, 일상적인 점검을 위해 몇 가지 저장된 쿼리를 준비해 두는 것도 좋습니다. 아래 예제는 검사해야 할 세션의 활동을 읽을 수 있는 권한이 있는 PostgreSQL 시스템에 초점을 맞춥니다.

pg_stat_activity 뷰 이해하기

pg_stat_activity는 데이터베이스 클러스터에 연결된 각 서버 프로세스에 대해 하나의 행을 가지는 동적 시스템 뷰입니다. 여기에는 클라이언트 백엔드, 백그라운드 워커 및 유휴 상태이지만 여전히 연결된 세션이 포함됩니다.

이 뷰를 모니터링하면 데이터베이스가 지금 정확히 무엇을 하고 있는지 확인할 수 있으므로, 일반 로그 파일이 효과적으로 포착하기 어려운 갑작스러운 성능 저하를 디버깅하거나 일시적인 경합 문제를 진단하는 데 매우 유용합니다.

성능 분석을 위한 주요 열

pg_stat_activity에는 수십 개의 열이 포함되어 있지만, 성능 문제를 진단할 때 다음 열이 필수적입니다:

열 이름	설명	튜닝 관련성
pid	백엔드의 프로세스 ID	세션 취소 또는 종료에 필요
datname	이 백엔드가 연결된 데이터베이스 이름	다중 데이터베이스 환경에서 모니터링 범위 지정에 도움
usename	연결을 시작한 사용자	특정 애플리케이션 또는 사용자 활동 식별
application_name	연결하는 애플리케이션 이름(클라이언트가 설정한 경우)	특정 마이크로서비스의 연결 식별에 탁월
state	현재 활동 상태(예: active, idle, idle in transaction)	백엔드가 무엇을 하고 있는지의 핵심 지표
query	현재 쿼리 또는 유휴 세션의 마지막 쿼리. 권한 및 설정에 따라 가시성이 제한될 수 있음	관련된 SQL 문 식별
query_start	현재 쿼리 실행이 시작된 타임스탬프	쿼리 기간 계산에 사용
wait_event_type 및 wait_event	프로세스가 대기 중인 항목에 대한 세부 정보(예: 잠금 획득, I/O)	경합 및 차단 진단에 중요

실용적인 모니터링 사용 사례

pg_stat_activity의 진정한 힘은 데이터를 필터링하여 특정 성능 질문에 답하는 데서 나옵니다.

모든 활성 쿼리 보기

현재 명령문을 실행 중인 프로세스만 보려면(유휴 상태 제외) state 열로 뷰를 필터링하세요.

-- 현재 실행 중인 모든 쿼리 보기
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

장기 실행 쿼리 식별

예상보다 오래 실행된 쿼리를 식별하는 것은 성능 튜닝의 첫 번째 단계인 경우가 많습니다. 이러한 쿼리는 리소스를 소비하고 I/O 스파이크를 유발하거나 잠금을 보유할 수 있습니다.

특정 임계값(예: 5초)보다 오래 실행된 쿼리를 식별하려면 now()와 query_start를 사용하여 간격 차이를 계산하세요.

-- 5초 이상 실행 중인 쿼리 찾기
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

워크로드에 맞는 임계값을 사용하세요. OLTP 앱에서 5초짜리 체크아웃 쿼리는 심각할 수 있지만, 보고서 쿼리가 업무 시간 외에 실행된다면 5분이 정상일 수 있습니다.

트랜잭션 내 유휴 세션 진단

idle in transaction 상태인 연결은 트랜잭션을 시작했지만 커밋하거나 롤백하지 않았습니다. 클라이언트가 다음 명령을 보내기를 기다리고 있습니다. 이러한 세션은 잠금을 보유하고 이전 행 버전을 계속 표시할 수 있으며, 이는 autovacuum의 정리를 지연시키고 테이블 블로트에 기여할 수 있습니다.

-- 유휴 상태이지만 열린 트랜잭션을 보유한 세션 찾기
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

트랜잭션을 몇 분 또는 몇 시간 동안 열어 둔 세션을 발견하면 트랜잭션을 연 애플리케이션 경로를 확인하세요. 일반적인 원인으로는 예외 후 롤백 처리 누락, 정리 전에 풀로 반환된 연결, 또는 열린 상태로 남겨진 대화형 관리자 세션이 있습니다.

잠금 경합 및 차단 분석

쿼리가 중단되면 종종 다른 프로세스가 보유한 잠금을 기다리고 있는 것입니다. pg_stat_activity 뷰는 pg_locks와 결합하여 경합을 진단하는 데 중요합니다.

현재 리소스(잠금, I/O 등)를 기다리고 있는 세션을 찾으려면 wait_event 열을 확인하세요. 세션이 차단된 경우 wait_event_type이 종종 Lock입니다.

-- 현재 잠금에 의해 차단된 프로세스 식별
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

"누가 누구를 차단하고 있는지" 빠르게 확인하려면 PostgreSQL은 pg_blocking_pids()도 제공합니다.

-- 차단된 세션과 차단하는 세션 표시
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

긴 관리 업데이트를 기다리는 차단된 웹 요청이 반환되면, 애플리케이션 세션을 종료하는 것보다 관리자 쿼리를 취소하는 것이 더 안전할 수 있습니다.

문제 세션 관리

문제가 있는 쿼리나 세션이 프로세스 ID(pid)로 식별되면 PostgreSQL은 이를 관리하기 위한 두 가지 함수를 제공합니다.

pg_cancel_backend로 쿼리 취소

이 함수는 특정 쿼리의 실행을 정상적으로 중지하려고 시도합니다. 세션 자체는 연결된 상태로 유지되며 향후 쿼리에 사용할 수 있습니다.

-- 예: PID 12345에서 실행 중인 쿼리 취소
SELECT pg_cancel_backend(12345);

pg_terminate_backend로 세션 종료

이 함수는 서버에서 백엔드 프로세스를 강제로 연결 해제합니다. 세션이 트랜잭션 중간에 있었다면 PostgreSQL이 자동으로 트랜잭션을 롤백합니다.

-- 예: PID 54321의 세션을 강제로 종료
SELECT pg_terminate_backend(54321);

세션이 단순히 잘못된 쿼리를 실행 중인 경우 먼저 pg_cancel_backend를 시도하세요. 세션이 중단되었거나, 버려졌거나, 정상적으로 정리할 수 없는 열린 트랜잭션을 보유하고 있는 경우 pg_terminate_backend를 사용하세요. 대규모 트랜잭션을 롤백하는 데는 시간이 걸리고 I/O 부하가 추가될 수 있으므로 신중하게 수행하세요.

모니터링 모범 사례

적극적으로 필터링

기본 프로덕션 습관으로 SELECT * FROM pg_stat_activity를 사용하지 마세요. 출력이 많고, 애플리케이션이 바인드 매개변수 대신 리터럴을 보내는 경우 query 텍스트가 민감한 값을 노출할 수 있습니다. 필요한 열만 선택하고 state, datname, application_name 또는 기간별로 필터링하세요.

자동 모니터링 도구 사용

수동 확인은 장애 상황에서 유용하지만, 추세는 모니터링에 속합니다. PostgreSQL 대시보드에서 활성 세션, 대기 세션, 장기 트랜잭션 및 idle in transaction 수를 추적하세요.

명령문 로깅 구성

실시간 모니터링을 기록 데이터와 결합하세요. log_min_duration_statement와 같은 매개변수를 구성하여 특정 임계값을 초과하는 쿼리를 기록하면 쿼리 실행이 완료된 후에도 분석할 수 있는 데이터를 제공합니다.

핵심 요점

세 가지 저장된 검사를 유지하세요: 기간별 활성 쿼리, 트랜잭션 기간별 유휴 트랜잭션, 차단자와 함께 차단된 세션. PostgreSQL이 느리게 느껴질 때, 이러한 뷰는 SQL을 튜닝할지, 트랜잭션 처리를 수정할지, 아니면 차단 세션을 정리할지 알려줍니다.