PostgreSQL: Latency Spike 관련

PostgreSQL

PostgreSQL: Latency Spike 관련

dewstream 2025. 5. 14. 08:00

※ PostgreSQL: Latency Spike.

안녕하세요. 듀스트림입니다.

Latency Spike(지연 시간 급증)는 시스템이나 애플리케이션에서 일시적으로 응답 시간이 급격히 증가하는 현상을 말합니다.
일반적인 평균 지연(latency)은 안정적인 수준을 유지하지만, 어떤 순간에 특정 요청이나 작업이 비정상적으로 오래 걸리는 상황을 뜻합니다.

Latency Spike는 어떤 시스템에서든 일어날 수 있습니다.

이번 포스팅에서는 PostgreSQL(DBMS) 관점에서의 Latency Spike 사례들과 원인 그리고 보완 방법에 대해 알아보겠습니다.

주요 사례 및 원인

1. hot_standby_feedback로 인한 복제 지연

원인

hot_standby_feedback은 리플리카(standby)에서 실행 중인 쿼리가 마스터(primary)의 VACUUM 작업으로 인해 중단되는 것을 방지하기 위해 사용됩니다.
이 설정을 활성화하면 리플리카는 마스터에 현재 사용 중인 트랜잭션 스냅샷 정보를 전달하여, 마스터가 해당 데이터를 VACUUM으로 제거하지 않도록 합니다.
이로 인해 마스터의 VACUUM이 지연되어 불필요한 튜플이 제거되지 않고 남아있게 되며, 이는 마스터의 디스크 공간 증가와 성능 저하를 초래할 수 있습니다. 또한, 리플리카에서 장기 실행 쿼리가 있을 경우, 마스터의 WAL(Write-Ahead Logging) 적용이 지연되어 복제 지연(replication lag)이 발생할 수 있습니다.

분석 및 결과

복제 지연: 리플리카에서 장기 실행 쿼리가 있을 경우, 마스터의 VACUUM이 지연되어 WAL 적용이 지연되고, 이는 복제 지연으로 이어집니다.
디스크 공간 증가: 제거되지 않은 튜플이 마스터에 남아있어 디스크 공간을 차지하고, 이는 성능 저하로 이어질 수 있습니다.

보완 방법

hot_standby_feedback 비활성화: 이 설정을 비활성화하면 마스터의 VACUUM이 정상적으로 작동하여 디스크 공간을 확보할 수 있습니다. 그러나 리플리카의 장기 실행 쿼리가 중단될 수 있습니다.
max_standby_streaming_delay 조정: 리플리카에서 WAL 적용 지연 허용 시간을 조정하여, 장기 실행 쿼리와 WAL 적용 간의 균형을 맞출 수 있습니다.
리플리카 역할 분리: OLTP와 OLAP 용도로 리플리카를 분리하여, 장기 실행 쿼리가 마스터의 VACUUM에 영향을 주지 않도록 합니다.

2. ORDER BY로 인한 디스크 정렬 및 힌트 비트(Hint Bits) 쓰기

원인

