show engine innodb status 輸出結果解讀

孔個個發表於2021-04-23

show engine innodb status 輸出結果解讀

  1. 基於MySQL 5.7.32
    最近想整理一下show engine innodb status的解讀，但是發現中文網際網路上相關的資訊要麼是不完整，要麼是有諸多錯誤，要麼是版本過於老，很多新增條目並未包含，決定自己整理一份。
    這裡將 show engine innodb status 輸出結果 按照章節部分依次剖析含義。
    主要參考自官方文件 via Dash，
    部分參考了https://cloud.tencent.com/developer/article/1400095
    最近有想將自己基於MySQL 5.7和8.0.18的知識大綱和筆記書公開出來，看看有沒有時間。

目錄

輸出示例

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2021-04-22 11:33:11 0x7fe51c222700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 780 srv_active, 0 srv_shutdown, 12 srv_idle
srv_master_thread log flush and writes: 792

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1225
OS WAIT ARRAY INFO: signal count 1336
RW-shared spins 0, rounds 2286, OS waits 398
RW-excl spins 0, rounds 2371, OS waits 70
RW-sx spins 229, rounds 699, OS waits 8
Spin rounds per wait: 2286.00 RW-shared, 2371.00 RW-excl, 3.05 RW-sx

------------
TRANSACTIONS
------------
Trx id counter 15061670
Purge done for trx's n:o < 15061468 undo n:o < 0 state: running but idle
History list length 1107
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422101800984176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422101800982352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15061669, ACTIVE (PREPARED) 0 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 5, OS thread handle 140621995427584, query id 523581 42.192.58.85 kk starting
commit

--------
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)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
95343 OS file reads, 195458 OS file writes, 135366 OS fsyncs
49.90 reads/s, 16384 avg bytes/read, 241.48 writes/s, 169.18 fsyncs/s

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 192, seg size 194, 1122 merges
merged operations:
 insert 1226, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 315 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 307 buffer(s)
Hash table size 1182691, node heap has 403 buffer(s)
Hash table size 1182691, node heap has 293 buffer(s)
Hash table size 1182691, node heap has 290 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 298 buffer(s)
66.29 hash searches/s, 1026.70 non-hash searches/s

---
LOG
---
Log sequence number 14187726461
Log flushed up to   14187725424
Pages flushed up to 14142755783
Last checkpoint at  14142399692
1 pending log flushes, 0 pending chkp writes
92454 log i/o's done, 102.50 log i/o's/second

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 4397727744
Dictionary memory allocated 450320
Buffer pool size   262112
Free buffers       163810
Database pages     95770
Old database pages 35430
Modified db pages  43010
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 312, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95265, created 505, written 97170
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 988 / 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: 95770, unzip_LRU len: 0
I/O sum[0]:cur[792], unzip sum[0]:cur[0]

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
...
...
<same as BUFFER POOL AND MEMORY>
...

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17254, Main thread ID=140622046865152, state: sleeping
Number of rows inserted 52629, updated 104255, deleted 52135, read 156400
59.79 inserts/s, 115.49 updates/s, 59.39 deletes/s, 174.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.01 sec)

1.HEAD

  • Per second averages calculated from the last 10 seconds

    表示本次報告內"每秒平均"是統計自最後10秒的情況做的計算。

2.BACKGROUND THREAD

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 780 srv_active, 0 srv_shutdown, 12 srv_idle
srv_master_thread log flush and writes: 792

  • srv_master_thread loops: 780 srv_active, 0 srv_shutdown, 12 srv_idle

    srv_master_thread loops ： master執行緒的迴圈次數。
    每次迴圈時會選擇一種狀態來執行（active、shutdown、idle）。
    其中，active狀態的數量增加與資料變化有關，與查詢無關。
    可以通過srv_activesrv_idle的差值來獲得系統的整體負載情況。
    active值越大表示系統越繁忙，當active遠遠高出idle時，說明這個資料庫相對比較繁忙。

    MySQL主要執行緒簡介

    • Master thread：主執行緒，主要負責非同步重新整理和資料一致性處理。

    • IO thread：使用非同步IO模型，負責處理不同型別的IO請求回撥。

    • Purge thread：事務提交後，回收undo。

    • Page Cleaner thread： 執行ibp的髒頁重新整理。
      而對於Page Cleaner thread,預設值為1，如果在MySQL日誌中看到如下的資訊，說明我們的Cleaner Thread需要調整一下了。

      2019-02-14T23:50:00.501209Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 28469710ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.

  • srv_master_thread log flush and writes: 792

    主執行緒日誌重新整理和寫入次數。

