mysql> show engine innodb status\G;

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

Type: InnoDB

Name:

Status:

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

2016-11-17 10:20:57 7f5fd92a8700 INNODB MONITOR OUTPUT

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

Per second averages calculated from the last 5 seconds

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

BACKGROUND THREAD

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

srv_master_thread loops: 263748 srv_active, 0 srv_shutdown, 959366 srv_idle

srv_master_thread log flush and writes: 1223114

InnoDB 存储引擎的核心操作大部分都集中在 Mater Thread 后台线程中，该状态显示了后台线程状态信息，Master Thread 的主要工作：

主循环(loop)主要以每一秒和每十秒的频率执行刷新日志缓存，合并插入缓存，刷新脏页缓存，删除无用 undo 页等操作

如果当前没有用户活动，则进入后台循环流程(backgroud loop)，主要执行删除无用的 undo 页，合并插入缓存

如果没有什么事情可以做了，便进入了暂停循环(suspend loop)，等待事件循环唤起

-----------------BACKGROUNDTHREAD-----------------#srv_active为每秒的循环次数，srv_idle为每10秒的的循环次数，srv_shutdown为停止的循环，通常为0#如果每秒循环次数少，每10秒次数多，证明当前负载很低；如果每秒循环次数多，每10秒次数少，远大于10：1，证明当前负载很高srv_master_threadloops:2818842srv_active,0srv_shutdown,411srv_idle#日志缓冲刷盘次数srv_master_threadlogflushandwrites:2819194

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 335693

OS WAIT ARRAY INFO: signal count 7995932

Mutex spin waits 2260302, rounds 4566188, OS waits 73333

RW-shared spins 4335920, rounds 14935679, OS waits 171554

RW-excl spins 209573, rounds 11472909, OS waits 84315

Spin rounds per wait: 2.02 mutex, 3.44 RW-shared, 54.74 RW-excl

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

LATEST DETECTED DEADLOCK

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

2016-11-16 14:54:08 7f5fdcd77700

*** (1) TRANSACTION:

TRANSACTION 200992143, ACTIVE 2 sec fetching rows

mysql tables in use 4, locked 4

LOCK WAIT 1760 lock struct(s), heap size 210472, 339660 row lock(s), undo log entries 1

MySQL thread id 591553, OS thread handle 0x7f5fdcdb8700, query id 36452144 192.168.10.42 VIPUSER Sending data

UPDATE pre_order

LEFT JOIN `order` ON `order`.pre_order_id = pre_order.id

LEFT JOIN loan_demand ON loan_demand.id = pre_order.demand_id

LEFT JOIN store_customer ON store_customer.mobile = pre_order.lender_phone

SET pre_order.saleman_id = 224,

`order`.saleman_id = 224,

loan_demand.saleman_charge = 287,

store_customer.saleman_id=224,

store_customer.update_time = 1479279001

WHERE

`store_customer`.mobile = 18662175906 and pre_order.lender_phone=18662175906 and pre_order.status in( 4)

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1558 page no 3636 n bits 248 index `PRIMARY` of table `vip`.`store_customer` trx id 200992143 lock_mode X waiting

Record lock, heap no 141 PHYSICAL RECORD: n_fields 18; compact format; info bits 0

0: len 4; hex 00053499; asc4 ;;

1: len 6; hex 00000bfae59a; asc ;;

2: len 7; hex a80000015c012a; asc \ *;;

3: len 4; hex 0000000b; asc ;;

4: len 4; hex 0000013a; asc :;;

5: len 4; hex 00062e65; asc .e;;

6: len 11; hex 3135323334313337313233; asc 15234137123;;

7: len 1; hex 81; asc ;;

8: len 1; hex 02; asc ;;

9: len 4; hex 582c028e; asc X, ;;

10: len 4; hex 00000000; asc ;;

11: len 1; hex 02; asc ;;

12: len 1; hex 00; asc ;;

13: len 4; hex 00000000; asc ;;

14: len 4; hex 00000000; asc ;;

15: len 1; hex 00; asc ;;

16: len 1; hex 00; asc ;;

17: len 1; hex 02; asc ;;

*** (2) TRANSACTION:

TRANSACTION 200992154, ACTIVE 2 sec starting index read

mysql tables in use 1, locked 1

13 lock struct(s), heap size 2936, 92 row lock(s), undo log entries 232

MySQL thread id 592115, OS thread handle 0x7f5fdcd77700, query id 36453180 192.168.10.42 VIPUSER updating

