SHOW ENGINE INNODB STATUS와 InnoDB 모니터링

MySQL 클라이언트에서 아래와 같이 명령어(SHOW ENGINE INNODB STATUS)를 실행시키면 INNODB 정보가 출력이 된다. 이 정보는 튜닝에 활용 된다. 만약 mysql(대화형 SQL 클라이언트)를 사용한다면 세미콜론(;) 대신 고(\G)를 사용하는 것이 더 읽기 수훨하다.

 

mysql> SHOW ENGINE INNODB STATUS;  혹은  mysql> SHOW ENGINE INNODB STATUS\G

 

mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Status: ===================================== 030709 13:00:59 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 18 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 413452, signal count 378357 --Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135 a writer (thread id 32782) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0sea.c line 731 Last time write locked in file btr0sea.c line 1347 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits 375485 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 030709 13:00:59 Transaction: TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 inserting 15 lock struct(s), heap size 2496, undo log entries 9 MySQL thread id 25, query id 4668733 localhost heikki update insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') Foreign key constraint fails for table test/ibtest11a: ,   CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,   `D`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index PRIMARY tuple:  0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2:  len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4:  len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;; But in parent table test/ibtest11b, in index PRIMARY, the closest match we can find is record: RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex 80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex 0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5: len 3; hex 6b6864; asc khd;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 030709 12:59:58 *** (1) TRANSACTION: TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733 inserting LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146 MySQL thread id 21, query id 4553379 localhost heikki update INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t', 'e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'),7 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290252780 lock mode S waiting Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc aa35818;; 1: *** (2) TRANSACTION: TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782 inserting 130 lock struct(s), heap size 11584, undo log entries 437 MySQL thread id 23, query id 4554396 localhost heikki update REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','', NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290251546 lock_mode X locks rec but not gap Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138; asc aa35818;; 1: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230; asc aa35720;; 1: *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 Total number of lock structs in row lock hash table 70 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002 MySQL thread id 32, query id 4668737 localhost heikki show innodb status ---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id 38929 inserting 1 lock struct(s), heap size 320 MySQL thread id 29, query id 4668736 localhost heikki update insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjg jlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh ---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id 28684 committing 1 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 19, query id 4668734 localhost heikki update insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgj gjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf ---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id 36880 starting index read LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 27, query id 4668644 localhost heikki Searching rows for update update ibtest11a set B = 'kHdkkkk' where A = 89572 ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index PRIMARY trx id 0 290328327 lock_mode X waiting Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc supremum.;; ------------------ ---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831 rollback of SQL statement ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9 MySQL thread id 25, query id 4668733 localhost heikki update insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk') ---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id 32782 58 lock struct(s), heap size 5504, undo log entries 159 MySQL thread id 23, query id 4668732 localhost heikki update REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t', 'e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d %H:%i'), ---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id 30733 inserting 4 lock struct(s), heap size 1024, undo log entries 165 MySQL thread id 21, query id 4668735 localhost heikki update INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','', NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200) -------- FILE 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 (write thread) Pending normal aio reads: 0, aio writes: 0,  ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs 25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf for space 0: size 1, free list len 19, seg size 21, 85004 inserts, 85004 merged recs, 26669 merges Hash table size 207619, used cells 14461, node heap has 16 buffer(s) 1877.67 hash searches/s, 5121.10 non-hash searches/s --- LOG --- Log sequence number 18 1212842764 Log flushed up to   18 1212665295 Last checkpoint at  18 1135877290 0 pending log writes, 0 pending chkp writes 4341 log i/o's done, 1.22 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 84966343; in additional pool allocated 1402624 Buffer pool size   3200 Free buffers       110 Database pages     3074 Modified db pages  2674 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 171380, created 51968, written 194688 28.72 reads/s, 20.72 creates/s, 47.55 writes/s Buffer pool hit rate 999 / 1000 -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue Main thread process no. 3004, id 7176, state: purging Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779 1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================

 

만약 TRANSACTIONS 세션에 락이 기다리고 있다면 어플리케이션은 락 장애를 받아야만 한다. 그 출력은 트랜잭션 데드락의 원인을 남긴다.

 

SEMAPHORES 세션에 세마포어를 기다리는 쓰레드와 얼마나 많은 쓰레드가 mutex 혹은 rw-lock 세마포어에 spin 아님 wait가 필요한지를 통계로 기록된다. 세마포어를위해 기다리는 많은 수의 쓰레드는 디스크I/O 혹은 InnoDB의 충돌의 문제의 결과를 내야한다.충돌은 무거운 유사 쿼리 혹은 운영체제 쓰레트 스케쥴링의 문자 때문일 수 있다. innodb_thread_concurrency의 설정은 이와 같은 상황에서 도울 수 있는 기본 값 보다 더 작아야 한다.

 

BUFFER POOL AND MEMORY 세션은 페이지 읽기와 쓰기 통계를 낸다. 현재 쿼리를 실행하는 데 있어 얼마나 많은 데이타 파일 I/O 동작이 되는 지를 계산한다.

 

ROW OPERATIONS 세션은 주요 쓰레드가 동작하는 것을 보여준다

 

위와 같은 커맨드는 특정 InnoDB 통계 수치를 제공한다. 그 커맨드(SHOW ENGINE INNODB STATUS)는 이런 커맨드(SHOW STATUS)와 차이가 있다. 그 차이는 트랜잭션 상태, 버퍼 정보, 트랜잭션 로그 등과 같은 정보를 제공한다는 것이다.

 

다음과 같은 설정은 최종 사용자와/혹은 데이터베이스 어플리케이션의 필요에 의해 더 좋게 튜닝된 InnoDB 테이블과 성능 향상을 줄 수 있다.

 

 innodb_log_files_in_group
 : InnoDB 로그 파일 수 정의, 기본 값 2, 가능한 변경하지 않는 것을 권장.

 innodb_additional_mem_pool_size
 : 딕셔너리(dictionary) 캐쉬 사이즈 정의, 8 ~ 16M 설정, 이 커맨드(SHOW INNODB STATUS)에 나타나면 증가 권장.

 innodb_autoextend_increment
 : InnoDB 테이블스페이스(tablespace)가 확장될 때 증가되는 사이즈(MB) 정의, 기본 값 8MB.

 innodb_thread_concurrency
 :InnoDB에서 보다 적거나 혹은 같은 변수값의 한계치가 주어진다고 해도 InnoDB는 운영체지의 스레드 수를 유지하려 한다.   
  만약 성능의 이슈가 발생한다면, 이 커맨드(SHOW INNODB STATUS)로 세마포어를 위해 기다리는 많은 쓰레드("thrashing")

  이 들어나고 이런 변수 설정으로 더 높게 혹은 더 낮게 시도해야만 한다. 만약 그 서버가 많은 프로세서와 디스크를 가진

  서버에서 운영된다면 서버의 리소스를 더 많이 사용하게 만들기 위해서 그 값을 더 높게 설정하라. 권장 값은 프로세서의 수와 

  그 시스템 디스크 수의 합이다. 기본 값은 8이고 동시에 확인되는 것은 그 설정 값이 20보다 크거나 같다면 동시에 확인은 무력

  해 질 것이다.(동시에 무한해 지는 것을 고려)