SHOW ENGINE INNODB STATUS\G 해석

 

MySQL 또는 MariaDB에서 InnoDB 스토리지 엔진의 내부 상태를 상세히 확인하기 위해 사용하는 SQL 명령어

 

출력 결과의 주요 섹션 

1. BACKGROUND THREAD:
   • InnoDB의 백그라운드 스레드 상태를 보여줍니다.
2. SEMAPHORES:
   • 세마포어(잠금) 관련 정보를 제공합니다. 잠금 경합이 발생하는지 확인할 수 있습니다.
3. TRANSACTIONS:
   • 현재 실행 중인 트랜잭션과 잠금 정보를 보여줍니다.
4. FILE I/O:
   • InnoDB의 파일 I/O 작업 상태를 나타냅니다.
5. INSERT BUFFER AND ADAPTIVE HASH INDEX:
   • 인서트 버퍼와 Adaptive Hash Index의 상태를 보여줍니다.
6. LOG:
   • InnoDB의 로그(Redo Log) 상태를 나타냅니다.
7. BUFFER POOL AND MEMORY:
   • 버퍼 풀과 메모리 사용 상태를 보여줍니다.
8. ROW OPERATIONS:
   • 현재 실행 중인 행 작업(삽입, 업데이트, 삭제 등)에 대한 정보를 제공합니다.

---

예시결과

*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2023-10-10 12:34:56 0x7f8b8c0b7700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 60 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 123456 srv_active, 0 srv_shutdown, 123456 srv_idle
srv_master_thread log flush and writes: 123456
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 123456
OS WAIT ARRAY INFO: signal count 123456
Mutex spin waits 123456, rounds 123456, OS waits 123456
RW-shared spins 123456, OS waits 123456; RW-excl spins 123456, OS waits 123456
------------
TRANSACTIONS
------------
Trx id counter 123456
Purge done for trx's n:o < 123456 undo n:o < 123456
History list length 123456
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 123456, ACTIVE 10 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0]
Pending flush writes: 0
123456 OS file reads, 123456 OS file writes, 123456 OS fsyncs
0.00 reads/s, 0.00 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 123456, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 123456
Log flushed up to 123456
Pages flushed up to 123456
Last checkpoint at 123456
0 pending log writes, 0 pending chkp writes
123456 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 123456;
Additional memory allocated 123456
Dictionary memory allocated 123456
Buffer pool size   123456
Free buffers       123456
Database pages     123456
Old database pages 123456
Modified db pages  123456
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 123456, not young 123456
0.00 youngs/s, 0.00 non-youngs/s
Pages read 123456, created 123456, written 123456
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 123456, id 123456, state: sleeping
Number of rows inserted 123456, updated 123456, deleted 123456, read 123456
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
----------------------------
1 row in set (0.00 sec)

 

각 섹션에 대한 의미 분석

BACKGROUND THREAD ( InnoDB 메인 백그라운드 스레드의 작업실행 통계  )

InnoDB는 서버 상태에 따라 1초에 한 번씩 백그라운드 작업을 수행하는 소스 스레드가 있습니다.

BACKGROUND THREAD
-----------------
srv_master_thread loops: 123456 srv_active, 0 srv_shutdown, 123456 srv_idle
srv_master_thread log flush and writes: 123456

srv_master_thread loops:

마스터 스레드의 루프수입니다.
srv_active:

스레드가 활성 상태로 작업을 수행한 횟수입니다.
이 값이 높다면, 스레드가 지속적으로 작업을 처리하고 있다는 것을 의미합니다.
srv_shutdown:
스레드가 종료(shutdown) 상태로 진입한 횟수입니다.
일반적으로 이 값은 0이며, 데이터베이스가 종료될 때 증가합니다. 서버 종료가 진행되지 않고 정상적으로 운영 중
srv_idle:
스레드가 유휴(idle) 상태로 대기한 횟수입니다.
이 값이 높다면, 스레드가 작업을 기다리는 시간이 많다는 것을 의미합니다.

srv_master_thread log flush and writes :

스레드가 로그를 디스크에 기록(Flush)한 횟수입니다.