UPDATE `store_customer` SET `mobile`='13903404842',`store_id`=11,`saleman_id`=314,`pre_order_id`=405131,`is_repeat`=1,`update_time`=1479279248 WHERE ( `id` = 303168 )

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 1558 page no 3636 n bits 248 index `PRIMARY` of table `vip`.`store_customer` trx id 200992154 lock_mode X locks rec but not gap

Record lock, heap no 141 PHYSICAL RECORD: n_fields 18; compact format; info bits 0

0: len 4; hex 00053499; asc4 ;;

1: len 6; hex 00000bfae59a; asc ;;

2: len 7; hex a80000015c012a; asc \ *;;

3: len 4; hex 0000000b; asc ;;

4: len 4; hex 0000013a; asc :;;

5: len 4; hex 00062e65; asc .e;;

6: len 11; hex 3135323334313337313233; asc 15234137123;;

7: len 1; hex 81; asc ;;

8: len 1; hex 02; asc ;;

9: len 4; hex 582c028e; asc X, ;;

10: len 4; hex 00000000; asc ;;

11: len 1; hex 02; asc ;;

12: len 1; hex 00; asc ;;

13: len 4; hex 00000000; asc ;;

14: len 4; hex 00000000; asc ;;

15: len 1; hex 00; asc ;;

16: len 1; hex 00; asc ;;

17: len 1; hex 02; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 1558 page no 3206 n bits 288 index `PRIMARY` of table `vip`.`store_customer` trx id 200992154 lock_mode X locks rec but not gap waiting

Record lock, heap no 33 PHYSICAL RECORD: n_fields 18; compact format; info bits 0

0: len 4; hex 0004a040; asc@;;

1: len 6; hex 00000a77948a; ascw ;;

2: len 7; hex 48000001611c78; asc Ha x;;

3: len 4; hex 0000000b; asc ;;

4: len 4; hex 000000ad; asc ;;

5: len 4; hex 0005888d; asc ;;

6: len 11; hex 3133393033343034383432; asc 13903404842;;

7: len 1; hex 83; asc ;;

8: len 1; hex 01; asc ;;

9: len 4; hex 581bf514; asc X ;;

10: len 4; hex 581bf621; asc X !;;

11: len 1; hex 02; asc ;;

12: len 1; hex 00; asc ;;

13: len 4; hex 00000000; asc ;;

14: len 4; hex 00000000; asc ;;

15: len 1; hex 00; asc ;;

16: len 1; hex 00; asc ;;

17: len 1; hex 01; asc ;;

*** WE ROLL BACK TRANSACTION (2)

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

TRANSACTIONS

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

Trx id counter 202747662

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

History list length 2516---还有unpurge 2516

LIST OF TRANSACTIONS FOR EACH SESSION:

---TRANSACTION 0, not started

MySQL thread id 626494, OS thread handle 0x7f5fd92a8700, query id 38472637 127.0.0.1 root init

show engine innodb status

--------

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)

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: 1

117165 OS file reads, 4860463 OS file writes, 3532584 OS fsyncs

0.00 reads/s, 0 avg bytes/read, 9.00 writes/s, 9.20 fsyncs/s

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

INSERT BUFFER AND ADAPTIVE HASH INDEX

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

Ibuf: size 1, free list len 75, seg size 77, 839 merges ---insert buffer合并插入

merged operations:

insert 373, delete mark 904, delete 130

discarded operations:

insert 0, delete mark 0, delete 0

Hash table size 4425293, node heap has 7459 buffer(s)

37262.35 hash searches/s, 269.75 non-hash searches/s

---

LOG

---

Log sequence number 9856475404

Log flushed up to9856475404

Pages flushed up to 9856471068

Last checkpoint at 9856470666

0 pending log writes, 0 pending chkp writes

