MySQL 심화 튜닝 — InnoDB 내부부터 JPA 연계까지

야간 3시. 모니터링 알람이 울렸다. “DB 평균 응답 8,200ms”. 평소 80ms 수준이었다. 인덱스는 걸려 있다. 코드 배포도 없었다. 무엇부터 봐야 하나.

[WHY] 왜 애플리케이션 코드보다 DB 튜닝의 ROI가 큰가. 애플리케이션 단의 최적화는 보통 전체 응답 시간의 일부를 깎는다. 반면 느린 쿼리 하나가 전체 요청의 지연을 좌우하는 경우가 흔하다 — 인덱스를 못 타는 쿼리 하나가 초 단위로 늘어지면 그 위에 어떤 캐시·비동기 처리를 얹어도 근본 병목은 그대로다. 인덱스 컬럼 순서 한 줄을 바꿔 스캔 행 수가 수백만에서 수백으로 줄면 응답이 수십 배 빨라지기도 한다. 같은 노력 대비 효과가 가장 큰 지점이 DB라서, 가장 먼저 들여다볼 가치가 있다.

MySQL이 느려졌다. 어디서부터 봐야 하나?

무작정 쿼리를 보는 건 틀린 순서다. 어느 레이어에서 시간이 걸리는지를 먼저 좁혀야 한다.

1단계: 지금 뭐가 걸리고 있나?

SHOW PROCESSLIST;

State 컬럼이 Waiting for lock으로 가득하면 락 경합이 원인이다. Sending data가 많으면 쿼리 자체가 많은 데이터를 처리 중이다. Sleep이 가득하면 커넥션이 idle인데 반납이 안 되고 있다—커넥션 풀 설계 문제다.

2단계: 핵심 지표 확인

SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
  'Innodb_buffer_pool_reads',          -- 디스크에서 직접 읽은 횟수
  'Innodb_buffer_pool_read_requests',  -- 전체 읽기 요청 수
  'Innodb_row_lock_waits',             -- 락 대기 발생 횟수
  'Innodb_row_lock_time_avg',          -- 락 평균 대기 시간 (ms)
  'Slow_queries'                        -- 슬로우 쿼리 누적 수
);

Slow_queries가 빠르게 올라가고 있으면 쿼리 레벨 문제다. Innodb_row_lock_waits이 많으면 락 경합이다. 이 두 가지를 먼저 구분해야 다음 행동이 달라진다.

3단계: InnoDB 전반 상태

SHOW ENGINE INNODB STATUS\G

TRANSACTIONS 섹션에서 장기 미커밋 트랜잭션을 찾는다. BUFFER POOL AND MEMORY 섹션에서 히트율을 본다. History list length가 수천 이상이면 Undo Log가 쌓이고 있다—성능이 서서히 나빠지는 징조다.

이 세 가지를 보면 원인이 세 가지 중 하나로 좁혀진다: 쿼리, 락, Buffer Pool. 그 다음부터 파고들면 된다.

Buffer Pool이 없으면 어떤 일이 생기나?

MySQL은 데이터를 디스크에 저장한다. 쿼리를 실행하면 디스크에서 데이터를 읽어야 한다.

HDD 랜덤 읽기: 초당 100200번. SSD도 초당 수만 번 수준이다. 메모리 접근은 초당 수억수십억 번이다.

Buffer Pool 없이 매 쿼리마다 디스크를 찌른다고 상상해보자. 1초에 1,000개 쿼리가 들어온다. 각 쿼리가 디스크를 10번씩 읽는다고 치면 초당 10,000번의 디스크 랜덤 읽기가 필요하다. SSD라도 버틸 수 있는 한계에 금방 닿는다.

InnoDB는 이 문제를 Buffer Pool로 해결한다. 디스크에서 읽어온 데이터 페이지를 메모리에 올려두고, 같은 데이터를 다시 읽을 때는 디스크를 건드리지 않는다.

쓰기도 같다. UPDATE를 날리면 디스크를 즉시 수정하는 게 아니라 Buffer Pool의 페이지를 수정하고(이걸 Dirty Page라 한다), 나중에 Checkpoint 시점에 일괄로 디스크에 내려쓴다. 쓰기도 빠른 이유다.

Buffer Pool 히트율 = “원하는 데이터가 이미 메모리에 있는 비율”

