MySQL 8.4 版本注意事項
鎖定監控器與標準監控器相同，但包含額外的鎖定資訊。啟用任一監控器進行定期輸出會開啟相同的輸出串流，但如果啟用鎖定監控器，則串流會包含額外資訊。例如，如果您啟用標準監控器和鎖定監控器，則會開啟單一輸出串流。在您停用鎖定監控器之前，該串流會包含額外的鎖定資訊。
使用 SHOW ENGINE INNODB STATUS 陳述式產生標準監控器輸出時，會限制為 1MB。此限制不適用於寫入伺服器標準錯誤輸出 (stderr) 的輸出。
標準監控器輸出範例
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2018-04-12 15:14:08 0x7f971c063700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 1122 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 24
OS WAIT ARRAY INFO: signal count 24
RW-shared spins 4, rounds 8, OS waits 4
RW-excl spins 2, rounds 60, OS waits 2
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 2.00 RW-shared, 30.00 RW-excl, 0.00 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-04-12 14:57:24 0x7f97a9c91700 Transaction:
TRANSACTION 7717, 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 8, OS thread handle 140289365317376, query id 14 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 `test`.`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 `test`.`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 000000001e19; asc ;;
2: len 7; hex 81000001110137; asc 7;;
------------
TRANSACTIONS
------------
Trx id counter 7748
Purge done for trx's n:o < 7747 undo n:o < 0 state: running but idle
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421764459790000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 7747, ACTIVE 23 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 9, OS thread handle 140286987249408, query id 51 localhost root updating
DELETE FROM t WHERE i = 1
------- TRX HAS BEEN WAITING 23 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
trx id 7747 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000202; asc ;;
1: len 6; hex 000000001e41; asc A;;
2: len 7; hex 820000008b0110; asc ;;
3: len 4; hex 80000001; asc ;;
------------------
TABLE LOCK table `test`.`t` trx id 7747 lock mode IX
RECORD LOCKS space id 4 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t`
trx id 7747 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000202; asc ;;
1: len 6; hex 000000001e41; asc A;;
2: len 7; hex 820000008b0110; asc ;;
3: len 4; hex 80000001; asc ;;
--------
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
833 OS file reads, 605 OS file writes, 208 OS fsyncs
0.00 reads/s, 0 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 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 1 buffer(s)
Hash table size 553253, node heap has 3 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
Hash table size 553253, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 19643450
Log buffer assigned up to 19643450
Log buffer completed up to 19643450
Log written up to 19643450
Log flushed up to 19643450
Added dirty pages up to 19643450
Pages flushed up to 19643450
Last checkpoint at 19643450
129 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 409606
Buffer pool size 131072
Free buffers 130095
Database pages 973
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 810, created 163, written 404
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
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 973, 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 65043
Database pages 491
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 411, created 80, written 210
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
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 491, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 65536
Free buffers 65052
Database pages 482
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 399, created 83, written 194
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: 482, 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=5772, Main thread ID=140286437054208 , state=sleeping
Number of rows inserted 57, updated 354, deleted 4, read 4421
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================如需標準監控器報告的每個指標的描述，請參閱指標章節，該章節位於Oracle Enterprise Manager for MySQL Database 使用者指南中。
狀態本節顯示時間戳記、監控器名稱，以及每秒平均值的依據秒數。秒數是目前時間和上次列印
InnoDB監控器輸出之間經過的時間。背景執行緒srv_master_thread行會顯示主要背景執行緒完成的工作。信號量此章節報告等待號誌的執行緒，以及關於執行緒在互斥鎖或讀寫鎖號誌上需要旋轉或等待次數的統計資訊。大量執行緒等待號誌可能導因於磁碟 I/O，或
InnoDB內部的競爭問題。競爭可能源於查詢的高度平行化，或作業系統執行緒排程的問題。在這種情況下，將innodb_thread_concurrency系統變數設定為小於預設值可能會有幫助。 「每次等待的旋轉次數」行顯示每個互斥鎖 OS 等待的旋轉鎖回合數。互斥鎖指標由
SHOW ENGINE INNODB MUTEX報告。最新的外鍵錯誤此章節提供有關最近一次外鍵約束錯誤的資訊。如果沒有發生此類錯誤，則不會顯示此章節。內容包括失敗的陳述式，以及有關失敗的約束、被參考和參考的表格資訊。
最新的偵測到的死鎖此章節提供有關最近一次死鎖的資訊。如果沒有發生死鎖，則不會顯示此章節。內容顯示了哪些交易涉及其中，每個交易試圖執行的陳述式、它們擁有和需要的鎖，以及
InnoDB決定回滾以打破死鎖的交易。本節報告的鎖定模式在 第 17.7.1 節，「InnoDB 鎖定」 中進行了說明。交易如果此章節報告鎖定等待，您的應用程式可能存在鎖定競爭。輸出也有助於追蹤交易死鎖的原因。
檔案 I/O此章節提供有關
InnoDB用於執行各種類型 I/O 的執行緒資訊。其中前幾個專用於常規的InnoDB處理。內容還會顯示有關擱置的 I/O 操作和 I/O 效能統計資訊。這些執行緒的數量由
innodb_read_io_threads和innodb_write_io_threads參數控制。請參閱 第 17.14 節，「InnoDB 啟動選項和系統變數」。插入緩衝區和自適應雜湊索引此章節顯示
InnoDB插入緩衝區（也稱為變更緩衝區）和自適應雜湊索引的狀態。有關相關資訊，請參閱 第 17.5.2 節，「變更緩衝區」和 第 17.5.3 節，「自適應雜湊索引」。
日誌此章節顯示有關
InnoDB日誌的資訊。內容包括目前日誌序號、日誌已刷新到磁碟的程度，以及InnoDB上次建立檢查點的位置。（請參閱 第 17.11.3 節，「InnoDB 檢查點」。）此章節還會顯示有關擱置的寫入和寫入效能統計資訊。緩衝池和記憶體此章節提供有關讀取和寫入頁面的統計資訊。您可以根據這些數字計算您的查詢目前正在執行多少資料檔案 I/O 操作。
有關緩衝池統計資訊的說明，請參閱使用 InnoDB 標準監視器監視緩衝池。有關緩衝池操作的更多資訊，請參閱第 17.5.1 節，「緩衝池」。
列操作此章節顯示主執行緒正在執行的操作，包括每種類型列操作的數量和效能速率。