3.SEMAPHORES

This section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin

or a wait on a mutex or a rw-lock semaphore.

A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside InnoDB.

Contention can be due to heavy parallelism of queries or problems in operating system thread scheduling.

Setting the innodb_thread_concurrency system variable smaller than the default value might help in such situations.

The Spin rounds per wait line shows the number of spinlock rounds per OS wait for a mutex.

Mutex metrics are reported by SHOW ENGINE INNODB MUTEX.

本節報告了執行緒等待訊號量的統計資訊，並統計執行緒需要spin或等待mutex或rw-lock訊號量的次數。

  • 較大的threads waiting for semaphores可能是磁碟I / O或InnoDB內部爭用問題的結果。

  • 爭用可能是由於查詢的高並行或作業系統執行緒排程中的問題所致。

  • 在這種情況下，將innodb_thread_concurrency系統變數設定為小於預設值可能會有所幫助。

  • 每個spin rounds per wait顯示的是 每個作業系統等待mutex的spinlock round數。

  • mutex度量標準由SHOW ENGINE INNODB MUTEX報告。

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1225
OS WAIT ARRAY INFO: signal count 1336
RW-shared spins 0, rounds 2286, OS waits 398
RW-excl spins 0, rounds 2371, OS waits 70
RW-sx spins 229, rounds 699, OS waits 8
Spin rounds per wait: 2286.00 RW-shared, 2371.00 RW-excl, 3.05 RW-sx

  • OS WAIT ARRAY INFO: reservation count 1225

    OS的等待陣列資訊。InnoDB分配槽的額度。

  • OS WAIT ARRAY INFO: signal count 1336

    OS的等待陣列資訊。衡量的是執行緒通過陣列得到的訊號的頻度。（沒懂）

  • RW-shared spins 0, rounds 2286, OS waits 398

    spin共享鎖(shared)的計數器。

  • RW-excl spins 0, rounds 2371, OS waits 70

    spin排它鎖的計數器。

  • RW-sx spins 229, rounds 699, OS waits 8

    spin共享寫鎖的計數器。

    （猜測sx應該是shared x，共享寫鎖常出現在事務中的update操作，環境中正是進行著大量update。）

  • Spin rounds per wait: 2286.00 RW-shared, 2371.00 RW-excl, 3.05 RW-sx

    每個spin rounds per wait顯示的是 每個作業系統等待mutex的spinlock round數。

InnoDB有一個多階段的等待策略。
首先會對鎖進行自旋（spin），如果經歷了一個自旋週期後還沒有持有鎖，則進入到作業系統等待狀態（os wait），等待被喚醒。
如果在一秒中看到幾十萬個spin wait，則需要關注show engine innodb mutex;

4.TRANSACTIONS

If this section reports lock waits, your applications might have lock contention.
The output can also help to trace the reasons for transaction deadlocks.

當前活躍事務列表。
如果此部分報告了 lock waits，則您的應用程式可能具有鎖爭用。
這部分資訊還可以幫助跟蹤事務死鎖的原因。

Trx id counter 15061670
Purge done for trx's n:o < 15061468 undo n:o < 0 state: running but idle
History list length 1107
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422101800984176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422101800982352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 15061669, ACTIVE (PREPARED) 0 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2
MySQL thread id 5, OS thread handle 140621995427584, query id 523581 42.192.58.85 kk starting
commit

  • Trx id counter 15061670

    每建立一個新事務就會累加。

  • Purge done for trx's n:o < 15061468 undo n:o < 0 state: running but idle

    小於15061468這個ID的事務的歷史資料都被清理掉了。

    減少此類開銷的最好辦法就是確保事務已完成就立即提交，不要讓事務長時間地處於開啟狀態，因為一個開啟的事務即使不做任何操作，也會影響到innodb清理舊版本的行資料。

    undo n:o < 0這個是innodb清理程式正在使用的撤銷日誌編號，為0 0時說明清理程式處於空閒狀態。

  • History list length 1107

    歷史記錄中包含多少個事務的列表長度。就是InnoDB資料檔案undo裡的未清除事務的數量。當一個事務執行了更新並提交後，這個數字就會增加。當清除程式移除一箇舊版本資料時，它就會遞減（purge thread也會更新purge done for …也就是上一行的資料。）。

  • ---TRANSACTION 422101800984176, not started

    每個session的事務情況。事務ID超級大的就是隻讀事務。一旦有寫的動作，才會切換為讀寫事務，分配正常的事務ID。

    not started表示這個事務已經提交併且沒有再發起影響事務的語句，可能剛好空閒。看事務ID，這是一個查詢事務。一旦事務落盤，就not started。

  • ---TRANSACTION 15061669, ACTIVE (PREPARED) 0 sec

  • 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 2

    active 0 sec，活躍事務，持續了0秒。狀態可能是not started，或者其他。如果有鎖等待，在這裡應該也會看到。

  • MySQL thread id 5, OS thread handle 140621995427584, query id 523581 42.192.58.85 kk starting

    MySQL執行緒ID及其他資訊。