SELECT
  ROUND(
    (1 - (
      (SELECT variable_value FROM performance_schema.global_status
        WHERE variable_name = 'Innodb_buffer_pool_reads') /
      (SELECT variable_value FROM performance_schema.global_status
        WHERE variable_name = 'Innodb_buffer_pool_read_requests')
    )) * 100, 2
  ) AS hit_rate_pct;

이 수치가 99% 아래라면 쿼리마다 디스크를 자주 찌르고 있다는 의미다.

Buffer Pool 히트율이 99% 미만이면 어떻게 올리나?

낮은 원인은 두 가지다. Buffer Pool이 너무 작거나, 쿼리가 너무 많은 페이지를 훑거나.

Buffer Pool 크기 설정

# my.cnf
innodb_buffer_pool_size = 24G         # RAM의 70~80% 권장
innodb_buffer_pool_instances = 8      # 총 크기 / 1GB (최대 64)

# Redo Log 용량 — MySQL 8.0.30+
innodb_redo_log_capacity = 4G         # 8.0.30부터 도입된 단일 변수

# 8.0.30 미만(레거시)에서만 사용
# innodb_log_file_size = 2G           # Redo Log 파일 크기
# innodb_log_files_in_group = 2       # 총 Redo Log = 4G

innodb_log_file_size와 innodb_log_files_in_group은 MySQL 8.0.30부터 deprecated다. 둘을 곱해서 총 Redo Log 용량을 계산하던 방식 대신, innodb_redo_log_capacity 하나로 총 용량을 직접 지정한다. 두 방식을 동시에 설정하면 신규 변수(innodb_redo_log_capacity)가 우선한다. 8.0.30 이상이라면 신규 변수를 쓰고 레거시 두 개는 지운다.

왜 70~80%인가? 나머지를 써야 하는 것들이 있다: OS 파일 캐시, 커넥션 스레드 스택(기본 256KB × 최대 커넥션 수), Redo Log 버퍼, Sort Buffer, 각종 임시 공간.

직접 겪은 사고: 64GB RAM 서버에서 innodb_buffer_pool_size=60G로 잡았다. 야간 배치가 돌면서 OS가 스왑을 쓰기 시작했다. 응답 시간이 평소의 20배가 됐다. 48G로 줄이자 바로 안정화됐다. 숫자에 욕심 부리지 말 것.

innodb_buffer_pool_instances가 필요한 이유

Buffer Pool 단일 인스턴스는 내부적으로 뮤텍스 하나로 보호된다. 커넥션이 수십 개 동시에 접근하면 이 뮤텍스 경합이 병목이 된다. 인스턴스를 나누면 각 인스턴스가 독립 뮤텍스를 가져서 경합이 분산된다.

Redo Log와 Checkpoint

Redo Log 총 용량(8.0.30+에서는 innodb_redo_log_capacity, 그 이전은 innodb_log_file_size × innodb_log_files_in_group)이 작으면 Redo Log가 빨리 차고, 꽉 차기 전에 Dirty Page를 강제로 디스크에 쓰는 Checkpoint가 발생한다. Checkpoint = 디스크 I/O 폭발. 피크 타임 1시간 동안 발생하는 Redo Log 양을 측정해서 그 이상으로 잡아야 한다.

-- 1분 간격으로 두 번 실행해서 "Log sequence number" 차이를 계산
-- 차이 × 60 = 1시간 추정 발생량 → 이 값 이상으로 log_file_size 설정
SHOW ENGINE INNODB STATUS\G

이걸 적용하면 어떤 차이가 생기나?

히트율 27%포인트 차이가 응답 100배 차이를 만들기도 한다.

인덱스를 걸었는데 EXPLAIN을 보면 full scan이다. 왜?

주니어가 가장 당황하는 순간 중 하나다. 분명히 인덱스를 만들었다. EXPLAIN을 실행했더니 type: ALL. full scan이다.

원인이 세 가지다.

원인 1: Selectivity가 너무 낮다

status = 'ACTIVE'인 행이 전체의 90%라면, 옵티마이저 입장에서는 인덱스를 타는 게 더 비싸다. 인덱스 B-tree를 탐색하고 랜덤 I/O로 90만 개 페이지를 찾아오는 것보다 테이블을 순차로 훑는 게 낫다는 판단이다.

