[MySQL][InnoDB] 모니터

■ InnoDB 모니터 타입

InnoDB 모니터에는 두 가지 유형이 있습니다.

+ 표준 InnoDB 모니터는 다음 유형의 정보를 표시합니다.

- 메인 백그라운드 스레드에 의해 수행 된 작업

- 세마포어 대기

- 가장 최근의 외래 키 및 교착 상태 오류에 대한 데이터

- 트랜잭션에 의한 잠금대기

- 활성 트랜잭션이 보유한 테이블 및 레코드 잠금

- 보류중인 I/O 작업 및 관련 통계

- 삽입 버퍼 및 적응 형 해시 인덱스 통계

- 재실행 로그 데이터

- 버퍼 풀 통계

- 행 연산 데이터

 

+ InnoDB 잠금 모니터는 표준 InnoDB 모니터 출력의 일부로 추가 잠금 정보를 인쇄합니다.

 

■ InnoDB 모니터 사용

InnoDB 모니터가 주기적인 출력을 위해 활성화되면 InnoDB는 약 15초마다 출력을 mysqld서버 표준 오류 출력(stderr)에 기록합니다.

 

InnoDB는 잠재적인 버퍼 오버 플로우를 피하기 위해 stdout또는 고정 크기 메모리 버퍼 대신 stderr로 모니터 출력을 보냅니다.

 

Windows에서 stderr은 달리 구성하지 않는 한 기본 로그 파일로 지정됩니다. 출력을 오류 로그가 아닌 콘솔 창으로 보내려면 --console 옵션을 사용하여 콘솔 창의 명령 프롬프트에서 서버를 시직합니다. 유닉스 및 유닉스 계열 시스템에서 stderr은 달리 구성되지 않는 한 일반적으로 터미널로 연결됩니다.

 

중요한점은 출력 생성으로 인해 성능이 약간 저하되므로 실제로 모니터 정보를 보려는 경우에만 InnoDB 모니터를 활성화해야 합니다. 또한 모니터 출력이 오류 로그로 지정된 경우 나중에 모니터를 사용 안함으로 설정하지 않으면 로그가 상당히 커질 수 있습니다. 

 

InnoDB 모니터 출력은 타임 스탬프 및 모니터 이름이 포함 된 헤더로 시작합니다. 예를 들면 다음과 같습니다.

=====================================
2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT
=====================================

 

표준 InnoDB 모니터 (INNODB MONITOR OUTPUT)의 헤더는 잠금 모니터에도 사용됩니다. 잠금 모니터는 추가 잠금 정보를 추가하여 동일한 출력을 생성하기 때문입니다.

innodb_status_output 및 innodb_status_output_locks 시스템 변수는 표준 InnoDB 모니터 및 InnoDB 잠금 모니터를 활성화하는 데 사용됩니다.

InnoDB 모니터를 활성화 또는 비활성화하려면 PROCESS 권한이 필요합니다.

 

▶︎ 표준 InnoDB 모니터 활성화

innodb_status_output 시스템 변수를 ON으로 설정하여 표준 InnoDB 모니터를 활성화합니다.

mysql> SET GLOBAL innodb_status_output=ON;

표준 InnoDB 모니터를 비활성화하려면 innodb_status_output을 OFF로 설정합니다.

서버를 종료하면 innodb_status_output 변수가 기본 OFF 값으로 설정됩니다.

 

▶︎ InnoDB 잠금 모니터 활성화

InnoDB 잠금 모니터 데이터는 InnoDB 표준 모니터 출력으로 인쇄됩니다. InnoDB 표준 모니터와 InnoDB 잠금 모니터는 InnoDB 잠금 모니터 데이터가 주기적으로 인쇄되도록 활성화해야 합니다.

 

InnoDB 잠금 모니터를 사용하려면 innodb_status_output_locks 시스템 변수를 ON으로 설정합니다. InnoDB 표준 모니터와 InnoDB 잠금 모니터는 InnoDB 잠금 모니터 데이터가 주기적으로 인쇄되도록 활성화해야 합니다.

mysql> SET GLOBAL innodb_status_output=ON;

mysql> SET GLOBAL innodb_status_output_locks=ON;

 