5.FILE I/O

This section provides information about threads that InnoDB uses to perform various types of I/O. The first few of these are dedicated to general InnoDB processing. The contents also display information for pending I/O operations and statistics for I/O performance.

The number of these threads are controlled by the innodb_read_io_threads and innodb_write_io_threads parameters. See Section 14.14, “InnoDB Startup Options and System Variables”.

本節提供有關InnoDB用於執行各種型別的I / O的執行緒的資訊。其中的前幾個專用於常規InnoDB處理。內容還顯示有關掛起的I / O操作的資訊和有關I / O效能的統計資訊。

這些執行緒的數量由innodb_read_io_threads和innodb_write_io_threads引數控制。請參見“ InnoDB啟動選項和系統變數”。

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)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
95343 OS file reads, 195458 OS file writes, 135366 OS fsyncs
49.90 reads/s, 16384 avg bytes/read, 241.48 writes/s, 169.18 fsyncs/s

  • I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

    插入緩衝區執行緒。負責change buffer的合併。

  • I/O thread 1 state: waiting for completed aio requests (log thread)

    日誌執行緒。負責非同步的日誌重新整理。

  • I/O thread 2 state: waiting for completed aio requests (read thread)

    讀執行緒，負責讀前置的操作（read-ahead），預測InnoDB將要使用的資料並讀入記憶體。

    讀執行緒的數量受引數 innodb_read_io_threads 控制。

  • I/O thread 6 state: waiting for completed aio requests (write thread)

    寫執行緒，重新整理髒頁。

    寫執行緒的數量受引數 innodb_write_io_threads 控制。

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

    讀寫執行緒掛起操作的數量。aio就是非同步io。

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

insert buffer thread掛起的fsync運算元量。

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

    log thread 掛起的fsync運算元量。

  • 95343 OS file reads, 195458 OS file writes, 135366 OS fsyncs

    讀、寫以及fsyncs操作的次數。

  • 49.90 reads/s, 16384 avg bytes/read, 241.48 writes/s, 169.18 fsyncs/s

    在頭部區域“Per second averages calculated from the last 10 seconds ” 的時間段內，平均每秒的執行次數。

三行掛起讀寫執行緒、緩衝池執行緒、日誌執行緒的統計資訊的值(Pending normal aio reads、 ibuf aio reads、Pending flushes (fsync) log)是檢測I/O受限的應用的一個好方法，如果這些I/O大部分有掛起操作，那麼負載可能I/O受限。

在linux系統下使用引數：innodb_read_io_threads和innodb_write_io_threads兩個變數來配置讀寫執行緒的數量，預設為各4個執行緒。
insert buffer thread：負責插入緩衝合併，如：記錄被從插入緩衝合併到表空間中
log thread：負責非同步刷事務日誌
read thread：執行預讀操作以嘗試預先讀取innodb預感需要的資料
write thread：重新整理髒頁緩衝

6.INSERT BUFFER AND ADAPTIVE HASH INDEX

This section shows the status of the InnoDB insert buffer (also referred to as the change buffer) and the adaptive hash index.

For related information, see Section 14.5.2, “Change Buffer”, and Section 14.5.3, “Adaptive Hash Index”.

此部分顯示InnoDB插入緩衝區（也稱為更改緩衝區）和自適應雜湊索引的狀態。 有關相關資訊，請參見第14.5.2節“更改緩衝區”和第14.5.3節“自適應雜湊索引”。

Ibuf: size 1, free list len 192, seg size 194, 1122 merges
merged operations:
 insert 1226, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1182691, node heap has 315 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 307 buffer(s)
