MySQL INNODB Status Monitoring

-  해당 STATUS출력내역은 각 상세내역 설명을 위해 특정장비를 기준으로 특정시점의 결과를 샘플링  

- 세부적으로 설명한 부분도 있지만 직관적으로 상태정보를 보여주는 부분은 상세한 설명은 생략함. (이 부분들에 대한 추가적인 질문은 개인별로 문의하세요)

- 각 부분에 대한 간략한 기능적 설명이며 모니터링상 중요한 영역에 대한 코멘터리는 각 영역에 대한 설명 마지막 부분에 기재하였음.

-----------------------------------------------------------------

SQL> SHOW ENGINE INNODB STATUS\G

=====================================

131129 12:58:12 INNODB MONITOR OUTPUT      <--- 분석시점에 대한 출력시간

=====================================

Per second averages calculated from the last 30 seconds   <--- 분석에 대한 대상시간범위 (출력시간으로부터 *초 전까지의 범위)

----------------------------

BACKGROUND THREAD      

----------------------------               
InnoDB 메인 백그라운드 스레드의 작업실행 통계 : 각 숫자는 Innodb Engine이 시작된 이후부터의 카운트 값

srv_master_thread loops: 18613866 1_second, 18613864 sleeps, 1861347 10_second, 1755 background, 1739 flush

<--- 순서대로 초당 루프횟수 / 초당 슬립횟수 / 10초당 루프횟수 / User Transaction이 없는 유휴시간대 백그라운드 연산의 루프횟수, 플러시 루프횟수를 의미

srv_master_thread log flush and writes: 18683584

<--- 로그 메시지를 기록하고 플러시한 횟수를 의미 (Redo + Undo)

-----------------

SEMAPHORES              

-----------------          
InnoDB 내부 세마포어(뮤텍스나 리드/라이트 락 세마포어를 기다리는 스레드에 대한 정보)의  통계 : 각 숫자는 Innodb Engine이 시작된 이후부터의 카운트 값이며 각 카운트 값이 갑자기 높은 시간대에 대해서는 DISK I/O 성능 및 InnoDB 엔진경합을 의심

OS WAIT ARRAY INFO: reservation count 1206724952, signal count 3304987791

<--- 전역 대기 배열정보 : 배열이 생성된 후에 셀을 예약한 횟수 / 객체가 시그널을 받은 횟수를 의미

Mutex spin waits 39567225498, rounds 173270862365, OS waits 330572230

<--- 뮤텍스 대기 스핀정보 : 뮤텍스를 기다리는 스핀락의 호출횟수 / 스핀루프의 반복횟수 / OS의 호출을 기다린 횟수를 의미

RW-shared spins 2945944901, rounds 37722913141, OS waits 601963335

<--- 공유 리드락 스핀정보 : 공유 리드락이 걸린 시간동안 스핀락이 기다린 횟수 / 스핀루프의 반복횟수 / OS의 호출을 기다린 횟수를 의미                       

RW-excl spins 684786004, rounds 12265810889, OS waits 163680647

<--- 배타적 라이트락 스핀정보 : 배타적 라이트락이 걸린 시간동안 스핀락이 기다린 횟수 / 스핀루프의 반복횟수 / OS의 호출을 기다린 횟수를 의미

Spin rounds per wait: 4.38 mutex, 12.81 RW-shared, 17.91 RW-excl

<--- 각 뮤텍스에 대해 OS의 호출을 기다리며 스핀루프가 기다린 횟수를 의미

------------------------------------

LATEST FOREIGN KEY ERROR 

------------------------------------            
최근 발생한 트랜잭션별 참조키 에러내역 히스토리 : 제약조건에 위배되는 내역에 대한 트랜잭션 및 쿼리 정보 확인

세부 에러 내역은 생략


------------------------------------

LATEST DETECTED DEADLOCK

------------------------------------           
최근 발생한 트랜잭션별 데드락내역 히스토리 : 가장 최근 발생한 트랜잭션간 데드락 이슈에 대한 처리내역 확인 (경합된 트랜잭션간의 내역 및 데드락 해결을 위한 트랜잭션 처리내역)

세부 데드락 내역은 생략

