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

■ InnoDB 스토리지 엔진 모니터링 방법

InnoDB 스토리지의 상태를 확인하는 방법은 infomration_Schema와 sys스키마, performance 스키마를 확인하는 방법등이 있습니다.

이중에서 가장 대표전인것이 Show 명령어를 이용하여 엔젠상태를 확인하는 방법입니다.

이 명령어를 통해서 나오는 스토리지 엔진에 대해 하나하나 확인해 보겠습니다.

 

■ 스토리지 엔진 내용

MySQL에서 다음과 같은 명령어를 입력하면 아래와 같은 화면이 나옵니다. 이 내용 안에는 Deadlock 정보, 버퍼풀 정보, 트랜잭션 정보등 정말 DBA에게 중요한 정보들이 많이 나옵니다.

이 정보들에 대해 확인해 보겠습니다.

MySQL> show engine innodb status;
=====================================
2022-01-07 07:40:39 0x7f3239fdc700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 206 srv_active, 0 srv_shutdown, 290 srv_idle
srv_master_thread log flush and writes: 496
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 34365
OS WAIT ARRAY INFO: signal count 32616
RW-shared spins 0, rounds 33799, OS waits 8183
RW-excl spins 0, rounds 80580, OS waits 1956
RW-sx spins 7341, rounds 128295, OS waits 2790
Spin rounds per wait: 33799.00 RW-shared, 80580.00 RW-excl, 17.48 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-07 07:40:05 0x7f320536e700
*** (1) TRANSACTION:
TRANSACTION 295821, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 70 lock struct(s), heap size 8400, 71 row lock(s), undo log entries 80
MySQL thread id 26, OS thread handle 139853697754880, query id 1927572 localhost root updating
DELETE FROM sbtest7 WHERE id=503795
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 78 page no 6933 n bits 144 index PRIMARY of table `sbtest`.`sbtest7` trx id 295821 lock_mode X locks rec but not gap waiting
Record lock, heap no 60 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8007aff3; asc     ;;
 1: len 6; hex 000000048399; asc       ;;
 2: len 7; hex 6d0000077202b7; asc m   r  ;;
 3: len 4; hex 80079ce8; asc     ;;
 4: len 30; hex 37383234313034373632352d39323638323032383033362d343039363533; asc 78241047625-92682028036-409653; (total 120 bytes);
 5: len 30; hex 38333536363336383530342d32373738393432383438302d333231373130; asc 83566368504-27789428480-321710; (total 60 bytes);

