beautifulsoups.dev
Published on

MySQL 핫스팟 락 대폭주 - Redis 캐시+인덱스 튜닝

Authors

서버는 살아있는데 응답이 갑자기 수 초~수십 초로 늘고, CPU는 애매하게 낮은데 커넥션만 잔뜩 쌓이는 순간이 있습니다. MySQL에서는 이런 상황이 핫스팟 락(Hotspot Lock) 으로 자주 나타납니다. 특정 행(row) 또는 특정 인덱스 레인지에 트래픽이 집중되면서 InnoDB 락 경합이 폭발하고, 그 여파로 애플리케이션 레벨 타임아웃, 재시도 폭주, 더 큰 락 경합으로 이어지는 악순환이 발생합니다.

이 글에서는 핫스팟 락 대폭주를 관측 → 원인 규명 → 즉시 완화(캐시) → 구조 개선(인덱스/쿼리/트랜잭션) 순서로 정리합니다. 특히 Redis 캐시를 “무조건 앞단에 붙이는” 방식이 아니라, 핫키/캐시 스탬피드/일관성까지 고려해 안전하게 적용하는 방법을 다룹니다.

관련해서 캐시와 키 회전/캐시 점검 관점은 JWT invalid signature - JWK 회전·캐시 점검법에서도 유사한 사고 패턴을 참고할 수 있습니다. 또한 타임아웃이 연쇄 장애를 키우는 관점은 AWS ALB 502/504 급증? 타임아웃 7곳 점검도 함께 보면 좋습니다.

핫스팟 락이 터질 때 흔한 증상

1) DB 지표

  • Threads_running 급증, Threads_connected 증가
  • InnoDB_row_lock_time, InnoDB_row_lock_waits 증가
  • Handler_read_rnd_next 증가(풀스캔/비효율 접근 신호)
  • performance_schema.data_locks 에 특정 테이블/레코드 락이 집중

2) 애플리케이션 증상

  • 동일 API에서 p95/p99 급증
  • 재시도 로직이 있으면 QPS가 더 올라가며 상황 악화
  • “조회”인데도 느려지는 경우가 많음(조회가 잠금 대기 중인 업데이트/갭락에 걸림)

먼저 확인할 것: 락이 ‘어디에’ 몰리는가

핫스팟 락은 “원인은 다양하지만 결과는 비슷”합니다. 그래서 진단은 대기 중인 트랜잭션과 락 객체를 구체적으로 찍는 것부터 시작해야 합니다.

대기 트랜잭션/락 확인 쿼리

MySQL 8.x 기준으로 performance_schema 를 활용합니다.

-- 어떤 락을 기다리는지(대기 관계)
SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;

-- performance_schema에서 락 상세(테이블/인덱스/락 타입)
SELECT
  dl.OBJECT_SCHEMA,
  dl.OBJECT_NAME,
  dl.INDEX_NAME,
  dl.LOCK_TYPE,
  dl.LOCK_MODE,
  dl.LOCK_STATUS,
  dt.THREAD_ID,
  es.SQL_TEXT
FROM performance_schema.data_locks dl
JOIN performance_schema.data_lock_waits dw
  ON dl.ENGINE_LOCK_ID = dw.REQUESTING_ENGINE_LOCK_ID
JOIN performance_schema.threads dt
  ON dt.THREAD_ID = dl.THREAD_ID
LEFT JOIN performance_schema.events_statements_current es
  ON es.THREAD_ID = dt.THREAD_ID
ORDER BY dl.OBJECT_NAME, dl.INDEX_NAME;

핵심은 다음을 분류하는 것입니다.

  • 단일 PK 행에 UPDATE가 몰리는가? (예: counters, inventory, wallet)
  • 유니크 인덱스 충돌/삽입 경합인가? (예: 동일 user_id 로 로그 삽입)
  • 범위 조건(레인지) + 격리수준 때문에 갭락/넥스트키락이 생기는가?
  • 긴 트랜잭션이 락을 오래 잡고 있는가? (외부 API 호출, 대량 처리)

대표 시나리오 3가지와 해결 방향