-------------------

TRANSACTIONS  

-------------------           
현재 트랜잭션별 락모니터링 정보 : 실제 수행되고 있는 트랜잭션별 작업내역에 대한 락 핸들링 정보 및 락 사용 세부쿼리 내역을 확인

세부 트랜잭션 내역은 생략


----------

FILE I/O    

----------         
다양한 I/O 연산에 대한 Innodb 스레드 실행내역 및 통계 : 내부적인 I/O 스레드의 현재 상태 및 전체 통계값 제공하며 만약 I/O상의 부하나 상태이상이 의심될 때 참조될 수 있음.

I/O thread 0 state: waiting for i/o request (insert buffer thread)

I/O thread 1 state: waiting for i/o request (log thread)

I/O thread 2 state: waiting for i/o request (read thread)

I/O thread 3 state: waiting for i/o request (read thread)

I/O thread 4 state: waiting for i/o request (read thread)

I/O thread 5 state: waiting for i/o request (read thread)

I/O thread 6 state: waiting for i/o request (write thread)

I/O thread 7 state: waiting for i/o request (write thread)

I/O thread 8 state: waiting for i/o request (write thread)

I/O thread 9 state: waiting for i/o request (write thread)

---> 현재 Innodb엔진이 사용중인 I/O 스레드들의 현재 상태 (각 스레드의 역할에 대한 명칭은 각 줄 끝 괄호내역 확인)

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

 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

Pending flushes (fsync) log: 0; buffer pool: 0

---> 대기하는 연산에 대한 정보  (aio 는 비동기 입출력을 의미)

21113936456 OS file reads, 7012874230 OS file writes, 5248276317 OS fsyncs

---> Innodb 엔진이 시작된 이후 전체통계 카운트

670.33 reads/s, 16384 avg bytes/read, 387.61 writes/s, 196.80 fsyncs/s

---> 마지막으로 통계를 출력한 이후의 전체통계 값에 대한 초단위 처리 카운트

------------------------------------------------------

INSERT BUFFER AND ADAPTIVE HASH INDEX      

------------------------------------------------------        
인서트 버퍼와 적응형 해시에 대한 정보 : 버퍼와 해시의 효율에 대한 통계정보 확인

Ibuf: size 38747, free list len 48247, seg size 86995, 183927359 merges

---> 페이지에 있는 삽입버퍼의 인덱스트리 사이즈 / 사용가능한 프리리스트의 길이 / 삽입버퍼트리와 헤더를 갖고 있는 파일에 할당된 페이지수 / 합병된 페이지수 를 의미

merged operations:

 insert 1037160756, delete mark 902047899, delete 260585507

---> 인덱스 페이지에 합병한 각 DML연산의 횟수를 의미

discarded operations:

 insert 110253, delete mark 5399, delete 2043

---> 테이블스페이스나 인덱스가 삭제되어 합병하지 않고 버려진 각 DML연산의 횟수를 의미

Hash table size 4425293, node heap has 1037 buffer(s)

---> 적응형 해시 인덱스 테이블에 있는 셀의 개수와 예약된 버퍼프레임의 개수를 의미

1057.94 hash searches/s, 1352.65 non-hash searches/s

---> 검색한 적응형 해시 인덱스 검색에 성공한 횟수 / 적응형 해시 인덱스를 사용할 수 없을때 B-tree 인덱스를 검색한 횟수를 의미

-------

LOG

-------
Innodb Log 활동에 대한 통계

Log sequence number 7804454235313     

---> 현재 로그 일련번호 (lsn)

Log flushed up to   7804454234099

---> 로그파일에서 플러시된 항목의 개수

Last checkpoint at  7804369829116

---> 가장 최근의 체크포인트된 lsn 정보

0 pending log writes, 0 pending chkp writes

4882215617 log i/o's done, 153.85 log i/o's/second

log switch 이슈와 관련

VIEW POINT : (Log flushed up to - Last checkpoint at) 값은 체크포인트가 얼마나 오래되었는지를 확인할 수 있음.

체크 포인트값이 (innodb_log_file_size * innodb_log_files_in_group)의 77% 이상이 되지 않게 모니터링하고 유지되어야 함. (권장된 모니터링수치)

