beautifulsoups.dev
Published on

MySQL InnoDB 히든 병목 7종 - 잠금·버퍼·Redo 튜닝

Authors

서버 스펙은 충분한데 TPS가 더 이상 안 오르고, 지연이 간헐적으로 튀며, CPU는 놀고 있는데도 쿼리가 밀린다면 InnoDB 내부의 “숨은 병목”을 의심해야 합니다. 특히 InnoDB는 잠금(락)과 버퍼 풀, Redo 로그(로그 플러시)가 서로 영향을 주며 병목이 연쇄적으로 발생합니다.

이 글은 운영에서 자주 만나는 InnoDB 히든 병목 7가지를 증상 → 확인 방법 → 처방 순서로 정리합니다. 예시는 MySQL 8.0 기준이며, 관측은 performance_schema, sys 스키마, SHOW ENGINE INNODB STATUS를 중심으로 합니다.

진단 준비: “지표 없이 튜닝”을 피하는 최소 셋업

필수: 현재 설정과 워크로드 스냅샷

-- 핵심 InnoDB 파라미터 빠르게 확인
SHOW VARIABLES WHERE Variable_name IN (
  'innodb_buffer_pool_size',
  'innodb_buffer_pool_instances',
  'innodb_flush_log_at_trx_commit',
  'sync_binlog',
  'innodb_log_file_size',
  'innodb_log_files_in_group',
  'innodb_redo_log_capacity',
  'innodb_flush_method',
  'innodb_io_capacity',
  'innodb_io_capacity_max',
  'innodb_adaptive_hash_index',
  'innodb_thread_concurrency'
);

-- InnoDB 엔진 상태(락, I/O, purge, history length 등)
SHOW ENGINE INNODB STATUS\G

권장: sys 스키마로 병목 후보를 빠르게 좁히기

-- 상위 대기 이벤트
SELECT * FROM sys.session_waits ORDER BY total_waits DESC LIMIT 20;

-- I/O가 많은 테이블
SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total_read DESC LIMIT 20;

-- 잠금 대기(락) 관련
SELECT * FROM sys.innodb_lock_waits LIMIT 20;

1) 잠금 대기 폭증: “인덱스 미스 + 긴 트랜잭션” 조합

대표 증상

  • Lock wait timeout exceeded가 늘거나, 요청이 큐처럼 밀림
  • CPU 사용률은 낮은데 응답 시간이 길어짐
  • 같은 테이블의 일부 행 업데이트에서만 지연이 집중

확인 방법

-- 현재 잠금 대기 관계
SELECT
  waiting_pid,
  waiting_query,
  blocking_pid,
  blocking_query,
  locked_table,
  locked_index,
  locked_type,
  waiting_trx_started,
  blocking_trx_started
FROM sys.innodb_lock_waits\G

-- 오래 열린 트랜잭션(특히 autocommit=0, 대화형 세션)
SELECT *
FROM information_schema.innodb_trx
ORDER BY trx_started;

처방

  • 인덱스가 WHERE를 정확히 타는지 확인하고, 불필요한 범위 스캔을 줄입니다.
  • 트랜잭션을 “짧게” 유지합니다. 특히 웹 요청에서 트랜잭션을 열어둔 채 외부 API 호출을 섞는 패턴은 치명적입니다.
  • SELECT ... FOR UPDATE 사용 시 범위를 최소화하고, 필요하면 커버링 인덱스를 설계합니다.
-- 나쁜 예: 인덱스 없는 조건으로 잠금 범위가 커짐
UPDATE orders SET status='PAID'
WHERE user_id = 123 AND created_at >= '2026-01-01';

-- 개선: (user_id, created_at) 복합 인덱스 추가 후 범위 축소
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

2) 갭 락과 넥스트키 락: “REPEATABLE READ에서의 범위 잠금”

InnoDB 기본 격리 수준이 REPEATABLE READ인 환경에서는 팬텀을 막기 위해 넥스트키 락(레코드 락 + 갭 락) 이 걸립니다. 범위 조건이 들어간 SELECT ... FOR UPDATE나 UPDATE ... WHERE range가 예상보다 넓게 잠금을 잡아 병목을 만들 수 있습니다.

대표 증상

  • 특정 범위 업데이트가 다른 INSERT까지 막음
  • “레코드 하나만” 수정하는 것 같은데 동시성이 급락

확인 방법

SHOW ENGINE INNODB STATUS\G에서 LATEST DETECTED DEADLOCK 또는 락 섹션을 보고, lock_mode X locks gap 같은 표현이 보이면 갭 락 가능성이 큽니다.

처방

  • 가능하면 동등 조건(=) 으로 좁히고, 범위 조건은 인덱스를 타도록 설계합니다.
  • 워크로드가 팬텀을 강하게 요구하지 않는다면 격리 수준을 READ COMMITTED로 검토합니다.
-- 세션 단위로 격리 수준 변경(애플리케이션에서 커넥션 풀 사용 시 주의)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3) 데드락은 “버그”가 아니라 “경합 패턴”이다

