Ubuntu에서 MySQL이 “갑자기 느려졌다”, “간헐적으로 멈춘다”처럼 보이는 순간은 실제로 CPU가 아니라 락(LOCK) 대기일 때가 많습니다. 이 글은 증상(슬로우/락) → 측정 → 개선 순서로, 그리고 인덱스/스키마 중심으로 재발을 줄이는 방법을 정리합니다.
먼저 “지금 무엇을 기다리고 있는지”부터 확인하면, 불필요한 튜닝으로 삽질하는 시간을 크게 줄일 수 있어요.
1) 락으로 느려질 때 흔한 증상 패턴(슬로우와 구분)
락 대기는 슬로우 쿼리처럼 보여도 양상이 조금 다릅니다. 아래 패턴이 겹치면 “쿼리가 느린 게 아니라 서로 기다리는 상황”일 가능성이 큽니다.
- 특정 API/배치가 시작된 뒤 다른 쿼리까지 연쇄적으로 느려짐(도미노)
- CPU 사용률은 높지 않은데 응답 시간이 크게 늘어남
- 같은 쿼리도 어떤 때는 즉시 끝나고 어떤 때는 오래 대기함(변동 폭이 큼)
- MySQL 오류 로그에 deadlock 기록이 간헐적으로 보임
- 웹/앱에서 타임아웃이 늘고, DB 커넥션 풀이 쉽게 고갈됨
이 단계에서는 “DB가 느리다”가 아니라 락 대기 때문에 처리량이 떨어진다로 가설을 잡는 게 핵심입니다.
2) 지금 누가 누구를 막고 있는지: 즉시 확인용 쿼리
운영 중일 때는 길게 분석하기 전에, 우선 현재 대기/점유 관계를 빠르게 보는 게 좋습니다. 아래는 위험한 조작 없이 조회만 합니다.
2-1. 실행 중 스레드/대기 확인
MySQL 8.0 기준으로는 performance_schema가 가장 정확합니다. 우선 간단히는 다음을 확인합니다.
SHOW PROCESSLIST에서 다음을 눈여겨보세요.
- State에 Waiting for table metadata lock, Waiting for row lock 등 대기 상태
- Time이 비정상적으로 큰데, 실제로는 실행이 아니라 대기 중인 쿼리
- 같은 테이블을 대상으로 UPDATE/INSERT가 길게 잡혀 있는 세션
2-2. InnoDB 락/트랜잭션 상태(즉시용)
다음은 “누가 오래 잡고 있나”를 보는 데 도움이 됩니다.
- SHOW ENGINE INNODB STATUS (LATEST DETECTED DEADLOCK, TRANSACTIONS 섹션)
deadlock이 발생했다면, 여기의 로그에 “서로 어떤 인덱스를 잡으려다 충돌했는지”가 비교적 상세히 남습니다. 개선 포인트(인덱스/쿼리 순서)가 직접 드러나는 경우가 많아요.
3) 측정 단계: “락이 원인인지”를 숫자로 고정하기
감으로는 팀을 설득하기 어렵고, 개선 후 검증도 힘듭니다. 아래 지표를 최소한으로 잡아두면, 락이 병목인지 훨씬 명확해집니다.
- 락 대기 시간/횟수: performance_schema의 wait 이벤트(가능한 경우)
- 슬로우 로그: Query_time이 긴 쿼리와 함께, 동일 시점에 대기 상태가 있었는지 교차 확인
- 트랜잭션 지속 시간: “오래 열린 트랜잭션”이 락을 길게 잡는지
- Rows_examined 대비 Rows_sent: 인덱스 미스(스캔)가 락 시간을 키우는지
운영에서 자주 나오는 케이스는 이 조합입니다: 인덱스 미스 → 많은 row를 스캔/락 → 트랜잭션 시간이 늘어남 → 다른 세션 대기 증가.
4) 인덱스 중심 개선 1: “락 시간을 줄이는 인덱스”부터 만든다
락 문제를 인덱스로 해결한다는 말이 이상하게 들릴 수 있는데, 실무에서는 매우 흔합니다. 핵심은 “락 자체를 없애기”가 아니라 락을 잡는 시간과 범위를 줄이는 것입니다.
4-1. WHERE/JOIN 조건 컬럼에 인덱스가 없으면, 업데이트가 ‘검색’ 때문에 오래 걸립니다
- UPDATE/DELETE가 대상 row를 찾기 위해 풀스캔을 하면, 그 동안 더 많은 row/페이지를 건드리며 락 경쟁이 심해질 수 있습니다.
- 먼저 해당 UPDATE/DELETE의 WHERE 조건에 맞는 인덱스를 설계합니다. (복합 조건이면 보통 복합 인덱스 고려)
4-2. “선택도 높은 컬럼”을 앞쪽에 두는 복합 인덱스
- 예: (tenant_id, status, created_at) 같은 조건이라면, 실제 분포에 따라 (tenant_id, status)까지만으로도 충분한지 검토
- 정렬/범위(created_at) 조건이 섞이면, 앞쪽 equality 조건을 먼저 두는 설계가 대체로 유리
4-3. 커버링 인덱스로 “읽기 락 대기”도 줄어드는 경우
- 조회가 테이블을 많이 읽고, 그 결과로 업데이트와 충돌한다면, 커버링 인덱스(필요 컬럼을 인덱스에 포함)로 디스크 읽기/처리 시간을 줄여 전체 경쟁을 완화하는 경우가 있습니다.
4-4. 인덱스 추가 전 체크리스트
- 해당 쿼리의 EXPLAIN에서 type이 ALL(풀스캔)인지, key가 NULL인지
- 후보 인덱스가 이미 있는데도 안 타면, 조건 형태(함수/형변환/LIKE 패턴) 때문에 못 타는지
- 인덱스 추가가 쓰기 비용을 증가시키므로, 쓰기 빈도 높은 테이블은 꼭 영향도 확인
5) 스키마 중심 개선 2: 긴 트랜잭션과 핫스팟을 구조적으로 줄이기
인덱스만으로 부족할 때는, 락을 오래 잡게 만드는 “스키마/데이터 구조”를 손보는 편이 재발 방지에 더 효과적입니다.
5-1. 핫스팟(Hot row) 패턴 피하기
- 한 row에 카운터를 계속 누적 업데이트(예: today_count)하면 그 row가 병목이 됩니다.
- 대안: 집계 테이블을 시간/키로 분산(예: (date, shard, ...)로 분산)하거나, 배치 집계로 전환
5-2. 대량 UPDATE/DELETE를 작은 단위로 끊기 쉬운 키 설계
- 예: created_at, id 같은 단조 증가 키를 기준으로 범위를 잘라 처리할 수 있게 스키마/인덱스를 맞춥니다.
- 이렇게 하면 한 번에 잡는 락 범위를 줄이고, 트랜잭션 시간을 짧게 유지하기가 쉬워집니다.
5-3. FK(외래키)로 인한 예상치 못한 락 범위 확인
- FK가 걸린 테이블에서 DELETE/UPDATE가 발생하면 관련 테이블까지 검사/락이 확장될 수 있습니다.
- FK가 필요한 무결성인지, 또는 애플리케이션 레벨로 대체 가능한지(팀 정책에 따라) 점검 포인트입니다.
6) 개선 후 검증: “빨라졌다”가 아니라 “대기 시간이 줄었다”로 확인
락 이슈는 재현이 어렵기 때문에, 검증은 더더욱 지표 중심이어야 합니다. 아래 순서로 확인하면 깔끔합니다.
- 개선 대상 쿼리의 EXPLAIN 변화(인덱스 사용, rows 추정치 감소)
- 슬로우 로그에서 Query_time의 분산 감소(최악 구간이 줄었는지)
- 동일 시간대에 Waiting 상태 세션 수 감소(processlist/performance_schema)
- deadlock 발생 빈도 추이(있었다면) 감소
팁으로, 인덱스 추가/변경 후에는 통계가 최신이 아니어서 플랜이 흔들릴 수 있습니다. 꼭 “바로 좋아졌다/안 좋아졌다”만으로 판단하지 말고, 최소한 같은 트래픽 패턴에서 몇 번은 관찰하세요.
마무리
Ubuntu 환경의 MySQL 락 문제는 결국 “누가 오래 잡고 있나”를 찾고, 그 시간을 인덱스/스키마로 짧게 만드는 작업으로 수렴하는 경우가 많습니다.
증상 확인 → 대기 관계 측정 → 인덱스/스키마 개선 → 지표로 검증, 이 루틴을 한 번 만들어두면 다음 장애 때 대응 속도가 확 달라집니다.