=====================================
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

原理: https://mysqlonarm.github.io/Understanding-InnoDB-rwlock-stats/

----------
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
============================