데드락은 보통 두 트랜잭션이 서로 다른 순서로 자원을 잠그는 순간 발생합니다. InnoDB는 데드락을 감지하면 한쪽을 롤백합니다. 문제는 데드락 자체보다, 데드락이 잦으면 재시도 폭증으로 지연이 커진다는 점입니다.

확인 방법

SHOW ENGINE INNODB STATUS\G

출력에서 데드락 섹션에 어떤 인덱스를 어떤 순서로 잠갔는지가 나옵니다.

처방

  • 애플리케이션 레벨에서 락 획득 순서를 통일합니다.
  • 배치 업데이트는 PK 순서로 처리해 충돌 확률을 낮춥니다.
-- PK 순서로 업데이트(범위를 쪼개고 정렬)
SELECT id FROM orders
WHERE status='PENDING'
ORDER BY id
LIMIT 1000;

-- 가져온 id 목록을 같은 순서로 업데이트
UPDATE orders SET status='PROCESSING'
WHERE id IN ( ... );

4) 버퍼 풀 미스와 “더티 페이지 플러시 폭탄”

버퍼 풀은 InnoDB 성능의 핵심입니다. 단순히 innodb_buffer_pool_size를 키우는 것만으로 해결되지 않는 경우가 많습니다. 특히 더티 페이지가 한꺼번에 쌓였다가 특정 시점에 플러시가 몰리면, I/O가 급증하면서 지연이 튑니다.

대표 증상

  • 평소엔 빠르다가 특정 구간에서만 지연이 급상승
  • 디스크 I/O utilization이 순간적으로 100% 근접

확인 방법

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Innodb_pages%';

-- 버퍼 풀 히트율 근사
-- hit = 1 - (reads / read_requests)
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

처방

  • 메모리 여유가 있다면 innodb_buffer_pool_size를 늘리되, OS 캐시와 다른 프로세스 메모리를 고려합니다.
  • 버퍼 풀 인스턴스는 큰 메모리에서 경합을 줄이지만, 과도하게 쪼개면 효율이 떨어질 수 있습니다.
  • 플러시가 몰리는 패턴이면 innodb_io_capacity, innodb_io_capacity_max를 스토리지 성능에 맞게 조정해 “지속적으로 조금씩” 플러시되도록 유도합니다.
-- 예시: 스토리지가 NVMe급이고 플러시가 따라오지 못할 때
SET PERSIST innodb_io_capacity = 2000;
SET PERSIST innodb_io_capacity_max = 4000;

5) Redo 로그 병목: innodb_flush_log_at_trx_commit와 fsync 지옥

트랜잭션 커밋 시 Redo 로그를 얼마나 강하게 디스크에 강제할지에 따라 지연과 내구성의 트레이드오프가 결정됩니다.

  • innodb_flush_log_at_trx_commit=1: 매 커밋마다 로그 버퍼를 파일로 쓰고 fsync까지 수행(내구성 최강, 지연 증가)
  • =2: 매 커밋마다 파일 write는 하지만 fsync는 1초마다(대부분의 OLTP에서 타협점)
  • =0: 1초마다 write+fsync(지연 최소, 장애 시 손실 가능)

대표 증상

  • TPS가 특정 지점에서 더 이상 증가하지 않음
  • fsync 관련 대기가 늘어남
  • binlog까지 켜져 있으면 sync_binlog와 함께 병목이 증폭

확인 방법

SHOW GLOBAL STATUS LIKE 'Innodb_os_log_%';
SHOW GLOBAL STATUS LIKE 'Innodb_log_%';

-- Performance Schema에서 fsync 대기(환경에 따라 이벤트 명은 다를 수 있음)
SELECT *
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/io/file/%'
ORDER BY sum_timer_wait DESC
LIMIT 20;

처방

  • 금융/결제처럼 강한 내구성이 필수면 =1을 유지하되, 스토리지와 파일시스템 튜닝을 우선합니다.
  • 일반 웹 서비스의 세션/로그성 업데이트 등 일부 워크로드는 =2로 현실적인 타협이 가능합니다.
  • binlog를 쓰는 복제 환경에서는 sync_binlog=1도 함께 비용이 큽니다. 요구되는 RPO에 맞춰 조정합니다.
-- 내구성 타협(장애 시 최근 1초 손실 가능성)
SET PERSIST innodb_flush_log_at_trx_commit = 2;

-- binlog fsync 빈도도 함께 검토
SET PERSIST sync_binlog = 100;

주의: 위 설정은 장애 시 데이터 손실 가능성을 증가시킵니다. 운영 정책과 합의 없이 적용하면 안 됩니다.

6) Redo 용량과 체크포인트: 로그가 작으면 “자주 멈춰서 닦는다”

Redo 로그 용량이 작으면 체크포인트가 자주 발생하고, 더티 페이지를 급하게 밀어내야 해서 쓰기 지연이 커질 수 있습니다. MySQL 8.0에서는 innodb_redo_log_capacity가 핵심이며, 구버전 스타일의 innodb_log_file_size 조합도 여전히 흔합니다.