-- Selectivity 확인: 높을수록 인덱스 효과 있음
SELECT
  COUNT(DISTINCT status) / COUNT(*) AS selectivity
FROM orders;
-- 0.05 미만이면 단독 인덱스 효과 거의 없음

원인 2: 컬럼에 함수를 적용했다

인덱스는 컬럼의 값 기준으로 만들어진다. 컬럼에 함수를 씌우면 인덱스를 쓸 수 없다.

-- 이건 인덱스를 못 탄다 (컬럼에 함수 적용)
WHERE DATE(created_at) = '2026-06-29'
WHERE YEAR(created_at) = 2026
WHERE user_id + 0 = 123
WHERE UPPER(email) = 'HELLO@TEST.COM'

-- 이렇게 고쳐야 인덱스를 탄다 (값을 변환, 컬럼은 그대로)
WHERE created_at >= '2026-06-29 00:00:00'
  AND created_at < '2026-06-30 00:00:00'
WHERE email = 'hello@test.com'  -- DB에 소문자로 저장되어 있다면

원인 3: 통계가 오래됐다

옵티마이저는 실행 계획을 세울 때 테이블 통계를 참고한다. 대규모 INSERT/DELETE 이후 통계를 갱신하지 않으면 옵티마이저가 잘못된 판단을 한다.

ANALYZE TABLE orders;

-- 통계 확인
SELECT * FROM information_schema.INNODB_TABLE_STATS
WHERE table_name = 'orders';

복합 인덱스 컬럼 순서를 왜 고민해야 하나? 잘못 걸면 어떻게 되나?

인덱스를 걸었는데도 EXPLAIN의 rows가 수백만이다. 순서 하나 잘못 걸어서.

InnoDB B-tree 인덱스는 왼쪽부터 순서대로 정렬된다. (user_id, status, created_at) 인덱스라면 user_id로 먼저 정렬되고, 같은 user_id 안에서 status로 정렬되고, 같은 status 안에서 created_at으로 정렬된다.

범위 조건 뒤의 컬럼은 인덱스에서 죽는다

-- 자주 실행하는 쿼리
SELECT * FROM orders
WHERE user_id = 123
  AND status = 'PENDING'
  AND created_at >= '2026-01-01';

-- 잘못 만든 인덱스: created_at이 앞에 오면 안 된다
CREATE INDEX idx_wrong ON orders (created_at, user_id, status);
-- created_at이 범위 조건이라 그 뒤 user_id, status는 인덱스 탐색에 사용되지 않음
-- EXPLAIN의 rows: 전체 테이블 수준

-- 올바른 인덱스
CREATE INDEX idx_correct ON orders (user_id, status, created_at);
-- 등치(user_id, status) 먼저 → 범위(created_at) 나중

왜 그럴까? user_id = 123 AND status = 'PENDING'까지는 B-tree에서 정확한 위치를 특정할 수 있다. 그 위치에서 created_at >= '2026-01-01'은 순서대로 스캔하면 된다. 하지만 created_at이 첫 번째 컬럼이면, 날짜 범위에 해당하는 모든 위치를 뒤져야 하고 그 안에서 user_id, status를 거를 수 없다.

실제 사고 사례

팀원이 (created_at, user_id, status) 순서로 인덱스를 만들었다. 쿼리 조건이 user_id = ? AND status = ? AND created_at >= ?인데 created_at이 범위 조건이라 user_id, status는 인덱스에서 필터링이 되지 않았다. EXPLAIN의 rows: 4,800,000. 인덱스 순서를 (user_id, status, created_at)으로 바꾸자 rows: 183.

이걸 적용하면 어떤 차이가 생기나?

인덱스 삭제하고 새로 만드는 데 5분이 걸렸다.

커버링 인덱스가 왜 빠른가? B-tree 레벨에서 설명

InnoDB 세컨더리 인덱스의 리프 노드 구조를 알면 이해가 쉽다.

세컨더리 인덱스 리프에는 인덱스 컬럼 값 + Primary Key 값이 들어있다. (user_id, status) 인덱스에서 user_id=123, status='COMPLETED'를 찾으면 리프 노드에 {user_id: 123, status: 'COMPLETED', id: 5001}이 담겨 있다.

