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 고갈

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

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
• Percona Toolkit - pt-online-schema-change