InnoDB 잠금 모니터를 비활성화하려면 innodb_status_output_locks를 OFF로 설정합니다. InnoDB 표준 모니터도 비활성화하려면 innodb_status_output을 OFF로 설정합니다.

서버를 종료하면 innodb_status_output 및 innodb_status_output_locks 변수가 기본 OFF 값으로 설정됩니다.

 

SHOW ENGINE INNODB STATUS 출력에 InnoDB Lock Monitor를 활성화하려면 innodb_status_output_locks 만 활성화하면 됩니다.

 

▶︎ 요청시 표준 InnoDB 모니터 출력 얻기

주기적 출력을 위해 표준 InnoDB 모니터를 활성화하는 대신 SHOW ENGINE INNODB STATUS SQL 문을 사용하여 요청시 표준 InnoDB 모니터 출력을 얻을 수 있습니다. 이 명령문은 클라이언트 프로그램으로 출력을 가져옵니다. mysql 대화식 클라이언트를 사용하는 경우 일반적인 세미콜론 명령문 종결자를 \G로 바꾸면 출력을 더 읽을 수 있습니다.

mysql> SHOW ENGINE INNODB STATUS\G

SHOW ENGINE INNODB STATUS output also includes InnoDB Lock Monitor data if the InnoDB Lock Monitor is enabled.

ENGINE INNODB STATUS 출력에는 InnoDB Lock Monitor가 활성화 된 경우 InnoDB Lock Monitor 데이터도 포함됩니다.

 

▶︎표준 InnoDB 모니터 출력을 상태 파일로 보내기

시작시 --innodb-status-file 옵션을 지정하여 표준 InnoDB 모니터 출력을 활성화하고 상태 파일로 보낼 수 있습니다. 이 옵션을 사용하면 InnoDB는 데이터 디렉토리에 innodb_status.pid라는 파일을 생성하고 약 15 초마다 출력을 기록합니다.

 

서버가 정상적으로 종료되면 InnoDB가 상태 파일을 제거합니다. 비정상적인 종료가 발생하면 상태 파일을 수동으로 제거해야 할 수 있습니다.

 

--innodb-status-file 옵션은 출력 생성이 성능에 영향을 줄 수 있고 innodb_status.pid 파일이 시간이 지남에 따라 상당히 커질 수 있으므로 임시로 사용하기위한 것입니다.

 

 

 

■ InnoDB 표준 모니터 및 잠금 모니터 출력

잠금 모니터는 추가 잠금 정보를 포함한다는 점을 제외하고 표준 모니터와 동일합니다. 주기적 출력을 위해 모니터를 사용 가능하게하면 동일한 출력 스트림이 켜지지만 잠금 모니터가 사용 가능한 경우 스트림에 추가 정보가 포함됩니다. 예를 들어 표준 모니터 및 잠금 모니터를 활성화하면 단일 출력 스트림이 켜집니다. 잠금 모니터를 비활성화 할 때까지 스트림에 추가 잠금 정보가 포함됩니다.

 

SHOW ENGINE INNODB STATUS 문을 사용하여 생성된 경우 표준 모니터 출력은 1MB로 제한됩니다. 이 한계는 tserver 표준 오류 출력 (stderr)에 기록 된 출력에는 적용되지 않습니다.

 

표준 모니터 출력 예 :

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2014-10-16 18:37:29 0x7fc2a95c1700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 38 srv_active, 0 srv_shutdown, 252 srv_idle
srv_master_thread log flush and writes: 290
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 119
OS WAIT ARRAY INFO: signal count 103
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 38, rounds 76, OS waits 38
RW-excl spins 2, rounds 9383715, OS waits 3
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 2.00 RW-shared, 4691857.50 RW-excl,
0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2014-10-16 18:35:18 0x7fc2a95c1700 Transaction:
TRANSACTION 1814, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
MySQL thread id 2, OS thread handle 140474041767680, query id 74 localhost
root update
INSERT INTO child VALUES
    (NULL, 1)
    , (NULL, 2)
    , (NULL, 3)
    , (NULL, 4)
    , (NULL, 5)
    , (NULL, 6)
