728x90

소개

애플리케이션이 평소보다 느리거나 데드락이 잦아질 때, 가장 빠르게 내부 상태를 엿볼 수 있는 도구가 SHOW ENGINE INNODB STATUS입니다. 이 명령은 InnoDB의 트랜잭션·잠금·버퍼 풀·I/O·백그라운드 스레드 정보 등 “그 시점의 스냅샷”을 텍스트로 덤프합니다. 스키마 변경이나 추가 설치가 필요 없고, 프로덕션에서도 저비용으로 실행할 수 있어 MySQL 사용자라면 반드시 익혀둘 가치가 있습니다.

본문

1) SHOW ENGINE 개요와 기본 사용법

MySQL의 SHOW ENGINE은 저장 엔진 별 내부 상태를 출력하는 진단 명령입니다.

기본 형식:
SHOW ENGINE <engine_name> STATUS;
가장 자주 쓰는 대상은 InnoDB:\G를 붙이면 긴 텍스트가 세로 방향으로 보기 좋게 출력됩니다.
SHOW ENGINE INNODB STATUS\G

다른 엔진(예: PERFORMANCE_SCHEMA, MyISAM)은 서버/버전 상태에 따라 정보가 제한적일 수 있습니다. 실무에서는 사실상 InnoDB 전용 툴이라 생각하면 편합니다.

2) SHOW ENGINE INNODB STATUS 결과 읽는 법(핵심 섹션별 가이드)

결과는 여러 블록으로 나뉘며, 다음 순서로 보는 것을 추천합니다.

LATEST DETECTED DEADLOCK
- 가장 최근 데드락의 관련 트랜잭션·쿼리·잠금 타입이 기록됩니다.
- “WE ROLL BACK TRANSACTION” 부분에서 어떤 트랜잭션이 희생됐는지 확인하세요.
- 개선 팁: 인덱스 적중률, 잠금 순서 일관성(테이블 접근 순서 통일), 트랜잭션 단위 축소.
TRANSACTIONS
- 활성 트랜잭션 목록, 각 트랜잭션의 ACTIVE 시간(초), 격리 수준, 잠금 대기 정보가 나옵니다.
- LOCK WAIT가 보이면 어떤 객체(레코드/인덱스)에서 대기 중인지 확인합니다.
- 개선 팁: 오래 열린 트랜잭션(특히 autocommit=0인 세션) 종료, 비필요한 대기 줄이기.
SEMAPHORES
- 래치/뮤텍스 경합을 보여줍니다.
- waits 값이 많으면 내부 동시성 경합이 심한 상태일 수 있습니다.
- 개선 팁: 너무 큰 동시 처리(커넥션 폭증) 조절, 핫스팟 테이블/인덱스 분산.
FILE I/O / BUFFER POOL AND MEMORY
- 디스크 I/O 큐, 읽기/쓰기 요청, 버퍼 풀 크기·적중률(hit rate), 플러시 상황이 나타납니다.
- Buffer pool hit rate가 낮고 I/O가 많다면 버퍼 풀 크기 확대를 고려합니다.
- 개선 팁: innodb_buffer_pool_size 튜닝, 페이지 청크 크기/인스턴스 수(innodb_buffer_pool_instances) 점검.
ROW OPERATIONS
- 레코드 수준 작업 통계(삽입/갱신/삭제)와 purge 진행 상황이 보입니다.
- history list length가 길면 purge가 밀리는 신호로, 장기 트랜잭션이 원인인 경우가 많습니다.

3) 실전 예시로 이해하기

증상: 특정 업데이트 트랜잭션이 길게 대기
관찰: TRANSACTIONS에서 A 트랜잭션이 LOCK WAIT 상태, B 트랜잭션은 300초 이상 ACTIVE
추론: B가 같은 레코드/인덱스를 오래 잡고 있어 A가 대기
대응:
1. B 트랜잭션이 실행 중인 쿼리 및 코드 경로 점검, 트랜잭션 범위 축소
2. 필요한 인덱스 보강으로 레코드 범위를 좁혀 잠금 충돌 감소
3. 재현 가능하면 트랜잭션 접근 순서 정렬(예: 항상 고객→주문 순)