대용량 테이블에서 ORDER BY를 사용하여 정렬할 경우, 정렬 대상 데이터가 메모리(work_mem)를 초과하면 PostgreSQL은 디스크를 사용하여 정렬을 수행합니다.
이 과정에서 external merge sort(외부 병합 정렬)이 발생하며, 이는 디스크 I/O를 증가시켜 성능 저하를 초래할 수 있습니다.
또한, 정렬 과정에서 힌트 비트(Hint Bits)가 업데이트되어 같은 페이지가 여러 번 디스크에 쓰여지는 현상이 발생할 수 있습니다. 이는 디스크 쓰기 작업을 증가시켜 IOPS(초당 입출력 작업 수) 스파이크를 유발할 수 있습니다.
- 힌트 비트는 왜 디스크 쓰기를 유발하는가?
  - 어떤 트랜잭션이 커밋된 후, 해당 트랜잭션의 xmin이나 xmax를 갖는 행에 접근한 다른 트랜잭션이 커밋 여부를 확인하고, 그 결과를 해당 행에 “힌트 비트”로 표시합니다.
  - 힌트 비트는 디스크 페이지에 직접 쓰여지며, WAL 로그에는 기록되지 않습니다.
    - 비정상 종료 시에는 힌트 비트 정보는 손실될 수 있습니다.
    - 이를 방지하고 Streaming Replication을 사용하기 위해 postgresql.conf에서 wal_log_hints = on 설정
      (off 시 pg_rewind 사용 불가)
  - PostgreSQL은 성능을 위해 WAL을 줄이고 힌트 비트를 디스크에만 기록합니다. 하지만 이 과정에서 페이지가 dirty 상태가 되어 디스크 쓰기 발생 → I/O 부하 유발 가능

특히 ORDER BY, Seq Scan 등으로 테이블 전체를 순회하면서 힌트 비트를 갱신하게 되면, 대량의 디스크 쓰기가 발생하여 latency spike나 IOPS 급증을 유발할 수 있습니다.

힌트 비트와 관련된 중요 개념들

항목	설명
pg_xact	트랜잭션 상태를 저장하는 시스템 테이블 (예전에는 pg_clog)
Hint Bit	트랜잭션 커밋 여부를 확인한 후, 해당 행에 COMMITTED 등의 플래그를 부여
WAL 미기록	힌트 비트는 WAL에 기록되지 않음 (write-after-read 발생 가능)
VACUUM	힌트 비트가 설정된 후에는 pg_xact에서 해당 정보 삭제 가능
full-page write	힌트 비트로 dirty page가 만들어져 FPI(Full Page Image)가 증가할 수 있음

힌트 비트로 인한 성능 이슈 대응 방안

대응 전략	설명
autovacuum 강화	힌트 비트를 VACUUM이 더 빨리 쓰게 만들어 디스크 write를 분산
정렬 쿼리 튜닝	불필요한 ORDER BY 제거, LIMIT 사용, work_mem 확대 등
read-only replica 사용	읽기 전용 조회를 replica에서 처리하여 힌트 비트 갱신 방지
read-only transaction 설정	트랜잭션을 SET TRANSACTION READ ONLY;로 지정하면 힌트 비트 쓰기를 제한
wal_log_hints 활성화	힌트 비트를 WAL에 기록하여 replica에서도 동기화 (복제 기반의 환경에서 필요)

※ 추가 팁

pg_stat_io 또는 pg_stat_database의 blks_written 값을 통해 힌트 비트로 인한 write 증가 유추 가능
pg_stat_statements로 높은 실행 빈도의 쿼리 중 I/O 많은 것을 추적
정기 리포트용 SELECT가 오히려 쓰기 성능에 영향을 줄 수 있으므로 주의

분석 및 결과

디스크 I/O 증가: 외부 병합 정렬로 인해 디스크 사용량이 증가하여 성능 저하가 발생할 수 있습니다.
IOPS Spike: 힌트 비트 업데이트로 인해 동일한 페이지가 반복적으로 디스크에 쓰여져 IOPS 스파이크가 발생할 수 있습니다.
- 매일 정기 실행되는 SELECT ... ORDER BY 쿼리에서 이상하게 Write IOPS 스파이크가 발생
- 해당 쿼리는 읽기 전용이었음에도 → 디스크에 쓰기 발생
  - 정렬로 인해 테이블 전체 스캔
  - 이 과정에서 힌트 비트가 갱신
  - WAL로 기록되지 않기 때문에 페이지가 dirty 되어 write 발생

보완 방법

