쿼리 성능 저하로 인한 서버 다운 방지: 데이터베이스 복구 전략과 최적화 방법

데이터베이스는 서비스의 심장입니다. 하지만 트래픽이 몰리거나 비효율적인 쿼리가 누적되면, 순간적으로 CPU와 I/O가 포화되고 연결 풀(Connection Pool)이 고갈되어 서버가 다운될 수 있습니다. 이 글에서는 쿼리 성능 저하로부터 서버를 보호하기 위한 즉각 대응 플레북, 근본 원인을 해결하는 최적화 방법, 그리고 최악의 상황에서 빠르게 복구할 수 있는 데이터베이스 복구 전략을 실전 중심으로 정리했습니다.

왜 쿼리 성능 저하가 서버 다운으로 이어질까

병목의 연쇄 작용

• 비효율적인 쿼리 → CPU/메모리/I/O 급증 → 대기열 증가 → 연결 고갈 → 타임아웃 → 재시도 폭주 → 서버 다운
• 긴 트랜잭션/락 경합 → 다른 트랜잭션이 줄줄이 대기 → 전체 응답 시간 급증 → 장애 전파

흔한 원인

• 인덱스 미비로 인한 풀 스캔, 광범위한 LIKE, 함수 기반 필터
• OFFSET/LIMIT 기반의 깊은 페이지네이션
• N+1 쿼리 패턴(ORM에서 빈번)
• 카디널리티 오차로 인한 잘못된 실행 계획
• 통계 부재/노후화(ANALYZE 부족)와 Autovacuum 튜닝 미흡(PostgreSQL)
• 과도한 동시성, 긴 트랜잭션, 불필요한 잠금 범위
• 대규모 배치/백필(backfill)을 주간 피크 시간에 실행
• 쿼리 캐시/애플리케이션 캐시 미활용

관측 가능한 신호

• p95/p99 DB 응답 시간 급등
• 활성 연결 수/대기 연결 수 급증
• CPU, Disk I/O, 체크포인트 빈도 증가
• Cache hit ratio 하락(Buffer Pool/Shared Buffers)
• Lock wait, deadlock, replication lag 증가
• 슬로우 쿼리 로그 폭증

장애 징후 시 즉각 대응 플레북

다음 순서는 “서비스 보호 → 근본 원인 식별 → 점진적 정상화”에 초점이 있습니다.

1. 서비스 보호

• 연결 폭주 방지: 애플리케이션 Connection Pool 상한을 낮추고 재시도에 지수 백오프 적용
• 타임아웃 상한: DB 쿼리 타임아웃을 적극적으로 설정
  • PostgreSQL: statement_timeout, lock_timeout
  • MySQL: max_execution_time(쿼리 힌트), innodb_lock_wait_timeout
• Read 트래픽 분산: 읽기 요청을 읽기 전용 Replica로 급히 스위치
• 배치/리포트 일시 중단: 무거운 배치 Job과 대규모 집계를 중단하거나 오프피크로 재스케줄

2. 병목 차단

• 긴 쿼리 종료:
  • PostgreSQL: pg_terminate_backend(pid)
  • MySQL: KILL QUERY <thread_id>;
• 슬로우 쿼리 캡처 활성화:
  • PostgreSQL: log_min_duration_statement = 200ms (예)
  • MySQL: slow_query_log = ON, long_query_time = 0.2
• 쿼리 임시 리라이팅: 위험한 OFFSET/LIMIT, 와일드카드 LIKE, 큰 IN 리스트 쿼리를 임시 차단 또는 캐시로 우회

3. 근본 원인 규명

• 실행 계획 수집(EXPLAIN/EXPLAIN ANALYZE)
• 잠금 그래프 확인(pg_locks, INFORMATION_SCHEMA.INNODB_LOCKS)
• 통계 최신화(ANALYZE), 테이블/인덱스 상태 확인(bloat, cardinality)

4. 점진적 정상화

• 고비용 쿼리 최적화 또는 인덱스 추가
• 캐시 레이어 강화(앱/Redis/프리컴퓨트)
• 트래픽 정상화 후 타임아웃 및 풀 사이즈 원복