1186853 log i/o's done, 2.40 log i/o

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10989076480; in additional pool allocated 0
Dictionary memory allocated 932159
Buffer pool size 655352-----innodb buffer size大小 655353页*16k
Free buffers8505 ------free 列表中页的数量
Database pages 624627-----lRu列表中页的数量
Old database pages 230411------lru列表中old list（非热快数据：5/8-列表最后）的页的数量
Modified db pages 26------脏页，存在于flush list中也存在lru list中，各司其职
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8034747, not young 844963
23.47 youngs/s, 0.00 non-youngs/s
Pages read 78033, created 1790917, written 492746631
0.00 reads/s, 0.47 creates/s, 42.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000------内存命中率这个值应该大于95%，不然sql需要优化啊
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 624627, unzip_LRU len: 0
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size16384
Free buffers1025
Database pages 14428
Old database pages 5305
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 1, single page 0
Pages made young 111884, not young 729335
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14143, created 18647, written 441843
0.00 reads/s, 0.00 creates/s, 0.40 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: 14428, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size16384
Free buffers1026
Database pages 14430
Old database pages 5306
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 106938, not young 556784
0.00 youngs/s, 0.00 non-youngs/s
Pages read 13932, created 18419, written 259908
0.00 reads/s, 0.00 creates/s, 0.20 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: 14430, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size16384
Free buffers1025
Database pages 14414
Old database pages 5300
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 111161, not young 408726
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14114, created 18430, written 406694
0.00 reads/s, 0.00 creates/s, 0.60 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: 14414, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size16384
Free buffers1025
Database pages 14420
Old database pages 5303
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 113167, not young 708499
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14242, created 18731, written 409053
0.00 reads/s, 0.00 creates/s, 0.40 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: 14420, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size16384
Free buffers1027
Database pages 14440
Old database pages 5310
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 110331, not young 461397
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14224, created 18645, written 278405
0.00 reads/s, 0.00 creates/s, 0.20 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: 14440, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size16384
Free buffers1024
Database pages 14425
Old database pages 5304
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 108069, not young 370734
0.00 youngs/s, 0.00 non-youngs/s
Pages read 13755, created 18494, written 233833
0.00 reads/s, 0.00 creates/s, 0.40 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: 14425, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size16384
Free buffers1027
Database pages 14426
Old database pages 5305
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 106842, not young 789185
0.00 youngs/s, 0.00 non-youngs/s
Pages read 13862, created 18461, written 351585
0.00 reads/s, 0.00 creates/s, 0.60 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: 14426, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size16384
Free buffers1025
Database pages 14426
Old database pages 5305
Modified db pages 5
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 109852, not young 628401
0.00 youngs/s, 0.00 non-youngs/s
Pages read 14021, created 18798, written 336451
0.00 reads/s, 0.00 creates/s, 0.60 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: 14426, unzip_LRU len: 0
I/O sum[247]:cur[2], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 13133, id 140049721313024, state: sleeping
Number of rows inserted 2464227, updated 569725, deleted 1790, read 56368048059
1.40 inserts/s, 1.80 updates/s, 0.00 deletes/s, 206769.85 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================1 row in set (0.00 sec)ERROR:
No query specified

点击(此处)折叠或打开

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 10989076480; in additional pool allocated 0
Dictionary memory allocated 932159
Buffer pool size 655352-----innodb buffer size大小 655353页*16k
Free buffers8505 ------free 列表中页的数量
Database pages 624627-----lru列表中页的数量
Old database pages 230411------lru列表中old list（非热快数据：5/8-列表最后）的页的数量
Modified db pages 26------脏页，存在于flush list中也存在lru list中，各司其职
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8034747, not young 844963
23.47 youngs/s, 0.00 non-youngs/s
Pages read 78033, created 1790917, written 492746631
0.00 reads/s, 0.47 creates/s, 42.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000------内存命中率这个值应该大于95%，不然sql需要优化啊
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 624627, unzip_LRU len: 0

这部分显示了缓冲池和内存的利用率相关信息。可以看到Innodb分配的所有内存(有些时候可能比你设置的还要多点)，以及额外的内存池分配情况 (可以检查它的大小是否正好)，缓冲池总共有多少个内存页，有多少空闲内存页，数据库分配了多少个内存页以及有多少个脏内存页。从这些信息中，就可以判断 内存缓冲池是否设定合理，如果总是有大量空闲内存页，则不需要设置那么多内存，可以适当减小一点。如果空闲内存页为 0，这种情况下数据库内存页就不一定会和缓冲池的总数一致，因为缓冲池还需要保存锁信息，自适应哈希索引以及其他系统结构等信息。

等待中的读写是指内存缓冲池级别的请求。Innodb可能会把多个文件级别的请求合并到一个上，因此各不相同。我们还可以看到Innodb提交的各 种不同类型的IO，LRU内存页中需要刷新的页 - 脏内存页，它们不会被长时间存取；刷新列表 -
检查点进程处理完之后需要刷新的旧内存页；独立内存页 - 独立的写内存页。

我们还可以看到内存页总共读写了多少次。已经创建的内存页是当前一个内存页中的内容没有读取到内存缓冲池中时，专门为新数据创建的空内存页。

最后我们可以看到缓冲池的命中率，它预示着缓冲池的效率。1000/1000 相当于 100% 的命中率。不过这样也很难说明缓冲池的命中率就足够高了，这要需要根据不同的负载环境而定。通常情况下，950/1000 就够了，有些时候在IO负载较高的环境下，命中率可能为 995/1000。