beautifulsoups.dev
Published on

AWS RDS PostgreSQL IOPS 폭증과 Slow Query 해결

Authors

서버가 멀쩡해 보이는데도 AWS RDS PostgreSQL의 IOPS가 갑자기 튀고, 동시에 p95/p99 응답 시간이 늘어나며 Slow Query가 쏟아지는 상황은 꽤 전형적입니다. 특히 RDS는 OS 레벨 접근이 제한되기 때문에 “무엇이 디스크를 두드리는가?”를 DB 내부/CloudWatch 지표로 역추적해야 합니다.

이 글은 다음을 목표로 합니다.

  • IOPS 폭증을 지표로 분해(읽기/쓰기, 랜덤/시퀀셜, 체크포인트/오토백업/쿼리)
  • Slow Query를 **pg_stat_statements + EXPLAIN(ANALYZE, BUFFERS)**로 특정
  • 쿼리/인덱스/파라미터/스토리지(특히 gp3, io2)까지 재발 방지

1) 먼저 “IOPS 폭증”을 숫자로 쪼개기

IOPS가 튄다는 말은 보통 아래 중 하나입니다.

  • ReadIOPS 폭증: 인덱스 미스/Seq Scan/캐시 미스/랜덤 읽기 증가
  • WriteIOPS 폭증: UPDATE/DELETE 폭증, VACUUM, 체크포인트, WAL flush
  • Read/Write Throughput은 낮은데 IOPS만 높음: 작은 랜덤 I/O가 많음(가장 위험)

CloudWatch에서 꼭 같이 볼 지표

  • ReadIOPS, WriteIOPS
  • ReadLatency, WriteLatency
  • ReadThroughput, WriteThroughput
  • FreeableMemory (캐시 부족 → 읽기 IOPS 폭증)
  • CPUUtilization (CPU가 낮은데 latency/IOPS만 높으면 디스크 병목 가능)
  • DatabaseConnections (커넥션 폭증 → 쿼리 폭증/락 경합)
  • Enhanced Monitoring 사용 시 OSDiskQueueDepth(가능한 경우)

패턴 해석 예시

  • ReadIOPS↑ + FreeableMemory↓ + ReadLatency↑ → 버퍼 캐시 부족(working set이 메모리 초과) 또는 비효율 쿼리
  • WriteIOPS↑ + WriteLatency↑ + CPU는 보통 → 체크포인트/WAL/대량 업데이트

2) RDS에서 “누가 느린가?”: pg_stat_statements로 상위 쿼리 잡기

RDS PostgreSQL에서 슬로우 쿼리를 찾는 가장 실전적인 방법은 pg_stat_statements입니다.

2.1 pg_stat_statements 활성화 (RDS Parameter Group)

  • shared_preload_libraries = 'pg_stat_statements'
  • pg_stat_statements.track = all
  • 적용 후 재부팅 필요

확인 및 extension 생성:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT name, setting
FROM pg_settings
WHERE name IN ('shared_preload_libraries', 'pg_stat_statements.track');

2.2 “느림”을 총합/평균/IO 관점으로 상위 정렬

총 시간 기준(서비스 전체에 가장 큰 피해):

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows,
  left(regexp_replace(query, '\s+', ' ', 'g'), 160) AS sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

IO를 직접적으로 의심할 때(버퍼 히트 vs 디스크 읽기):

SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  shared_blks_hit,
  shared_blks_read,
  round((shared_blks_read::numeric / NULLIF(shared_blks_hit + shared_blks_read, 0)) * 100, 2) AS read_pct,
  left(regexp_replace(query, '\s+', ' ', 'g'), 160) AS sample
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;
  • shared_blks_read가 큰 쿼리는 실제 디스크 읽기를 유발할 확률이 높습니다.
  • read_pct가 높으면 캐시 히트가 낮아 IOPS 폭증과 연결되기 쉽습니다.

3) EXPLAIN(ANALYZE, BUFFERS)로 “IOPS 유발자”를 증명

pg_stat_statements에서 용의자를 찾았으면 실행 계획으로 확정합니다.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...
FROM ...
WHERE ...;

여기서 핵심은:

  • Seq Scan / Bitmap Heap Scan이 과도한지
  • Buffers: shared read=...가 큰지(디스크 읽기)
  • Rows Removed by Filter가 큰지(필터가 늦게 적용됨)
  • Nested Loop로 큰 테이블을 반복 조회하는지

흔한 케이스 1: 인덱스가 없거나, 조건이 인덱스를 못 타는 경우

예: WHERE created_at::date = '2026-02-23' 같은 형태는 인덱스를 망칩니다.

  • 해결: 표현식을 제거하거나, 표현식 인덱스를 고려
