ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 【Mysql】show engine innodb status详解

【Mysql】show engine innodb status详解

原创 MySQL 作者：小亮520cl 时间：2016-03-04 14:25:02 0 删除 编辑


  1. mysql > show engine innodb status\G ;
  2. * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 . row * * * * * * * * * * * * * * * * * * * * * * * * * * *
  3.    Type : InnoDB
  4.    Name :
  5. Status :
  6. = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  7. 2016 -11 -17 10 :20 :57 7f5fd92a8700 INNODB MONITOR OUTPUT
  8. = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
  9. Per second averages calculated from the last 5 seconds
  10. - - - - - - - - - - - - - - - - -
  11. BACKGROUND THREAD
  12. - - - - - - - - - - - - - - - - -
  13. srv_master_thread loops : 263748 srv_active , 0 srv_shutdown , 959366 srv_idle

  14. srv_master_thread log flush and writes : 1223114

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

  16. 主循环(loop)主要以每一秒和每十秒的频率执行刷新日志缓存，合并插入缓存，刷新脏页缓存，删除无用 undo 页等操作
  17. 如果当前没有用户活动，则进入后台循环流程(backgroud loop)，主要执行删除无用的 undo 页，合并插入缓存
  18. 如果没有什么事情可以做了，便进入了暂停循环(suspend loop)，等待事件循环唤起
  19. -----------------
