-- 顯示innodb存儲(chǔ)引擎狀態(tài)的統(tǒng)計(jì)和配置信息
show engine innodb status;

展示的主要內(nèi)容有
-----------------
BACKGROUND THREAD  --后臺(tái)線程
-----------------
srv_master_thread loops: 19610306 srv_active, 0 srv_shutdown, 9705136 srv_idle  --統(tǒng)計(jì)Innodb啟動(dòng)后的活動(dòng)
srv_master_thread log flush and writes: 29312902  --寫(xiě)入和刷新日志的次數(shù)
----------
SEMAPHORES  --信號(hào)量包含了線程等待互斥鎖或讀寫(xiě)鎖的信息
----------
OS WAIT ARRAY INFO: reservation count 52795642  --os等待數(shù)組信息，分配插槽的次數(shù)
OS WAIT ARRAY INFO: signal count 57522728  --os等待數(shù)組信息，線程通過(guò)數(shù)組得到信號(hào)的次數(shù)
RW-shared spins 0, rounds 77349143, OS waits 9180114  --共享鎖期間，讀寫(xiě)鎖存器上自旋等待個(gè)數(shù)，自旋循環(huán)迭代次數(shù)以及操作系統(tǒng)調(diào)用的等待個(gè)數(shù)
RW-excl spins 0, rounds 179767865, OS waits 2534243  --排他鎖期間，讀寫(xiě)鎖存器上自旋等待個(gè)數(shù)，自旋循環(huán)迭代次數(shù)以及操作系統(tǒng)調(diào)用的等待個(gè)數(shù)
RW-sx spins 2068750, rounds 40171680, OS waits 844522  --共享排他鎖期間，讀寫(xiě)鎖存器上自選等待個(gè)數(shù)，自旋循環(huán)迭代次數(shù)以及操作系統(tǒng)調(diào)用的等待個(gè)數(shù)
Spin rounds per wait: 77349143.00 RW-shared, 179767865.00 RW-excl, 19.42 RW-sx  --對(duì)于每一個(gè)互斥鎖，操作系統(tǒng)調(diào)用等待的每一個(gè)自旋循環(huán)迭代個(gè)數(shù)
------------
TRANSACTIONS --包含所有當(dāng)前正在執(zhí)行的事務(wù)的信息
------------
Trx id counter 1888483436  --當(dāng)前事務(wù)id
Purge done for trx s n:o < 1888483436 undo n:o < 0 state: running but idle --所有編號(hào)小于1888483436的事務(wù)都已經(jīng)從歷史記錄列表中清除了，清除舊的MVCC行時(shí)所用的事務(wù)id，這個(gè)值與當(dāng)前事務(wù)ID進(jìn)行比較，就可以知道有多少老版本的數(shù)據(jù)未被清除
History list length 17 --歷史列表的長(zhǎng)度，位于Innodb數(shù)據(jù)文件的撤銷空間里的頁(yè)面的數(shù)目，如果事務(wù)執(zhí)行了更新并提交，該數(shù)目就會(huì)增加，當(dāng)清理進(jìn)程移除舊版本數(shù)據(jù)時(shí)，該數(shù)目會(huì)減少
LIST OF TRANSACTIONS FOR EACH SESSION: 當(dāng)前事務(wù)列表
---TRANSACTION 422068961001072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068960999248, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961005632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961013840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961012016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961010192, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961001984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961000160, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961017488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961011104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961012928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961004720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961002896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961003808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422068961007456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O  --各種IO操作的Innodb內(nèi)部線程以及執(zhí)行了多少次IO操作
--------
-- 有四個(gè)線程
-- insert buffer thread 負(fù)責(zé)插入緩沖合并
-- log thread 負(fù)責(zé)異步刷日志
-- read thread 執(zhí)行預(yù)讀操作以嘗試預(yù)先讀取Innodb預(yù)感需要的數(shù)據(jù)
-- write thread 刷臟緩沖
I/O thread 0 state: waiting for completed aio requests (insert buffer thread) --IO線程的狀態(tài)
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] , 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 -- 掛起操作的信息，aio是指異步io
83934578288 OS file reads, 282688772 OS file writes, 190348192 OS fsyncs --innodb啟動(dòng)后的總統(tǒng)計(jì)信息
984.40 reads/s, 16384 avg bytes/read, 10.15 writes/s, 9.12 fsyncs/s --最后一次顯示后的總統(tǒng)計(jì)信息
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX  --插入緩沖區(qū)與自適應(yīng)散列統(tǒng)計(jì)信息
-------------------------------------
Ibuf: size 1, free list len 3078, seg size 3080, 8815726 merges --在頁(yè)中插入緩沖索引樹(shù)的當(dāng)前大小，空閑列表的長(zhǎng)度，在包含插入緩沖樹(shù)與頭信息的文件段中已分配頁(yè)的個(gè)數(shù)，被合并頁(yè)的個(gè)數(shù)
merged operations:
 insert 6898371, delete mark 38430046, delete 1226485  --通過(guò)類型區(qū)分，索引頁(yè)被執(zhí)行合并操作的次數(shù)
