성능 모니터링 & 메모리 측면
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 요청이 아닌 엑셀을 통한 대량의 데이터 삽입에서 검증이 누락되어 발생한 문제로 전파했습니다.
'Study > 데이터베이스' 카테고리의 다른 글
| 왜 인덱스를 쓰지 않는건데?! (1) | 2024.05.28 |
|---|---|
| [REDIS] Lettuce VS Jedis (1) | 2023.11.16 |
| MySQL - ERROR 1410 (42000): You are not allowed to create a user whith GRANT (0) | 2022.10.10 |
| 1. 데이터베이스 개요 (1) | 2022.10.10 |
| 데이터베이스의 정의와 특징 (0) | 2022.09.13 |