-- 나쁜 예: 컬럼에 함수 적용
SELECT * FROM orders WHERE created_at::date = CURRENT_DATE;

-- 개선: 범위 조건
SELECT * FROM orders
WHERE created_at >= date_trunc('day', now())
  AND created_at <  date_trunc('day', now()) + interval '1 day';

-- 또는 표현식 인덱스
CREATE INDEX CONCURRENTLY idx_orders_created_at_date
ON orders ((created_at::date));

흔한 케이스 2: LIMIT가 있어도 정렬 때문에 디스크를 읽는 경우

SELECT *
FROM events
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 50;

인덱스가 없으면 Sort + 큰 범위 스캔이 발생합니다.

CREATE INDEX CONCURRENTLY idx_events_user_created_desc
ON events (user_id, created_at DESC);

4) IOPS 폭증의 진짜 주범: 체크포인트, VACUUM, 대량 UPDATE/DELETE

슬로우 쿼리만이 IOPS의 원인은 아닙니다. 특히 WriteIOPS가 튈 때는 아래를 의심합니다.

4.1 체크포인트(checkpoint)로 인한 쓰기 폭발

체크포인트가 자주/급격히 발생하면 dirty page flush로 디스크 쓰기가 폭증합니다.

체크포인트 상태 확인:

SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_checkpoint,
  buffers_clean,
  maxwritten_clean
FROM pg_stat_bgwriter;
  • checkpoints_req가 빠르게 증가하면 WAL이 차서 강제 체크포인트가 발생했을 가능성이 있습니다.

RDS Parameter Group에서 완화에 자주 쓰는 값(워크로드에 따라 조정 필요):

  • checkpoint_timeout 증가(예: 5min → 15min)
  • max_wal_size 증가(체크포인트 빈도 감소)
  • checkpoint_completion_target 0.9 수준(쓰기 분산)

> 주의: 값만 올리면 “나중에 더 크게 터지는” 형태가 될 수 있어, WriteLatency/IOPS 패턴을 보며 점진 조정이 안전합니다.

4.2 VACUUM/Autovacuum이 밀려서 발생하는 폭증

Dead tuple이 쌓이면 쿼리도 느려지고, 나중에 vacuum이 한 번에 돌며 IO가 폭발합니다.

테이블별 vacuum 상태:

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_vacuum,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

특정 테이블에 autovacuum 튜닝(예: update가 많은 대형 테이블):

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_threshold = 5000
);
  • scale_factor를 낮추면 더 자주 조금씩 청소해서 IO 스파이크를 완화할 수 있습니다.

4.3 대량 UPDATE/DELETE가 만드는 WAL + bloat

  • 대량 UPDATE는 사실상 “쓰기 + 인덱스 재작성 + WAL 증가”입니다.
  • 특히 인덱스가 많은 테이블은 WriteIOPS가 급격히 증가합니다.

가능하면:

  • 배치 작업은 작게 쪼개서 실행
  • hot row를 줄이도록 파티셔닝 고려

배치 쪼개기 예시:

-- 예: 오래된 데이터 soft delete를 5천 건씩 반복
WITH cte AS (
  SELECT id
  FROM events
  WHERE created_at < now() - interval '90 days'
    AND deleted_at IS NULL
  ORDER BY id
  LIMIT 5000
)
UPDATE events e
SET deleted_at = now()
FROM cte
WHERE e.id = cte.id;

5) 커넥션 폭증과 락 경합이 IOPS를 키우는 방식

커넥션이 과도하면 동일 쿼리가 동시에 실행되며 캐시가 흔들리고, 디스크 read가 늘어납니다. 또한 락 대기로 인해 트랜잭션이 길어지면 vacuum이 밀리며 악순환이 생깁니다.

5.1 락/대기 원인 확인

SELECT
  a.pid,
  a.usename,
  a.state,
  a.wait_event_type,
  a.wait_event,
  now() - a.query_start AS running_for,
  left(a.query, 120) AS query
FROM pg_stat_activity a
WHERE a.state <> 'idle'
ORDER BY running_for DESC
LIMIT 30;

블로킹 체인 확인:

SELECT
  blocked.pid AS blocked_pid,
  blocking.pid AS blocking_pid,
  blocked.query AS blocked_query,
  blocking.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 AND blocking_locks.pid <> blocked_locks.pid
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

5.2 애플리케이션 레벨: PgBouncer로 커넥션 풀링

RDS에서 커넥션 수가 늘면 메모리도 줄고 캐시 효율도 떨어집니다. 트래픽이 많은 서비스라면 PgBouncer(트랜잭션 풀링)를 고려할 가치가 큽니다.