Hash table size 1182691, node heap has 403 buffer(s)
Hash table size 1182691, node heap has 293 buffer(s)
Hash table size 1182691, node heap has 290 buffer(s)
Hash table size 1182691, node heap has 313 buffer(s)
Hash table size 1182691, node heap has 298 buffer(s)
66.29 hash searches/s, 1026.70 non-hash searches/s

  • Ibuf: size 1, free list len 192, seg size 194, 1122 merges

    size 1，已經合併記錄頁的數量。

    Free list len，插入緩衝中空閒列表的長度。

    Seg size，當前insert buffer的長度，單位為頁（16k）。

    merges，代表合併插入的次數。

  • merged operations:

  • insert 1226, delete mark 0, delete 0

三個專案分別表示merge操作合併了多少個insert buffer、delete buffer（標記刪除）、purge buffer（真正刪除）。

  • discarded operations:

  • insert 0, delete mark 0, delete 0

三個專案分別表示當change buffer發生merge時，表可能已經被刪除了，就不再需要合併到輔助索引中了。

  • Hash table size 1182691, node heap has 315 buffer(s)

    自適應雜湊索引狀態。

    AHI大小，及AHI的使用情況（存有多少個buffer）。

  • 66.29 hash searches/s, 1026.70 non-hash searches/s

    根據頭部的時間段，雜湊索引查詢次數和非雜湊索引的查詢次數。

    雜湊索引僅適用於等值查詢。可以根據searches的比例來結合業務情況，瞭解是否需要繼續啟用AHI特性（預設啟用）。

7.LOG

This section displays information about the InnoDB log. The contents include the current log sequence number, how far the log has been flushed to disk, and the position at which InnoDB last took a checkpoint. (See Section 14.12.3, “InnoDB Checkpoints”.) The section also displays information about pending writes and write performance statistics.

本節顯示有關InnoDB日誌的資訊。內容包括當前的日誌序列號，將日誌重新整理到磁碟的距離以及InnoDB上次執行檢查點的位置。 （請參見第14.12.3節“ InnoDB檢查點”。）本節還顯示有關掛起寫入和寫入效能統計資訊。

Log sequence number 14187726461
Log flushed up to   14187725424
Pages flushed up to 14142755783
Last checkpoint at  14142399692
1 pending log flushes, 0 pending chkp writes
92454 log i/o's done, 102.50 log i/o's/second

  • Log sequence number 14187726461

    當前日誌LSN

  • Log flushed up to 14187725424

    日誌已重新整理落盤到的位置的LSN

  • Pages flushed up to 14142755783

    pages flushed up to指的是下一次即將做checkpoint lsn 的位置；

    在沒有新資料的寫入的情況下， pages flushed up to取的是Log sequence number（log_sys->lsn）；

    • 在沒資料寫入的情況下，為什麼last checkpoint point不等於pages flushed up to？是因為做checkpoint是同時redo日誌會寫MLOG_CHECKPOINT，而MLOG_CHECKPOINT佔用九個位元組，所以會出現pages flushed up to-last checkpoint point=9；

      https://blog.csdn.net/cuiyandan4490/article/details/100464485

  • Last checkpoint at 14142399692

    最新ckpt的LSN

  • 1 pending log flushes, 0 pending chkp writes

    還未完成的日誌操作及統計資訊

  • 92454 log i/o's done, 102.50 log i/o's/second

    已經發生的日誌操作統計資訊

8.BUFFER POOL AND MEMORY / INDIVIDUAL BUFFER POOL INFO

This section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

For buffer pool statistics descriptions, see Monitoring the Buffer Pool Using the InnoDB Standard Monitor. For additional information about the operation of the buffer pool, see Section 14.5.1, “Buffer Pool”.

本部分為您提供有關已讀和已寫頁面的統計資訊。您可以從這些數字中計算出查詢當前正在執行多少個資料檔案I / O操作。 有關緩衝池統計資訊的描述，請參閱使用InnoDB Standard Monitor監視緩衝池。有關緩衝池操作的更多資訊，請參見第14.5.1節“緩衝池”。

InnoDB儲存引擎 讀書筆記/05.LRU、頁壓縮、髒頁、檢查點.md

