innodb存儲引擎狀态參數

InnoDB存儲引擎層體系結構圖：

<a href="http://s3.51cto.com/wyfs02/M02/73/A5/wKioL1YDWjvxhm5GAAHnCeQgG6k709.jpg" target="_blank"></a>

1.innodb基本資訊

mysql&gt; show engine innodb status \G

*************************** 1. row ***************************

  Type: InnoDB

  Name: 

Status: 

=====================================

2017-01-03 15:34:17 7f916c114700 INNODB MONITOR OUTPUT    --輸出的時間

Per second averages calculated from the last 38 seconds

-----------------

2.背景線程

BACKGROUND THREAD

srv_master_thread loops: 2323156 srv_active, 0 srv_shutdown, 2593442 srv_idle  --主線程每秒loop循環的次數(2323156  激活的次數  0  停止的次數  2593442  等待的次數)

srv_master_thread log flush and writes: 4916598      --主線程日志重新整理和寫入的次數

----------

3.鎖資訊

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 2882668       --全局等待數組資訊(當資料組建立後預留的計數)

OS WAIT ARRAY INFO: signal count 59482367          --收到的通知次數

Mutex spin waits 57404086, rounds 70574291, OS waits 688683   --57404086表示互斥等待的個數,70574291表示循環疊帶的個數,688683系統調用的等待個數

RW-shared spins 23269113, rounds 80987143, OS waits 1648604   --23269113表示共享讀鎖期間讀寫鎖等待的個數,80987143表示循環疊帶的個數,1648604系統調用的等待個數

RW-excl spins 3761579, rounds 143069955, OS waits 524639      --3761579表示排它寫鎖期間讀寫鎖等待的個數,80987143表示循環疊帶的個數,1648604系統調用的等待個數

Spin rounds per wait: 1.23 mutex, 3.48 RW-shared, 38.03 RW-excl

------------------------

4.事物資訊

TRANSACTIONS

------------

Trx id counter 5381        --下一個事物号

Purge done for trx's n:o &lt; 4553 undo n:o &lt; 0 state: running but idle  --所有編号小于4553的事物都已經從曆史記錄清單中清除了

History list length 75     --曆史清單的長度

LIST OF TRANSACTIONS FOR EACH SESSION:       

---TRANSACTION 0, not started

MySQL thread id 2, OS thread handle 0x7f69957c9700, query id 3 localhost root init

show engine innodb status

--------

5.檔案IO

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)   --4個異步讀線程

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)  --8個異步寫入線程

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)

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

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

Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,    --每個讀寫線程狀态(4個讀8個寫)

 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0     --插入緩沖線程的狀态,每秒日志IO狀态,IO同步的狀态

Pending flushes (fsync) log: 0; buffer pool: 0      --檔案同步的日志狀态,緩沖池的個數

72539 OS file reads, 27925279 OS file writes, 21542873 OS fsyncs  --讀了多少個檔案,系統寫了多少個檔案,系統同步了多少個檔案

0.00 reads/s, 0 avg bytes/read, 7.50 writes/s, 5.50 fsyncs/s     --每秒讀的資訊,平均每秒讀的位元組數,每秒寫入的資訊,每秒同步的資訊

------------------------------------- 

6.插入緩沖和自适應哈稀索引

INSERT BUFFER AND ADAPTIVE HASH INDEX

-------------------------------------

Ibuf: size 1, free list len 488, seg size 490, 3595 merges  --1表示緩沖索引樹的目前大小,488空閑清單的長度,490檔案段中已配置設定段的個數,3595合并頁的個數

merged operations:

 insert 3553, delete mark 168, delete 0   --insert插入緩沖的次數,delete mark标記為已删除的次數,delete表示purge的次數(删除)

discarded operations:

 insert 0, delete mark 0, delete 0      --有多少個insert buffer被丢棄,有多少個insert buffer被标記為已删除,purge多少個insert buffer等

Hash table size 9461329, node heap has 8274 buffer(s)     --自适應哈稀索引單元格的數量與預留緩沖結構的數量

21976.71 hash searches/s, 1793.98 non-hash searches/s     --使用哈稀索引的數量與不能使用哈稀索引時向下搜尋B樹索引的次數

---

7.重做日志資訊

LOG

Log sequence number 24627115613       --目前重做日志序列号

Log flushed up to   24627115603      --重新整理到重做日志檔案的序列号

Pages flushed up to 24627105147       --重新整理到磁盤的日志序列号

Last checkpoint at  24627105147      --下一個日志序列号

0 pending log writes, 0 pending chkp writes   --挂起日志寫入數,挂起檢查點寫入數

8868627 log i/o's done, 2.63 log i/o's/second  --innodb啟動後的io個數,最近一次顯示之後的每秒io操作個數

----------------------

8.緩存池和記憶體

BUFFER POOL AND MEMORY

Total memory allocated 4395565056; in additional pool allocated 0     --配置設定記憶體總數和額外的記憶體池

Dictionary memory allocated 961282    --資料字典占用的位元組數

Buffer pool size   262134         --緩沖池的個數    

Free buffers       142352        --剩餘緩沖區的個數

Database pages     111508         --LRU中資料頁的個數

Old database pages 41039           --LRU中舊資料頁的個數

Modified db pages  37            --LRU中已修改的資料頁個數

Pending reads 0        --挂起讀操作的個數

Pending writes: LRU 0, flush list 0, single page 0   --通過使用LRU算法等待重新整理的頁數,在BUF_FLUSH_LIST清單等待重新整理的頁數,在BUF_FLUSH_SINGLE_PAGE等待重新整理的頁數

Pages made young 504766, not young 550   --504766第一次通路變成新頁面的次數,550沒有變成新頁面的次數

0.05 youngs/s, 0.00 non-youngs/s       --LRU中每秒變成新頁面的速率,沒有變成新頁面的速率

Pages read 71261, created 40267, written 14051008    --71261讀操作的頁面個數,40267在緩沖池中建立了沒有讀取的頁面個數,14051008寫操作的頁面個數

0.00 reads/s, 0.00 creates/s, 3.82 writes/s          --LRU中每秒讀取資料的頁速率,每秒建立資料的頁速率,每秒寫入資料的頁速率

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: 111508, unzip_LRU len: 0       --LRU清單長度,unzip_LRU清單長度

I/O sum[978]:cur[0], unzip sum[0]:cur[0]  --LRU在LRU清單中I/O操作的次數,unzip在LRU清單中I/O操作的次數

9.行操作

ROW OPERATIONS

--------------

0 queries inside InnoDB, 0 queries in queue   --有多少個正在查詢操作個數

0 read views open inside InnoDB           --顯示隻讀視圖的數量

Main thread process no. 9822, id 140262664857344, state: sleeping  --顯示主線程的ID及其狀态  

Number of rows inserted 2044551, updated 2917695, deleted 411325, read 319298239254 --從innodb存儲引擎啟動後插入,更新,删除,查詢的行數

0.45 inserts/s, 0.79 updates/s, 0.11 deletes/s, 136529.62 reads/s  --最近一次顯示增,删,改,查的速率

----------------------------

END OF INNODB MONITOR OUTPUT

============================

1 row in set (0.00 sec)

mysql&gt; 

本文轉自 z597011036 51CTO部落格，原文連結：http://blog.51cto.com/tongcheng/1633777，如需轉載請自行聯系原作者