6) 스토리지(gp3/io2)와 파라미터: “버티기”가 아니라 “근본 개선”

쿼리/인덱스 개선이 1순위지만, 당장 장애를 막기 위해 스토리지를 조정해야 할 때가 있습니다.

6.1 gp3: IOPS/Throughput을 분리해서 올릴 수 있음

  • gp2는 용량에 따라 IOPS가 따라오지만, gp3는 IOPS/Throughput을 독립적으로 설정합니다.
  • IOPS만 올리고 처리량이 낮으면 작은 랜덤 I/O에는 효과가 있지만, 큰 스캔/백업/배치엔 throughput도 같이 봐야 합니다.

6.2 io2: 일관된 고IOPS가 필요할 때

  • 금융/결제처럼 latency 변동에 민감하고, 지속적으로 높은 IOPS가 필요하면 io2가 맞는 경우가 있습니다.

6.3 Parameter 튜닝에서 자주 놓치는 것

  • work_mem: 정렬/해시가 디스크로 떨어지는지에 영향(단, 커넥션 수 * work_mem 폭발 주의)
  • effective_cache_size: 플래너가 캐시를 얼마나 믿을지(과소 설정 시 잘못된 계획)
  • shared_buffers: RDS 권장 범위 내에서 조정(무작정 크게는 금물)

정렬이 디스크로 떨어졌는지 확인(세션 단위):

SET track_io_timing = on;
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ORDER BY ...;

7) 재발 방지 체크리스트(운영 루틴)

7.1 “IOPS 폭증” 알람을 쪼개서 건다

  • ReadIOPS, WriteIOPS를 분리
  • Latency(Read/Write)도 함께 알람
  • FreeableMemory 하락 알람(캐시 붕괴 조기 탐지)

7.2 슬로우 쿼리 수집을 상시화

  • pg_stat_statements 상시 활성화
  • 배포 전후 total_exec_time, shared_blks_read 상위 쿼리 비교

7.3 쿼리 변경은 “실행 계획”을 같이 리뷰

  • PR에 EXPLAIN(가능하면 EXPLAIN ANALYZE) 첨부
  • 인덱스 추가 시 CREATE INDEX CONCURRENTLY로 락 최소화
CREATE INDEX CONCURRENTLY idx_orders_user_id_created_at
ON orders (user_id, created_at DESC);

8) 장애 상황에서의 실전 대응 순서(요약)

  1. CloudWatch에서 Read/Write IOPS와 Latency를 분리해서 본다.
  2. pg_stat_statements로 shared_blks_read 상위 쿼리를 찾는다.
  3. 해당 쿼리를 EXPLAIN (ANALYZE, BUFFERS)로 확인해 Seq Scan/Sort/Join 폭탄을 잡는다.
  4. pg_stat_bgwriter, pg_stat_user_tables로 checkpoint/vacuum 폭증 여부를 확인한다.
  5. 급하면 gp3 IOPS/throughput 상향 또는 인스턴스 스케일업으로 숨을 쉬고, 근본은 쿼리/인덱스/배치 쪼개기로 해결한다.

참고: “체크리스트 기반 디버깅” 접근법

이런 유형의 문제는 감으로 때리기보다, 지표 → 용의자 쿼리 → 실행 계획 → 쓰기/청소 작업 순서로 좁히는 게 재현성과 속도가 좋습니다. 비슷한 방식의 체크리스트형 트러블슈팅 글로는 AWS ALB 502·504 난사 - 원인별 해결 체크리스트, Kubernetes CrashLoopBackOff 원인별 로그·Probe·리소스 디버깅도 함께 참고하면 “증상→원인 분해” 흐름을 잡는 데 도움이 됩니다.

마무리

RDS PostgreSQL의 IOPS 폭증은 대개 (1) 캐시 미스가 큰 비효율 쿼리, (2) 정렬/조인/스캔이 인덱스를 못 타는 설계, (3) 체크포인트·VACUUM·대량 쓰기 작업 중 하나(혹은 복합)로 설명됩니다. 핵심은 “IOPS가 튄다”를 하나의 현상으로 보지 말고, 읽기/쓰기/지연/캐시/청소 작업으로 분해해 원인을 고립시키는 것입니다.

원하시면 현재 상황의

  • CloudWatch 스크린샷(지표 6~8개)
  • pg_stat_statements 상위 20개 결과
  • 문제 쿼리 1~2개와 EXPLAIN (ANALYZE, BUFFERS)

를 기준으로, 케이스별로 어떤 인덱스/파라미터/배치 전략이 최선인지 더 구체적으로 같이 잡아드릴 수 있습니다.

Discuss on Twitter • View on GitHub