시나리오 A: “조회가 많은데 왜 락이?” (갭락/넥스트키락)

예를 들어 다음 패턴이 흔합니다.

  • SELECT ... FOR UPDATE 를 범위 조건으로 수행
  • 인덱스가 애매해서 더 넓은 레인지에 락이 걸림
  • 다른 트랜잭션의 단순 조회/삽입까지 막아버림

대응 방향

  1. 가능한 한 정확한 인덱스로 범위를 좁히기
  2. 불필요한 FOR UPDATE 제거 또는 더 작은 단위로 분리
  3. 격리수준을 REPEATABLE READ 에서 READ COMMITTED 로 낮추는 것이 가능한지 검토(갭락 영향 감소)

시나리오 B: 단일 행 카운터/재고/포인트가 병목

예: UPDATE counters SET value = value + 1 WHERE name='order_seq' 같은 구조는 트래픽이 늘면 100% 핫스팟이 됩니다.

대응 방향

  • DB 단일 행에 몰리는 쓰기를 샤딩(버킷) 하거나
  • Redis 원자 연산(예: INCRBY)로 흡수 후 비동기 플러시
  • 또는 MySQL 기능(예: AUTO_INCREMENT)로 목적 대체

시나리오 C: “인덱스는 있는데도 느린” 커버링/정렬/페이징 문제

  • ORDER BY created_at DESC LIMIT 20 인데 WHERE user_id = ? 와 결합
  • 복합 인덱스 순서가 잘못되어 filesort 발생
  • 페이지가 깊어질수록(OFFSET) 더 느려지고 락 대기까지 유발(긴 실행시간이 락과 결합)

대응 방향

  • 복합 인덱스를 WHERE → ORDER BY 순서로 설계
  • 커서 기반 페이징으로 전환
  • 조회를 캐시로 흡수해 DB 동시성을 확보

1차 응급처치: Redis 캐시로 “읽기 폭주”를 먼저 끊는다

핫스팟 락은 쓰기 락이 원인이더라도, 실제로는 읽기 트래픽이 DB 커넥션/스레드 풀을 잠식하면서 장애가 커집니다. 따라서 1차로는 “DB로 들어오는 읽기”를 줄여야 합니다.

어떤 데이터를 캐시해야 하나

  • 동일 키로 반복 조회되는 값: 상품 상세, 유저 프로필, 설정, 권한, 카테고리
  • DB에서 계산 비용이 큰 집계/정렬 결과: 인기글 리스트, 랭킹(단, 갱신 전략 필요)
  • 락 경합의 간접 원인이 되는 조회: 재고 확인, 중복 체크 등

반대로 다음은 신중해야 합니다.

  • 강한 일관성이 필요한 잔액/재고 “최종 확정 값”
  • 개인화가 심해 키가 폭발하는 데이터

캐시 키/TTL 기본 설계

  • 키: entity:{id}:v{version} 처럼 버전 또는 마지막 수정 시각을 포함
  • TTL: 너무 길면 일관성 이슈, 너무 짧으면 DB로 되돌아감
  • TTL 지터(jitter): 만료가 한 시점에 몰려 캐시 스탬피드가 나지 않도록 랜덤 분산
// Node.js 예시: TTL에 지터를 더해 만료 쏠림 방지
function ttlWithJitter(baseSec, jitterSec = 30) {
  const j = Math.floor(Math.random() * jitterSec);
  return baseSec + j;
}

캐시 스탬피드(만료 폭주) 방지: 단일 플라이트 + 소프트 TTL

핫키가 만료되는 순간, 수백/수천 요청이 동시에 DB로 미스가 나면 락 경합이 재발합니다. 해결책은 다음 조합이 실전에서 안정적입니다.

  • soft TTL: 캐시가 “조금 오래된 값”을 잠깐 허용
  • 단일 플라이트(single-flight): 같은 키에 대해 1개 요청만 원본 조회
  • 락 키: SET lock_key value NX EX 로 짧게 잠금