work_mem 조정: 세션 또는 쿼리 수준에서 work_mem을 증가시켜 정렬 작업이 메모리 내에서 완료되도록 합니다.
인덱스 활용: 정렬 대상 컬럼에 인덱스를 생성하여 정렬 작업을 최소화합니다.
불필요한 ORDER BY 제거: 정렬이 필수적이지 않은 경우, ORDER BY 절을 제거하여 디스크 정렬을 방지합니다.
쿼리 최적화: EXPLAIN ANALYZE를 사용하여 쿼리 실행 계획을 분석하고, 정렬 작업이 효율적으로 수행되도록 쿼리를 최적화합니다.
정기적인 VACUUM 작업:
- 힌트 비트 설정: VACUUM은 아직 설정되지 않은 힌트 비트를 강제로 설정하여, 이후 쿼리에서 pg_xact 조회를 생략할 수 있게 합니다.
- 트랜잭션 상태 정리: 힌트 비트가 설정된 트랜잭션에 대해 pg_xact의 정보를 안전하게 제거할 수 있습니다.

※ 추가 팁

인덱스 생성이 디스크 정렬을 방지할 수 있는 경우

조건:
1. 정렬 대상 컬럼에 적절한 B-Tree 인덱스가 있고
2. 쿼리에 ORDER BY가 인덱스 정렬 순서와 일치하며
3. 쿼리가 LIMIT 또는 selectivity가 높은 조건과 함께 사용되는 경우
예시:

-- 인덱스 생성
CREATE INDEX idx_users_created_at ON users(created_at);

-- 쿼리
SELECT * FROM users
WHERE status = 'active'
ORDER BY created_at ASC
LIMIT 100;

PostgreSQL은 인덱스를 따라 Index Scan 또는 Index Only Scan을 수행하고, 별도의 디스크 정렬을 하지 않음
→ 이 경우 정렬 비용이 거의 발생하지 않음 (latency spike 방지 가능)

인덱스를 사용해도 디스크 정렬이 발생할 수 있는 경우

조건:
1. ORDER BY 순서와 인덱스가 맞지 않거나
2. WHERE 조건이 매우 포괄적이거나 없고
3. LIMIT이 없어서 대량의 데이터를 정렬해야 하는 경우
예시:

-- 인덱스는 아래처럼 있음
CREATE INDEX idx_users_created_at ON users(created_at);

-- 쿼리는 복잡하거나 정렬 대상이 다양함
SELECT * FROM users
ORDER BY last_login DESC;

해당 인덱스가 last_login에 맞지 않음 → Index Scan 대신 Seq Scan + External Sort
정렬 대상 데이터가 많으면 work_mem을 초과 → 디스크 정렬
정렬 도중 힌트 비트 업데이트 발생 → IOPS spike 발생 가능

정렬 피하기 위한 인덱스 설계 방법

전략	설명
멀티컬럼 인덱스	WHERE + ORDER BY 패턴을 고려한 인덱스
정렬 순서 일치	ASC/DESC 방향까지 쿼리와 일치해야 효과적
Index Only Scan 유도	필요한 컬럼이 모두 인덱스에 포함되어 있어야 함
Covering Index	읽기 속도와 정렬 회피를 동시에 얻을 수 있음

인덱스 설계 관련 요약

조건	디스크 정렬 방지 가능 여부	비고
정렬 대상 컬럼에 인덱스 있고, 쿼리와 정렬 순서 일치	✅	Index Scan/Only Scan 활용
인덱스가 없거나 정렬 대상과 일치하지 않음	❌	디스크 정렬 발생 가능
정렬 데이터가 많고 work_mem 초과	❌	외부 정렬 발생 가능

디스크 정렬 여부 확인 방법

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users ORDER BY age DESC;