쿼리 최적화: 반드시 효과를 내는 방법들

1. 실행 계획 읽기와 재현

• 우선 EXPLAIN으로 현재 쿼리가 “어떻게” 실행되는지 확인합니다.
• 예시(PostgreSQL):

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, user_id, created_at
FROM orders
WHERE user_id = 123 AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

• 확인 포인트
  • Seq Scan(전체 스캔)인지 Index Scan/Index Only Scan인지
  • 예상/실제 로우 수 괴리(통계 부정확성)
  • 필터가 인덱스에 잘 걸리는지(선행 컬럼, 선택도)
  • 정렬/해시 메모리 스필 여부

2. 인덱싱 전략

• 합성 인덱스와 컬럼 순서
  • WHERE와 ORDER BY에 동시에 쓰이는 컬럼 조합을 우선 고려
  • “선택도가 높은 컬럼”을 앞쪽에 두되, 정렬 컬럼과 균형 있게 설계
• 커버링 인덱스(Index Only Scan)
  • 쿼리가 참조하는 모든 컬럼을 인덱스에 포함해 테이블 접근 최소화
• 부분(부분적) 인덱스/필터 인덱스
  • 부분 범위만 자주 조회되는 경우 유리
  • PostgreSQL: WHERE 절을 가진 PARTIAL INDEX
• 특수 인덱스
  • PostgreSQL: GIN/GiST(JSONB, Full Text, 배열)
  • MySQL: InnoDB는 B-Tree, Fulltext 인덱스(문서 검색)
• 과도한 인덱스는 쓰기 성능 저하와 저장 공간 낭비를 유발. 삭제 후보를 주기적으로 평가.

예시: 최근 7일 주문만 자주 조회하는 경우

-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC) WHERE created_at >= now() - interval '30 days';

3. 페이지네이션 최적화

• OFFSET/LIMIT는 깊은 페이지로 갈수록 선형 비용이 증가합니다.
• 키셋 페이지네이션(Seek Method)으로 전환:

