DB 운영 중 데드락과 쿼리 부하 빠르게 진단하기
실제 DB를 운영하다 보면 자주 마주치는 두 가지 주요 상황이 있습니다.
- 데드락(Deadlock)
- 스펙 대비 쿼리 과부하
다음은 각 상황별로 유용한 쿼리입니다.
1. 데드락 확인 및 해결
(1) 현재 트랜잭션 상태 보기
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
- 진행 중인 트랜잭션을 바로 확인할 수 있습니다.
(2) 트랜잭션 잠금 상태 확인
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
- 잠금을 유발하는 트랜잭션을 빠르게 파악합니다.
(3) 현재 프로세스 중 Locked 상태 확인
SHOW FULL PROCESSLIST;
- Locked 상태의 쿼리를 바로 찾아낼 수 있습니다.
(4) 교착 상태(Deadlock) 기록 확인
SHOW ENGINE INNODB STATUS;
- 데드락이 발생한 경우 로그 형태로 제공됩니다.
(5) 특정 트랜잭션 Kill 하기
KILL [process_id];
2. 쿼리 부하 및 성능 저하 분석
(1) 현재 연결된 세션 개수 확인
SHOW STATUS LIKE 'Threads_connected';
(2) 현재 실행 중인 쿼리 개수 확인
SHOW STATUS LIKE 'Threads_running';
(3) 실행 중인 쿼리 상태 실시간 확인
SHOW FULL PROCESSLIST;
- Query 상태로 긴 시간 유지되는 쿼리를 찾습니다.
(4) 상태별 세션 개수 확인
SELECT command, COUNT(*) AS session_count
FROM INFORMATION_SCHEMA.PROCESSLIST
GROUP BY command;
- Sleep이나 Query 등 상태별 현황을 파악합니다.
(5) 오래된 Sleep 상태 세션 찾기
SELECT id, user, host, db, time, state
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE command = 'Sleep' AND time > 300;
(6) 최근 실행된 쿼리 히스토리 확인 (빠르게 지나가는 쿼리)
SELECT event_id, event_name, sql_text, timer_start, timer_end
FROM performance_schema.events_statements_history
ORDER BY timer_start DESC LIMIT 10;
(7) 서버 최대 연결 허용치와 과거 최대 연결 개수 확인
SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Max_used_connections';
