500만 건 UPDATE로 인한 MySQL 락 대기 10분 장애 대응

12월 23일, 라이브 서버에서 MySQL 응답이 10분가량 사실상 멈췄다.
API 응답 시간이 치솟았고, 일부 요청은 타임아웃으로 실패했다.
원인은 주니어 개발자가 운영 DB에서 직접 실행한 대용량 UPDATE 쿼리였다.

장애 상황, 원인 분석, 대응 과정을 정리한다.

#1. 상황 발생

평소 50ms 수준이던 API 응답 시간이 30초, 60초 이상으로 급증했다.
일부 API는 Connection Timeout이 발생했고, Grafana 대시보드에서는 MySQL 활성 커넥션 수가 함께 치솟고 있었다.

#2. 원인이 된 대용량 UPDATE 쿼리

주니어 개발자가 실행한 쿼리는 이런 형태였다.

UPDATE user_logs 
SET status = 'archived' 
WHERE created_at < '2024-01-01';

user_logs는 수천만 건 규모의 로그 테이블이고, WHERE 조건에 걸리는 row는 500만 건 이상이었다.
두 수치는 당시 COUNT로 정확히 확인한 값이 아니라 테이블 전체 규모와 날짜 분포를 바탕으로 추정한 값이다.
이 분량을 트랜잭션 하나로 UPDATE하려 한 것이 문제였다.

#왜 UPDATE 하나가 전체 지연으로 번졌나

InnoDB는 UPDATE 시 대상 row에 락을 건다.
500만 건의 row가 한꺼번에 잠기면서 같은 테이블을 건드리는 쿼리들이 대기 상태에 빠졌다.

여기에 Undo Log 부담이 겹쳤다.
InnoDB는 트랜잭션 롤백에 대비해 변경 전 데이터를 Undo Log로 유지하는데, 500만 건 분량이 쌓이면서 메모리와 I/O 압박이 발생했다.

락을 기다리는 쿼리는 대기하는 동안에도 커넥션을 계속 점유했다.
Connection Pool이 고갈되자 새로운 요청은 커넥션조차 얻지 못하고 타임아웃으로 이어졌다.

#3. innodb_trx로 트랜잭션 추적

장애 원인을 찾는 데 사용한 쿼리다.

SELECT 
    trx_mysql_thread_id,
    trx_started, 
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS trx_age_sec, 
    trx_state, 
    LEFT(trx_query, 200) AS trx_query_sample 
FROM information_schema.innodb_trx 
ORDER BY trx_started;

#쿼리 결과 분석

thread_id 12345가 360초째 실행 중이었고, 나머지 트랜잭션은 전부 LOCK WAIT 상태였다.
문제의 쿼리는 UPDATE user_logs였다.

#innodb_trx 컬럼 설명

락을 누가 잡고 누가 기다리는지까지 락 단위로 파고드는 진단(data_locks, data_lock_waits)은 별도 글로 이어서 다룬다.

#4. 문제 쿼리 Kill

문제 쿼리를 찾았으니 강제 종료한다.

-- 1. 문제 스레드 확인
SHOW PROCESSLIST;
 
-- 2. 해당 스레드 Kill
KILL 12345;

#Kill 후 주의사항

쿼리를 Kill하면 InnoDB가 롤백을 시작한다.
대용량 UPDATE의 롤백은 변경분을 전부 되돌리는 작업이라 시간이 걸릴 수 있다.

-- 롤백 진행 상황 확인
SHOW ENGINE INNODB STATUS\G

TRANSACTIONS 섹션에서 undo log entries 수치가 줄어드는 것을 확인할 수 있다.

타임라인의 14:41은 API 응답 시간이 평소 수준으로 돌아온 시점이다.
응답 회복과 롤백 완료는 별개의 사건인데, 롤백이 끝날 때까지 걸린 시간은 당시 따로 기록해두지 않았다.

이번 대응은 응답 지연과 커넥션 급증을 확인한 뒤, innodb_trx로 장기 실행 트랜잭션을 찾고 SHOW PROCESSLIST로 쿼리를 특정해 KILL했다.
이후 Undo Log가 정리될 때까지 롤백을 확인했다.

#5. 대용량 UPDATE를 안전하게 실행하는 방법

#배치로 나눠서 실행

한 번에 전부 갱신하는 대신 1만 건 단위로 나눠 반복 실행한다.

-- affected rows가 0이 될 때까지 반복 실행
UPDATE user_logs 
SET status = 'archived' 
WHERE created_at < '2024-01-01' 
  AND status != 'archived'
LIMIT 10000;

MySQL의 REPEAT … UNTIL 같은 루프 문법은 스토어드 프로시저 안에서만 동작하므로, 루프는 애플리케이션이나 배치 스크립트 쪽에서 도는 편이 낫다.
affected rows가 0이 될 때까지 위 쿼리를 반복하되, 반복 사이에 짧은 대기를 넣어 다른 쿼리가 락을 잡을 틈을 준다.

#pt-archiver 사용

오래된 로그를 다른 테이블로 옮기거나 지우는 게 목적이라면, Percona Toolkit의 pt-archiver가 청크 단위 커밋을 대신 처리해준다.

pt-archiver \
    --source h=db-host,D=mydb,t=user_logs \
    --dest h=db-host,D=mydb,t=user_logs_archive \
    --where "created_at < '2024-01-01'" \
    --limit 1000 \
    --commit-each

#운영 DB 직접 접근 제한

• 운영 DB 접근 권한을 최소화
• 대용량 쿼리는 반드시 코드 리뷰 후 실행
• 읽기 전용 Replica에서 먼저 테스트

정리하면 이렇다.

#6. 모니터링 개선

이번 장애를 계기로 모니터링을 추가했다.

#장기 실행 트랜잭션 알람

-- 60초 이상 실행 중인 트랜잭션
SELECT COUNT(*) 
FROM information_schema.innodb_trx 
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60;

이 값이 0보다 크면 슬랙 알람을 보낸다.

#Lock Wait 모니터링

-- Lock 대기 중인 쿼리 수
SELECT COUNT(*) 
FROM information_schema.innodb_trx 
WHERE trx_state = 'LOCK WAIT';

#Grafana 대시보드 추가

• Active Connections: 활성 커넥션 수 추이
• Lock Wait Time: 락 대기 시간 분포
• Long Running Queries: 10초 이상 실행 쿼리 수

#마무리

주니어 개발자의 실수로 시작된 장애였지만, 그 실수를 걸러내지 못한 시스템의 문제이기도 했다.
그래서 재발 방지도 사람을 탓하는 대신, 대용량 쿼리 실행 가이드와 장기 실행 트랜잭션 알람처럼 실수를 막는 장치를 만드는 쪽으로 정리했다.