대표 증상

  • 쓰기 부하에서 주기적으로 지연이 튐
  • SHOW ENGINE INNODB STATUS에서 체크포인트/flush 관련 압박이 보임

확인 방법

SHOW VARIABLES LIKE 'innodb_redo_log_capacity';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';

SHOW ENGINE INNODB STATUS\G

처방

  • 쓰기 비중이 높은 서비스라면 Redo 용량을 늘려 체크포인트 압박을 줄입니다.
  • 다만 로그가 너무 크면 크래시 리커버리 시간이 늘 수 있어, 운영 요구사항과 함께 결정합니다.
-- MySQL 8.0에서 권장되는 방식(버전에 따라 즉시 반영/재시작 필요)
SET PERSIST innodb_redo_log_capacity = 8589934592; -- 8GiB 예시

7) Purge 지연과 History List Length: “삭제/갱신이 많은데 읽기도 많은”

InnoDB는 MVCC를 위해 undo를 쌓고, 백그라운드 purge 스레드가 이를 정리합니다. purge가 따라가지 못하면 history list length가 증가하고, 결국 읽기/쓰기 모두에 악영향을 줍니다. 특히 긴 트랜잭션이 있으면 purge가 진행되지 못해 악화됩니다.

대표 증상

  • 삭제/업데이트 많은 테이블에서 시간이 갈수록 성능 저하
  • SHOW ENGINE INNODB STATUS에서 History list length가 비정상적으로 큼

확인 방법

SHOW ENGINE INNODB STATUS\G

-- 오래 열린 트랜잭션이 purge를 막는지 확인
SELECT trx_id, trx_state, trx_started, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

처방

  • 가장 먼저 긴 트랜잭션을 제거합니다. purge 이슈의 상당수는 이것으로 해결됩니다.
  • 대량 삭제는 한 번에 하지 말고 배치로 쪼갭니다.
-- 대량 삭제를 배치로 쪼개기
DELETE FROM events
WHERE created_at < '2025-01-01'
LIMIT 10000;

실전 체크리스트: 30분 안에 병목 방향 잡기

  1. sys.innodb_lock_waits로 잠금 대기 관계를 확인하고, blocking 쿼리와 트랜잭션 시작 시간을 본다.
  2. information_schema.innodb_trx에서 오래 열린 트랜잭션이 있는지 확인한다.
  3. SHOW ENGINE INNODB STATUS에서 History list length, flush 압박, 데드락 로그를 본다.
  4. 버퍼 풀 히트율 근사값을 계산하고, Innodb_buffer_pool_reads가 급증하는지 본다.
  5. performance_schema에서 파일 I/O 대기 상위를 보고, redo fsync가 상위인지 확인한다.
  6. 쓰기 부하에서 주기적 지연이 있으면 redo 용량과 체크포인트 압박을 의심한다.
  7. 설정 변경은 SET PERSIST로 남기고, 변경 전후 지표를 반드시 비교한다.

운영 팁: 튜닝은 “재현 가능한 관측”과 세트다

InnoDB 병목은 대부분 “한 가지 원인”이 아니라, 잠금 경합이 redo 플러시를 키우고, redo 압박이 더티 페이지 플러시를 흔들고, 그 결과 쿼리가 길어져 다시 잠금이 커지는 식으로 증폭됩니다. 그래서 다음 두 가지를 습관화하면 시행착오가 크게 줄어듭니다.

  • 변경 전후로 같은 지표를 비교할 것(락 대기, fsync 대기, 버퍼 풀 reads)
  • 애플리케이션 재시도 로직과 DB 설정을 함께 볼 것(데드락/락 타임아웃 재시도가 폭주하면 증상이 과장됨)

관측과 자동화 관점에서의 접근은 다른 장애 글에서도 유사합니다. 예를 들어 외부 요인으로 지연이 튀는 상황을 분해하는 방법은 Cloud Run 503·컨테이너 미기동 원인 7가지에서의 “원인 분류”와도 닮아 있고, 빌드/배포 파이프라인 병목을 캐시 관점에서 푸는 방식은 Jenkins 빌드가 갑자기 느릴 때 Docker 레이어 캐시 복구에서의 접근과도 연결됩니다.

마무리: 7가지 중 무엇부터 손대야 하나

  • 에러 로그에 락 타임아웃/데드락이 보이면 1, 2, 3부터
  • 쓰기 TPS가 특정 지점에서 막히고 I/O 대기가 크면 5, 6부터
  • 지연이 주기적으로 튀고 디스크가 바빠지면 4, 6부터
  • 시간이 갈수록 느려지고 삭제/업데이트가 많다면 7부터

원하시면 현재 SHOW ENGINE INNODB STATUS\G 출력과 주요 변수(innodb_buffer_pool_size, innodb_flush_log_at_trx_commit, sync_binlog, redo 용량)를 기반으로, 이 7가지 중 어디가 가장 유력한지 “우선순위 진단” 형태로 함께 정리해드릴 수 있습니다.

Discuss on Twitter • View on GitHub