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

데이터베이스 성능은 효율적인 자원 관리와 병목 현상의 신속한 식별에 크게 좌우됩니다. PostgreSQL 관리자와 개발자에게 내장 시스템 뷰인 pg_stat_activity는 실시간 모니터링 및 즉각적인 성능 튜닝을 위한 가장 중요한 단일 도구입니다.

이 가이드에서는 pg_stat_activity를 활용하여 모든 활성 백엔드 프로세스를 검사하고, 장기 실행 쿼리를 식별하며, 연결 문제를 진단하고, 잠금 경합(lock contention)을 해결하여 건전하고 반응성 있는 데이터베이스 환경을 유지하는 방법을 설명합니다.

`pg_stat_activity` 뷰 이해하기

pg_stat_activity는 데이터베이스 클러스터에 연결된 각 서버 프로세스(백엔드)에 대해 한 행을 제공하는 동적 시스템 뷰입니다. 여기에는 쿼리를 실행 중인 클라이언트, 백그라운드 워커, 현재 유휴 상태이지만 연결을 유지하고 있는 프로세스가 포함됩니다.

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

성능 분석을 위한 주요 컬럼

pg_stat_activity에는 수십 개의 컬럼이 있지만, 성능 문제를 진단할 때 다음 컬럼들이 필수적입니다:

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

실제 모니터링 활용 사례

pg_stat_activity의 진정한 힘은 특정 성능 질문에 답하기 위해 데이터를 필터링하는 데 있습니다.

1. 모든 활성 쿼리 보기

현재 문장을 실행 중인 프로세스(유휴 상태가 아닌)만 보려면 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;

2. 장기 실행 및 느린 쿼리 식별

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

특정 임계값(예: 5초)보다 오래 실행되는 쿼리를 식별하려면 now()와 query_start를 사용하여 간격(interval)을 뺄셈합니다.

-- 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;

팁: 임계값 (5 seconds)을 일반적인 작업 부하에 따라 사용자 정의하세요. OLTP 환경에서는 1초를 초과하는 모든 것이 느리다고 간주될 수 있습니다.

3. 트랜잭션 내 유휴(Idle In Transaction) 세션 진단

idle in transaction 상태인 연결은 트랜잭션 블록(BEGIN)을 시작했지만 아직 커밋하거나 롤백하지 않았으며, 현재 클라이언트 애플리케이션이 다음 명령을 발행하기를 기다리고 있음을 의미합니다. 이러한 세션은 종종 잠금을 유지하고 Vacuum 작업을 방해하여 데이터베이스 팽창(bloat) 및 트랜잭션 ID 소진으로 이어질 수 있으므로 위험합니다.

-- 유휴 상태이지만 열린 트랜잭션을 유지하는 세션 찾기
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;

만약 몇 분 또는 몇 시간 동안 열린 트랜잭션을 유지하는 세션을 발견한다면, 클라이언트 애플리케이션에 논리 오류(예: 예외 발생 후 커밋 실패)가 있거나 잘못 구성되었을 가능성(예: 연결 풀링 문제)이 있습니다.

4. 잠금 경합 및 차단 분석

쿼리가 멈추면, 종종 다른 프로세스가 보유하고 있는 잠금(lock)을 기다리는 경우가 많습니다. pg_stat_activity 뷰는 pg_locks와 함께 경합(contention)을 진단하는 데 중요합니다.

현재 리소스(잠금, 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;

완벽한 잠금 분석(누가 누구를 기다리고 있는지)을 위해서는 pg_stat_activity와 pg_locks를 조인하여 잠금을 보유하고 있는 프로세스(granted = true)와 이를 기다리고 있는 프로세스(granted = false)를 연관시키는 것이 필요합니다.

문제성 세션 관리

문제성 쿼리 또는 세션이 프로세스 ID(pid)를 사용하여 식별되면, PostgreSQL은 이를 관리하기 위한 두 가지 함수를 제공합니다:

1. 쿼리 취소 (`pg_cancel_backend`)

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

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

2. 세션 종료 (`pg_terminate_backend`)

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

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

⚠️ 경고: 종료는 신중하게 사용하세요

항상 pg_cancel_backend를 먼저 사용해 보세요. 세션 강제 종료(pg_terminate_backend)는 응답하지 않거나 리소스를 과도하게 사용하는 세션에 한해 사용해야 합니다. 대규모 트랜잭션을 롤백하는 데 상당한 I/O 리소스와 시간이 소요될 수 있기 때문입니다.

모니터링을 위한 베스트 프랙티스

적극적으로 필터링하세요

수천 개의 연결이 있는 프로덕션 서버에서 SELECT * FROM pg_stat_activity를 실행하지 마십시오. 결과는 대개 압도적이며, 쿼리 자체도 약간의 오버헤드를 추가할 수 있습니다. 항상 WHERE 절(예: WHERE state = 'active')을 사용하여 조사를 집중하세요.

자동화된 모니터링 도구 사용

수동 확인이 문제 해결에 필수적이지만, pg_stat_activity 데이터를 표준 모니터링 도구(예: Prometheus, DataDog 또는 전문 PostgreSQL 대시보드)에 통합하여 쿼리 실행 시간, 평균 활성 연결 수 및 idle in transaction 수의 추세를 시간 경과에 따라 추적하십시오.

문장 로깅 구성

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

결론

pg_stat_activity는 PostgreSQL DBA가 서버의 실시간 작업을 들여다볼 수 있는 필수적인 창입니다. 이 뷰를 정기적으로 쿼리하고 필터링함으로써, 성능 문제를 진단하고, 비효율적인 SQL을 식별하며, 차단 상황을 신속하게 해결하는 데 필요한 즉각적인 가시성을 확보할 수 있습니다. state, duration, wait_event의 해석을 숙달하면 성능 튜닝이 반응적인 노력에서 사전 예방적인 관리 프로세스로 전환됩니다.

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

pg_stat_activity 뷰 이해하기