이 값은 Redo 로그가 얼마나 자주 디스크에 기록되었는지를 나타냅니다.

높은 값은 로그 기록 작업이 빈번하게 발생하고 있음을 의미합니다.

 

참고로 MySQL메인 스레드에는 다음과 같은 스레드들이 있다.

- Master Thread : 메인 스레드, 주로 비동기 새로 고침 및 데이터 일관성 처리를 담당합니다.

버퍼 풀 관리:더티 페이지(Dirty Page, 수정된 페이지)를 디스크에 기록(Flush)합니다. 사용되지 않는 페이지를 정리합니다.
로그 관리:Redo 로그(트랜잭션 로그)를 디스크에 기록합니다.
체크포인트 생성:주기적으로 체크포인트를 생성하여, 데이터베이스의 일관성을 유지합니다.
삭제 작업 처리:삭제된 데이터를 정리(Purge)합니다.

- IO Thread: 비동기 IO 모델을 사용하여 다양한 유형의 IO 요청 콜백 처리를 담당합니다.

- Purge thread: 트랜잭션이 커밋된 후 Recycling undo.

- Page Cleaner Thread : ibp Dirty 페이지 새로 고침을 수행합니다.

 

 

• 높은 srv_active: InnoDB가 작업을 활발히 수행 중임을 나타냅니다. 쓰기 또는 읽기 작업이 많을 때 주로 발생합니다.
• 높은 srv_idle: 시스템이 주로 대기 상태에 있음을 의미합니다. 트래픽이 적거나 부하가 낮을 때 관찰됩니다.

 

---

SEMAPHORES ( 세마포어(Semaphore)와 뮤텍스(Mutex)의 상태 )

InnoDB 내부에서 락(lock)과 동기화 기법에 대한 정보를 제공합니다.

이 출력은 InnoDB가 얼마나 자주 대기(wait)하거나 신호(signal)를 처리했는지, 그리고 동기화를 위해 어떤 작업이 수행되었는지를 보여주며, 잠금 경합(Contention)이나 병목 현상을 진단하는 데 매우 유용

SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 123456
OS WAIT ARRAY INFO: signal count 123456
Mutex spin waits 123456, rounds 123456, OS waits 123456
RW-shared spins 123456, OS waits 123456; RW-excl spins 123456, OS waits 123456

reservation count: 

세마포어를 예약한 횟수를 나타냅니다. 이 값은 스레드가 잠금을 요청한 횟수를 의미합니다.
signal count: 

세마포어를 해제한 횟수를 나타냅니다. 이 값은 잠금이 해제된 횟수를 의미합니다.

적정선:reservation count와 signal count는 일반적으로 비슷한 값을 유지해야 합니다. 차이가 크다면 잠금 경합이 발생하고 있음을 의미합니다.

 

• 높은 reservation count는 리소스 경합(contention)이 많음을 나타낼 수 있습니다.
• 높은 signal count는 대기 상태를 해소하기 위해 많은 작업이 성공적으로 처리되었음을 나타냅니다.

 

reservation count와 signal count의 차이가 큰 경우
원인: 세마포어 예약과 해제가 불균형하다는 것을 의미합니다. 이는 잠금이 제때 해제되지 않아 경합이 발생하고 있음을 나타냅니다.
해결 방안:
트랜잭션을 더 짧게 유지하여 잠금을 빨리 해제합니다.
불필요한 잠금을 피하도록 쿼리를 최적화합니다.

 

Mutex spin waits:
InnoDB에서 리소스 사용을 위한 잠금(mutual exclusion, mutex)을 요청할 때, 잠금을 얻기 위해 대기한 시도 횟수입니다.
rounds:
하나의 spin wait 동안 수행된 반복 회수입니다.
높은 값은 리소스를 얻기 위해 여러 번 시도했음을 나타냅니다.
OS waits:
spin wait로 리소스를 얻지 못한 경우, 운영 체제(OS) 수준에서 리소스를 기다린 횟수입니다.

• 높은 spin waits와 rounds는 InnoDB에서 동기화 작업이 빈번하다는 신호일 수 있습니다.
• 높은 OS waits는 심각한 리소스 경합을 나타내며, 시스템 성능 문제로 이어질 수 있습니다.

 

 