Foreign key constraint fails for table `mysql`.`child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent`
  (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index par_ind tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000003; asc     ;;
 1: len 4; hex 80000003; asc     ;;

But in parent table `mysql`.`parent`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000000070a; asc       ;;
 2: len 7; hex aa0000011d0134; asc       4;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-10-16 18:36:30 0x7fc2a95c1700
*** (1) TRANSACTION:
TRANSACTION 1824, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 3, OS thread handle 140474041501440, query id 80 localhost
root updating
DELETE FROM t WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table
`mysql`.`t` trx id 1824 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 00000000071f; asc       ;;
 2: len 7; hex b80000012b0110; asc     +  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1825, ACTIVE 29 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 2, OS thread handle 140474041767680, query id 81 localhost
root updating
DELETE FROM t WHERE i = 1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table
`mysql`.`t` trx id 1825 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info
bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 00000000071f; asc       ;;
 2: len 7; hex b80000012b0110; asc     +  ;;
 3: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 35 page no 3 n bits 72 index GEN_CLUST_INDEX of table
`mysql`.`t` trx id 1825 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info
bits 0
 0: len 6; hex 000000000200; asc       ;;
 1: len 6; hex 00000000071f; asc       ;;
 2: len 7; hex b80000012b0110; asc     +  ;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1950
Purge done for trx's n:o < 1933 undo n:o < 0 state: running but idle
History list length 23
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421949033065200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421949033064280, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1949, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 1850 row lock(s), undo log entries 17415
MySQL thread id 4, OS thread handle 140474041235200, query id 176 localhost
root update
INSERT INTO `salaries` VALUES (55723,39746,'1997-02-25','1998-02-25'),
(55723,40758,'1998-02-25','1999-02-25'),(55723,44559,'1999-02-25','2000-02-25'),
(55723,44081,'2000-02-25','2001-02-24'),(55723,44112,'2001-02-24','2001-08-16'),
(55724,46461,'1996-12-06','1997-12-06'),(55724,48916,'1997-12-06','1998-12-06'),
(55724,51269,'1998-12-06','1999-12-06'),(55724,51932,'1999-12-06','2000-12-05'),
(55724,52617,'2000-12-05','2001-12-05'),(55724,56658,'2001-12-05','9999-01-01'),
(55725,40000,'1993-01-30','1994-01-30'),(55725,41472,'1994-01-30','1995-01-30'),
(55725,45293,'1995-01-30','1996-01-30'),(55725,473
--------
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)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
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
224 OS file reads, 5770 OS file writes, 803 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 264.84 writes/s, 23.05 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 4425293, node heap has 444 buffer(s)
68015.25 hash searches/s, 106259.24 non-hash searches/s
---
LOG
---
Log sequence number 165913808
Log flushed up to   164814979
Pages flushed up to 141544038
Last checkpoint at  130503656
0 pending log flushes, 0 pending chkp writes
258 log i/o's done, 6.65 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   65536
Free buffers       62412
Database pages     2899
Old database pages 1050
Modified db pages  449
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3, not young 0
0.05 youngs/s, 0.00 non-youngs/s
Pages read 107, created 2792, written 2586
0.00 reads/s, 92.65 creates/s, 122.89 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 2899, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   65536
Free buffers       62496
Database pages     2821
Old database pages 1021
Modified db pages  461
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.05 youngs/s, 0.00 non-youngs/s
Pages read 90, created 2731, written 2474
0.00 reads/s, 98.25 creates/s, 122.04 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 2821, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=35909, Main thread ID=140471692396288, state: sleeping
Number of rows inserted 1526363, updated 0, deleted 3, read 11
52671.72 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

 

 

 

▶︎ 표준 모니터 출력 섹션

표준모니터 출력 섹션에 나온 내용는 다음과 같습니다.

+ Status

이 섹션에는 타임 스탬프, 모니터 이름 및 초당 평균이 기반으로하는 시간 (초)이 표시됩니다. 초는 현재 시간과 마지막으로 InnoDB 모니터 출력이 인쇄 된 시간 사이의 경과 시간입니다.

 

+ BACKGROUND THREAD

srv_master_thread 행은 기본 백그라운드 스레드가 수행한 작업을 보여줍니다.

 

+ SEMAPHORES

이 섹션에서는 세마포어를 기다리는 스레드 및 스레드가 뮤텍스 또는 rw-lock 세마포어에서 스핀 또는 대기가 필요한 횟수에 대한 통계를 보고합니다. 세마포어를 기다리는 많은 수의 스레드는 디스크 I/O 또는 InnoDB 내부의 경합 문제로 인해 발생할 수 있습니다. 경합은 쿼리의 병렬 처리나 운영 체제 스레드 스케줄링의 문제점으로 인해 발생할 수 있습니다. innodb_thread_concurrency 시스템 변수를 기본값보다 작게 설정하면 이러한 상황에 도움이 될 수 있습니다. 대기 라인당 스핀 라운드 수는 뮤텍스를 기다리는 OS 당 스핀 록 라운드 수를 나타냅니다.

 

뮤텍스 메트릭은 SHOW ENGINE INNODB MUTEX에 의해보고됩니다.

mysql> show engine innodb mutex

 

+ LATEST FOREIGN KEY ERROR

이 섹션에서는 최신 외래키 제약 조건 오류에 대한 정보를 제공합니다. 이러한 오류가 발생하지 않으면 존재하지 않습니다. 내용에는 실패한 명령문과 실패한 제한 조건 및 참조 및 참조 테이블에 대한 정보가 포함됩니다.

 

+ LATEST DETECTED DEADLOCK

이 섹션에서는 최신 교착 상태에 대한 정보를 제공합니다. 교착 상태가 발생하지 않은 경우 존재하지 않습니다. 내용은 관련된 트랜잭션, 각각의 실행을 시도한 명령문, 가지고있는 잠금 및 필요한 잠금 및 교착 상태를 해결하기 위해 롤백하기로 결정한 트랜잭션을 보여줍니다.

 

+ TRANSACTIONS

이 섹션에서 잠금 대기를보고하면 애플리케이션에 잠금 경합이 있을 수 있습니다. 출력은 또한 트랜잭션 교착 상태의 원인을 추적하는 데 도움이 될 수 있습니다.

 

+ FILE I/O

이 섹션에서는 InnoDB가 다양한 유형의 I/O를 수행하는 데 사용하는 스레드에 대한 정보를 제공합니다. 이들 중 처음 몇 개는 일반적인 InnoDB 처리 전용입니다. 내용에는 보류중인 I/O 작업에 대한 정보와 I/O 성능에 대한 통계도 표시됩니다.

 

이러한 스레드 수는 innodb_read_io_threads 및 innodb_write_io_threads 매개 변수에 의해 제어됩니다. 

 

+ INSERT BUFFER AND ADAPTIVE HASH INDEX

이 섹션에서는 InnoDB 삽입 버퍼 (변경 버퍼라고도 함) 및 적응 형 해시 인덱스의 상태를 보여줍니다.

 

+ LOG

이 섹션에는 InnoDB 로그에 대한 정보가 표시됩니다. 내용에는 현재 로그 시퀀스 번호, 로그가 디스크로 플러시 된 로그의 길이 및 InnoDB가 마지막으로 체크 포인트를 수행 한 위치가 포함됩니다. 이 섹션에는 보류중인 쓰기 및 쓰기 성능 통계에 대한 정보도 표시됩니다.

 

+ BUFFER POOL AND MEMORY

이 섹션에서는 읽고 쓰는 페이지에 대한 통계를 제공합니다. 현재 쿼리에서 수행중인 데이터 파일 I/O 작업 수를 이 숫자에서 계산할 수 있습니다.

버퍼 풀 통계에 대한 설명은 InnoDB 표준 모니터를 사용하여 버퍼 풀 모니터링을 참조합니다.

 

+ ROW OPERATIONS

이 섹션에서는 각 행 작업 유형의 수와 성능을 포함하여 메인 스레드가 수행하는 작업을 보여줍니다.

 

모니터 출력에 대한 각 세션의 자세한 내용은 다음 페이지를 참고바랍니다.

 

https://myinfrabox.tistory.com/258

[MySQL] InnoDB 스토리지 엔진 상태 모니터링

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※