--- QUERY PLAN ---
 Sort  (cost=36154.66..36929.66 rows=310000 width=14) (actual time=94.834..125.413 rows=310000 loops=1)
   Output: id, city, age
   Sort Key: users.age DESC
   Sort Method: external merge  Disk: 7896kB
   Buffers: shared hit=1676, temp read=987 written=988
   ->  Seq Scan on test.users  (cost=0.00..4776.00 rows=310000 width=14) (actual time=0.035..25.808 rows=310000 loops=1)
         Output: id, city, age
         Buffers: shared hit=1676
 Query Identifier: -3326976063532154083
 Planning:
   Buffers: shared hit=4
 Planning Time: 0.213 ms
 Execution Time: 141.922 ms
(13 rows)

-- 디스크 정렬 발생
Sort Method: external merge  Disk: 7896kB

메모리 내 정렬이면 다음과 비슷하게 표시됩니다.

Sort Method: quicksort  Memory: 1024kB

external merge 또는 external sort = work_mem을 초과하여 디스크를 사용한 정렬
quicksort, top-N heapsort = 메모리 내 정렬 (빠름)

3. Citus 확장 사용 시 advisory lock으로 인한 지연

원인

Citus는 PostgreSQL의 확장으로, 테이블을 샤딩하여 분산 처리할 수 있게 해줍니다. 그러나 분산 키(distribution key)를 WHERE 절에 명시하지 않으면, 쿼리가 모든 샤드에 브로드캐스트되어 실행됩니다.
이 과정에서 각 샤드에 대해 advisory lock이 획득되며, 동시에 여러 쿼리가 실행되면 lock 충돌이 발생하여 지연이 발생할 수 있습니다.

분석 및 결과

브로드캐스트 쿼리: 분산 키가 명시되지 않은 쿼리는 모든 샤드에 전달되어 실행됩니다.
advisory lock 충돌: 동시에 여러 쿼리가 실행되면 각 샤드에서 advisory lock을 획득하려고 시도하며, 이로 인해 충돌이 발생하고 쿼리 지연이 발생합니다.

보완 방법

분산 키 명시: 쿼리의 WHERE 절에 분산 키를 명시하여 브로드캐스트를 방지합니다.
쿼리 최적화: 필요한 경우 쿼리를 리팩토링하여 분산 키를 활용하도록 합니다.
advisory lock 모니터링: citus_lock_waits 뷰를 사용하여 현재 대기 중인 lock을 확인하고, 병목 현상을 파악합니다.

4. 컨테이너 환경에서의 CPU 제한으로 인한 지연

원인

컨테이너 환경에서는 CPU 리소스가 제한되거나 스케줄링이 지연되면, PostgreSQL 프로세스가 필요한 만큼의 CPU를 확보하지 못해 쿼리 지연이 발생할 수 있습니다.
특히 Kubernetes와 같은 오케스트레이션 환경에서는 CPU 제한이 설정되어 있을 경우, 컨테이너가 할당된 CPU를 초과하여 사용하려고 하면 throttling이 발생하여 성능 저하가 일어납니다.

분석 및 결과

CPU delay: 컨테이너가 CPU 리소스를 기다리는 시간으로, container_resources_cpu_delay_seconds_total 메트릭을 통해 확인할 수 있습니다.
CPU throttling: 컨테이너가 할당된 CPU를 초과하여 사용하려고 할 때 발생하며, container_resources_cpu_throttled_seconds_total 메트릭을 통해 확인할 수 있습니다.
쿼리 지연: CPU 리소스 부족으로 인해 PostgreSQL 쿼리의 응답 시간이 증가합니다.

보완 방법

CPU 리소스 할당 조정: 컨테이너에 할당된 CPU 리소스를 늘리거나, 필요한 경우 제한을 제거합니다.
쿼리 최적화: CPU 사용량이 높은 쿼리를 최적화하여 리소스 소비를 줄입니다.
모니터링 도구 활용: 모니터링 도구를 사용하여 CPU 사용량과 지연 시간을 모니터링하고, 병목 현상을 파악합니다.

늘 그렇듯 완벽하면 좋겠지만 완벽한 시스템은 없는 거 같습니다.

오늘도 화이팅~

저작자표시 비영리 변경금지 (새창열림)