MySQL innoDB 장애대응

 

성능 모니터링 & 메모리 측면

INNODB_BUFFER_PAGE / _LRU

innoDB의 핵심인 버퍼 풀에 현재 어떤 데이터 페이지들이 올라와 있는지 볼 수 있습니다. page type을 보면 UNDO_LOG, SYSTEM, INDEX 등등 버퍼풀에 올라와있는걸 볼 수 있습니다.  추가로 어떤 테이블인지? 어떤 인덱스를 올려 뒀는지? 등등 확인할 수 있어서

인덱스가 자주 사용되는지 등 체크할 수 있는 정보를 제공하고 있습니다.

 

INNODB_BUFFER_POOL_STATS

INNODB_BUFFER_POOL_STATS 테이블은 InnoDB 버퍼 풀(Buffer Pool)의 현재 상태와 지표를 한눈에 파악할 수 있는 모니터링 테이블입니다. 내 DB의 메모리(캐시)가 얼마나 효율적으로 일하고 있는지, 여유 공간은 있는지, 얼마나 바쁘게 움직이는지 확인할 수 있습니다.

HIT_RATE(버퍼 풀 적중률), DATABASE_PAGES(데이터가 들어있는 페이지 수), FREE_BUFFERS(여유 공간) 등을 파악해서 메모리 증설이라 던가 데이터 갱신 및 변경 작업이 얼마나 밀려있는지 볼 수 있습니다.

 

INNODB_TRX

현재 실행 중인 모든 트랜잭션의 정보를 보여줍니다. 누가 Lock을 잡고 안놔주고 있는지? 어떤 쿼리가 올래 걸리는지? 등 찾을 때 trx_mysql_thread_id와 PROCESSLIST를 조인해서 범인 스레드를 찾는데 도움이 되기도 합니다.

INNODB_LOCKS / INNODB_LOCK_WAITS 등을 활용해서 누구 때문에 기다리고 있는지 찾을 수 있기도합니다.

컬럼명	의미	설명 & 활용 포인트
trx_id	트랜잭션 ID	InnoDB가 내부적으로 부여하는 고유 식별자입니다.
trx_state	현재 상태	가장 중요합니다. - RUNNING: 정상 실행 중. - LOCK WAIT: 🚨 비상! 다른 트랜잭션이 잡은 락(Lock)이 풀리길 기다리는 중. (장애 징후)
trx_started	시작 시간	이 시간이 현재 시간보다 너무 오래되었다.
trx_requested_lock_id	기다리는 락 ID	LOCK WAIT 상태일 때, 도대체 어떤 락 때문에 기다리는지 알려줍니다.
trx_wait_started	대기 시작 시간	언제부터 기다렸는지 보여줍니다.
trx_mysql_thread_id	스레드 ID	가장 실무적입니다. SHOW PROCESSLIST의 ID와 같습니다.
trx_query	실행 중인 쿼리	현재 실행 중인 SQL 문장입니다. ⚠️ 주의: 트랜잭션 안에서 쿼리와 쿼리 사이(Java 로직 수행 중)라면 **NULL**로 나올 수 있습니다.

 

 

유용하게 사용할 수 있는 쿼리

SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread, 
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
         INNER JOIN information_schema.innodb_trx b  -- Blocker 정보 조인
                    ON b.trx_id = w.blocking_engine_transaction_id
         INNER JOIN information_schema.innodb_trx r  -- Waiter 정보 조인
                    ON r.trx_id = w.requesting_engine_transaction_id;

현재 누가 멱살을 잡고 있고(Blocker), 누가 잡혀 있는지(Waiter) 한눈에 파악이 필요할 때 

 

SELECT
    dl.OBJECT_NAME,
    dl.LOCK_DATA,
    t.trx_id,
    t.trx_started,
    t.trx_query,
    p.info AS current_query,
    p.user,
    p.host
FROM performance_schema.data_locks dl
         JOIN information_schema.innodb_trx t
              ON dl.ENGINE_TRANSACTION_ID = t.trx_id
         LEFT JOIN information_schema.processlist p
                   ON t.trx_mysql_thread_id = p.id;

도대체 "어떤 테이블의 어떤 데이터(행)" 때문에 싸우고 있는지 확인합니다.

 

SELECT
    t.trx_id,
    th.PROCESSLIST_ID,
    esh.EVENT_ID,
    esh.SQL_TEXT,
    esh.TIMER_WAIT / 1000000000000 AS duration_sec, -- 실행 소요 시간(초)
    esh.rows_affected,  -- 몇 줄이나 건드렸나
    esh.LOCK_TIME / 1000000000000 AS lock_wait_sec
FROM information_schema.innodb_trx t
         INNER JOIN performance_schema.threads th
                    ON th.PROCESSLIST_ID = t.trx_mysql_thread_id
         INNER JOIN performance_schema.events_statements_history esh
                    ON esh.THREAD_ID = th.THREAD_ID
WHERE t.trx_id = :tx_id -- 트랜잭션 id
ORDER BY esh.EVENT_ID DESC
LIMIT 300;

현재(trx_query)는 아무것도 안 하고(NULL) 침묵하고 있을 때, "조금 전까지 무슨 짓(SQL)을 했길래 락을 잡았는지" 과거 이력을 알아낼 수 있습니다.

 

본인 사례

실무에서 특정 작업이 처리 되지 않는 다는 고객문의가 들어왔었는데 실제로 30분이상 작업을 처리하고 있는걸 확인할 수 있었습니다. 어디에서 막힘이 있었는지 파악해보니 DB에 오래 실행중인 트랜잭션이 발견되었고 해당 트랜잭션에서 쿼리가 추가로 발생하지 않는것을 볼 수 있었습니다. 추가로 확인해 봤을 때 lock을 기다린다거나 하는 상황은 아니라고 판단해서 애플리케이션 단에 코드를 확인했었고 계층 구조를 가진 데이터에서 parentId가 본인이여서 순환 참조가 걸려 무한 루프를 돌고 있는 문제를 발견할 수 있었습니다. 물론 해당 부분의 대한 검증은 항상 이뤄져야 하는게 맞지만 API 요청이 아닌 엑셀을 통한 대량의 데이터 삽입에서 검증이 누락되어 발생한 문제로 전파했습니다.