Total large memory allocated 4397727744
Dictionary memory allocated 450320
Buffer pool size   262112
Free buffers       163810
Database pages     95770
Old database pages 35430
Modified db pages  43010
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 312, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 95265, created 505, written 97170
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 988 / 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: 95770, unzip_LRU len: 0
I/O sum[0]:cur[792], unzip sum[0]:cur[0]

  • Total large memory allocated 4397727744

    分配給InnoDB Buffer Pool的總記憶體，單位bytes。

  • Dictionary memory allocated 450320

    分配給InnoDB資料字典的記憶體，單位bytes。

  • Buffer pool size 262112

    分配給IBP的記憶體，單位pages

  • Free buffers 163810

    Buffer Pool Free List 總大小，單位pages

  • Database pages 95770

    Buffer Pool LRU List 總大小，單位pages

  • Old database pages 35430

    IBP old LRU 總大小，單位pages（冷端）

  • Modified db pages 43010

    當前IBP中髒頁的數量，單位pages

  • Pending reads 0

    等待讀入IBP的頁數量，單位pages

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

    LRU，從LRU列表的底部開始寫入的舊髒頁數。 （沒懂）；flush list，ckpt期間要重新整理的緩衝池頁面數；single page，IBP中暫掛的獨立頁面寫入數。

  • Pages made young 312, not young 0

    LRU中被made young的頁面數（LRU列表中頁移動到前端的次數）；not young，LRU中保持在old 子列表中的頁面數（沒被移動到前端，因innodb_old_blocks_time設定。）

  • 0.00 youngs/s, 0.00 non-youngs/s

    youngs/s度量標準僅用於old pages，基於對page的訪問次數，而不是頁的數量。對頁進行多次訪問都會被計算。如果見到非常低的值，可能需要減小延遲或增加old page LRU list 的比例。增大後，頁面需要更長的時間才會移動到尾部，這就增加了再次訪問page，從而使他們made young的可能性增大。 Not young，如果在執行大表掃描時未看到較高的non-young和non-youngs/s，請增加innodb_old_blocks_time。

  • Pages read 95265, created 505, written 97170

    從磁碟到緩衝池讀取的頁面總數。在緩衝池中建立的頁面總數（從ibp分配，但並未從檔案中讀入資料的頁面，可能屬於一個被刪除的表）。從緩衝池到磁碟寫入的頁面總數。

  • 0.00 reads/s, 0.00 creates/s, 0.00 writes/s

    平均每秒讀的頁數，建立的頁數，寫的頁數。

  • Buffer pool hit rate 988 / 1000, young-making rate 0 / 1000 not 0 / 1000

    buffer pool hit ，緩衝池的命中率，用來衡量innodb在緩衝池中查詢到所需頁的比例，（不確定是不是：它度量自上次Innodb狀態輸出後到本次輸出這段時間內的命中率，因此，如果伺服器自那以後一直很安靜，你將會看到No buffer pool page gets since the last printout。它對於度量快取池的大小並沒有用處。）；young-making rate ，指對所有buffer pool的訪問，不僅僅是old page LRU list。Young making和not young通常不會合計到整個IBP的命中率（因為old命中了就移動到new，而new的命中只有在與head有一定距離時才會移動到頭部；not，因innodb_old_blocks_time設定而不滿足移動到new，或命中new但是並沒使其向頭部移動的全ibp的命中率，不僅僅是old LRU list。

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

    每秒平均預讀操作次數；每秒因未被訪問而被逐出ibp的頁數；每秒平均隨機預讀次數。

  • LRU len: 95770, unzip_LRU len: 0

    LRU len，IBP中LRU列表包含頁面的總數（Database pages）；壓縮頁的unzip_LRU列表包含頁面的總數（0則表示沒有）。

  • I/O sum[0]:cur[792], unzip sum[0]:cur[0]

    最近50秒內訪問的緩衝池LRU列表頁面的總數；已訪問的緩衝池LRU列表頁面的總數； 已訪問的緩衝池unzip_LRU列表頁面的總數；已訪問的緩衝池unzip_LRU列表頁面的總數。

9.ROW OPERATIONS

This section shows what the main thread is doing, including the number and performance rate for each type of row operation.

本節顯示了主執行緒在做什麼，包括每種行操作的數量和效能比率。

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=17254, Main thread ID=140622046865152, state: sleeping
Number of rows inserted 52629, updated 104255, deleted 52135, read 156400
59.79 inserts/s, 115.49 updates/s, 59.39 deletes/s, 174.88 reads/s

  • 0 queries inside InnoDB, 0 queries in queue

  • 0 read views open inside InnoDB

  • Process ID=17254, Main thread ID=140622046865152, state: sleeping

  • Number of rows inserted 52629, updated 104255, deleted 52135, read 156400

    累計值。

  • 59.79 inserts/s, 115.49 updates/s, 59.39 deletes/s, 174.88 reads/s

    前面的統計時間，平均下來每秒值。

相關文章