-- 마지막으로 본 created_at, id를 기준으로 다음 페이지 조회
SELECT id, user_id, created_at
FROM orders
WHERE user_id = 123
  AND (created_at, id) < (TIMESTAMP '2025-01-01 00:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 50;

• 인덱스: (user_id, created_at DESC, id DESC)

4. 조인과 데이터 접근 패턴

• 조인 전에 필터링: 서브쿼리/CTE로 범위를 줄이고 조인
• 필요한 컬럼만 선택: SELECT * 지양
• 조인 타입 선택: 해시 조인 vs 중첩 루프 vs 머지 조인, 데이터 분포와 인덱스 유무로 결정
• 빈번한 집계는 물리화: Materialized View/요약 테이블로 캐시하고 오프피크에 리프레시

예시: 자주 쓰는 대시보드 카운터

CREATE MATERIALIZED VIEW mv_daily_orders AS
SELECT date_trunc('day', created_at) AS day, count(*) AS cnt
FROM orders
GROUP BY 1;
-- 주기적 리프레시
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_orders;

5. N+1 쿼리 제거(ORM 주의)

• Eager loading, JOIN, WHERE IN으로 한 번에 가져오기
• GraphQL/REST에서 데이터 로더 패턴(DataLoader)로 배치

예시:

-- 비효율적: 유저 100명 루프하며 각자 주문 조회 (N+1)
-- 개선: 한 번에 묶어서 조회
SELECT u.id, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN ( ... 100 ids ... );

6. 실행 계획 안정화

• 통계 최신화: ANALYZE; Autovacuum 튜닝(작업량/지연/임계치)
• 파라미터 스니핑 이슈(특정 바운드 값에 최적화된 계획):
  • 바인드 변수/힌트/파티셔닝으로 분포 편차를 흡수
  • PostgreSQL: enable_nestloop/hashjoin 등 힌트 익스텐션 고려
• 계획 캐시와 Prepare Statement를 균형 있게 사용

7. 트랜잭션/락 전략

• 트랜잭션은 짧고 단순하게. 사용자 상호작용 사이에 트랜잭션 유지 금지
• 필요 최소 범위만 잠금(SELECT FOR UPDATE SKIP LOCKED 등)
• 격리 수준 조정: 과도한 SERIALIZABLE은 경합을 늘림. 기본은 READ COMMITTED/REPEATABLE READ 검토

8. 대량 처리: 배치와 백필

• 작은 청크로 나눠 처리(예: 1,000~10,000 행 단위), 각 청크 사이 sleep으로 I/O 완충
• 오프피크 시간대 실행, 쿼리 우선순위 낮추기
• Upsert/배치 Insert 사용

-- PostgreSQL upsert
INSERT INTO products(id, name, price)
VALUES ...
ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price;

복구 전략: 다운타임을 최소화하는 백업과 페일오버

RTO와 RPO 정의

• RTO(Recovery Time Objective): 복구까지 허용 가능한 최대 시간
• RPO(Recovery Point Objective): 허용 가능한 데이터 손실 범위(초/분)

서비스 성격에 따라 목표를 수치화하고, 그에 맞춘 백업/복구 체계를 설계해야 합니다.

백업 전략

1. 물리 백업 vs 논리 백업

• 물리(파일 단위): 빠르고 복구가 신속. PostgreSQL basebackup, MySQL XtraBackup 등
• 논리(SQL 덤프): 이식성 높고 테이블/스키마 단위 복구 용이. 대규모 데이터셋에는 느릴 수 있음.

2. 증분 백업과 PITR(Point-in-Time Recovery)

• 트랜잭션 로그(WAL/binlog/redo)를 보관해 특정 시점으로 되감기
• 보관/암호화/무결성 검증 필수

3. 백업 검증

• 복구 테스트를 자동화(CI 파이프라인에서 샘플 복구), 체크섬 비교, 랜덤 레코드 샘플링

예시(PostgreSQL PITR)

• 설정: archive_mode = on, archive_command 지정
• 복구: base backup + WAL 로그 리플레이, recovery.signal와 recovery_target_time 사용

예시(MySQL PITR)

• 바이너리 로그 활성화: log_bin=ON
• 복구: 풀 백업 복원 후 mysqlbinlog로 특정 시점까지 플레이

복제와 자동 페일오버

• 비동기 복제: 성능 유리, 단 RPO > 0 가능성(데이터 유실)
• 동기 복제: RPO 0 가능, 쓰기 지연 증가 가능
• 장애 시나리오
  • 마스터(Primary) 장애 → Replica 승격(Promotion)
  • 자동화 도구: PostgreSQL Patroni/pg_auto_failover, MySQL Orchestrator 등
• 복제 지연 모니터링과 읽기 트래픽 분산 정책
• 크로스 리전 DR: 다른 리전에 비동기 복제, DNS/프로시 라우팅으로 재해 대비

스키마 변경과 배포 전략

• 온라인 스키마 변경
  • MySQL: gh-ost, pt-online-schema-change
  • PostgreSQL: CONCURRENTLY 옵션으로 인덱스 생성
• 이중 쓰기/이중 읽기(Double-write/Double-read)
  • 마이그레이션 중 구/신 스키마 병행 운영, 백필로 데이터 정합성 확보
• 배포 전략
  • 블루/그린 또는 카나리 배포로 위험 분산
  • 스키마는 선 배포(확장적 변경 → 코드 → 축소적 변경)

운영 튜닝과 용량 계획

커넥션과 스레드 풀 관리

• DB는 “소수 정예” 연결에 강함. 애플리케이션 당 커넥션 풀을 작은 값으로 제한하고 큐잉을 애플리케이션에서 처리
• 간단한 산식(예시)
  • DB 동시 처리 가능한 워커 수 ≈ CPU 코어 × 2~4
  • 전체 연결 상한 ≈ 워커 수 + 여유치(관리/복제/백업)

메모리/버퍼 튜닝

• PostgreSQL
  • shared_buffers: RAM의 25% 내외
  • effective_cache_size: OS 캐시 고려(예: RAM의 50~75%)
  • work_mem: 정렬/해시 1연산 당 메모리. 과도하면 OOM 위험
  • maintenance_work_mem: 인덱스 생성/VACUUM에 사용
• MySQL(InnoDB)
  • innodb_buffer_pool_size: RAM의 50~75%
  • innodb_log_file_size/flush_log_at_trx_commit 등 쓰기 패턴에 맞춤

I/O와 스토리지

• NVMe SSD, WAL/redo 로그 디스크 분리
• 파일시스템 옵션, 체크포인트 튜닝, fsync 신뢰성 확보
• 모니터링: IOPS, 대기 시간, 큐 깊이

Autovacuum와 Bloat 관리(PostgreSQL)

• autovacuum_vacuum_scale_factor/threshold 조정으로 대형 테이블 민감도 향상
• table bloat 모니터링, 필요 시 VACUUM FULL/pg_repack
• fillfactor로 HOT 업데이트 유도

병렬성과 리소스 거버넌스

• PostgreSQL: max_parallel_workers_per_gather, max_worker_processes
• MySQL: innodb_thread_concurrency, resource groups
• 쿼리 우선순위/리소스 제한으로 대형 리포트가 OLTP에 영향 주지 않게 분리

모니터링과 얼럿 설계

핵심 메트릭

• 성능
  • 평균/장애구간 p95/p99 응답 시간
  • 활성/대기 연결 수, 큐 대기 시간
  • Cache hit ratio(Buffer Pool/Shared Buffers)
  • Temp file/Temp table 생성량(메모리 스필 지표)
• 안정성
  • Deadlock 수, Lock wait 시간
  • 체크포인트 빈도/시간, autovacuum 작업량
  • 복제 지연(replication lag)
• 용량
  • 디스크 사용량/WAL 증가량
  • 인덱스/테이블 크기 변화, bloat 추정

슬로우 쿼리 로깅 예시

PostgreSQL:

ALTER SYSTEM SET log_min_duration_statement = '200ms';
SELECT pg_reload_conf();

MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;

경보 임계치 예시

• p99 DB 응답 시간 > 800ms (5분 지속)
• 사용 가능한 연결 < 10% (1분 지속)
• 복제 지연 > 10초 (읽기 분산 사용 시)
• WAL/redo 증가율 급등(평시 대비 2배 이상 10분 지속)

추적/프로파일링

• APM으로 쿼리별 트레이스, N+1 탐지
• 샘플링 기반 프로파일러로 상위 10개 핫 쿼리 주기적 리포트

실전 시나리오: OFFSET/LIMIT로 인한 실서버 장애

상황

• 모바일 피드 API에서 OFFSET/LIMIT로 페이지네이션
• 트래픽 증가와 함께 OFFSET 50,000 이상 요청 다수
• CPU 100%, 활성 연결치 급증, p99 5초 상회

대응

1. 즉시

• statement_timeout 1.5s 설정, 긴 쿼리 강제 종료
• 모바일 앱 API 게이트웨이에 스로틀링 룰 적용
• 슬로우 쿼리 로깅과 상위 쿼리 추출

2. 원인 해결

• 키셋 페이지네이션으로 전환
• (user_id, created_at DESC, id DESC) 커버링 인덱스 추가(CONCURRENTLY)
• ORM 설정으로 N+1 사전 로딩 활성화
• API 응답 캐시(사용자별 첫 페이지 30초 캐시)

3. 사후

• p99 300ms로 회복, DB CPU 40% 이하
• 연휴 대비 리허설: 트래픽 2배 부하 테스트, 장애 대응 롤플레이

복구 연습: Postgres PITR 간단 예시

사전 준비

• postgresql.conf
  • archive_mode = on
  • archive_command = 'cp %p /mnt/archived_wal/%f'
• 정기 basebackup: pg_basebackup 스케줄링

사고 발생(오후 2:45 잘못된 DELETE)

• 대상 시점: 14:44:50

복구 절차(새 인스턴스)

1. 최신 basebackup 복원
2. 복구 신호 파일 생성(recovery.signal)
3. postgresql.auto.conf에 복구 목표 설정

restore_command = 'cp /mnt/archived_wal/%f %p'
recovery_target_time = '2025-01-01 14:44:50+09'

4. 기동 후 “복구 완료” 확인, 읽기 전용 검증
5. 애플리케이션을 신규 인스턴스로 스위치(커넥션 문자열/프록시 업데이트)

교훈

• 아카이브 저장소 접근성, 권한, 용량, 성능까지 사전에 검증
• 복구 리허설 없이는 RTO/RPO 달성이 어렵다

보안과 거버넌스

• 최소 권한 원칙: 운영 쿼리/배치 작업 계정 분리
• 감사 로그/데이터 변경 이력 테이블(soft delete, change log)
• PII 열은 인덱싱 전략과 함께 암호화/마스킹 고려(성능 영향 감안)
• 백업 암호화, 키 관리, 전송 구간 암호화(TLS)

환경별 가이드: 개발/스테이징/운영

• 개발: 프로파일링을 습관화. EXPLAIN을 PR 리뷰에 포함
• 스테이징: 프로덕션과 동일한 쿼리 플랜을 위해 유사 데이터 분포로 샘플링/합성
• 운영: 기능 플래그로 쿼리 변경 롤백 경로 확보. 신규 인덱스는 CONCURRENTLY/ONLINE, 카나리 릴리스로 영향 최소화

체크리스트: 오늘 바로 적용할 것

성능 보호

• statement_timeout/lock_timeout(또는 max_execution_time) 설정
• Connection Pool 상한과 큐잉 전략 점검(재시도 백오프)
• 슬로우 쿼리 로그 상시 활성화/수집

쿼리 최적화

• 상위 10개 비용 쿼리에서 EXPLAIN(ANALYZE) 실행
• 합성/커버링/부분 인덱스 도입 여부 검토
• OFFSET→키셋 페이지네이션 전환 계획
• ORM N+1 탐지 및 Eager loading 정책

운영 튜닝

• Autovacuum/통계 갱신 빈도 조정(PostgreSQL)
• Buffer Pool/Shared Buffers/Work_mem 조율
• 읽기 Replica로 리포팅 분리

복구/DR

• 정기 백업 + 증분 로그 보관 + 무결성 검증
• 분기별 복구 리허설(PITR/페일오버)
• RTO/RPO 문서화 및 모니터링 대시보드

모니터링

• p95/p99, 활성 연결, Lock wait, Replication lag 대시보드
• 이상 징후 알람 임계치 설정 및 온콜 룰

자주 쓰는 진단 쿼리/명령 모음

PostgreSQL

-- 락 확인
SELECT pid, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

-- 인덱스/시퀀스 사용 통계
SELECT relname, idx_scan, seq_scan
FROM pg_stat_user_tables
ORDER BY idx_scan + seq_scan DESC;

-- Autovacuum 상태
SELECT relname, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

-- 긴 쿼리 종료
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE now() - query_start > interval '5 seconds';

MySQL

-- 현재 쿼리
SHOW PROCESSLIST;

-- InnoDB 락
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- 슬로우 로그 설정(세션/글로벌)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.2;

-- 긴 쿼리 종료
KILL QUERY <thread_id>;

마무리: 예방, 대응, 복구의 삼박자

서버 다운을 막는 가장 강력한 방법은 예방과 설계입니다. 쿼리를 설계할 때부터 키셋 페이지네이션, 적절한 인덱싱, 짧은 트랜잭션, 캐시 활용을 기본값으로 삼으세요. 장애가 시작되면 즉각적인 보호 조치(타임아웃, 풀 제한, 슬로우 쿼리 캡처)로 확산을 차단하고, 근본 원인을 빠르게 식별해 고비용 쿼리를 최적화합니다. 그리고 정기 백업과 복구 리허설, 자동 페일오버 체계를 갖춰 “설령” 문제가 터져도 짧은 시간 안에 안전하게 되돌릴 수 있어야 합니다.

오늘 당장 슬로우 쿼리 로그를 켜고, 상위 10개 쿼리에 EXPLAIN을 적용해 보세요. 다음 스프린트에서는 키셋 페이지네이션과 핵심 인덱스 보강, 그리고 복구 리허설을 계획에 넣으세요. 작은 습관의 누적이 서버 다운을 막는 가장 확실한 보험입니다.