이처럼 상태 덤프에서 “누가 누구를 막는지” 연결고리를 찾아내면 원인 파악이 빨라집니다.

4) MySQL 5.7과 8.0의 변화와 주의점

InnoDB 잠금/트랜잭션 출력 형식 변경: 8.0에서는 내부 구현이 발전하면서 보고서 포맷과 일부 라벨이 달라질 수 있습니다. 스크립트로 파싱한다면 버전별 차이를 감안하세요.
Purge/Undo 관련 동작: 8.0은 Redo/Undo, 데이터 딕셔너리(공간 관리) 등이 개선되어 history list length 상승 원인과 회복 속도가 5.7과 다르게 관찰될 수 있습니다.
데드락 로깅: 8.0에선 에러 로그(진단 로그)와 PERFORMANCE_SCHEMA를 병행하면 더 풍부한 정보를 얻습니다. SHOW ENGINE만으로 부족할 때 performance_schema의 events_transactions_*, data_locks, data_lock_waits 뷰를 함께 보세요.
유니코드/정렬 규칙: 8.0에서 기본 정렬 규칙/문자셋(예: utf8mb4_0900_ai_ci) 변화로 인덱스 선택과 잠금 패턴이 달라질 수 있습니다. 같은 쿼리여도 실행 계획과 경합 양상이 달라질 수 있음을 염두에 두세요.
Global Transaction IDs/복제 환경: 8.0에서 그룹 리플리케이션/GTID와 병행할 때 트랜잭션 체류 시간이 지연될 수 있으므로, SHOW ENGINE 관찰값과 레플리케이션 지표(Replica_* 상태)도 함께 확인해야 합니다.

5) SHOW ENGINE과 보완 도구를 함께 쓰기

잠금 상세:실제로 어떤 레코드/인덱스 키가 충돌하는지 구조화된 형태로 파악할 수 있습니다.
SELECT * FROM performance_schema.data_locks\G SELECT * FROM performance_schema.data_lock_waits\G
슬로우 쿼리 상관 분석: SHOW ENGINE의 대기/경합 지표와 슬로우 로그를 맞춰 보면, 문제 구간과 쿼리를 연결하기 쉽습니다.
상시 모니터링: 주기적으로 SHOW ENGINE INNODB STATUS를 캡처해 보관하면, 장애 시점과 평상시의 차이를 비교 분석할 수 있습니다.

결론 및 실무 팁

정리: SHOW ENGINE INNODB STATUS는 프로덕션에서 가볍게 실행 가능한 강력한 1차 진단 도구입니다. 데드락, 잠금 대기, 버퍼 풀/I/O 병목, purge 지연 등 핵심 신호를 한 번에 제공합니다. 5.7과 8.0은 출력 포맷과 내부 동작이 달라 관찰 결과가 다를 수 있으니 버전별 특성을 인지하고 해석하세요.

실무 팁:

출력 자동 수집: 장애 징후(응답 지연, 커넥션 급증) 감지 시 SHOW ENGINE INNODB STATUS\G를 즉시 덤프해서 파일로 남기도록 스크립팅하세요. 사건 전후 비교가 큰 힘이 됩니다.
장기 트랜잭션 사냥: TRANSACTIONS 섹션에서 ACTIVE 시간이 긴 세션을 우선 종료·최적화하세요. 장기 트랜잭션은 purge 지연과 잠금 폭증의 단골 원인입니다.
버퍼 풀 지표 체크리스트: Buffer pool hit rate, 플러시 큐 길이, I/O 대기 시간이 나쁘면 innodb_buffer_pool_size와 체크포인트/플러시 파라미터(예: innodb_flush_neighbors, innodb_io_capacity)를 점검하세요.

공식 문서

MySQL Reference Manual – SHOW ENGINE 및 InnoDB 상태:
https://dev.mysql.com/doc/refman/8.0/en/show-engine.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-monitors.html

(5.7을 사용 중이라면 5.7 문서를 참고하세요: https://dev.mysql.com/doc/refman/5.7/en/show-engine.html)