=====================================
140114 11:23:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 49 seconds
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     根據最近49秒的資料進行計算
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 405 1_second, 405 sleeps, 40 10_second, 5 background, 5 flush
srv_master_thread log flush and writes: 419
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1_second 主迴圈執行次數

sleeps   每秒掛起次數

1_second和 sleeps的差值越大系統壓力越大的。1_second 做什麼事情？
        flush redo log;  

        merge insert buffer;

        flush dirty page;   

         嘗試sleep(1)，系統壓力決定

10_second 如果小於 1_second/10, 說明1_second的比較繁忙，導致1_second不會每次都sleep(1)。做什麼事情?
        可能(IO壓力小、髒頁過多)

       flush dirty page;   

       merge insert buffer;        

       flush redo log;        

       purge(固定數量); 
background 大於0，代表系統在這段時間壓力比較小。做什麼事情?
        purge(清理所有undo不用頁)、merge(固定數量)insert buffer
flush大於0，代表系統在這段時間壓力非常小。做什麼事情?
        迴圈flush dirty page

TODO: 根據指標對資料庫壓力進行打標分級

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----------
SEMAPHORES   高併發重點關注 

MySQL latch爭用深入分析  

https://www.cnblogs.com/geaozhang/p/7287108.html

原理:

----------
OS WAIT ARRAY INFO: reservation count 138, signal count 139
Mutex spin waits 222, rounds 1333, OS waits 26
RW-shared spins 35, rounds 1021, OS waits 34
RW-excl spins 0, rounds 2346, OS waits 78
Spin rounds per wait: 6.00 mutex, 29.17 RW-shared, 2346.00 RW-excl

reservation count： 執行緒嘗試訪問os wait array的次數，大於等於執行緒進入os wait狀態的執行緒數

  (因為嘗試放入os wait array可能不成功，不成功的時候reservation count也會++)

Signal count：執行緒被喚醒的次數，進入os wait的執行緒，在佔用資源執行緒釋放mutex的時候會透過signal喚醒等待執行緒。

Mutex spin waits 222  這個代表的是執行緒無法獲取鎖, 進入spin-wait。

rounds 1333是執行緒在spin-wait迴圈檢查mutex是否已經釋放的探測次數。
OS waits 26是spin-wait完成以後，還是沒有獲得mutex，不得不sleep的次數。
這個主要是評估mutex 獲取不到的比例,這裡請求mutex不到的情況是 222，但是經過spin-wait，實際上最終只有26次。也就是說只有10%需要真正sleep，90%稍微等一下就能拿到mutex
知識點:
Innodb在獲得mutex時是兩階段的。如果Mutex被別人鎖住了，那麼它並不會直接就sleep等待被喚醒了。而是先做一個迴圈，不斷去獲取mutex鎖，稱之為spin-wait，然後才sleep。因為sleep等待被喚醒的代價還是比較高的。透過spin-wait，可以明顯降低這個開銷。.

latch爭用發生的原因 --  http://www.cnblogs.com/data-zhang/p/6971718.html

1、記憶體訪問太頻繁（不停地找）

2、list鏈太長（鏈上掛10000個快，被持有的機率太大）

所以有時候會增加instance的數量，把大pool切成小的pool，讓list鏈變的短一些

如何降低latch爭用

1.最佳化sql，降低對記憶體讀的數量——效果比較明顯

2.增加instances的數量

如何準確發現解決latch

1.sh ow engine Innodb mutex

2.檢視什麼型別的latch