// Redis 기반 단일 플라이트(의사 코드)
// 주의: 실제 운영에서는 예외/타임아웃/해제 실패까지 고려해야 함
async function getOrLoad(key, ttlSec, loader) {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const lockKey = `lock:${key}`;
  const gotLock = await redis.set(lockKey, "1", { NX: true, EX: 3 });

  if (gotLock) {
    try {
      const value = await loader(); // DB 조회
      await redis.set(key, JSON.stringify(value), { EX: ttlSec });
      return value;
    } finally {
      await redis.del(lockKey);
    }
  }

  // 락을 못 잡았으면 잠깐 기다렸다가 캐시 재시도
  await new Promise(r => setTimeout(r, 50));
  const retry = await redis.get(key);
  if (retry) return JSON.parse(retry);

  // 최후 fallback: 여기서 DB를 때리면 스탬피드가 재발할 수 있으니 정책적으로 결정
  return loader();
}

캐시 일관성: 삭제 기반(invalidate) vs 갱신(write-through)

핫스팟 락 상황에서는 “완벽한 일관성”보다 “DB를 살리는 것”이 우선인 경우가 많습니다.

  • invalidate 패턴: 쓰기 성공 후 관련 키 삭제
    • 장점: 구현 쉬움
    • 단점: 삭제 직후 읽기 폭주 시 스탬피드 위험
  • write-through: DB 쓰기와 함께 캐시도 갱신
    • 장점: 읽기 미스 감소
    • 단점: 트랜잭션 경계/실패 처리 복잡

현실적인 권장안은 다음입니다.

  • 기본은 invalidate
  • 핫키는 write-through 또는 “삭제 + 짧은 소프트 TTL” 혼합
  • 다중 키(리스트/랭킹)는 이벤트 기반 비동기 재생성

2차 구조 개선: 인덱스 튜닝으로 락 범위를 줄인다

캐시로 숨을 돌렸다면, 이제 DB에서 락이 “왜 넓게 걸리는지”를 줄여야 합니다. InnoDB에서 락 경합은 정확한 인덱스 선택과 직결됩니다. 인덱스가 부정확하면 더 많은 레코드/갭을 건드리고, 그만큼 잠금 충돌이 늘어납니다.

1) 실행 계획으로 “범위”를 확인한다

EXPLAIN ANALYZE
SELECT id, title
FROM posts
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

여기서 체크 포인트:

  • type 이 ref/range 인지, ALL 인지
  • rows 추정치가 과도하게 큰지
  • Using filesort, Using temporary 가 뜨는지

2) 복합 인덱스 순서: WHERE → ORDER BY

예시 쿼리에 맞춘 권장 인덱스:

CREATE INDEX idx_posts_user_created
ON posts (user_id, created_at DESC, id);
  • user_id 로 먼저 좁히고
  • created_at 정렬을 인덱스에서 해결
  • id 를 뒤에 붙여 커버링/타이브레이커에 도움

주의: MySQL 버전/옵티마이저에 따라 DESC 인덱스가 의미가 다를 수 있습니다. 하지만 핵심은 “정렬을 인덱스로 해결”해서 실행 시간을 줄이고, 그 결과 락 보유 시간과 동시 대기열을 줄이는 것입니다.

3) 유니크 충돌/중복 체크는 “쿼리 2번” 대신 “원자 1번”

다음 패턴은 경합을 키웁니다.

  • 먼저 SELECT 로 존재 확인
  • 없으면 INSERT

경합 상황에서 동시 요청이 몰리면 둘 다 SELECT 를 통과하고 INSERT 에서 충돌/락 대기가 생깁니다. 가능하면 다음처럼 바꿉니다.

