PostgreSQL의 BufferPin 충돌: 찾고 수정하는 방법

# PostgreSQL의 BufferPin 충돌: 복제본과 Autovacuum을 위한 진단 및 최적화

버퍼를 고정(pinning)할 때 발생하는 BufferPin 충돌은 PostgreSQL에서 숨겨진 치명적인 성능 문제 중 하나로, 특히 복제본(replicas)과 페이지 동결(freezing) 중에 두드러집니다. 이는 표준 모니터링 지표에 나타나지 않고, "멈춤(hangs)"이나 "설명할 수 없는 지연(unexplained lag)"으로 위장하며, 진단을 위해 DBMS 내부 구조에 대한 깊은 이해가 필요합니다. 이 글에서는 이러한 충돌이 발생하는 원리, 증상, 탐지 방법, 그리고 실질적인 완화 방안을 자세히 분석합니다.

BufferPin 충돌이 위험하고 발견하기 어려운 이유

주요 문제는 BufferPin 대기(wait)가 pg_locks에 잠금으로 기록되지 않고, 충돌 통계에도 항상 나타나지 않는다는 점입니다. 핀(pin)이 해제될 때까지 대기하는 프로세스는 단순히 wait_event = 'BufferPin' 상태로 멈춰 서서 경고를 발생시키지 않습니다. 이는 특히 복제본에서 심각한 영향을 미칩니다:

• WAL 기록을 재생(replay)할 때 startup 프로세스(예: HOT 정리)가 버퍼에 대한 배타적 접근 권한이 필요합니다. 즉, pincount = 0이 될 때까지 대기해야 합니다.
• 복제본에서 실행 중인 장기 실행 쿼리(예: Seq Scan)가 블록에 핀을 유지하고 있습니다.
• startup이 블록이 해제될 때까지 완전히 멈춥니다.
• WAL 재생이 단일 스레드에서 이루어지기 때문에 하나의 블록 지연이 전체 복제본의 지연(lag)을 쌓이게 합니다.

기본 서버와 복제본의 autovacuum에서도 페이지 동결(freeze) 중 유사한 상황이 발생합니다. Autovacuum은 일반 모드에서 핀된 블록을 건너뛰지만, 공격적 모드(동결 시)에서는 대기합니다. 이러한 블록이 많으면 워커가 멈추고, 정리 지연이 쌓이며, 이는 트랜잭션 ID 랩어라운드(transaction ID wraparound)로 이어질 수 있습니다.

숨겨진 증상:

• 눈에 띄는 부하 없이 복제본 지연 증가.
• automatic aggressive vacuum of table... 또는 skipped due to pins 같은 로그 메시지.
• pg_locks에 대기가 보이지 않지만 pg_stat_activity.wait_event에 BufferPin이 존재.

문제 재현 및 진단 방법

메커니즘을 이해하기에 간단한 예시로 충분합니다. 테이블을 생성하고 트랜잭션 내 커서를 사용해 행을 핀합니다:

CREATE TABLE t AS SELECT 1 c;
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM t;
FETCH c;

이제 pg_buffercache를 통해 버퍼 상태를 확인합니다:

select c.relname, bufferid, relforknumber, usagecount, pinning_backends
  from pg_buffercache b join pg_class c on b.relfilenode =
   pg_relation_filenode(c.oid)
   and c.relname = 't';

결과는 pinning_backends = 1을 보여줍니다—우리 트랜잭션이 핀을 유지하고 있습니다.

두 번째 세션에서 공격적 VACUUM을 실행합니다:

vacuum (freeze, verbose, skip_locked off) t;

VACUUM이 멈춥니다. pg_buffercache를 다시 쿼리하면 pinning_backends = 2가 됩니다. 대기를 확인합니다:

SELECT query, wait_event FROM pg_stat_activity WHERE query ILIKE 'vacuum%';
-- wait_event = 'BufferPin'

유사한 커서를 가진 세션을 몇 개 더 열면 pinning_backends가 계속 증가합니다. 모든 트랜잭션이 완료될 때까지 VACUUM은 진행되지 않습니다.

실전 모니터링 및 예방 방법

표준 도구로는 실시간 문제를 파악하기 어렵습니다. 하지만 pg_buffercache와 pg_stat_activity를 기반으로 효과적인 모니터링을 구축할 수 있습니다.

핫 블록(hot blocks) 탐지 기본 쿼리:

SELECT 
  n.nspname AS schema_name,
  c.relname AS table_name,
  b.bufferid,
  b.pinning_backends,
  b.usagecount,
  b.isdirty,
  c.relpages
FROM pg_buffercache b 
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
  AND b.pinning_backends > 1
ORDER BY b.pinning_backends DESC
LIMIT 10;

정기적으로 확인할 항목:

• 높은 pinning_backends를 가진 테이블—최적화 우선 대상.
• 장기 실행 트랜잭션 존재(pg_stat_activity.xact_start).
• wait_event = 'BufferPin'인 대기 프로세스.

충돌 줄이는 방법:

• fillfactor 최적화—행 수가 적고 빈번한 업데이트가 발생하는 테이블에서 fillfactor를 낮추면 행이 여러 블록에 분산되어 단일 버퍼 경쟁을 줄입니다.
• 전용 복제본—사용자 쿼리가 없는 복제본은 핀 충돌을 완전히 피하며 안정적인 동기화를 보장합니다. 예를 들어 OpenAI에서 이렇게 운영합니다.
• max_standby_streaming_delay 튜닝—값을 높이면 복제본 쿼리에 더 많은 실행 시간을 주지만 지연 위험이 증가합니다.
• 트랜잭션 지속 시간 제한—타임아웃 구현과 멈춘 세션 자동 종료.
• 동결 전 사전 정리—부하가 낮은 기간에 수동 VACUUM FREEZE 실행.

심층 분석을 위한 확장 및 대안

Alexandra Bondar의 발표에서 핫 블록 모니터링을 위한 커스텀 확장을 언급했습니다. 이는 콜백 훅과 메모리 교대 쓰기를 사용해 오버헤드를 최소화합니다. 그러나 이런 솔루션은 커뮤니티에서 거의 받아들여지지 않습니다—핵심 수정이 필요하고 매우 틈새적인 문제를 다루기 때문입니다.

핵심 변경 없이 대안:

• pg_wait_sampling—샘플링을 통해 BufferPin을 포함한 대기 통계를 수집.
• pg_buffercache + cron—주기적 데이터 수집 및 이전 상태 비교.
• Autovacuum 로깅—log_autovacuum_min_duration = 0으로 설정해 건너뛴 블록 모든 사례 포착.

로깅 설정 예시:

ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();

이후 로그에 다음과 같은 항목이 포함됩니다:

LOG:  skipping block N of relation "public.t" --- block is pinned

이렇게 하면 활성 모니터링 없이도 문제 테이블을 식별할 수 있습니다.

주요 요약

• BufferPin 충돌은 복제본 지연 증가와 autovacuum 멈춤의 보이지 않는 원인입니다.
• 진단은 pg_buffercache와 pg_stat_activity.wait_event를 통해서만 가능합니다.
• fillfactor와 전용 복제본이 경쟁을 줄이는 가장 효과적인 방법입니다.
• 0 임계값 autovacuum 로깅으로 프로덕션에서 문제 테이블을 발견합니다.
• 표준 지표(pg_stat_database_conflicts)는 대부분의 사례를 놓칩니다—커스텀 모니터링이 필수입니다.

— Editorial Team