*** (2) TRANSACTION:
TRANSACTION 295833, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
44 lock struct(s), heap size 8400, 44 row lock(s), undo log entries 43
MySQL thread id 36, OS thread handle 139852812576512, query id 1927592 localhost root updating
UPDATE sbtest7 SET c='73949859626-50072193593-29120902820-60718977840-24230354234-72941910530-07679215452-08883893432-76110919521-23758613934' WHERE id=500994
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 78 page no 6933 n bits 144 index PRIMARY of table `sbtest`.`sbtest7` trx id 295833 lock_mode X locks rec but not gap
Record lock, heap no 60 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8007aff3; asc     ;;
 1: len 6; hex 000000048399; asc       ;;
 2: len 7; hex 6d0000077202b7; asc m   r  ;;
 3: len 4; hex 80079ce8; asc     ;;
 4: len 30; hex 37383234313034373632352d39323638323032383033362d343039363533; asc 78241047625-92682028036-409653; (total 120 bytes);
 5: len 30; hex 38333536363336383530342d32373738393432383438302d333231373130; asc 83566368504-27789428480-321710; (total 60 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 6895 n bits 144 index PRIMARY of table `sbtest`.`sbtest7` trx id 295833 lock_mode X locks rec but not gap waiting
Record lock, heap no 33 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8007a502; asc     ;;
 1: len 6; hex 00000004838d; asc       ;;
 2: len 7; hex 67000035eb02e4; asc g  5   ;;
 3: len 4; hex 8005c8f1; asc     ;;
 4: len 30; hex 35333539323633323939352d38383834313039333031302d383434363234; asc 53592632995-88841093010-844624; (total 120 bytes);
 5: len 30; hex 33363833393032393735352d38333234383038313435302d373339373933; asc 36839029755-83248081450-739793; (total 60 bytes);

*** WE ROLL BACK TRANSACTION (2)
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060717  4:29:00 Transaction:
TRANSACTION 0 336342767, ACTIVE 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, undo log entries 1
MySQL thread id 9697561, query id 188161264 localhost root update
insert into child values(2,2)
Foreign key constraint fails for table `test/child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
Trying to add in child table, in index `par_ind` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000002; asc     ;; 1: len 6; hex 000000000401; asc       ;;

But in parent table `test/parent`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 0000140c2d8f; asc     - ;; 2: len 7; hex 80009c40050084; asc    @
------------
TRANSACTIONS
------------
Trx id counter 306556
Purge done for trx's n:o < 306530 undo n:o < 0 state: running
History list length 734
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421333361268560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421333361277680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 306555, ACTIVE 1 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 58, OS thread handle 139853697754880, query id 2666465 localhost root updating
UPDATE sbtest5 SET k=k+1 WHERE id=465539
---TRANSACTION 306554, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 57, OS thread handle 139852813117184, query id 2666401 localhost root updating
UPDATE sbtest11 SET k=k+1 WHERE id=454965
---TRANSACTION 306553, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
22 lock struct(s), heap size 3520, 23 row lock(s), undo log entries 23
MySQL thread id 60, OS thread handle 139853698295552, query id 2666469 localhost root updating
UPDATE sbtest11 SET k=k+1 WHERE id=499900
---TRANSACTION 306552, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
14 lock struct(s), heap size 1136, 13 row lock(s), undo log entries 13
MySQL thread id 65, OS thread handle 139853329504000, query id 2666381 localhost root updating
UPDATE sbtest12 SET k=k+1 WHERE id=503361
---TRANSACTION 306549, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
51 lock struct(s), heap size 8400, 61 row lock(s), undo log entries 62
MySQL thread id 69, OS thread handle 139852812035840, query id 2666377 localhost root update
INSERT INTO sbtest19 (id, k, c, pad) VALUES (498813, 504835, '99300435931-65887917083-04322205500-18683563041-99141628713-03710044337-64201417394-45508685890-97040644661-23325285465', '42919674865-30804103718-25324362966-55580432136-02170847456')
---TRANSACTION 306548, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
65 lock struct(s), heap size 8400, 67 row lock(s), undo log entries 74
MySQL thread id 62, OS thread handle 139853699106560, query id 2666483 localhost root update
INSERT INTO sbtest10 (id, k, c, pad) VALUES (499002, 475829, '26485738868-83570434929-36608198146-02417856059-01283318352-07818066143-46952478647-71647044867-22704530070-53778342965', '55685497700-97176696068-62058219274-16772103057-35542488067')
---TRANSACTION 306547, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
13 lock struct(s), heap size 1136, 13 row lock(s), undo log entries 13
MySQL thread id 64, OS thread handle 139853598000896, query id 2666489 localhost root updating
UPDATE sbtest11 SET k=k+1 WHERE id=499580
---TRANSACTION 306546, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
67 lock struct(s), heap size 8400, 71 row lock(s), undo log entries 82
MySQL thread id 61, OS thread handle 139852812576512, query id 2666461 localhost root update
INSERT INTO sbtest2 (id, k, c, pad) VALUES (587265, 562659, '47737906346-27094050249-86818159961-18178463344-82229256206-18291139991-85714545226-46633035783-37993233576-40832684295', '14899070194-60137970795-19615836475-01984727871-51953701519')
---TRANSACTION 306545, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
28 lock struct(s), heap size 3520, 28 row lock(s), undo log entries 28
MySQL thread id 68, OS thread handle 139852812306176, query id 2666404 localhost root updating
UPDATE sbtest20 SET k=k+1 WHERE id=499577
---TRANSACTION 306543, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
65 lock struct(s), heap size 8400, 67 row lock(s), undo log entries 73
MySQL thread id 63, OS thread handle 139853698836224, query id 2666500 localhost root update
INSERT INTO sbtest2 (id, k, c, pad) VALUES (502033, 498474, '78219308422-78157314385-21884484838-95387599099-96945463605-55510163390-59550979312-14647179548-69131177252-80624313378', '02896743400-36161062104-46889011040-84754140588-73054152597')
---TRANSACTION 306542, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
74 lock struct(s), heap size 8400, 75 row lock(s), undo log entries 90
MySQL thread id 56, OS thread handle 139853330044672, query id 2666357 localhost root update
INSERT INTO sbtest9 (id, k, c, pad) VALUES (498475, 504836, '75401914579-80897458574-73500687068-49412109848-11684286709-00234841976-75647811027-65463258732-16493776524-79453467272', '13575597334-28534544110-85228492262-76714881131-04163906868')
---TRANSACTION 306538, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
61 lock struct(s), heap size 8400, 61 row lock(s), undo log entries 61
MySQL thread id 67, OS thread handle 139853698565888, query id 2666441 localhost root updating
DELETE FROM sbtest3 WHERE id=501610
---TRANSACTION 306537, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
74 lock struct(s), heap size 8400, 72 row lock(s), undo log entries 84
MySQL thread id 59, OS thread handle 139853329774336, query id 2666303 localhost root update
INSERT INTO sbtest15 (id, k, c, pad) VALUES (499087, 503633, '06271622406-41561432480-45062327433-66552866024-77768962194-96304362651-11827837399-90083706426-95043165699-42166346499', '17657451051-61440433272-86853927962-81574210462-09901479623')
---TRANSACTION 306536, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
65 lock struct(s), heap size 8400, 69 row lock(s), undo log entries 78
MySQL thread id 55, OS thread handle 139852812846848, query id 2666405 localhost root update
INSERT INTO sbtest20 (id, k, c, pad) VALUES (504375, 498920, '95811338969-62096374662-15983309057-15483748623-74839698588-47524273977-98265647158-12193624560-14284434128-70407278148', '41481680640-94888971786-40692222393-24847242573-86085321556')
---TRANSACTION 306527, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
76 lock struct(s), heap size 8400, 78 row lock(s), undo log entries 96
MySQL thread id 66, OS thread handle 139852811765504, query id 2666367 localhost root update
INSERT INTO sbtest3 (id, k, c, pad) VALUES (504058, 500467, '57829382378-81394609679-30229764177-10157476458-50691132199-48314184755-73148525541-91942692028-67880617556-52607368389', '45737358763-84786376313-77474862519-24899073362-98086305071')
--------
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 (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)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
124873 OS file reads, 157072 OS file writes, 30694 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 214.14 writes/s, 39.77 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 108, seg size 110, 13111 merges
merged operations:
 insert 10912, delete mark 15369, delete 7650
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 2 buffer(s)
Hash table size 1182691, node heap has 13 buffer(s)
Hash table size 1182691, node heap has 5 buffer(s)
Hash table size 1182691, node heap has 14 buffer(s)
Hash table size 1182691, node heap has 5 buffer(s)
Hash table size 1182691, node heap has 3170 buffer(s)
Hash table size 1182691, node heap has 14 buffer(s)
Hash table size 1182691, node heap has 3201 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 91686106838
Log flushed up to   91686106838
Pages flushed up to 90557871086
Last checkpoint at  90557871086
0 pending log flushes, 0 pending chkp writes
17059 log i/o's done, 2.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 250600
Buffer pool size   262112
Free buffers       115388
Database pages     140300
Old database pages 51884
Modified db pages  86490
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8396, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 124835, created 15465, written 136724
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 140300, unzip_LRU len: 0
I/O sum[0]:cur[1624], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32764
Free buffers       14431
Database pages     17527
Old database pages 6489
Modified db pages  10863
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1057, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15730, created 1797, written 17415
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17527, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   32764
Free buffers       14303
Database pages     17658
Old database pages 6523
Modified db pages  10844
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 991, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15529, created 2129, written 16930
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17658, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   32764
Free buffers       14548
Database pages     17414
Old database pages 6446
Modified db pages  10679
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1108, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15635, created 1779, written 16924
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17414, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   32764
Free buffers       14407
Database pages     17557
Old database pages 6484
Modified db pages  10884
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1005, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15611, created 1946, written 16845
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17557, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   32764
Free buffers       14537
Database pages     17430
Old database pages 6453
Modified db pages  10471
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1074, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15687, created 1743, written 17113
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17430, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   32764
Free buffers       14216
Database pages     17742
Old database pages 6541
Modified db pages  11231
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1101, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15619, created 2123, written 17435
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17742, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   32764
Free buffers       14426
Database pages     17530
Old database pages 6490
Modified db pages  10718
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 995, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15507, created 2023, written 17162
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17530, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   32764
Free buffers       14520
Database pages     17442
Old database pages 6458
Modified db pages  10800
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1065, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 15517, created 1925, written 16900
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 17442, unzip_LRU len: 0
I/O sum[0]:cur[203], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=2504, Main thread ID=139853750003456, state: sleeping
Number of rows inserted 396997, updated 794399, deleted 396999, read 10641787
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

 

 

 

 

■ 상세 내용

▶︎ 출력 정보

show engine innodb status를 실행한 날짜입니다. 마지막 행은 출력 이후의 시간이거나 마지막 내부 재설정 이후의 시간입니다.

=====================================
2022-01-07 07:40:39 0x7f3239fdc700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 13 seconds

 

 

▶︎ BACKGROUND THREAD 정보

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 206 srv_active, 0 srv_shutdown, 290 srv_idle
srv_master_thread log flush and writes: 496

 

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

서버의 작업량이 많은 경우, 원본 스레드는 백그라운드 테이블 드롭 수행, 적응적으로 변경 버퍼 병합 수행, redo 로그를 디스크에 플러시,

크기 제한을 충족하기 위해 필요한 경우 사전 캐시에서 테이블을 제거하고 체크포인트를 만듭니다.

서버가 유휴 상태인 경우: 체크포인트 시기로 인해 필요할 경우 백그라운드 테이블 삭제, 플러시 및/또는 redo 로그 체크포인트를 수행하고, 전체 I/O 용량으로 변경 버퍼 병합을 수행하고,

필요할 경우 사전 캐시에서 테이블을 제거하고, 체크포인트를 만듭니다.

 

위의 내용은 다음과 같습니다.

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

srv_active : 활성 서버 상태에 대해 위의 1초 루프가 실행된 횟수를 나타냅니다.

srv_shutdown : 셧다운 서버 상태에 대해 위의 1초 루프가 실행된 횟수를 나타냅니다.

srv_idle : 유후 서버 상태에 대해 위의 1초 루프가 실행된 횟수를 나타냅니다.

 

active 상태 수의 증가는 데이터의 변경과 관련이 있으며 쿼리와 관련이 없습니다.

srv_master_thread log flush and writes : 기본 스레드 로그 새로 고침 및 쓰기 시간입니다.

 

active 값이 높을수록 시스템의 부하가 높아지고 있으며, 활성일때 유휴 상태일 때보다 훨씬 더 높습니다.

 

참고로 MySQL메인 스레드에는 다음과 같은 스레드들이 있는데 참고로 알아두시면 좋습니다.

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

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

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

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

 

 

▶︎ SEMAPHORES THREAD 정보

동시성이 높은 작업이 있다면 세마포어에 병목 상태가 나타납니다. 이 내용을 이용하여 병목상태 현상을 간접적으로 확인해 볼 수 있습니다.

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 34365
OS WAIT ARRAY INFO: signal count 32616
RW-shared spins 0, rounds 33799, OS waits 8183
RW-excl spins 0, rounds 80580, OS waits 1956
RW-sx spins 7341, rounds 128295, OS waits 2790
Spin rounds per wait: 33799.00 RW-shared, 80580.00 RW-excl, 17.48 RW-sx

• OS WAIT ARRAY INFO: reservation count 34365
## OS 어레이 정보 대기 중 .InnoDB 할당 슬롯 할당량 .

• OS WAIT ARRAY INFO: signal count 32616
## OS 어레이 정보를 기다리는 중입니다. 스레드가 어레이를 통해 얻는 신호의 횟수를 측정합니다.

• RW-shared spins 0, rounds 33799, OS waits 8183
## 스핀 공유 잠금(공유) 카운터 .

• RW-excl은 0을 회전하고 2371을 돌고 OS는 70을 기다립니다.
## 스핀 전용 카운터 .

• RW-sx 스핀 229, 라운드 699, OS 대기 8
## 스핀 쓰기 잠금을 공유하는 카운터.
## (sx는 x를 공유해야 합니다. 공유 쓰기 잠금은 트랜잭션 업데이트 작업에서 일반적입니다. 환경 업데이트에서 많은 일이 진행 중입니다.)

• Spin rounds per wait: 33799.00 RW-shared, 80580.00 RW-excl, 17.48 RW-sx
## wait(대기)당 모든 스핀 라운드는 각 운영 체제가 스핀록 라운드 카운트의 뮤텍스를 대기하는 것으로 표시됩니다.

## InnoDB에는 multi-stage 대기 전략이 있습니다.
## 먼저, lock(spin)을 돌리고, 스핀(spin) 주기 후에 잠금을 유지하지 않으면 운영 체제 대기 상태(os wait)로 들어갑니다.
## 두 번째 스핀 대기에서 수십만을 본다면 엔진 innodb mutex를 표시하는 데 주의를 기울여야 합니다.

 

 

▶︎ 최근 Deadlock 정보

최근에 발생한 Deadlock 정보를 나타냅니다. 데드록이 걸린 이유에 대해 분석할 수 있도록 여러 정보를 나열합니다.

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-01-07 07:40:05 0x7f320536e700
## 마지막으로 교착 상태가 발생한 날짜와 시간을 보여줍니다.

*** (1) TRANSACTION:
TRANSACTION 295821, ACTIVE 1 sec starting index read
TRANSACTION ID는 295821이며 한개의 ACTIVE 세션이 시작하면서 인덱스를 읽고 있습니다.

mysql tables in use 1, locked 1
## mysql 테이블들중 1개를 사용중이고 lock이 1개 있습니다.

LOCK WAIT 70 lock struct(s), heap size 8400, 71 row lock(s), undo log entries 80
## LOCK WAIT정보입니다. LOCK WAIT 문자는 lock wait 정보를 나낸다는 의미입니다. 두의 내용들과 겹쳐보이거나 처음 볼 시 헷갈릴 수 있어 알려 드립니다.
## 위의 의미는 8400바이트의 메모리(heap size)를 차지하고 70행의 레코드를 포함하는 71개의 잠금을 기다리고 있음을 나타냅니다.
## 트랜잭션이 여러 데이터 행을 잠근 경우 잠긴 구조의 수를 나타내는 정보 행(ex. 70 lock struct(s))이 있습니다. (참고 이것은 행 잠금과 동일합니다.)
## Innodb는 특수 비트맵을 사용하여 행 잠금을 구현합니다. 각 잠금 행은 비트로 표시되며 테스트 결과 각 잠금은 일반적으로 4비트 이하인 것으로 나타났습니다.

MySQL thread id 26, OS thread handle 139853697754880, query id 1927572 localhost root updating
DELETE FROM sbtest7 WHERE id=503795
## thread 26, os handle 139853697754880, 쿼리 id 1927572 그리고 호스트는 localhost, 사용중인 계정(root)에서 업데이트(update)를 하고 있는데 그 쿼리 내용은 다음과 같습니다.
## 위의 내용인 DELETE 로 시작하는 내용이 쿼리 내용입니다.

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 78 page no 6933 n bits 144 index PRIMARY of table `sbtest`.`sbtest7` trx id 295821 lock_mode X locks rec but not gap waiting
## RECORD LOCKS의 정보입니다. 마찬가지로 RECORD LOCKS문자열은 해당 정보를 나타내기 위한 시작점입니다.
## 이 정보 행은 대기 중인 잠금이 레코드 잠금이고 space id가 441이고 페이지 번호(page no)가 3이며 대략적인 위치(n bits)가 페이지의 144비트임을 나타냅니다.
## 잠금(lock)은 테이블 `sbtest`.`sbtest7`의 기본 키에서 발생하며, X 잠금이지만 gap lock은 아닙니다. Waiting은 잠금을 기다리는 것을 의미합니다.

Record lock, heap no 60 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8007aff3; asc     ;;
 1: len 6; hex 000000048399; asc       ;;
 2: len 7; hex 6d0000077202b7; asc m   r  ;;
 3: len 4; hex 80079ce8; asc     ;;
 4: len 30; hex 37383234313034373632352d39323638323032383033362d343039363533; asc 78241047625-92682028036-409653; (total 120 bytes);
 5: len 30; hex 38333536363336383530342d32373738393432383438302d333231373130; asc 83566368504-27789428480-321710; (total 60 bytes);
## Record lock이며 heap no는 60입니다. PHYSICAL RECORD(물리적 레코드)의 정보는 n_fields 6, compact format(간결한 형식), info bits(정보 비트)는 0입니다.
## 그리고 아래는 해당 레코드에 대한 정보를 나타압니다.

*** (2) TRANSACTION:
TRANSACTION 295833, ACTIVE 1 sec starting index read
## TRANSACTION ID는 295833이며 한개의 ACTIVE 세션이 시작하면서 인덱스를 읽고 있습니다.

mysql tables in use 1, locked 1
## mysql 테이블들중 1개를 사용중이고 lock이 1개 있습니다.

44 lock struct(s), heap size 8400, 44 row lock(s), undo log entries 43
## LOCK WAIT정보입니다. LOCK WAIT 문자는 lock wait 정보를 나낸다는 의미입니다. 두의 내용들과 겹쳐보이거나 처음 볼 시 헷갈릴 수 있어 알려 드립니다.
## 위의 의미는 8400바이트의 메모리(heap size)를 차지하고 44행의 레코드를 포함하는 44개의 잠금을 기다리고 있음을 나타냅니다.
## 트랜잭션이 여러 데이터 행을 잠근 경우 잠긴 구조의 수를 나타내는 정보 행(ex. 44 lock struct(s))이 있습니다. (참고 이것은 행 잠금과 동일합니다.)
## Innodb는 특수 비트맵을 사용하여 행 잠금을 구현합니다. 각 잠금 행은 비트로 표시되며 테스트 결과 각 잠금은 일반적으로 4비트 이하인 것으로 나타났습니다.

MySQL thread id 36, OS thread handle 139852812576512, query id 1927592 localhost root updating
UPDATE sbtest7 SET c='73949859626-50072193593-29120902820-60718977840-24230354234-72941910530-07679215452-08883893432-76110919521-23758613934' WHERE id=500994
## thread 36, os handle 139852812576512, 쿼리 id 1927592 그리고 호스트는 localhost, 사용중인 계정(root)에서 업데이트(update)를 하고 있는데 그 쿼리 내용은 다음과 같습니다.
## 위의 내용인 UPDATE 로 시작하는 내용이 쿼리 내용입니다.

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 78 page no 6933 n bits 144 index PRIMARY of table `sbtest`.`sbtest7` trx id 295833 lock_mode X locks rec but not gap
## 이 정보 행은 대기 중인 잠금이 레코드 잠금이고 space id가 441이고 페이지 번호(page no)가 6933이며 대략적인 위치(n bits)가 페이지의 144비트임을 나타냅니다.
## 잠금(lock)은 테이블 `sbtest`.`sbtest7`의 기본 키에서 발생하며, X 잠금이지만 gap lock은 아닙니다. Waiting은 잠금을 기다리는 것을 의미합니다.

Record lock, heap no 60 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8007aff3; asc     ;;
 1: len 6; hex 000000048399; asc       ;;
 2: len 7; hex 6d0000077202b7; asc m   r  ;;
 3: len 4; hex 80079ce8; asc     ;;
 4: len 30; hex 37383234313034373632352d39323638323032383033362d343039363533; asc 78241047625-92682028036-409653; (total 120 bytes);
 5: len 30; hex 38333536363336383530342d32373738393432383438302d333231373130; asc 83566368504-27789428480-321710; (total 60 bytes);
## Record lock이며 heap no는 60입니다. PHYSICAL RECORD(물리적 레코드)의 정보는 n_fields 6, compact format(간결한 형식), info bits(정보 비트)는 0입니다.
## 그리고 아래는 해당 레코드에 대한 정보를 나타압니다.

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 84 page no 6895 n bits 144 index PRIMARY of table `sbtest`.`sbtest7` trx id 295833 lock_mode X locks rec but not gap waiting
## 이 정보 행은 대기 중인 잠금이 레코드 잠금이고 space id가 441이고 페이지 번호(page no)가 6933이며 대략적인 위치(n bits)가 페이지의 144비트임을 나타냅니다.
## 잠금(lock)은 테이블 `sbtest`.`sbtest7`의 기본 키에서 발생하며, X 잠금이지만 gap lock은 아닙니다. Waiting은 잠금을 기다리는 것을 의미합니다.

Record lock, heap no 33 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 8007a502; asc     ;;
 1: len 6; hex 00000004838d; asc       ;;
 2: len 7; hex 67000035eb02e4; asc g  5   ;;
 3: len 4; hex 8005c8f1; asc     ;;
 4: len 30; hex 35333539323633323939352d38383834313039333031302d383434363234; asc 53592632995-88841093010-844624; (total 120 bytes);
 5: len 30; hex 33363833393032393735352d38333234383038313435302d373339373933; asc 36839029755-83248081450-739793; (total 60 bytes);
## Record lock이며 heap no는 33입니다. PHYSICAL RECORD(물리적 레코드)의 정보는 n_fields 6, compact format(간결한 형식), info bits(정보 비트)는 0입니다.
## 그리고 아래는 해당 레코드에 대한 정보를 나타압니다.

*** WE ROLL BACK TRANSACTION (2)
## ROLL BACK 된 트랜잭션 정보입니다. 이 내용에서는 섹션 (2) TRANSACTION 이 ROLL BACK된 것입니다.

 

 

위의 내용을 정리해보면 다음과 같습니다. 

현재 위에서 표시된  Transaction은 (1) TRANSACTION과, (2) TRANSACTION 입니다.

(1)번 트랜잭션에서 DELETE SQL쿼리를 수행하기 위해 sbtest7 테이블에 (1) WAITING FOR THIS LOCK TO BE GRANTED을 확인해보면 X LOCK을 걸기를 대기하고 있습니다.

(2)번 트랜잭션에서는 Update SQL쿼리를 수행하기 위해 (2) HOLDS THE LOCK(S) 즉 sbtest7에 업데이트를 하기 위해 LOCK을 획득해 업데이트를 하려고 하고 있습니다.

그리고 2번 자신의 트랜잭션에서도 sbtest7을 사용하기 위해 대기중에 있습니다.  이 2개의 트랜잭션이 서로 묶여서 Dead lock을 발생시키고 있습니다.

그래서 이 Deadlock 상태를 해결하기 위해 *** WE ROLL BACK TRANSACTION (2) 즉, 2번 트랜잭션을 Rollback시킨 것입니다.

또한 트랜잭션 2번을 Rollback 시킨 이유는 Undo 로그 사용량이 적었기 때문입니다. Undo log 사용량이 적어야 트랜잭션을 Rollback하는데 시간이 적게 소요되기 때문입니다.

 

▶︎ FOREIGN KEY 에러 정보

최근에 발생한 FOREIGN KEY에 대한 정보를 나타냅니다.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
060717  4:29:00 Transaction : 마지막 외래키 오류의 날짜와 시간
TRANSACTION 0 336342767, ACTIVE 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500
## 트랜잭션 번호, 수행시간, 프로세스 넘버, OS스레드 ID 번호, InnoDB스레드 안에서 선언된 스레드 번호(500번)

mysql tables in use 1, locked 1
## 사용되고 있는 테이블 수와 lock이 걸린 수

3 lock struct(s), heap size 368, undo log entries 1
## 3개의 Lock을 가짐, 메모리 크기, 언두 로그 엔트리 갯수

MySQL thread id 9697561, query id 188161264 localhost root update
## MySQL 스레드에서 수행중인 쿼리 정보. thread id, query  id, 그리고 수행중인 호스트(localshot)에서 수행중인 계정(root)
insert into child values(2,2)
Foreign key constraint fails for table `test/child`:
,
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE
Trying to add in child table, in index `par_ind` tuple:
DATA TUPLE: 2 fields;
 0: len 4; hex 80000002; asc     ;; 1: len 6; hex 000000000401; asc       ;;

But in parent table `test/parent`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 3; 1-byte offs TRUE; info bits 0
 0: len 4; hex 80000001; asc     ;; 1: len 6; hex 0000140c2d8f; asc     - ;; 2: len 7; hex 80009c40050084; asc    @

외래키 에러가 난 이유는 child테이블에 값을 입력하는데 데이터 형식이 달라서 발생한 경우이다. 부모와 자식의 데이터 형식이 모두 같아야 하기 때문에 다른 데이터 형식을 입력한다면 에러가 발생한다.

 

▶︎ Transaction 정보

현재 수행중인 트랜잭션 리스트입니다.

------------
TRANSACTIONS
------------
Trx id counter 306556
## 현재 트랜잭션 식별자로 새 트랜잭션마다 증가하는 시스템 변수입니다.

Purge done for trx's n:o < 306530 undo n:o < 0 state: running
## InnoDB가 사용하지 않는 오래된 MVCC행 버전을 퍼지시킨 트랜잭션의 ID입니다. 여기서 이 값과 현재 트랜잭션 ID의 차이로 제거되지 않은 오래된 버전수를 알 수 있습니다.

History list length 734
## 히스토리 목록의 길이로 InnoDb 데이터 파일나의 언두 영역에 있는 제거되지 않은 트랜잭션 수다. 이 수는 트랜잭션이 갱신하고 커밋하면 증가하고, 퍼지 프로세스가 오래된 버전을 제거하면 줄어든다.

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421333361268560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421333361277680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)

---TRANSACTION 306555, ACTIVE 1 sec updating or deleting
## 트랜잭션 ID 는 306555, 1초동안 Update나 Delete중인 세션이 활성화되어 있습니다.
## insert 중이라면 updating or delete대신 inserting이 표시됩니다.

mysql tables in use 1, locked 1
## mysql 테이블 1개를 사용중이고 lock 1개가 걸렸습니다.

2 lock struct(s), heap size 1136, 1 row lock(s)
## 1개의 row lock을 포함하여 2개의 lock이 사용중이며 메모리는 1136을 사용중입니다.

MySQL thread id 58, OS thread handle 139853697754880, query id 2666465 localhost root updating
UPDATE sbtest5 SET k=k+1 WHERE id=465539
## MySQL 스레드 ID는 58번이며 OS 스레드는 139853697754880, 쿼리 id는 2666465이며 로컬호스트에서 root계정으로 Update를 하고 있습니다. 그리고 UPDATE쿼리를 수행중입니다.

 

▶︎ File I/O 정보

이 섹션에는 버퍼 스레드, 로그 스레드, 읽기 스레드 및 쓰기 스레드를 포함한 버퍼 풀 활동 및 메모리 사용량이 표시됩니다. 

--------
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 (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: complete io for buf page (write thread) ev set
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 1
130959 OS file reads, 402069 OS file writes, 89627 OS fsyncs
43.74 reads/s, 16384 avg bytes/read, 892.01 writes/s, 106.11 fsyncs/s

 

위의 I/O 스레드들은  버퍼 병합 삽입, 비동기 로그 플러시, 더티 버퍼의 미리 읽기 및 플러시를 적절히 담당합니다. 쿼리 실행에서 발생하는 일반 읽기는 쿼리를 실행하는 스레드에 의해 실행됩니다. 유닉스/리눅스에서는 항상 4개의 도우미 스레드를 볼 수 있지만 윈도우즈에서는 innodb_file_io_threads 변수에 의해 조정될 수 있습니다. 각 도우미 스레드에 대해 스레드가 준비되었을 때, I/O 요청을 기다리거나 특정 작업을 실행 중인지 스레드 상태를 볼 수 있습니다.

 

각 도우미 스레드에 대해 보류 중인 작업의 수가 표시됩니다. 이 값은 실행 대기열에 있거나 동시에 실행 중인 작업의 양입니다. 또한 보류 중인 fsync 작업 수도 표시됩니다. 쓰기의 경우 Innodb는 데이터가 디스크에 저장되도록 해야 합니다. 데이터를 OS 캐쉬에 전달하는 것만으로는 부족합니다. 이 작업은 일반적으로 수정된 파일에 대해 fsync()를 호출하여 수행됩니다. 이러한 변수 중 하나에 대해 높은 값이 일정하다는 것은 IO 바운드 워크로드를 나타냅니다. 그러나 요청을 실행하는 스레드에 의해 제출된 IO 요청은 여기에 포함되지 않으므로 워크로드가 여전히 바인딩된 상태에서 0이 될 수 있습니다.

 

다음으로 파일 IO 작업 수와 계산된 평균이 표시됩니다. 이것은 그래프 작성과 모니터링에 유용한 파라미터입니다. "16384 avg 바이트/read"는 읽기 요청의 평균 크기를 나타냅니다. 랜덤 IO의 경우 페이지 크기는 16K여야 합니다. 전체 테이블 스캔 또는 인덱스 스캔 읽기 사전 검색을 수행할 경우 평균 읽기 크기를 크게 늘릴 수 있습니다. 따라서 이 값을 미리 읽기 효율성으로 생각할 수 있습니다.

 

 

▶︎ File I/O 정보

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 108, seg size 110, 13111 merges
## Ibuf(Insert Buffer)의 크기는 1, free list크기는 108, 세그먼트 크기는 110, 13111개가 머지(병합)중입니다.

merged operations:
 insert 10912, delete mark 15369, delete 7650
## 병합 운영중인 내용은 insert가 10912, delete 표시가 15369, delete가 7650

discarded operations:
 insert 0, delete mark 0, delete 0
## 버리는 운영정보들 입니다.

Hash table size 1182691, node heap has 2 buffer(s)
Hash table size 1182691, node heap has 13 buffer(s)
Hash table size 1182691, node heap has 5 buffer(s)
Hash table size 1182691, node heap has 14 buffer(s)
Hash table size 1182691, node heap has 5 buffer(s)
Hash table size 1182691, node heap has 3170 buffer(s)
Hash table size 1182691, node heap has 14 buffer(s)
Hash table size 1182691, node heap has 3201 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

 

이 섹션에는 삽입 버퍼 및 적응형 해시 상태가 표시됩니다. 첫 번째 줄에는 삽입 버퍼 – 세그먼트 크기와 여유 목록, 그리고 삽입 버퍼 레코드가 있는 경우 등이 표시됩니다. 다음으로, 삽입 버퍼에 얼마나 많은 수의 rec가 병합되었으며 얼마나 많은 병합이 수행되었는지를 보여줍니다. 인서트 수에 대한 병합 수의 비율은 인서트 버퍼 효율성에 가깝다.

 

ADAPTIVE HASH INDEX는 btree 검색을 해시 검색으로 대체하기 위해 일부 페이지에 대한 해시 인덱스 Innodb 빌드입니다. 이 섹션에는 해시 테이블 크기, 사용된 셀 수 및 적응형 해시 인덱스에서 사용되는 버퍼 수가 표시됩니다. 또한 해시 인덱스 조회 수 및 해시 인덱스 효율성을 나타내는 비 해시 인덱스 조회 수를 볼 수 있습니다.

 

현재 적응형 해시 인덱스를 조정하거나 버퍼 동작을 삽입하기 위해 할 수 있는 작업이 많지 않으므로 정보 제공 목적으로만 사용할 수 있습니다.

 

 

▶︎ LOG 정보

---
LOG
---
Log sequence number 91686106838
Log flushed up to   91686106838
Pages flushed up to 90557871086
Last checkpoint at  90557871086
0 pending log flushes, 0 pending chkp writes
17059 log i/o's done, 2.00 log i/o's/second

 

Log 섹션은 Innodb의 로그 서브시스템에 대한 정보를 제공합니다. 시스템 테이블스페이스 생성 이후 Innodb가 로그 파일에 기록한 바이트의 양인 현재 로그 시퀀스 번호를 볼 수 있습니다. 또한 로그가 플러시된 시점까지 확인할 수 있으므로 로그 버퍼에서 플러시되지 않은 데이터 양과 마지막 체크포인트가 수행된 시기를 확인할 수 있습니다. Innodb는 퍼지 체크포인트를 사용하므로 이 줄은 로그 시퀀스를 보유하며 버퍼 풀에서 플러시된 모든 변경 사항을 포함합니다. 로그 시퀀스가 더 높은 변경 사항은 로그에만 기록되고 버퍼 풀에서는 플러시되지 않으므로 로그 파일에 해당 로그 시퀀스를 덮어쓸 수 없습니다. 로그 순서 번호와 로그 플러시된 값을 모니터링하여 innodb_log_buffer_size가 최적인지 확인할 수 있습니다. 로그 버퍼 크기의 30% 이상이 플러시되지 않은 경우 이 값을 늘릴 수 있습니다.

 

보류 중인 일반 로그 쓰기 수와 체크포인트 로그 쓰기 수도 확인할 수 있습니다. 로그/IO 작업 수를 사용하면 로그 관련 IO와 테이블스페이스 관련 IO를 분리하여 로그 파일에 필요한 IO 양을 확인할 수 있습니다. 로그가 쓰는 innodb_flush_log_at_trx_commit 값에 따라 다소 비쌀 수 있습니다. innodb_interv_interval_at_trx_commit=2 로그 쓰기가 OS 캐시에 수행되고 순차 쓰기인 경우 로그 쓰기가 매우 빠릅니다.

 

▶︎ BUFFER POOL AND MEMORY 정보

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744 ## InnoDB에서 할당한 전체 메모리 크기(Byte단위)
Dictionary memory allocated 250600      ## 카탈로그 메모리 사이즈(Byte 단위)
Buffer pool size   262112               ## 버퍼풀 사이즈(Page 단위)
Free buffers       115388               ## 여유 메모리(Page 단위)
Database pages     140300               ## 데이터베이스 페이지 수(Page 단위)
Old database pages 51884                ## 오래된 데이터베이스 페이지 수(Page 단위)
Modified db pages  86490                ## 수정된 데이터베이스 페이지수 (더티 페이지 수)(Page 단위)
Pending reads      0                    ## 읽기 대기 수
페이지 단위는 MySQL페이지 단위로 계산해야 합니다. 기본값은 16K(16384)입니다.
즉, 바이드 단위로 보고 싶을때는 위의 숫자에 페이지를 곱해야 합니다.

Pending writes: LRU 0, flush list 0, single page 0
## 쓰기, 플러시, 싱글 페이지에 대한 대기

Pages made young 8396, not young 0
## 신규 할당 페이지, 할당되지 않은 페이지
0.00 youngs/s, 0.00 non-youngs/s
## 신규로 만들어진 페이지 시간, 신규페이지가 아닌 시간 즉 페이지 지속 시간

Pages read 124835, created 15465, written 136724
## 읽은 페이지 수, 생성된 페이지 수, 쓰여진 페이지 수
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
## 초당 읽기 페이지 수, 초당 만들어진 페이지 수

Buffer pool hit rate 999 / 1000
## 버퍼풀 적중률. 만약 상태 출력 이후 비활동적으로 변했다면 아래와 같은 내용이 출력됨.
(No buffer pool page gets since the last printout
## 마지막으로 조회 후 조회한 버퍼풀 페이지 수. 마지막 출력 이후 아무런 버퍼풀 페이지 읽기가 없었음.)

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
## 미리읽은 페이지 시간, 액세스되지 않고 제거된 시간, 랜덤 읽기 시간
LRU len: 140300, unzip_LRU len: 0
## LRU 길이, 압축되지 않은 LRU 크기
I/O sum[0]:cur[1624], unzip sum[0]:cur[0]
## I/O합계, 현제 갯수, 압축되지 않는 합계와 현재 수

 

이 섹션에는 버퍼 풀 활동 및 메모리 사용량이 표시됩니다. Innodb에 의해 할당된 총 메모리(예상치보다 높은 경우도 있음), 추가 메모리 풀에 할당된 메모리 양(크기가 올바른지 확인할 수 있음), 버퍼 풀의 총 페이지 수, 사용 가능한 페이지 수, 데이터베이스 페이지 및 더티 페이지별로 할당된 페이지를 볼 수 있습니다. 이러한 값에서 버퍼 풀의 크기가 적절한지 알 수 있습니다. 사용 가능한 페이지가 지속적으로 많은 경우 활성 데이터베이스 크기가 할당된 버퍼 풀 크기보다 작으므로 버퍼 풀 크기를 조정할 수 있습니다. 버퍼 풀은 잠금 정보, 적응형 해시 인덱스 및 기타 시스템 구조도 저장하기 때문에 사용 가능한 페이지가 0인 경우에도 버퍼 풀의 전체 크기와 같지 않습니다.

 

보류 중인 읽기 및 쓰기는 버퍼 풀 수준에서 보류 중인 요청입니다. Innodb는 파일 레벨에서 여러 요청을 하나로 병합할 수 있으므로 서로 다릅니다. 또한 Innodb가 제출한 다양한 IO 유형(LRU 페이지를 통해 플러시되는 페이지), 장시간 액세스되지 않은 더티 페이지, 플러시 목록, 체크포인트 프로세스로 플러시해야 하는 오래된 페이지 및 단일 페이지 쓰기 등 독립적인 페이지 쓰기를 확인할 수 있습니다.

 

우리는 또한 읽고 쓰는 페이지 수를 볼 수 있습니다. 생성된 페이지는 이전 페이지 내용을 버퍼 풀로 읽지 않은 경우 새 데이터를 위해 버퍼 풀에 작성된 빈 페이지입니다.

 

마지막으로 버퍼 풀 효율성을 측정하는 버퍼 풀 적중률을 확인할 수 있습니다. 1000/1000은 100% 적중률에 해당합니다. 워크로드 의존도가 매우 높기 때문에 어떤 버퍼 풀 적중률이 충분히 좋은지 구별하기 어렵습니다. 경우에 따라서는 1000당 950개면 충분할 수도 있고, 적중률이 1000당 995개인 IO 바인딩 워크로드를 확인할 수도 있습니다.

 

▶︎ 각각의 버퍼풀 정보

각각의 버퍼풀에 대한 자세한 정보들을 나타냅니다.

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   32764 : 버퍼풀 크기
Free buffers       14431 : 여유 버퍼 페이지 수
Database pages     17527 : 데이터베이스 페이지 수
Old database pages 6489 : 오래된 데이터베이스 페이지 수
Modified db pages  10863 : 변경된 데이터베이스 페이지 수
Pending reads      0 : 대기 읽기수

Pending writes: LRU 0, flush list 0, single page 0
## 대기중인 정보들. 쓰기 LRU 수, 플러시(비워진) 리스트 수, 싱글 페이지수

Pages made young 1057, not young 0
##신규 할당 페이지, 할당되지 않은 페이지

0.00 youngs/s, 0.00 non-youngs/s
## 신규로 만들어진 페이지 시간, 신규페이지가 아닌 시간 즉 페이지 지속 시간

Pages read 15730, created 1797, written 17415
## 읽은 페이지 수, 생성된 페이지 수, 쓰여진 페이지 수

0.00 reads/s, 0.00 creates/s, 0.00 writes/s
## 초당 읽기 페이지 수, 초당 만들어진 페이지 수

No buffer pool page gets since the last printout
## 마지막으로 조회 후 조회한 버퍼풀 페이지 수. 마지막 출력 이후 아무런 버퍼풀 페이지 읽기가 없었음. 원래는 버퍼풀 적중률을 나타냄.

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
## 미리읽은 페이지 시간, 액세스되지 않고 제거된 시간, 랜덤 읽기 시간

LRU len: 17527, unzip_LRU len: 0
## LRU 길이, 압축되지 않은 LRU 크기

I/O sum[0]:cur[203], unzip sum[0]:cur[0]
## I/O합계, 현제 갯수, 압축되지 않는 합계와 현재 수

 

▶︎ ROW 운영  정보

ROW에 대한 운영 상태를 나타냅니다.

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
## InnoDB에서 수행된 쿼리수, 큐에 쌓인 쿼리수

0 read views open inside InnoDB
## InnoDB안에서 열린 view의 수. 읽기 트랜잭션이 시작한 시점에 데이터베이스 내용에 대한 MVCC 스냅샷

Process ID=2504, Main thread ID=139853750003456, state: sleeping
## 프로세스 ID와 메인 스레드 ID, 상태는 대기중

Number of rows inserted 396997, updated 794399, deleted 396999, read 10641787
## insert되고 있는 row수, 업데이트 수, 삭제된 수, 일기 수

0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
## 초당 insert수, 초당 update수, 초당 delete수, 초당 읽기 수

 

행 기본 사항 및 일부 시스템 정보를 보여주는 행 작업입니다.

 

대기 중인 활성 쓰레드의 수인 innodb 스레드 대기열 상태를 보여줍니다. Innodb 내에서 열려 있는 읽기 뷰의 수 - 트랜잭션이 시작되었지만 현재 활성 상태인 Innodb 메인 스레드의 상태로 더티 페이지 플러시, 체크포인트, 삭제, 로그 플러싱, 버퍼 병합 수행 등의 시스템 작업 스케줄링을 제어합니다. "state" 필드의 값은 상태에 따라 여러가지로 나올 수 있습니다.

 

시스템 시작 이후의 Row수 및 평균값도 확인할 수 있습니다.  행 연산은 Innodb 부하상태를 나타냅니다. 모든 행 연산이 동일하게 만들어지는 것은 아니며 각 행마다 연산은 다릅니다.

 

한 가지 더 주의할 점은 SHOW ENGINE INODB Status가 수행할때마다 표시가 다를 수 있습니다. SHOW INODB STATUS의 다른 행은 다른 시점에 채워져 있기 때문에 때때로 충돌 정보를 볼 수 있습니다. 일관된 정보를 제공하기 위해 전역 잠금을 요구하면 상당한 오버헤드가 발생할 수 있기 때문에 설계된 것입니다.

 

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