MySQL 10분 장애 회고 - 대용량 UPDATE가 DB를 먹통으로 만든 날

12월 23일, 라이브 서버에서 갑자기 MySQL이 10분 가량 먹통이 됐다. API 응답 시간이 치솟고, 일부 요청은 타임아웃. 원인은 주니어 개발자가 실행한 대용량 UPDATE 쿼리였다.

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

1. 상황 발생

증상:

• 갑자기 API 응답 시간이 평소 50ms → 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만 건 이상
• 한 번의 트랜잭션으로 500만 건을 UPDATE하려고 시도

왜 DB가 먹통이 됐나?

1. Row-level Lock

InnoDB는 UPDATE 시 해당 row에 락(Lock)을 건다. 500만 건의 row가 한꺼번에 잠기면서 다른 쿼리들이 대기 상태에 빠졌다.

2. Undo Log 폭증

InnoDB는 트랜잭션 롤백을 위해 Undo Log를 생성한다. 500만 건의 Undo Log가 쌓이면서 메모리와 I/O 압박이 발생했다.

3. Connection Pool 고갈

락 대기하는 쿼리들이 커넥션을 점유하면서 새로운 요청은 커넥션을 얻지 못해 타임아웃이 발생했다.

flowchart LR
    A[대용량 UPDATE 실행] --> B[500만 row Lock]
    B --> C[다른 쿼리 대기]
    C --> D[Connection 고갈]
    D --> E[API Timeout]

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가 6분(360초) 동안 실행 중
• 다른 트랜잭션들은 모두 LOCK WAIT 상태
• 범인은 UPDATE user_logs 쿼리

innodb_trx 컬럼 설명

4. 대응: 문제 쿼리 Kill

범인 쿼리를 찾았으니 강제 종료한다:

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

Kill 후 주의사항

쿼리를 Kill하면 InnoDB가 롤백을 시작한다. 대용량 UPDATE의 롤백은 시간이 걸릴 수 있다.

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

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

5. 재발 방지: 대용량 UPDATE 안전하게 하기

배치로 나눠서 실행

-- 한 번에 1만 건씩 UPDATE
SET @batch_size = 10000;
 
REPEAT
    UPDATE user_logs 
    SET status = 'archived' 
    WHERE created_at < '2024-01-01' 
      AND status != 'archived'
    LIMIT @batch_size;
    
    SELECT ROW_COUNT() INTO @affected;
    SELECT SLEEP(0.5); -- 잠시 쉬어주기
UNTIL @affected = 0 END REPEAT;

pt-online-schema-change 사용

Percona Toolkit의 pt-online-schema-change를 사용하면 대용량 변경을 안전하게 수행할 수 있다.

pt-online-schema-change \
    --alter "ENGINE=InnoDB" \
    --execute \
    D=mydb,t=user_logs

운영 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초 이상 실행 쿼리 수

7. 얻은 것

장애 대응 체크리스트

1. 증상 확인: API 응답 지연? Connection 고갈?
2. innodb_trx 확인: 장기 실행 트랜잭션 찾기
3. SHOW PROCESSLIST: 문제 쿼리 상세 확인
4. KILL: 범인 쿼리 강제 종료
5. 롤백 대기: Undo Log 정리될 때까지 모니터링

대용량 쿼리 규칙

마무리

주니어 개발자의 실수지만, 시스템이 이런 실수를 막지 못한 것도 문제다.

이번 장애를 통해:

1. innodb_trx로 트랜잭션을 빠르게 추적하는 방법을 익혔다
2. 대용량 쿼리 실행 가이드를 만들었다
3. 장기 실행 트랜잭션 모니터링을 추가했다

장애는 배움의 기회다. 같은 실수를 반복하지 않도록 시스템과 프로세스를 개선하는 게 중요하다.

참고 자료

• MySQL 공식 문서 - InnoDB INFORMATION_SCHEMA