일반 쿼리는 이 id를 가지고 클러스터드 인덱스(Primary Key B-tree)를 한 번 더 탐색해 실제 행 데이터를 가져온다. 이걸 Bookmark Lookup이라 한다. 랜덤 I/O가 한 번 더 발생한다.

커버링 인덱스는 이 두 번째 조회를 없앤다. 쿼리가 필요한 컬럼이 전부 인덱스 리프에 있으면, 클러스터드 인덱스를 건드릴 필요 없이 인덱스 B-tree만으로 결과를 반환한다.

-- 이 인덱스가 있다면
CREATE INDEX idx_cover ON orders (user_id, status, created_at, total_amount);

-- 이 쿼리는 인덱스만으로 처리 (랜덤 I/O 없음)
SELECT status, created_at, total_amount
FROM orders
WHERE user_id = 123
  AND status = 'COMPLETED';
-- EXPLAIN Extra: "Using index"

SELECT *를 쓰는 순간 커버링 인덱스는 불가능하다. 컬럼이 20개짜리 테이블에서 3개만 필요한데 SELECT *를 날리면 커버링 인덱스 기회를 놓친다.

EXPLAIN 확인 포인트:

• Extra: Using index → 커버링 인덱스 작동 중. 랜덤 I/O 없음
• Extra: Using index condition → 인덱스를 타지만 데이터 파일도 접근. 랜덤 I/O 있음

이걸 적용하면 어떤 차이가 생기나?

I/O 횟수로 보면: 일반 인덱스는 세컨더리 B-tree 탐색 + 클러스터드 B-tree 재탐색. 커버링 인덱스는 세컨더리 B-tree 탐색만. I/O가 절반이다. 데이터가 Buffer Pool에 없어서 디스크를 찌르는 상황에서 차이가 크다.

JPA를 쓰면 N+1 문제가 왜 생기나?

주문 목록을 조회하는 코드를 짰다:

List<Order> orders = orderRepository.findAll(); // 쿼리 1번
for (Order order : orders) {
    System.out.println(order.getItems().size()); // 여기서 쿼리 N번 발생
}
// 주문 100개 → 총 쿼리 101번

왜 이런 설계인가? JPA의 LAZY 로딩 기본 전략 때문이다. Order를 조회할 때 연관된 OrderItem은 실제로 접근하기 전까지 로드하지 않는다. 메모리 낭비를 막기 위한 합리적인 기본값이지만, 반복문 안에서 연관 객체에 접근하면 루프 횟수만큼 쿼리가 나간다.

주문이 1,000개면 1,001번 쿼리가 나간다. 피크 타임에 이게 터지면 DB 커넥션이 순식간에 바닥난다.

해결책 1: Fetch Join

@Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.items WHERE o.userId = :userId")
List<Order> findByUserIdWithItems(@Param("userId") Long userId);

DISTINCT가 필요한 이유: JOIN이 Order × Items 카르테시안 곱을 만들어 Order가 중복으로 나오기 때문이다.

Fetch Join의 함정: 컬렉션 + 페이징

HHH90003004: firstResult/maxResults specified with collection fetch; applying in memory

@OneToMany Fetch Join과 페이징을 함께 쓰면 Hibernate가 이 경고를 내고 전체 데이터를 메모리에 올린 후 페이징한다. 데이터 10만 건이면 10만 건을 다 올린다. 치명적이다.

해결책 2: BatchSize (페이징과 함께 쓸 때 안전)

spring:
  jpa:
    properties:
      hibernate:
        default_batch_fetch_size: 100

지연 로딩 시 IN (id1, id2, ..., id100) 형태로 한 번에 조회한다. 100개 주문이면 쿼리 2번으로 끝난다.

이걸 적용하면 어떤 차이가 생기나?

N+1 탐지: 개발 환경 SQL 로깅으로 특정 API 호출 시 같은 테이블을 N번 쿼리하는 패턴이 보이면 N+1이다.

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.type.descriptor.sql: TRACE  # 파라미터 바인딩까지

@Transactional(readOnly=true)가 실제로 뭘 해주나? 안 붙이면 어떤 차이가 있나?

두 가지를 최적화한다. 많은 사람들이 하나만 알고 있다.

최적화 1: Dirty Checking 비활성화

JPA는 트랜잭션이 끝날 때 1차 캐시의 모든 엔티티를 스냅샷과 비교해서 변경된 게 있으면 자동으로 UPDATE를 날린다. 이걸 Dirty Checking이라 한다.