discarded operations:
 insert 1019, delete mark 0, delete 0 --無(wú)須合并丟棄操作的數(shù)量
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 74 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 2 buffer(s)
Hash table size 34673, node heap has 28 buffer(s)
Hash table size 34673, node heap has 7 buffer(s)
5203.54 hash searches/s, 128.14 non-hash searches/s --成功使用自適應(yīng)散列索引查找的數(shù)量，當(dāng)不能使用自適應(yīng)索引時(shí)向下搜索B樹(shù)的次數(shù)
---
LOG    --Innodb日志中活動(dòng)信息
---
Log sequence number 319041331834 --當(dāng)前日志序號(hào)
Log flushed up to   319041331699 -- 日志已經(jīng)刷到的位置
Pages flushed up to 319033170877 
Last checkpoint at  319033170877  -- 上一個(gè)檢查點(diǎn)，當(dāng)前日志序列號(hào)LSN
0 pending log flushes, 0 pending chkp writes 
169033177 log i/o's done, 8.92 log i/o's/second --掛起的日志寫(xiě)入次數(shù)，掛起的檢查點(diǎn)寫(xiě)入個(gè)數(shù)，innodb啟動(dòng)后的IO操作個(gè)數(shù)，從最近一次顯示之后的每秒IO操作個(gè)數(shù)
----------------------
BUFFER POOL AND MEMORY   --Innodb緩沖池與內(nèi)存使用情況
----------------------
Total large memory allocated 137428992 --分配的內(nèi)存
Dictionary memory allocated 1204989 --被數(shù)據(jù)字典表與索引對(duì)象所占空間的字節(jié)數(shù)
Buffer pool size   8191 --緩沖池個(gè)數(shù)
Free buffers       1024 --空閑緩沖區(qū)個(gè)數(shù)
Database pages     7052 --當(dāng)前緩沖區(qū)LRU隊(duì)列的長(zhǎng)度(分配用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)頁(yè)的頁(yè)數(shù))
Old database pages 2583 --舊的LRU隊(duì)列的長(zhǎng)度
Modified db pages  530 --需要刷新的頁(yè)面的數(shù)量(臟數(shù)據(jù)庫(kù)頁(yè)數(shù))
Pending reads      0 --掛起讀操作的個(gè)數(shù)
Pending writes: LRU 0, flush list 0, single page 0 --通過(guò)LRU算法，等待刷新的頁(yè)數(shù)
Pages made young 983912385, not young 304833753259 --因?yàn)樽罱谝淮伪辉L問(wèn)時(shí)，變成新頁(yè)面的數(shù)目和沒(méi)有變成新頁(yè)面的數(shù)目
1.54 youngs/s, 16246.04 non-youngs/s -- 上述兩個(gè)值每秒的速率
Pages read 83934649301, created 4135172, written 103030852 --讀操作的頁(yè)面數(shù)目，在緩沖區(qū)中創(chuàng)建但是沒(méi)有讀取的頁(yè)面數(shù)目，寫(xiě)操作的頁(yè)面數(shù)目
984.40 reads/s, 0.17 creates/s, 1.15 writes/s -- 上述值美妙的速率
Buffer pool hit rate 972 / 1000, young-making rate 0 / 1000 not 478 / 1000 --讀取到的頁(yè)面數(shù)與獲得的緩沖池頁(yè)面的比例，變?yōu)樾马?yè)面的頁(yè)面數(shù)與獲得緩沖池頁(yè)面的比例，沒(méi)有變?yōu)樾马?yè)面的頁(yè)面數(shù)與獲得緩沖池頁(yè)面的比例
Pages read ahead 913.79/s, evicted without access 5.60/s, Random read ahead 0.00/s --預(yù)讀的速率與不通過(guò)訪問(wèn)剔除的預(yù)讀頁(yè)面的個(gè)數(shù)
LRU len: 7052, unzip_LRU len: 0 --LRU列表的長(zhǎng)度，unzip_LRU列表的長(zhǎng)度
I/O sum[4121]:cur[0], unzip sum[0]:cur[0] --IO操作的次數(shù)：當(dāng)前間隔的IO
--------------
ROW OPERATIONS   --行操作
--------------
0 queries inside InnoDB, 0 queries in queue --當(dāng)前有多少個(gè)正在執(zhí)行的查詢，在innodb_thread_concurrency隊(duì)列中的查詢個(gè)數(shù)
0 read views open inside InnoDB --只讀視圖的數(shù)量
Process ID=1543, Main thread ID=140593683990272, state: sleeping --線程id以及狀態(tài)
Number of rows inserted 56092883, updated 133093048, deleted 40729879, read 477150639699 --從innodb啟動(dòng)后，插入、更新、刪除、讀取的行數(shù)
0.19 inserts/s, 7.73 updates/s, 0.00 deletes/s, 138100.85 reads/s -- 速率