BACKGROUND THREAD
-----------------
# srv_active 为每秒的循环次数，srv_idle 为每 10 秒的的循环次数，srv_shutdown 为停止的循环，通常为 0 
# 如果每秒循环次数少，每 10 秒次数多，证明当前负载很低；如果每秒循环次数多，每 10 秒次数少，远大于10：1，证明当前负载很高
srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle
# 日志缓冲刷盘次数
srv_master_thread log flush and writes: 2819194
  20. - - - - - - - - - -
  21. SEMAPHORES
  22. - - - - - - - - - -
  23. OS WAIT ARRAY INFO : reservation count 335693
  24. OS WAIT ARRAY INFO : signal count 7995932
  25. Mutex spin waits 2260302 , rounds 4566188 , OS waits 73333
  26. RW -shared spins 4335920 , rounds 14935679 , OS waits 171554
  27. RW -excl spins 209573 , rounds 11472909 , OS waits 84315
  28. Spin rounds per wait : 2 .02 mutex , 3 .44 RW -shared , 54 .74 RW -excl
  29. - - - - - - - - - - - - - - - - - - - - - - - -
  30. LATEST DETECTED DEADLOCK
  31. - - - - - - - - - - - - - - - - - - - - - - - -
  32. 2016 -11 -16 14 :54 :08 7f5fdcd77700
  33. * * * (1 ) TRANSACTION :
  34. TRANSACTION 200992143 , ACTIVE 2 sec fetching rows
  35. mysql tables in use 4 , locked 4
  36. LOCK WAIT 1760 lock struct (s ) , heap size 210472 , 339660 row lock (s ) , undo log entries 1
  37. MySQL thread id 591553 , OS thread handle 0x7f5fdcdb8700 , query id 36452144 192 .168 .10 .42 VIPUSER Sending data
  38. UPDATE pre_order
  39.                 LEFT JOIN `order` ON `order` .pre_order_id = pre_order .id
  40.                 LEFT JOIN loan_demand ON loan_demand .id = pre_order .demand_id
  41.                 LEFT JOIN store_customer  ON store_customer .mobile = pre_order .lender_phone

  43.                 SET pre_order .saleman_id = 224 ,
  44.                  `order` .saleman_id = 224 ,
  45.                  loan_demand .saleman_charge = 287 ,
  46.                 store_customer .saleman_id =224 ,
  47.                 store_customer .update_time = 1479279001
  48.                 WHERE
  49.                     `store_customer` .mobile = 18662175906 and pre_order .lender_phone =18662175906 and pre_order .status in ( 4 )
  50. * * * (1 ) WAITING FOR THIS LOCK TO BE GRANTED :
  51. 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
  52. Record lock , heap no 141 PHYSICAL RECORD : n_fields 18 ; compact format ; info bits 0
  53.  0 : len 4 ; hex 00053499 ; asc   4 ; ;
  54.  1 : len 6 ; hex 00000bfae59a ; asc ; ;
  55.  2 : len 7 ; hex a80000015c012a ; asc     \ * ; ;
  56.  3 : len 4 ; hex 0000000b ; asc ; ;
  57.  4 : len 4 ; hex 0000013a ; asc : ; ;
  58.  5 : len 4 ; hex 00062e65 ; asc .e ; ;
  59.  6 : len 11 ; hex 3135323334313337313233 ; asc 15234137123 ; ;
  60.  7 : len 1 ; hex 81 ; asc ; ;
  61.  8 : len 1 ; hex 02 ; asc ; ;
  62.  9 : len 4 ; hex 582c028e ; asc X , ; ;
  63.  10 : len 4 ; hex 00000000 ; asc ; ;
  64.  11 : len 1 ; hex 02 ; asc ; ;
  65.  12 : len 1 ; hex 00 ; asc ; ;
  66.  13 : len 4 ; hex 00000000 ; asc ; ;
  67.  14 : len 4 ; hex 00000000 ; asc ; ;
  68.  15 : len 1 ; hex 00 ; asc ; ;
  69.  16 : len 1 ; hex 00 ; asc ; ;
  70.  17 : len 1 ; hex 02 ; asc ; ;

  72. * * * (2 ) TRANSACTION :
  73. TRANSACTION 200992154 , ACTIVE 2 sec starting index read
  74. mysql tables in use 1 , locked 1
  75. 13 lock struct (s ) , heap size 2936 , 92 row lock (s ) , undo log entries 232
  76. MySQL thread id 592115 , OS thread handle 0x7f5fdcd77700 , query id 36453180 192 .168 .10 .42 VIPUSER updating
  77. 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 )
  78. * * * (2 ) HOLDS THE LOCK (S ) :
  79. 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
  80. Record lock , heap no 141 PHYSICAL RECORD : n_fields 18 ; compact format ; info bits 0
  81.  0 : len 4 ; hex 00053499 ; asc   4 ; ;
  82.  1 : len 6 ; hex 00000bfae59a ; asc ; ;
  83.  2 : len 7 ; hex a80000015c012a ; asc     \ * ; ;
  84.  3 : len 4 ; hex 0000000b ; asc ; ;
  85.  4 : len 4 ; hex 0000013a ; asc : ; ;
  86.  5 : len 4 ; hex 00062e65 ; asc .e ; ;
  87.  6 : len 11 ; hex 3135323334313337313233 ; asc 15234137123 ; ;
  88.  7 : len 1 ; hex 81 ; asc ; ;
  89.  8 : len 1 ; hex 02 ; asc ; ;
  90.  9 : len 4 ; hex 582c028e ; asc X , ; ;
  91.  10 : len 4 ; hex 00000000 ; asc ; ;
  92.  11 : len 1 ; hex 02 ; asc ; ;
  93.  12 : len 1 ; hex 00 ; asc ; ;
  94.  13 : len 4 ; hex 00000000 ; asc ; ;
  95.  14 : len 4 ; hex 00000000 ; asc ; ;
  96.  15 : len 1 ; hex 00 ; asc ; ;
  97.  16 : len 1 ; hex 00 ; asc ; ;
  98.  17 : len 1 ; hex 02 ; asc ; ;

  100. * * * (2 ) WAITING FOR THIS LOCK TO BE GRANTED :
  101. 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
  102. Record lock , heap no 33 PHYSICAL RECORD : n_fields 18 ; compact format ; info bits 0
  103.  0 : len 4 ; hex 0004a040 ; asc    @ ; ;
  104.  1 : len 6 ; hex 00000a77948a ; asc    w ; ;
  105.  2 : len 7 ; hex 48000001611c78 ; asc H   a x ; ;
  106.  3 : len 4 ; hex 0000000b ; asc ; ;
  107.  4 : len 4 ; hex 000000ad ; asc ; ;
  108.  5 : len 4 ; hex 0005888d ; asc ; ;
  109.  6 : len 11 ; hex 3133393033343034383432 ; asc 13903404842 ; ;
  110.  7 : len 1 ; hex 83 ; asc ; ;
  111.  8 : len 1 ; hex 01 ; asc ; ;
  112.  9 : len 4 ; hex 581bf514 ; asc X ; ;
  113.  10 : len 4 ; hex 581bf621 ; asc X ! ; ;
  114.  11 : len 1 ; hex 02 ; asc ; ;
  115.  12 : len 1 ; hex 00 ; asc ; ;
  116.  13 : len 4 ; hex 00000000 ; asc ; ;
  117.  14 : len 4 ; hex 00000000 ; asc ; ;
  118.  15 : len 1 ; hex 00 ; asc ; ;
  119.  16 : len 1 ; hex 00 ; asc ; ;
  120.  17 : len 1 ; hex 01 ; asc ; ;

  122. * * * WE ROLL BACK TRANSACTION (2 )
  123. - - - - - - - - - - - -
  124. TRANSACTIONS
  125. - - - - - - - - - - - -
  126. Trx id counter 202747662
  127. Purge done for trx 's n:o < 202747617 undo n:o < 0 state: running but idle
  128. History list length 2516            ---还有unpurge 2516
  129. LIST OF TRANSACTIONS FOR EACH SESSION:
  130. ---TRANSACTION 0, not started
  131. MySQL thread id 626494, OS thread handle 0x7f5fd92a8700, query id 38472637 127.0.0.1 root init
  132. show engine innodb status
  133. --------
  134. FILE I/O
  135. --------
  136. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  137. I/O thread 1 state: waiting for completed aio requests (log thread)
  138. I/O thread 2 state: waiting for completed aio requests (read thread)
  139. I/O thread 3 state: waiting for completed aio requests (read thread)
  140. I/O thread 4 state: waiting for completed aio requests (read thread)
  141. I/O thread 5 state: waiting for completed aio requests (read thread)
  142. I/O thread 6 state: waiting for completed aio requests (write thread)
  143. I/O thread 7 state: waiting for completed aio requests (write thread)
  144. I/O thread 8 state: waiting for completed aio requests (write thread)
  145. I/O thread 9 state: waiting for completed aio requests (write thread)
  146. Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
  147.  ibuf aio reads: 0, log i/o's : 0 , sync i/o 's: 0
  148. Pending flushes (fsync) log: 0; buffer pool: 1
  149. 117165 OS file reads, 4860463 OS file writes, 3532584 OS fsyncs
  150. 0.00 reads/s, 0 avg bytes/read, 9.00 writes/s, 9.20 fsyncs/s
  151. -------------------------------------
  152. INSERT BUFFER AND ADAPTIVE HASH INDEX
  153. -------------------------------------
  154. Ibuf: size 1, free list len 75, seg size 77, 839 merges     ---insert buffer合并插入
  155. merged operations:
  156.  insert 373, delete mark 904, delete 130
  157. discarded operations:
  158.  insert 0, delete mark 0, delete 0
  159. Hash table size 4425293, node heap has 7459 buffer(s)
  160. 37262.35 hash searches/s, 269.75 non-hash searches/s
  161. ---
  162. LOG
  163. ---
  164. Log sequence number 9856475404
  165. Log flushed up to   9856475404
  166. Pages flushed up to 9856471068
  167. Last checkpoint at  9856470666
  168. 0 pending log writes, 0 pending chkp writes
  169. 1186853 log i/o's done , 2 .40 log i/o
  170. ----------------------  
    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 buffers       8505                  ------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 size   16384
    Free buffers       1025
    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 size   16384
    Free buffers       1026
    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 size   16384
    Free buffers       1025
    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 size   16384
    Free buffers       1025
    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 size   16384
    Free buffers       1027
    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 size   16384
    Free buffers       1024
    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 size   16384
    Free buffers       1027
    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 size   16384
    Free buffers       1025
    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


点击( 此处)折叠或打开

  1. ----------------------  
    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 buffers       8505                  ------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。



来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29096438/viewspace-2038366/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 【Mysql】从binlog中找出单个表的binlog信息
下一篇： 【Mysql】mysql-proxy读写分离
请登录后发表评论 登录
全部评论
小亮520cl
专家姓名：陈亮
毕业以后专业任职数据库工程师职位，itpub一直作为自己的笔记记录的地方,blog写的不详细，草书笔记，仅供参考！

注册时间：2013-09-12

  • 博文量
    529
  • 访问量
    1126966

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593