readOnly=true를 붙이면 Hibernate는 FlushMode를 MANUAL로 설정한다. 스냅샷 자체를 만들지 않고 비교도 하지 않는다. 엔티티 100개를 조회하는 서비스라면 요청마다 100개 엔티티 비교 작업이 사라진다.

// readOnly 없음: 조회 후 Dirty Checking 실행 (CPU 낭비)
@Transactional
public List<ProductDto> getProducts() { ... }

// readOnly 있음: Dirty Checking 없음, 스냅샷 저장 없음
@Transactional(readOnly = true)
public List<ProductDto> getProducts() { ... }

최적화 2: Replica 라우팅

AbstractRoutingDataSource를 구현해두면 readOnly=true 트랜잭션을 자동으로 읽기 전용 DB(Replica)로 보낼 수 있다.

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? "replica"
            : "primary";
    }
}

조회 트래픽이 마스터에서 자동으로 분리된다.

주의사항

readOnly=true라도 엔티티 필드를 수정하는 코드를 막지 않는다. 플러시가 안 될 뿐이다. 나중에 save()를 명시적으로 호출하면 반영된다. 읽기 메서드에서 실수로 엔티티를 수정하고 “왜 DB에 안 들어가지?”를 디버깅하는 상황이 생긴다.

JPA로 대량 INSERT가 왜 느린가? IDENTITY 전략의 숨겨진 비용

1,000개 엔티티를 saveAll()로 저장했다. 2초가 넘게 걸렸다.

핵심은 @GeneratedValue(strategy = GenerationType.IDENTITY)다.

IDENTITY 전략은 DB에 INSERT를 해봐야 생성된 ID를 알 수 있다. MySQL AUTO_INCREMENT가 그렇다. Hibernate는 이 특성을 알기 때문에 saveAll() 시 배치 INSERT를 포기하고 하나씩 INSERT한다. 1,000개 엔티티 = 1,000번의 DB 왕복.

// IDENTITY 전략: 배치 INSERT 불가
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

// SEQUENCE 전략: 배치 INSERT 가능
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "order_seq")
@SequenceGenerator(name = "order_seq", sequenceName = "order_seq", allocationSize = 50)
private Long id;

SEQUENCE 전략은 INSERT 전에 시퀀스에서 ID를 미리 가져온다. Hibernate가 ID를 미리 알고 있으니 배치 INSERT가 가능하다. allocationSize=50이면 시퀀스 호출 1번에 50개 ID를 예약한다.

배치 INSERT를 활성화하는 설정:

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 50
          order_inserts: true   # 같은 타입 엔티티끼리 묶어서 배치
          order_updates: true

이걸 적용하면 어떤 차이가 생기나?

실제 사례: 이벤트 처리 결과를 매초 1,000개씩 저장하는 배치가 있었다. IDENTITY 전략이라 저장에만 2.1초. SEQUENCE + 배치로 전환 후 0.7초로 줄었다.

OFFSET 페이징이 왜 페이지가 뒤로 갈수록 느려지나?

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

이 쿼리를 실행하면 MySQL은 100,000 + 20 = 100,020행을 읽고, 앞의 100,000행을 버리고, 20행을 반환한다. 페이지가 뒤로 갈수록 버리는 행이 늘어나고 선형으로 느려진다.

1페이지는 빠른데 5,000페이지는 5초 걸리는 이유다. 처음에는 몰랐다. 데이터가 쌓이면서 특정 API 응답이 점점 느려지다가 타임아웃이 날 때쯤 원인을 찾는다.

커서 기반 페이징: 마지막으로 본 행의 위치를 기억한다

-- 첫 페이지
SELECT id, created_at, total_amount
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 다음 페이지 (마지막 행의 created_at, id를 커서로 사용)
SELECT id, created_at, total_amount
FROM orders
WHERE (created_at < '2026-06-15 10:30:00')
   OR (created_at = '2026-06-15 10:30:00' AND id < 49823)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at, id) 복합 인덱스가 있으면 이 쿼리는 항상 인덱스에서 커서 위치를 찾아 20개만 읽고 멈춘다. 100만 페이지여도 속도가 같다.