3.定位sql

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------
TRANSACTIONS
------------
Trx id counter 198FE
Purge done for trx's n:o < 1888B undo n:o < 0
History list length 1604
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 198FD, not started
MySQL thread id 15, OS thread handle 0x7f8f65fe9700, query id 13483 localhost root User sleep
select sleep(1)
---TRANSACTION 0, not started
MySQL thread id 13, OS thread handle 0x7f8f6606b700, query id 13483 10.0.4.34 root
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 11, OS thread handle 0x7f8f65f67700, query id 266 10.0.4.34 root
---TRANSACTION 18C38, not started
MySQL thread id 2, OS thread handle 0x7f8f660ed700, query id 125 Slave has read all relay log; waiting for the slave I/O thread to update it
---TRANSACTION 18C3A, ACTIVE 6122 sec
2 lock struct(s), heap size 376, 10 row lock(s), undo log entries 9
MySQL thread id 10, OS thread handle 0x7f8f65fa8700, query id 262 10.0.4.34 root
+++
 檢視事務狀態，未commit的看不到具體sql，只能看到鎖佔用的資源 1 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
Purge done for trx's n:o < 252BE116 undo n:o < 0
    Purge done for trx's n:o是回收程式啟動事務的數量
    undo n:o"顯示回收程式正在處理的undo日誌記錄數，如果當前沒有回收程式執行，則該值為0
History list length 2007
    History list length是在回滾段中未回收的事務數。當更新事務提交時該值增加，當回收執行緒執行時該值減少。

---TRANSACTION 18C3A, ACTIVE 6122 sec
2 lock struct(s), heap size 376, 10 row lock(s), undo log entries 9
鎖資訊: 2個struct，10行，9個undo

--------
FILE I/O
--------
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
638 OS file reads, 7513 OS file writes, 6948 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 23.37 writes/s, 21.72 fsyncs/s
+++
系統的IO執行緒資訊:讀執行緒、寫執行緒、1個redo log執行緒、1個insert buffer執行緒

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 42, seg size 44, 0 merges

--  size 代表已經合併記錄頁的數量、

--  free list 代表插入緩衝中空閒列表長度

--   seg size 代表當前insert buffer的長度，大小為44*16K(每個seg的大小)

--   merges 代表合併插入的次數

merged operations:
 insert 0, 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 276707, node heap has 1 buffer(s)

30.88 hash searches/s, 0.56 non-hash searches/s

Innodb在統計時間範圍內(見第一部分)每秒完成了多少雜湊索引操作

 hash searches/s 表示每秒使用AHI搜尋的情況

 non-hash searches/s 表示每秒沒有使用AHI搜尋的情況

 (因為雜湊索引只能用於等值查詢，而範圍查詢，模糊查詢是不能使用雜湊索引的。)

 透過hash searches: non-hash searches的比例瞭解雜湊索引的效率

可以透過innodb_adaptive_hash_index=ON|OFF引數來選擇是否需要

---
LOG 引數說明
---
Log sequence number293625084
     log buffer中的日誌序列號， 根據此值的增長情況，決定redo log file size(一般能容納1個小時的資料量)的大小
Log flushed up to  293625084
     log buffer重新整理到日誌檔案的序列號， 與LSN的差值代表log buffer中未重新整理到log file的位元組數
     innodb_flush_log_at_trx_commit 會影響
Last checkpoint at  293620088
    根據與LSN的差距，判斷髒頁的重新整理情況。差距越大寫壓力越大，考慮拆分寫業務
0 pending log writes, 0 pending chkp writes
    根據這兩個值，可以判斷 REDO log的IO大還是髒頁資料flush的IO大
6705 log i/o's done, 20.95 log i/o's/second
    日誌的統計/平均資訊
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 103790
Buffer pool size   8192
Free buffers       7699
Database pages     492
Old database pages 0
Modified db pages  17
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 471, created 21, written 3733
0.00 reads/s, 0.09 creates/s, 11.37 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: 492, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
+++
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000
    BP命中率，最低95%。否則要檢查原因了
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    預讀功能的效果:可以透過status觀察此功能
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 28212, id 140253862569728, state: sleeping
Number of rows inserted 3324, updated 9, deleted 0, read 110
10.42 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
+++
0 queries inside InnoDB, 0 queries in queue
    執行緒佇列中活躍/等待的數量
----------------------------
END OF INNODB MONITOR OUTPUT
============================