-- 展示Innodb的互斥體信息
show engine innodb mutex;

Type      Name                           Status
InnoDB    rwlock: dict0dict.cc:2782       waits=4
InnoDB    rwlock: dict0dict.cc:1228       waits=80
InnoDB    rwlock: log0log.cc:846         waits=75
InnoDB    sum rwlock: buf0buf.cc:1460     waits=11

-- name列顯示了創(chuàng)建互斥體的源文件和行號(hào)
-- status列顯示了互斥體在操作系統(tǒng)上的等待次數(shù)

show status like 'innodb%log%'

Variable_name                 Value
Innodb_log_waits                0     日志文件太小時(shí)，操作必須等待日志刷新的等待時(shí)間計(jì)數(shù)器，該值如果長(zhǎng)期大于0，可以適當(dāng)增加日志文件大小
Innodb_log_write_requests      4539    日志寫(xiě)入請(qǐng)求的數(shù)量
Innodb_log_writes               22     數(shù)據(jù)被寫(xiě)入日志的次數(shù)
Innodb_os_log_fsyncs           1020   操作系統(tǒng)文件同步的數(shù)量(fsync()方法調(diào)用)
Innodb_os_log_pending_fsyncs    0     阻塞的文件同步請(qǐng)求的數(shù)量，如果該值開(kāi)始增長(zhǎng)并長(zhǎng)期大于0，需要檢查磁盤(pán)訪問(wèn)問(wèn)題
Innodb_os_log_pending_writes    0     阻塞的日志寫(xiě)請(qǐng)求的次數(shù)，如果該值開(kāi)始增長(zhǎng)并長(zhǎng)期大于0，需要檢查磁盤(pán)訪問(wèn)問(wèn)題
Innodb_os_log_written         2855424  寫(xiě)入日志中的字節(jié)總量
Innodb_available_undo_logs    128

-- 可以查看存儲(chǔ)引擎的統(tǒng)計(jì)信息，其中包含有緩沖池的信息
show engine innodb status;

截取出緩沖池的信息來(lái)進(jìn)行分析
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 223164
Buffer pool size   8191
Free buffers       7374   空的且可用于緩沖數(shù)據(jù)的緩沖段個(gè)數(shù)
Database pages     809   
Old database pages 299
Modified db pages  0   發(fā)生變化的頁(yè)數(shù)
Pending reads      0   等待中的讀請(qǐng)求個(gè)數(shù)
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 503, created 306, written 2534
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: 809, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

show status like 'innodb%buf%'
Variable_name                         Value
Innodb_buffer_pool_pages_data           809    含有數(shù)據(jù)的頁(yè)數(shù)，包括不變和改變的頁(yè)
Innodb_buffer_pool_bytes_data         13254656 含有數(shù)據(jù)的字節(jié)數(shù)
Innodb_buffer_pool_pages_dirty          0      改變的字節(jié)數(shù)
Innodb_buffer_pool_bytes_dirty          0      改變的頁(yè)的數(shù)目
Innodb_buffer_pool_pages_flushed       2525    緩沖池頁(yè)面被刷新的次數(shù)
Innodb_buffer_pool_pages_free           7374    空頁(yè)面的數(shù)目
Innodb_buffer_pool_pages_misc            8      用于管理工作的頁(yè)數(shù)，公式為'Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free-Innodb_buffer_pool_pages_data'
Innodb_buffer_pool_pages_total         8191    緩沖池中的總頁(yè)數(shù)
Innodb_buffer_pool_read_ahead_rnd        0      掃描大塊數(shù)據(jù)時(shí)發(fā)生隨機(jī)讀頭的數(shù)量
Innodb_buffer_pool_read_ahead            0      
Innodb_buffer_pool_read_ahead_evicted    0
Innodb_buffer_pool_read_requests      107632   邏輯讀請(qǐng)求的次數(shù)
Innodb_buffer_pool_reads                504    直接從磁盤(pán)中邏輯讀取的次數(shù)(沒(méi)有從緩沖池中讀)
Innodb_buffer_pool_wait_free             0     如果緩沖池繁忙且沒(méi)有空頁(yè)，innodb需要等待頁(yè)面刷新，該值表示等待次數(shù)，若始終大于0，可適當(dāng)增加緩沖池大小
Innodb_buffer_pool_write_requests       47403   寫(xiě)入innodb緩沖池的次數(shù)