溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

Mysql中show engine innodb status怎么用

發(fā)布時間：2021-11-08 09:26:33 來源：億速云 閱讀：183 作者：小新 欄目：MySQL數(shù)據(jù)庫

這篇文章主要介紹了Mysql中show engine innodb status怎么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

  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 后臺線程中，該狀態(tài)顯示了后臺線程狀態(tài)信息，Master Thread 的主要工作：

  16. 主循環(huán)(loop)主要以每一秒和每十秒的頻率執(zhí)行刷新日志緩存，合并插入緩存，刷新臟頁緩存，刪除無用 undo 頁等操作

  17. 如果當(dāng)前沒有用戶活動，則進(jìn)入后臺循環(huán)流程(backgroud loop)，主要執(zhí)行刪除無用的 undo 頁，合并插入緩存

  18. 如果沒有什么事情可以做了，便進(jìn)入了暫停循環(huán)(suspend loop)，等待事件循環(huán)喚起

  19. -----------------
BACKGROUND THREAD
-----------------
# srv_active 為每秒的循環(huán)次數(shù)，srv_idle 為每 10 秒的的循環(huán)次數(shù)，srv_shutdown 為停止的循環(huán)，通常為 0 
# 如果每秒循環(huán)次數(shù)少，每 10 秒次數(shù)多，證明當(dāng)前負(fù)載很低；如果每秒循環(huán)次數(shù)多，每 10 秒次數(shù)少，遠(yuǎn)大于10：1，證明當(dāng)前負(fù)載很高
srv_master_thread loops: 2818842 srv_active, 0 srv_shutdown, 411 srv_idle
# 日志緩沖刷盤次數(shù)
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 列表中頁的數(shù)量
    Database pages     624627                -----lRu列表中頁的數(shù)量
    Old database pages 230411                ------lru列表中old list（非熱快數(shù)據(jù)：5/8-列表最后）的頁的數(shù)量
    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                   ------內(nèi)存命中率這個值應(yīng)該大于95%，不然sql需要優(yōu)化啊
    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

點(diǎn)擊(此處)折疊或打開

  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 列表中頁的數(shù)量
    Database pages     624627                -----lru列表中頁的數(shù)量
    Old database pages 230411                ------lru列表中old list（非熱快數(shù)據(jù)：5/8-列表最后）的頁的數(shù)量
    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                   ------內(nèi)存命中率這個值應(yīng)該大于95%，不然sql需要優(yōu)化啊
    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

這部分顯示了緩沖池和內(nèi)存的利用率相關(guān)信息?？梢钥吹絀nnodb分配的所有內(nèi)存(有些時候可能比你設(shè)置的還要多點(diǎn))，以及額外的內(nèi)存池分配情況 (可以檢查它的大小是否正好)，緩沖池總共有多少個內(nèi)存頁，有多少空閑內(nèi)存頁，數(shù)據(jù)庫分配了多少個內(nèi)存頁以及有多少個臟內(nèi)存頁。從這些信息中，就可以判斷 內(nèi)存緩沖池是否設(shè)定合理，如果總是有大量空閑內(nèi)存頁，則不需要設(shè)置那么多內(nèi)存，可以適當(dāng)減小一點(diǎn)。如果空閑內(nèi)存頁為 0，這種情況下數(shù)據(jù)庫內(nèi)存頁就不一定會和緩沖池的總數(shù)一致，因?yàn)榫彌_池還需要保存鎖信息，自適應(yīng)哈希索引以及其他系統(tǒng)結(jié)構(gòu)等信息。

等待中的讀寫是指內(nèi)存緩沖池級別的請求。Innodb可能會把多個文件級別的請求合并到一個上，因此各不相同。我們還可以看到Innodb提交的各 種不同類型的IO，LRU內(nèi)存頁中需要刷新的頁 - 臟內(nèi)存頁，它們不會被長時間存?。凰⑿铝斜?-
檢查點(diǎn)進(jìn)程處理完之后需要刷新的舊內(nèi)存頁；獨(dú)立內(nèi)存頁 - 獨(dú)立的寫內(nèi)存頁。

我們還可以看到內(nèi)存頁總共讀寫了多少次。已經(jīng)創(chuàng)建的內(nèi)存頁是當(dāng)前一個內(nèi)存頁中的內(nèi)容沒有讀取到內(nèi)存緩沖池中時，專門為新數(shù)據(jù)創(chuàng)建的空內(nèi)存頁。

最后我們可以看到緩沖池的命中率，它預(yù)示著緩沖池的效率。1000/1000 相當(dāng)于 100% 的命中率。不過這樣也很難說明緩沖池的命中率就足夠高了，這要需要根據(jù)不同的負(fù)載環(huán)境而定。通常情況下，950/1000 就夠了，有些時候在IO負(fù)載較高的環(huán)境下，命中率可能為 995/1000。

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“Mysql中show engine innodb status怎么用”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手