pg_stat_activity로 실시간 트랜잭션 추적하기

pg_stat_activity는 말 그대로 지금 내 데이터베이스에서 무슨 일이 일어나고 있는지 바로 볼 수 있는 실시간 창이야. 지난 강의에서 기본은 다뤘으니까, 이제 이 강력한 도구를 좀 더 깊게 파헤쳐보자.

pg_stat_activity에 대한 기본 쿼리 예시:

SELECT * 
FROM pg_stat_activity;

이 쿼리는 모든 활성 연결과 현재 실행 중인 쿼리를 보여줘. 좋아! 근데 데이터가 너무 많아서 다 보려면 한세월 걸릴 수도 있어. 그래서 중요한 정보만 골라서 보는 게 훨씬 유용하지.

pg_stat_activity의 주요 필드

이미 알고 있는 것에 더해서, 꼭 알아두면 좋은 핵심 필드들을 보자. query_start는 쿼리가 언제 시작됐는지 보여주는데, 오래 걸리는 작업을 찾을 때 진짜 중요해. pid는 연결 프로세스의 ID를 담고 있어서, 연결을 관리(예를 들어 종료)할 때 써. state_change는 현재 연결 상태가 언제 바뀌었는지 알려주는데, 오래 지속되는 문제 상태를 분석할 때 특히 유용해.

활성 프로세스만 뽑아보는 예시:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'active';

오래 걸리는 쿼리 추적하기

네가 데이터베이스 관리자라고 해보자. 갑자기 서버 부하가 미친 듯이 올라갔어. 뭐부터 해야 할까? 일단 어떤 쿼리가 리소스를 다 잡아먹고 있는지 파악해야지. pg_stat_activity로 이런 "욕심쟁이" 쿼리를 찾아보자.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '10 seconds';

이 쿼리는 10초 넘게 실행 중인 쿼리만 보여줘. interval 값은 네 상황에 맞게 바꿔서 쓰면 돼.

문제 쿼리 종료하기

이제 너무 오래 실행돼서 DB에 방해가 되는 쿼리를 어떻게 종료하는지 알아보자. pg_terminate_backend() 함수를 써서 프로세스를 강제로 종료할 수 있어.

특정 PID의 프로세스를 종료하는 예시:

SELECT pg_terminate_backend(12345);

여기서 12345는 pg_stat_activity의 pid 필드 값이야.

중요: 프로세스를 종료하면 제대로 끝나지 않은 트랜잭션은 rollback 될 수 있으니까 조심해야 해.

이제 만약 "걸려있는" 프로세스, 예를 들어 idle 트랜잭션을 자동으로 종료하고 싶으면, 아래 PL/pgSQL 블록을 써봐. 이미 프로그래밍에서 loop(루프) 개념은 배웠으니까, 반복문이 조건이 맞을 때까지 계속 실행된다는 건 알지? 아래 코드는 그런 식으로 동작해:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 minutes'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

이렇게 하면 문제 트랜잭션을 자동으로 정리할 수 있어. FOR 루프가 쿼리 결과의 각 레코드를 돌면서, 찾은 PID마다 프로세스 종료를 실행해.

곧 PL/pgSQL도 배울 거니까, 조금만 더 기다려줘 :P

트랜잭션 상태로 필터링하기

가끔은 단순히 활성 쿼리만 찾는 게 아니라, idle이나 idle in transaction 같은 특별한 상태의 연결도 보고 싶을 때가 있어. 이런 걸로 잠재적인 문제를 미리 발견할 수 있거든.

idle in transaction 상태에 있는 트랜잭션을 찾는 쿼리 예시:

SELECT pid, usename, query, state, state_change
FROM pg_stat_activity
WHERE state = 'idle in transaction';

state_change 필드는 이 상태가 언제 시작됐는지 보여줘. 그래서 아무것도 안 하면서 오래 살아있는 트랜잭션을 쉽게 찾을 수 있고, 이런 것들이 DB 리소스를 막고 있을 수도 있어.

실전 활용

운영 환경에서 오래 걸리는 쿼리 모니터링: 일정 시간 이상 걸리는 쿼리를 정기적으로 모니터링해서, Slack, Telegram 같은 알림 도구로 바로 알려줄 수 있어. 이렇게 하면 성능 문제에 빠르게 대응할 수 있지.

장애 상황에서 쿼리 분석: 서버가 느려지면 제일 먼저 pg_stat_activity를 확인해서 원인을 찾아봐. 이게 성능 문제 대응의 기본 프로토콜이야.

DB 관리: pg_stat_activity를 주기적으로 분석하면 비효율적인 쿼리를 찾아서 인덱스를 추가하거나 쿼리를 다시 짜는 등 최적화할 수 있어.

모니터링할 때는 필터링이나 데이터 해석을 잘못해서 실수할 수도 있어. 예를 들어 active 상태만 필터링하면 idle in transaction 상태의 쿼리를 놓칠 수 있는데, 이런 것도 리소스 블로킹의 원인이 될 수 있어. 또 너무 공격적으로 프로세스를 종료하면 트랜잭션이 원치 않게 롤백되고 데이터가 날아갈 수도 있으니까, 항상 상황을 잘 보고 신중하게 결정해야 해.

추가 모니터링 테크닉

좀 더 고급 모니터링을 하고 싶으면, 사용자별, 데이터베이스별, 쿼리 유형별로 통계를 보여주는 복잡한 쿼리를 만들어볼 수 있어. 예를 들어, 각 사용자가 쿼리 실행에 평균적으로 얼마나 시간을 쓰는지, 또는 어떤 데이터베이스에 활성 연결이 제일 많은지 추적할 수 있지.

그리고 PostgreSQL 설정에서 log_min_duration_statement랑 log_statement 같은 파라미터를 이용해서, 오래 걸리는 쿼리를 자동으로 로그 파일에 남기도록 하면 좋아. 이렇게 하면 나중에 성능 문제를 분석하거나, 앱의 쿼리 패턴을 파악하는 데 큰 도움이 돼.