DB 성능 문제는 어디서 시작되는가

DB 성능 문제는 대부분 서버 스펙 부족이 아니다. CPU, 메모리, 네트워크가 정상인데 P99 응답 시간이 200ms에서 3초로 치솟는 상황의 원인은 거의 항상 쿼리 레벨에 있다. 그렇다면 어떤 쿼리가 문제이고, 그 쿼리는 왜 느린가?

총 비용이 가장 큰 쿼리를 찾아라

Slow Query 분석의 흔한 착각은 “임계값을 초과한 쿼리만 문제”라는 믿음이다. long_query_time = 1초로 설정하면 0.5초짜리 쿼리는 기록되지 않는다. 하지만 0.5초 쿼리가 초당 1000번 실행된다면 DB가 이 쿼리에만 쏟는 시간은 초당 500초다.

진짜 지표는 SUM_TIMER_WAIT — 총 소비 시간이다.

SELECT
    LEFT(DIGEST_TEXT, 100) AS query_pattern,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1e9, 2) AS avg_ms,
    ROUND(SUM_TIMER_WAIT / 1e9, 2) AS total_ms,
    ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 0) AS examine_send_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND COUNT_STAR > 10
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

examine_send_ratio가 1000을 넘는다면 10만 행을 읽어 100행을 반환하고 있다는 뜻이다. 인덱스가 없거나 함수 조건으로 인덱스가 무력화된 경우다. 이 비율이 높은 쿼리에 인덱스를 추가하면 실행 시간이 수백 배 단축되기도 한다.

N+1 — 보이지 않기 때문에 더 위험하다

N+1 문제는 코드에서 전혀 눈에 띄지 않는다. order.getUser().getName() 한 줄이 루프 안에 있으면, 주문 100건을 조회할 때 사용자 테이블에 100번의 추가 쿼리가 발생한다. 개발 환경에서 데이터가 10건이면 11번 쿼리는 눈에 띄지 않는다. 프로덕션에서 초당 10명이 주문 목록을 조회하면 초당 1010번의 쿼리가 된다.

performance_schema에서 N+1은 명확한 패턴을 보인다.

DIGEST_TEXT: SELECT * FROM users WHERE id = ?
COUNT_STAR:  10,000   ← 같은 패턴이 1만 번
AVG_TIMER_WAIT: 3ms
SUM_TIMER_WAIT: 30,000ms

해결 전략은 관계 유형에 따라 다르다. N:1 또는 1:1 관계는 Fetch Join 또는 @EntityGraph로 1번의 JOIN 쿼리로 해결한다. 1:N 컬렉션은 Fetch Join에 페이징을 함께 쓸 수 없어서(메모리 페이징 위험) Batch Size가 올바른 대안이다.

spring.jpa.properties.hibernate.default_batch_fetch_size: 100

이 설정 하나로 N번의 단건 조회가 WHERE order_id IN (1, 2, ..., 100) 한 번으로 바뀐다. 코드 수정 없이 대부분의 N+1을 완화하는 가장 빠른 첫 조치다.

OFFSET은 버리는 행 수다

LIMIT 10 OFFSET 100000은 100010행을 읽고 100000행을 버린 뒤 10행을 반환한다. OFFSET이 클수록 비용은 선형으로 증가한다.

LIMIT 10 OFFSET 0:        0.001s
LIMIT 10 OFFSET 100000:   0.5s
LIMIT 10 OFFSET 1000000:  5.0s

커서 기반 페이징은 이 문제를 근본적으로 해결한다. WHERE id > last_id ORDER BY id LIMIT 10은 B-Tree에서 last_id 이후 위치로 직접 이동해 10행만 읽는다. 100만 번째 페이지나 1번째 페이지나 실행 시간이 같다.

배치 처리에서 OFFSET을 쓰면 O(N²)이 된다. 1000페이지를 순회하면 마지막 페이지에서 100만 행을 읽고 버린다. 배치에서는 반드시 커서 기반을 써야 한다.

페이지 번호 이동 UI가 필요한 관리자 기능에서는 지연 JOIN이 절충안이다. 서브쿼리로 ID만 먼저 추출(Covering Index 활용)하고 외부 쿼리로 나머지 컬럼을 조회하면 직접 OFFSET 대비 2~5배 빠르다.

파티셔닝은 파티션 키 조건이 있어야 효과가 있다

단일 테이블이 수억 건을 넘으면 인덱스가 있어도 버퍼 풀에 모든 Leaf Node를 올릴 수 없어 디스크 I/O가 증가한다. Range 파티셔닝은 이 문제를 특정 범위 쿼리에서 해결한다.

PARTITION BY RANGE (TO_DAYS(order_date)) (
    PARTITION p2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
    PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31' — 파티션 키가 조건에 포함되면 p2024만 스캔한다(Partition Pruning). 조건에 포함되지 않으면 모든 파티션을 스캔한다. 파티셔닝이 오히려 오버헤드가 되는 상황이다.

Connection Pool — 풀 크기보다 쿼리 속도가 먼저다

HikariPool-1 - Connection is not available, request timed out after 30000ms 에러는 DB 서버 다운이 아니다. Pool의 모든 Connection이 사용 중이라 새 요청이 30초를 기다리다 포기한 것이다.

HikariCP가 권장하는 Pool 크기 공식은 단순하다.

Pool Size = Ncpu × 2 + Neff_disk

AWS RDS db.m5.large(2 vCPU)이면 2 × 2 + 1 = 5. 놀랍게 작아 보이지만 DB CPU가 병목이면 Connection을 늘려도 처리량은 같고 Context Switching 오버헤드만 증가한다.

Connection Pool이 포화되는 두 번째 원인은 트랜잭션 내 외부 호출이다.

@Transactional
public void placeOrder(OrderRequest req) {
    Product product = productRepo.findById(req.getProductId());
    paymentGateway.pay(req.getUserId(), req.getAmount());  // 3초!
    orderRepo.save(new Order(product));
}

이 메서드는 시작부터 끝까지 Connection을 붙잡는다. 결제 API 3초 동안 Connection이 점유된다. Pool 10개면 초당 최대 3.3 TPS만 처리된다. 트랜잭션 범위를 DB 작업만으로 축소하면 Connection 점유 시간이 50ms로 줄고 처리량이 60배 증가한다.

정리

• SUM_TIMER_WAIT 기준으로 총 비용이 가장 큰 쿼리를 찾는다. 빠른 쿼리도 빈도가 높으면 진짜 병목이다.
• N+1은 performance_schema에서 COUNT_STAR 높고 AVG 낮은 패턴으로 드러난다. default_batch_fetch_size=100이 가장 빠른 첫 조치다.
• 배치 처리와 무한 스크롤에서 OFFSET은 금지다. 커서 기반 페이징으로 O(log N)을 유지한다.
• 파티셔닝은 파티션 키가 WHERE 조건에 있어야 효과가 있고, 오래된 데이터 DROP에서 가장 빛난다.
• Connection Pool 포화의 근본 해결은 Pool 크기 증가가 아니라 Slow Query 개선과 트랜잭션 범위 축소다.

다음 글에서는 MySQL Replication의 Binlog 구조와 Read Replica 지연이 발생하는 원리를 추적한다.