RW-shared spins 및 RW-excl spins

RW-shared spins:
공유(Shared) RW-Lock을 획득하기 위해 스핀 대기한 횟수를 나타냅니다. 공유 잠금은 여러 스레드가 동시에 읽기를 수행할 때 사용됩니다.
RW-excl spins:
배타적(Exclusive) RW-Lock을 획득하기 위해 스핀 대기한 횟수를 나타냅니다. 배타적 잠금은 쓰기 작업을 수행할 때 사용됩니다.
OS waits:
운영체제에 대기 요청을 한 횟수를 나타냅니다.

적정선
OS waits가 높다면 RW-Lock 경합이 심각하다는 것을 의미합니다. 이는 읽기/쓰기 작업이 지연되고 있음을 나타냅니다.

 

• 높은 RW-shared spins은 다중 스레드가 동시에 리소스에 접근하려고 시도했음을 나타냅니다.
• 높은 RW-excl spins은 리소스를 독점적으로 사용하려는 경합이 많다는 의미입니다.
• 높은 OS waits는 락 경합이 심각하며, 운영 체제 수준에서 자원을 대기하는 시간이 길어질 수 있음을 나타냅니다.

 

OS waits가 높은 경우:
원인: 뮤텍스 또는 RW-Lock 경합이 심각하다는 것을 의미합니다. 이는 여러 스레드가 동일한 자원에 접근하려고 할 때 발생합니다.
해결 방안:
쿼리 최적화를 통해 잠금 경합을 줄입니다.
innodb_thread_concurrency 값을 조정하여 동시에 실행되는 스레드 수를 제한합니다.
innodb_spin_wait_delay 값을 조정하여 스핀 대기 시간을 최적화합니다.

rounds가 높은 경우:
원인: 스핀 대기 중 루프를 많이 돌았다는 것을 의미합니다. 이는 뮤텍스나 RW-Lock을 획득하기 위해 많은 CPU 자원을 소모했음을 나타냅니다.
해결 방안:
innodb_spin_wait_delay 값을 조정하여 스핀 대기 시간을 최적화합니다.
CPU 자원이 충분한지 확인하고, 필요하다면 CPU를 추가합니다.

 

---

TRANSACTIONS ( 트랜잭션 상태 )

InnoDB에서 현재 실행 중인 트랜잭션과 관련된 정보를 제공합니다. 이 출력은 트랜잭션 상태, undo 로그 상태, 히스토리 리스트 길이, 그리고 각 세션에서 활성화된 트랜잭션의 세부 정보를 포함

TRANSACTIONS
------------
Trx id counter 123456
Purge done for trx's n:o < 123456 undo n:o < 123456
History list length 123456
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 123456, ACTIVE 10 sec
2 lock struct(s), heap size 1136, 1 row lock(s)

 

Trx id counter:

• 현재까지 생성된 트랜잭션 ID의 카운터 값을 나타냅니다.
• 이 값은 트랜잭션이 생성될 때마다 증가하며, 각 트랜잭션은 고유한 ID를 갖습니다.
• 의미: 이 값은 시스템에서 얼마나 많은 트랜잭션이 발생했는지를 나타냅니다.

Purge done for trx's n:o < 123456 undo n:o < 123456:

Purge는 InnoDB가 MVCC(Multi-Version Concurrency Control)로 인해 불필요해진 undo 로그를 정리하는 작업입니다.

• trx's n:o < 123456: 트랜잭션 ID가 123456보다 작은 트랜잭션에 대한 undo 로그 정리가 완료되었음을 나타냅니다.
• undo n:o < 123456: undo 번호가 123456보다 작은 undo 로그가 정리되었음을 의미합니다.

History list length:

• 현재 시스템에 남아 있는 언두 로그의 길이를 나타냅니다. undo 로그의 길이를 나타내며, 아직 정리되지 않은 오래된 트랜잭션과 관련된 정보를 포함
• 이 값은 아직 퍼지되지 않은 언두 로그의 양을 의미합니다.
• 적정선:
  • 이 값이 지속적으로 증가한다면 퍼지 작업이 지연되고 있음을 의미합니다. 이는 디스크 공간 부족이나 성능 저하로 이어질 수 있습니다.