INSERT INTO user_emails (user_id, email)
VALUES (123, 'a@b.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

또는 “정말로 중복이면 아무것도 하지 않기”라면:

INSERT IGNORE INTO user_emails (user_id, email)
VALUES (123, 'a@b.com');

이렇게 하면 애플리케이션 레벨의 불필요한 왕복이 줄고, 경합 구간이 단축됩니다.

3차 개선: 트랜잭션을 짧게, 잠금은 최소로

핫스팟 락 폭주에서 자주 발견되는 진짜 범인은 “인덱스”가 아니라 긴 트랜잭션입니다.

금지에 가까운 패턴

  • 트랜잭션 안에서 외부 API 호출
  • 트랜잭션 안에서 파일 업로드/다운로드
  • 사용자 입력 대기(웹소켓 등)
  • 대량 배치가 OLTP 테이블을 오래 잠금

개선 원칙

  • 트랜잭션은 “DB 변경”만 포함
  • 읽기 후 쓰기가 필요하면, 읽기는 캐시/리드레플리카로 분리 검토
  • 필요한 경우에만 SELECT ... FOR UPDATE

예시(의사 코드):

-- (1) 트랜잭션 밖에서 필요한 데이터 준비
-- 외부 호출/검증/계산 등

-- (2) 트랜잭션은 짧게
START TRANSACTION;

SELECT stock
FROM inventory
WHERE product_id = 10
FOR UPDATE;

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 10 AND stock > 0;

COMMIT;

핵심은 FOR UPDATE 로 잠근 후, 같은 트랜잭션에서 필요한 업데이트를 즉시 끝내는 것입니다.

“Redis를 붙였는데도” 락이 계속 터진다면

캐시는 읽기를 줄여주지만, 쓰기 핫스팟은 그대로 남습니다. 다음을 점검하세요.

1) 쓰기 핫스팟 샤딩(버킷) 전략

단일 카운터를 버킷으로 나눕니다.

  • counter:{name}:{bucket} 형태로 N개 버킷에 분산
  • 읽을 때 합산, 또는 주기적으로 합쳐서 스냅샷

Redis 예:

# 버킷 16개로 분산
INCRBY counter:order_seq:7 1

MySQL에 최종 반영은 비동기 배치로 모읍니다.

2) 재시도 폭주 차단

락 대기/타임아웃이 나면 클라이언트가 즉시 재시도하면서 부하를 증폭시킵니다.

  • 지수 백오프 + 랜덤 지터
  • 동일 사용자/키에 대한 애플리케이션 레벨 디바운스
  • 서킷 브레이커로 DB 보호

타임아웃 설계/점검은 AWS ALB 502/504 급증? 타임아웃 7곳 점검의 체크리스트를 그대로 적용할 수 있습니다.

3) 락 대기 시간/격리수준/데드락 로깅

운영에서 최소한 아래는 켜두는 편이 좋습니다.

-- 락 대기 제한(초)
SET GLOBAL innodb_lock_wait_timeout = 5;

-- 데드락 로그(에러 로그에 출력)
SET GLOBAL innodb_print_all_deadlocks = ON;

격리수준 변경은 영향 범위가 크므로, 특정 트랜잭션/세션 단위로 먼저 실험하는 것을 권장합니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

실전 적용 순서(가장 중요한 요약)

  1. 관측: innodb_lock_waits, performance_schema.data_locks, EXPLAIN ANALYZE 로 “어느 인덱스/레코드에” 몰리는지 확정
  2. 응급 완화: Redis로 읽기 트래픽을 차단하되, 핫키에 대해 단일 플라이트와 TTL 지터로 스탬피드 방지
  3. 인덱스 튜닝: WHERE/ORDER BY에 맞춘 복합 인덱스로 범위와 실행 시간을 축소
  4. 트랜잭션 단축: 외부 작업을 트랜잭션 밖으로 빼고, FOR UPDATE 를 필요한 최소 범위로
  5. 쓰기 핫스팟 제거: 단일 행 카운터/재고는 버킷 샤딩 또는 Redis 원자 연산+비동기 반영으로 구조 변경

핫스팟 락은 “DB가 느려졌다”가 아니라 “동시성이 무너졌다”는 신호입니다. Redis 캐시는 시간을 벌어주지만, 인덱스/트랜잭션/쓰기 모델을 함께 손봐야 재발을 막을 수 있습니다. 운영 환경에서는 캐시 도입 후에도 반드시 락 지표가 실제로 내려갔는지(대기 시간, 대기 건수, 상위 락 객체)로 효과를 검증하세요.

Discuss on Twitter • View on GitHub