public OrderCursorResponse getOrders(LocalDateTime cursorAt, Long cursorId) {
    Pageable pageable = PageRequest.of(0, 20);
    Slice<Order> slice = orderRepository
        .findByCreatedAtBeforeOrSameCursorOrderByCreatedAtDescIdDesc(cursorAt, cursorId, pageable);

    return OrderCursorResponse.of(
        slice.getContent(),
        slice.hasNext(),
        slice.getContent().isEmpty() ? null
            : slice.getContent().get(slice.getContent().size() - 1)
    );
}

Page<T> 대신 Slice<T>를 쓰면 전체 count 쿼리를 날리지 않는다. 무한 스크롤이나 “다음 페이지” 형태라면 전체 건수가 필요 없다.

이걸 적용하면 어떤 차이가 생기나?

단점: 특정 페이지로 직접 이동이 안 된다. “3페이지로 이동” 같은 UX가 필요하면 OFFSET을 써야 한다. 무한 스크롤이나 “더 보기” 형태라면 커서가 훨씬 낫다.

최적화 정리

튜닝: 무엇을 측정하고 무엇을 바꾸나

1단계: Slow Query Log로 용의자 찾기

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100

pt-query-digest /var/log/mysql/slow.log | head -100
# pt-query-digest 없으면: mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

2단계: EXPLAIN ANALYZE로 실행 계획 확인

EXPLAIN ANALYZE
SELECT o.id, o.status, o.total_amount
FROM orders o
WHERE o.user_id = 123
  AND o.status = 'COMPLETED'
ORDER BY o.created_at DESC
LIMIT 20;

rows_examined / rows_sent 비율이 크면 인덱스가 비효율적이다. 1:1에 가까울수록 좋다.

3단계: performance_schema로 쿼리 패턴 집계

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS executions,
  ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
  ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_sec,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

4단계: 락 경합 확인

SELECT
  r.trx_query AS waiting_query,
  b.trx_query AS blocking_query,
  r.trx_mysql_thread_id AS waiting_thread,
  b.trx_mysql_thread_id AS blocking_thread
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

운영상 주의사항

Undo Log 누적으로 성능이 서서히 저하되는 패턴

장기 미커밋 트랜잭션이 있으면 Undo Log가 계속 쌓인다. MVCC 때문에 오래된 버전을 삭제할 수 없어 History List Length가 수만, 수십만으로 불어난다. 모든 쿼리가 Undo Chain을 타고 올라가야 해서 점진적으로 느려지다가 어느 순간 터진다. 급격히 느려지는 게 아니라 하루에 걸쳐 서서히 느려지다 터지는 패턴이라 원인을 찾기 어렵다.

SHOW ENGINE INNODB STATUS\G
-- "History list length" 값 확인. 수천 이상이면 위험

범인은 보통 배치 작업에서 @Transactional을 걸고 수십만 행을 처리하는 코드, 또는 개발자 PC에서 트랜잭션을 열어두고 퇴근한 경우다.

B-tree 단편화

대량 삭제 이후 인덱스 B-tree에 빈 공간이 생긴다. 히트율은 높은데 쿼리가 느린 이상한 상황이 발생할 수 있다. 6개월에 한 번 정도 새벽에 OPTIMIZE TABLE을 돌리거나, pt-online-schema-change를 쓰면 서비스 중단 없이 재구성할 수 있다.

JPA @Modifying(clearAutomatically = false) 함정

@Modifying  // clearAutomatically 기본값이 false!
@Query("UPDATE Order o SET o.status = 'CANCELLED' WHERE o.id IN :ids")
int bulkCancel(@Param("ids") List<Long> ids);

벌크 UPDATE 후 같은 트랜잭션에서 엔티티를 조회하면 1차 캐시에 남아 있는 이전 상태가 나온다. DB는 CANCELLED인데 코드에서는 PENDING으로 보인다. clearAutomatically = true를 붙이거나 entityManager.clear()를 명시적으로 호출해야 한다.

커넥션 풀 설계

HikariCP 기본값은 maximumPoolSize=10이다. 트래픽이 늘면 커넥션 대기가 쌓이고 타임아웃이 발생한다. maximumPoolSize를 무작정 크게 잡으면 DB max_connections를 초과해 접속 자체가 거부된다.

공식: DB max_connections = (인스턴스 수 × maximumPoolSize) + 여유분

MySQL max_connections와 HikariCP maximumPoolSize를 함께 설계해야 한다.