LIST OF TRANSACTIONS FOR EACH SESSION:

각 세션에서 활성화된 트랜잭션에 대한 정보

 

TRANSACTION 123456

• 트랜잭션의 고유 ID입니다.
• 여기서는 트랜잭션 ID가 123456임을 나타냅니다.

ACTIVE 10 sec

• 트랜잭션이 활성화된 시간입니다.
• 이 예에서는 트랜잭션이 시작된 후 10초 동안 활성 상태로 유지되고 있음을 의미합니다.
• 긴 활성 시간은 잠금 경합이나 오래 실행되는 쿼리로 인해 문제가 발생할 가능성을 나타낼 수 있습니다.

2 lock struct(s), heap size 1136

• 2 lock struct(s): 이 트랜잭션이 사용 중인 잠금 구조체의 수입니다.
• heap size 1136: 트랜잭션을 관리하기 위해 할당된 힙 메모리 크기(바이트 단위)입니다.

1 row lock(s)

• 이 트랜잭션이 현재 1개의 행(row)에 잠금을 걸고 있음을 나타냅니다.
• 행 락은 특정 데이터 레코드에 대한 잠금으로, 다른 트랜잭션이 이 데이터에 접근하려고 하면 대기 상태가 발생할 수 있습니다.

 

 

Purge 작업 지연의 경우:

원인: 퍼지 스레드가 충분히 빠르게 작업을 처리하지 못하고 있습니다.

해결방안 :

innodb_purge_batch_size 값을 증가시켜 한 번에 처리하는 언두 로그의 양을 늘립니다.

디스크 I/O 성능을 개선합니다.

 

History list length가 지속적으로 증가하는 경우:

원인: 퍼지 작업이 지연되고 있음을 의미합니다. 이는 언두 로그가 쌓여 디스크 공간을 낭비하거나 성능 저하를 초래할 수 있습니다.

해결 방안:

innodb_purge_threads 값을 증가시켜 퍼지 스레드의 수를 늘립니다.

innodb_max_purge_lag 값을 조정하여 퍼지 작업의 우선순위를 높입니다.

 

행 잠금(row lock(s))이 많은 경우:

원인: 여러 트랜잭션이 동일한 행에 접근하려고 할 때 발생합니다.

해결 방안:

트랜잭션 격리 수준(Isolation Level)을 조정합니다(예: READ COMMITTED로 변경).

인덱스를 최적화하여 잠금 경합을 줄입니다.

 

---

FILE I / O ( file i/o )

 InnoDB 스토리지 엔진의 파일 I/O 작업과 관련된 정보를 제공합니다. 이 섹션을 통해 InnoDB가 디스크와 어떻게 상호작용하는지, I/O 작업의 상태와 성능을 확인할 수 있습니다. 이 정보는 디스크 I/O 병목 현상을 진단

 

FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0]
Pending flush writes: 0
123456 OS file reads, 123456 OS file writes, 123456 OS fsyncs
0.00 reads/s, 0.00 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

 

 

I/O thread 0 state 및 I/O thread 1 state:

InnoDB는 여러 I/O 스레드를 사용하여 비동기 I/O(Asynchronous I/O, AIO) 작업을 처리합니다.

I/O thread 0: 인서트 버퍼(Insert Buffer) 작업을 처리하는 스레드입니다.

I/O thread 1: 로그(Redo Log) 작업을 처리하는 스레드입니다.

waiting for completed aio requests: 스레드가 AIO 요청이 완료되기를 기다리고 있음을 나타냅니다.

 

Pending normal aio reads 및 Pending normal aio writes:

Pending normal aio reads: 대기 중인 일반 비동기 읽기 요청의 수를 나타냅니다.

Pending normal aio writes: 대기 중인 일반 비동기 쓰기 요청의 수를 나타냅니다.

이 값은 배열 형태로 표시되며, 각 요소는 특정 I/O 스레드의 대기 중인 요청 수를 나타냄

 

작업 부하가 거의 없는 상태

Pending normal aio reads: [0, 0, 0, 0], aio writes: [0, 0, 0, 0]

