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

12월 23일, 라이브 서버에서 MySQL 응답이 10분가량 사실상 멈췄다.
API 응답 시간이 치솟았고, 일부 요청은 타임아웃으로 실패했다.
직접 원인은 운영 DB에서 수동으로 실행된 대용량 UPDATE 한 건이었다.
다만 그 쿼리가 배치 분할이나 리뷰 없이 운영 DB에 그대로 닿을 수 있었다는 점이 더 근본적인 원인이었다.

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

#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로 정확히 확인한 값이 아니라 테이블 전체 규모와 날짜 분포를 바탕으로 추정한 값이다.
쿼리의 의도는 오래된 로그를 보관 상태로 바꾸는 정리 작업이었고, SQL 자체에 잘못된 곳은 없었다.
문제는 이 분량이 트랜잭션 하나로 묶여 실행됐다는 점이다.

#왜 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 직접 접근 제한

이 쿼리는 누가 실행했더라도 같은 결과를 냈을 것이다.
락과 Undo Log는 실행한 사람을 가리지 않으니, 개인이 더 조심하는 것만으로는 막기 어렵다.
대용량 쿼리가 운영 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초 이상 실행 쿼리 수

#마무리

쿼리를 실행한 사람이 누구였든 같은 결과가 났을 장애였다.
수백만 건짜리 UPDATE가 리뷰 없이 운영 DB에서 실행될 수 있었고, 실행 후 첫 알람이 오기까지 5분이 걸렸다.
그래서 재발 방지도 개인의 주의에 기대는 대신 배치 실행·사전 리뷰 같은 절차에서 찾기로 했고, 장기 실행 트랜잭션 알람 같은 모니터링은 다음 과제로 남겨뒀다.