만약 이 비율에 가깝거나 큰 비율 값이 나온다면 Innodb엔진은 공격적으로 플러싱을 시도하게 되며 이로 인한 DB연산이 멈출 수 있음.

------------------------------------

BUFFER POOL AND MEMORY

------------------------------------            
Innodb 버퍼풀과 메모리 사용량에 대한 통계정보 : Innodb 버퍼에 대한 LRU 관리방법에 의한 할당내역 및 사용내역(byte단위)과  버퍼사용율에 대한 세부통계수치 확인

Total memory allocated 2197815296; in additional pool allocated 0

---> 버퍼로 할당된 메모리 전체사이즈 / 추가풀에 할당된 메모리사이즈를 의미

Dictionary memory allocated 8409400

---> 데이터딕셔너리 테이블과 인덱스에 할당된 메모리사이즈를 의미

Buffer pool size   131072

Free buffers       21

---> 버퍼풀 사이즈를 페이지 단위로 알려주고 / 버퍼풀에 있는 해재된(free) 버퍼의 개수를 의미 : 프리버퍼가 없는 경우가 잦으면 버퍼사이즈를 늘려주는 것을 권장.

Database pages     130014

Old database pages 47973

Modified db pages  5660

---> 버퍼의 현재 LRU큐의 길이 / 과거 LRU큐의 길이 / 플러시해야할 페이지의 개수를 의미 : 기본적으로 Innodb는 버퍼풀을 LRU로 관리하며 할당과 해제에 대해서 페이지단위로 관리됨.

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

---> 대기하고 있는 읽기연산의 개수 / LRU 관리방식에 따른 플러시하기 위해 대기하고 있는 페이지의 개수 / Buf_flush_list에서 플러시하기위해 기다리고 있는 페이지의 개수 / Buf_flush_single_page 리스트에서 플러시하기 위해 기다리고 있는 페이지의 개수를 의미

Pages made young 47722254403, not young 0

3258.74 youngs/s, 0.00 non-youngs/s

---> 최근에 접근한 페이지의 개수 / 최근에 접근하지 않은 페이지의 개수를 의미

Pages read 25295158134, created 95903681, written 2392506665

670.33 reads/s, 2.00 creates/s, 235.76 writes/s

---> 페이지의 읽기 연산 카운트 / 버퍼풀에 생성했지만 읽지않은 페이지의 개수 / 쓰기 연산 카운트를 의미

Buffer pool hit rate 997 / 1000, young-making rate 19 / 1000 not 0 / 1000

---> 획득한 버퍼풀 페이지 수와 비교하여 읽은 페이지 수의 비율 / 획득한 버퍼풀 페이지 수와 비교 및 접근한 페이지 수의 비율 / 획득한 버퍼풀 페이지 수와 비교 및 접근하지 않은 페이지 수의 비율을 의미

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

---> 미리 읽기 비율 / 접근하지 않아서 제거된 미리 읽기 페이지수의 비율 / 랜덤 미리 읽기 비율

LRU len: 130014, unzip_LRU len: 0

I/O sum[108645]:cur[907], unzip sum[0]:cur[0]

----------------------

ROW OPERATIONS

----------------------
메인 스레드의 행 연산에 대한 통계 정보

2 queries inside InnoDB, 0 queries in queue

5 read views open inside InnoDB

---> 현재 실행중인 쿼리 / innodb_thread_concurrency 큐에 있는 쿼리의 개수 / 읽은 뷰의 개수를 의미

Main thread process no. 16530, id 140226923562752, state: sleeping

---> 메인 스레드의 ID 및 상태정보를 의미 (첫번째 no.는 OS의 프로세스ID)

Number of rows inserted 3941662945, updated 2611269253, deleted 1052306048, read 11030869627676

39.96 inserts/s, 57.94 updates/s, 2.00 deletes/s, 908015.98 reads/s

---> innodb 엔진 시작된 이후의 각 DML연산의 총합계 카운트 / 초당 각 DML연산의 횟수 카운트를 의미

--------------------------------------------------------

END OF INNODB MONITOR OUTPUT

========================================================