높은 I/O 부하

Pending normal aio reads: [10, 15, 12, 8], aio writes: [20, 25, 18, 22]

비대칭적인 요청 분포

Pending normal aio reads: [0, 0, 8, 5], aio writes: [10, 0, 0, 3]

읽기 요청이 집중된 상태

Pending normal aio reads: [12, 14, 11, 13], aio writes: [0, 0, 0, 0]

 

값의 분포:
모든 큐에 균등하게 분포되어 있는 경우, I/O 처리가 고르게 분산되고 있음을 나타냅니다.
특정 큐에만 높은 값이 집중된다면, 특정 스레드 또는 작업에서 병목 현상이 발생했을 가능성이 있습니다.
높은 대기 값:
읽기/쓰기 요청이 계속 높은 값을 유지하면, 디스크 I/O 병목 현상이나 InnoDB 설정 문제일 수 있습니다.
조정 방안:
innodb_io_capacity: 디스크의 처리 용량에 맞게 I/O 스레드가 처리할 수 있는 작업량을 조정합니다.
쿼리 최적화: 느린 쿼리나 테이블 스캔을 유발하는 작업을 점검합니다.
디스크 성능 개선: 빠른 스토리지 디바이스로 업그레이드하거나 RAID 구성을 개선합니다.

 

pending flush writes

pending flush writes: 0은 플러시(변경 내용을 디스크에 영구적으로 기록) 작업이 대기 중인 쓰기 요청의 수를 나타냅니다.

값이 0이면 모든 플러시 작업이 완료되었음을 의미합니다.

 

OS file reads, OS file writes, OS fsyncs: ( 시스템의 I/O 작업량 )

OS file reads: 운영체제(OS)가 처리한 파일 읽기 횟수입니다.

OS file writes: 운영체제가 처리한 파일 쓰기 횟수입니다.

OS fsyncs: 운영체제가 처리한 파일 동기화(fsync) 횟수입니다.

 

reads/s, avg bytes/read, writes/s, fsyncs/s: ( 시스템의 I/O 처리량 )

reads/s: 초당 파일 읽기 횟수입니다.

avg bytes/read: 읽기 작업당 평균 바이트 수입니다.

writes/s: 초당 파일 쓰기 횟수입니다.

fsyncs/s: 초당 파일 동기화(fsync) 횟수입니다.

 

Pending normal aio reads 또는 Pending normal aio writes가 높은 경우:

원인: 디스크 I/O 병목 현상이 발생하고 있음을 의미합니다.

해결 방안:

디스크 성능을 개선합니다(예: SSD로 업그레이드).

innodb_io_capacity 값을 증가시켜 InnoDB의 I/O 처리 능력을 높입니다.

innodb_read_io_threads 및 innodb_write_io_threads 값을 조정하여 I/O 스레드 수를 늘립니다.

 

Pending flush writes가 높은 경우:

원인: 디스크 쓰기 작업이 느리다는 것을 의미합니다.

해결 방안:

디스크 쓰기 성능을 개선합니다.

innodb_flush_method 값을 O_DIRECT로 설정하여 파일 시스템 캐시를 우회합니다.

 

OS fsyncs 또는 fsyncs/s가 높은 경우:

원인: 트랜잭션 커밋이 빈번하게 발생하거나, 로그 기록이 많다는 것을 의미합니다.

해결 방안:

innodb_flush_log_at_trx_commit 값을 2로 설정하여 로그 기록 빈도를 줄입니다(단, 데이터 안전성을 약간 희생합니다).

로그 파일 크기(innodb_log_file_size)를 늘려 로그 기록 빈도를 줄입니다.

 

reads/s 또는 writes/s가 높은 경우:

원인: 시스템에 I/O 부하가 크다는 것을 의미합니다.

해결 방안:

쿼리 최적화를 통해 I/O 작업을 줄입니다.

버퍼 풀 크기(innodb_buffer_pool_size)를 늘려 디스크 I/O를 줄입니다.

---

 

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 123456, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

 

 

 

 

 

 

참조

https://m.blog.naver.com/bomyzzang/222791481692

https://hyunki1019.tistory.com/65