• InnoDB存储引擎状态详解

    目录

    一、状态查询

    1. 查询InnoDB版本

    2. 查询InnoDB状态

    二、状态详解

    1. BACKGROUND THREAD（后台线程）

    2. SEMAPHORES（信号量）

    3. LATEST DETECTED DEADLOCK（检测到的死锁）

    4. TRANSACTIONS（事务）

    5. FILE I/O（文件IO）

    6. INSERT BUFFER AND ADAPTIVE HASH INDEX（插入缓冲和自适应哈希索引）

    7. LOG（日志）

    8. BUFFER POOL AND MEMORY（缓冲池与内存）

    9. ROW OPERATIONS（行操作）

    三、参考资料

    一、状态查询

    1. 查询InnoDB版本

    show variables like 'innodb_version'\G;

    或

    select * from information_schema.plugins;

    1. mysql> show variables like 'innodb_version'\G;
    2. *************************** 1. row ***************************
    3. Variable_name: innodb_version
    4.         Value: 5.7.31-34
    5. 1 row in set (0.01 sec)

    2. 查询InnoDB状态

    show engine innodb status\G;

            从以下查询看出，是最近47s计算的每秒平均数，即：每次查询时，参数动态变化。

    1. mysql> show engine innodb status\G;
    2. *************************** 1. row ***************************
    3.   Type: InnoDB
    4.   Name: 
    5. Status: 
    6. =====================================
    7. 2022-06-27 09:33:40 0x7fa1c8a5f700 INNODB MONITOR OUTPUT
    8. =====================================
    9. Per second averages calculated from the last 47 seconds
    10. -----------------
    11. BACKGROUND THREAD
    12. -----------------
    13. srv_master_thread loops: 13285067 srv_active, 0 srv_shutdown, 10606666 srv_idle
    14. srv_master_thread log flush and writes: 23891733
    15. ----------
    16. SEMAPHORES
    17. ----------
    18. OS WAIT ARRAY INFO: reservation count 57836764
    19. OS WAIT ARRAY INFO: signal count 269276481
    20. RW-shared spins 0, rounds 171890114, OS waits 34656067
    21. RW-excl spins 0, rounds 1245774156, OS waits 18379769
    22. RW-sx spins 80621, rounds 1707842, OS waits 34552
    23. Spin rounds per wait: 171890114.00 RW-shared, 1245774156.00 RW-excl, 21.18 RW-sx
    24. ------------------------
    25. LATEST DETECTED DEADLOCK
    26. ------------------------
    27. 2022-07-22 15:27:14 0x7fccb9113700
    28. *** (1) TRANSACTION:
    29. TRANSACTION 252274025, ACTIVE 33 sec inserting
    30. mysql tables in use 1, locked 1
    31. LOCK WAIT 13 lock struct(s), heap size 1136, 4 row lock(s)
    32. MySQL thread id 3556845, OS thread handle 140505809090304, query id 1181834385 192.168.134.194 zhengkun update
    33. insert into activity_team_figure_history (create_by, create_date, del_falg, update_by, update_date, figure_id, figure_id_fk, season_id, season_id_fk, team_id, team_id_fk, id) values ('OperationPlatform', null, 0, 'OperationPlatform', null, '1530713201', '150000307132', '1100546901', '110000005469', '1604693901', '160000046939', '46044169')
    34. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    35. RECORD LOCKS space id 61 page no 2017 n bits 72 index PRIMARY of table `nomswc_db`.`activity_team_figure_history` trx id 252274025 lock mode S locks rec but not gap waiting
    36. Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
    37.  0: len 8; hex 3436303434313639; asc 46044169;;
    38.  1: len 6; hex 00000f096653; asc     fS;;
    39.  2: len 7; hex de00003c3a0110; asc    <:  ;;
    40.  3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    41.  4: len 4; hex 62da5149; asc b QI;;
    42.  5: len 4; hex 80000000; asc     ;;
    43.  6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    44.  7: len 4; hex 62da5149; asc b QI;;
    45.  8: len 10; hex 31353330373133323031; asc 1530713201;;
    46.  9: len 12; hex 313530303030333037313332; asc 150000307132;;
    47.  10: len 10; hex 31313030353436393031; asc 1100546901;;
    48.  11: len 12; hex 313130303030303035343639; asc 110000005469;;
    49.  12: len 10; hex 31363034363933393031; asc 1604693901;;
    50.  13: len 12; hex 313630303030303436393339; asc 160000046939;;
    51.  
    52. *** (2) TRANSACTION:
    53. TRANSACTION 252274259, ACTIVE 33 sec inserting
    54. mysql tables in use 1, locked 1
    55. 14 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2458
    56. MySQL thread id 3556846, OS thread handle 140517254969088, query id 1181837074 192.168.134.194 zhengkun update
    57. insert into activity_team_figure_history (create_by, create_date, del_falg, update_by, update_date, figure_id, figure_id_fk, season_id, season_id_fk, team_id, team_id_fk, id) values ('OperationPlatform', null, 0, 'OperationPlatform', null, '1531706401', '150000317064', '1100546901', '110000005469', '1604748601', '160000047486', '46015169')
    58. *** (2) HOLDS THE LOCK(S):
    59. RECORD LOCKS space id 61 page no 2017 n bits 72 index PRIMARY of table `nomswc_db`.`activity_team_figure_history` trx id 252274259 lock_mode X locks rec but not gap
    60. Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
    61.  0: len 8; hex 3436303434313639; asc 46044169;;
    62.  1: len 6; hex 00000f096653; asc     fS;;
    63.  2: len 7; hex de00003c3a0110; asc    <:  ;;
    64.  3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    65.  4: len 4; hex 62da5149; asc b QI;;
    66.  5: len 4; hex 80000000; asc     ;;
    67.  6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    68.  7: len 4; hex 62da5149; asc b QI;;
    69.  8: len 10; hex 31353330373133323031; asc 1530713201;;
    70.  9: len 12; hex 313530303030333037313332; asc 150000307132;;
    71.  10: len 10; hex 31313030353436393031; asc 1100546901;;
    72.  11: len 12; hex 313130303030303035343639; asc 110000005469;;
    73.  12: len 10; hex 31363034363933393031; asc 1604693901;;
    74.  13: len 12; hex 313630303030303436393339; asc 160000046939;;
    75.  
    76. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    77. RECORD LOCKS space id 61 page no 2001 n bits 144 index PRIMARY of table `nomswc_db`.`activity_team_figure_history` trx id 252274259 lock_mode X insert intention waiting
    78. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    79.  0: len 8; hex 73757072656d756d; asc supremum;;
    80.  
    81. *** WE ROLL BACK TRANSACTION (1)
    82. ------------
    83. TRANSACTIONS
    84. ------------
    85. Trx id counter 54476451
    86. Purge done for trx's n:o < 54476451 undo n:o < 0 state: running but idle     
    87. History list length 80
    88. LIST OF TRANSACTIONS FOR EACH SESSION:
    89. ---TRANSACTION 421810410397688, not started
    90. 0 lock struct(s), heap size 1136, 0 row lock(s)
    91. ---TRANSACTION 421810410394304, not started
    92. 0 lock struct(s), heap size 1136, 0 row lock(s)
    93. ---TRANSACTION 421810410386408, not started
    94. 0 lock struct(s), heap size 1136, 0 row lock(s)
    95. ---TRANSACTION 421810410371744, not started
    96. 0 lock struct(s), heap size 1136, 0 row lock(s)
    97. ---TRANSACTION 421810410438296, not started
    98. 0 lock struct(s), heap size 1136, 0 row lock(s)
    99. --------
    100. FILE I/O
    101. --------
    102. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    103. I/O thread 1 state: waiting for completed aio requests (log thread)
    104. I/O thread 2 state: waiting for completed aio requests (read thread)
    105. I/O thread 3 state: waiting for completed aio requests (read thread)
    106. I/O thread 4 state: waiting for completed aio requests (read thread)
    107. I/O thread 5 state: waiting for completed aio requests (read thread)
    108. I/O thread 6 state: waiting for completed aio requests (write thread)
    109. I/O thread 7 state: waiting for completed aio requests (write thread)
    110. I/O thread 8 state: waiting for completed aio requests (write thread)
    111. I/O thread 9 state: waiting for completed aio requests (write thread)
    112. Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
    113.  ibuf aio reads:, log i/o's:, sync i/o's:
    114. Pending flushes (fsync) log: 0; buffer pool: 0
    115. 1072970879 OS file reads, 234098005 OS file writes, 67107797 OS fsyncs
    116. 13.06 reads/s, 16384 avg bytes/read, 6.57 writes/s, 3.28 fsyncs/s
    117. -------------------------------------
    118. INSERT BUFFER AND ADAPTIVE HASH INDEX
    119. -------------------------------------
    120. Ibuf: size 1, free list len 1686, seg size 1688, 305733 merges
    121. merged operations:
    122.  insert 2457103, delete mark 1535765, delete 799296
    123. discarded operations:
    124.  insert 0, delete mark 0, delete 0
    125. Hash table size 34673, node heap has 123 buffer(s)
    126. Hash table size 34673, node heap has 25 buffer(s)
    127. Hash table size 34673, node heap has 37 buffer(s)
    128. Hash table size 34673, node heap has 8 buffer(s)
    129. Hash table size 34673, node heap has 10 buffer(s)
    130. Hash table size 34673, node heap has 4 buffer(s)
    131. Hash table size 34673, node heap has 7 buffer(s)
    132. Hash table size 34673, node heap has 3 buffer(s)
    133. 209.66 hash searches/s, 87.55 non-hash searches/s
    134. ---
    135. LOG
    136. ---
    137. Log sequence number 29631227823
    138. Log flushed up to   29631227823
    139. Pages flushed up to 29631227489
    140. Last checkpoint at  29631227427
    141. Max checkpoint age    80826164
    142. Checkpoint age target 78300347
    143. Modified age          334
    144. Checkpoint age        396
    145. 0 pending log flushes, 0 pending chkp writes
    146. 42232278 log i/o's done, 2.25 log i/o's/second
    147. ----------------------
    148. BUFFER POOL AND MEMORY
    149. ----------------------
    150. Total large memory allocated 139722752
    151. Dictionary memory allocated 6567657
    152. Internal hash tables (constant factor + variable factor)
    153.     Adaptive hash index 5808064         (2219072 + 3588992)
    154.     Page hash           139112 (buffer pool 0 only)
    155.     Dictionary cache    7122425         (554768 + 6567657)
    156.     File system         1786152         (812272 + 973880)
    157.     Lock system         421480  (332872 + 88608)
    158.     Recovery system     0       (0 + 0)
    159. Buffer pool size   8191
    160. Buffer pool size, bytes 134201344
    161. Free buffers       1024
    162. Database pages     6950
    163. Old database pages 2545
    164. Modified db pages  3
    165. Pending reads      0
    166. Pending writes: LRU 0, flush list 0, single page 0
    167. Pages made young 702725123, not young 31227621937
    168. 0.00 youngs/s, 0.00 non-youngs/s
    169. Pages read 1072974321, created 83035821, written 184067179
    170. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    171. Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 48 / 1000
    172. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    173. LRU len: 6950, unzip_LRU len: 0
    174. I/O sum[805]:cur[0], unzip sum[0]:cur[0]
    175. --------------
    176. ROW OPERATIONS
    177. --------------
    178. 0 queries inside InnoDB, 0 queries in queue
    179. 0 read views open inside InnoDB
    180. 0 RW transactions active inside InnoDB
    181. Process ID=981, Main thread ID=140335122142976, state: sleeping
    182. Number of rows inserted 2819363420, updated 30356193, deleted 3622065, read 156168189485
    183. 0.30 inserts/s, 1.79 updates/s, 0.34 deletes/s, 502.56 reads/s
    184. ----------------------------
    185. END OF INNODB MONITOR OUTPUT
    186. ============================
    188. 1 row in set (0.00 sec)

    二、状态详解

    1. BACKGROUND THREAD（后台线程）

    1. -----------------
    2. BACKGROUND THREAD
    3. -----------------
    4. srv_master_thread loops: 13285067 srv_active, 0 srv_shutdown, 10606666 srv_idle
    5. srv_master_thread log flush and writes: 23891733

            以上查询所示，srv_master_thread loops是Master Thread的循环次数，每次循环时会选择一种状态（active、shutdown、idle）执行，其中active数量增加与数据变化有关，与查询无关，可以通过srv_active和srv_idle的差异可以看出，通过对比active和idle的值，来获得系统整体负载情况，如果active的值越大，证明服务越繁忙。

            srv_master_thread log是Master Thread对重做日志（redo log）写入磁盘的次数。

    2. SEMAPHORES（信号量）

    1. ----------
    2. SEMAPHORES
    3. ----------
    4. OS WAIT ARRAY INFO: reservation count 57836764
    5. OS WAIT ARRAY INFO: signal count 269276481
    6. RW-shared spins 0, rounds 171890114, OS waits 34656067
    7. RW-excl spins 0, rounds 1245774156, OS waits 18379769
    8. RW-sx spins 80621, rounds 1707842, OS waits 34552
    9. Spin rounds per wait: 171890114.00 RW-shared, 1245774156.00 RW-excl, 21.18 RW-sx

            InnoDB有一个多阶段的等待策略。首先会对锁进行自旋（spin），如果经历了一个自旋周期后还没有持有锁，则进入到操作系统等待状态（os wait），等待被唤醒。如果在一秒中看到几十万个spin wait，则需要关注show engine innodb mutex。

    名称描述

    OS WAIT ARRAY INFO:

            reservation count

    		OS的等待阵列信息：预计计数（InnoDB分配槽的额度）

    OS WAIT ARRAY INFO:

           signal count

    		OS的等待阵列信息：信号计数（线程通过阵列得到信号频率）

    RW-shared spins 0,

    rounds 171890114,

    OS waits 34656067

    		RW的共享锁的计数，轮询次数，等待时间

    RW-excl spins 0,

    rounds 1245774156,

    OS waits 18379769

    		RW的排他锁的计数，轮询次数，等待时间

    RW-sx spins 80621,

    rounds 1707842,

    OS waits 34552

    		RW的sx（意向排他锁）的计数，轮询次数，等待时间

    Spin rounds per wait:   

       171890114.00 RW-shared,

       1245774156.00 RW-excl,

       21.18 RW-sx

    每个spin rounds per wait显示的是：

        每个操作系统等待mutex的spinlock round

    3. LATEST DETECTED DEADLOCK（检测到的死锁）

    1. ------------------------
    2. LATEST DETECTED DEADLOCK
    3. ------------------------
    4. 2022-07-22 15:27:14 0x7fccb9113700
    5. *** (1) TRANSACTION:
    6. TRANSACTION 252274025, ACTIVE 33 sec inserting
    7. mysql tables in use 1, locked 1
    8. LOCK WAIT 13 lock struct(s), heap size 1136, 4 row lock(s)
    9. MySQL thread id 3556845, OS thread handle 140505809090304, query id 1181834385 192.168.134.194 zhengkun update
    10. insert into activity_team_figure_history (create_by, create_date, del_falg, update_by, update_date, figure_id, figure_id_fk, season_id, season_id_fk, team_id, team_id_fk, id) values ('OperationPlatform', null, 0, 'OperationPlatform', null, '1530713201', '150000307132', '1100546901', '110000005469', '1604693901', '160000046939', '46044169')
    11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    12. RECORD LOCKS space id 61 page no 2017 n bits 72 index PRIMARY of table `nomswc_db`.`activity_team_figure_history` trx id 252274025 lock mode S locks rec but not gap waiting
    13. Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
    14.  0: len 8; hex 3436303434313639; asc 46044169;;
    15.  1: len 6; hex 00000f096653; asc     fS;;
    16.  2: len 7; hex de00003c3a0110; asc    <:  ;;
    17.  3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    18.  4: len 4; hex 62da5149; asc b QI;;
    19.  5: len 4; hex 80000000; asc     ;;
    20.  6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    21.  7: len 4; hex 62da5149; asc b QI;;
    22.  8: len 10; hex 31353330373133323031; asc 1530713201;;
    23.  9: len 12; hex 313530303030333037313332; asc 150000307132;;
    24.  10: len 10; hex 31313030353436393031; asc 1100546901;;
    25.  11: len 12; hex 313130303030303035343639; asc 110000005469;;
    26.  12: len 10; hex 31363034363933393031; asc 1604693901;;
    27.  13: len 12; hex 313630303030303436393339; asc 160000046939;;
    28.  
    29. *** (2) TRANSACTION:
    30. TRANSACTION 252274259, ACTIVE 33 sec inserting
    31. mysql tables in use 1, locked 1
    32. 14 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2458
    33. MySQL thread id 3556846, OS thread handle 140517254969088, query id 1181837074 192.168.134.194 zhengkun update
    34. insert into activity_team_figure_history (create_by, create_date, del_falg, update_by, update_date, figure_id, figure_id_fk, season_id, season_id_fk, team_id, team_id_fk, id) values ('OperationPlatform', null, 0, 'OperationPlatform', null, '1531706401', '150000317064', '1100546901', '110000005469', '1604748601', '160000047486', '46015169')
    35. *** (2) HOLDS THE LOCK(S):
    36. RECORD LOCKS space id 61 page no 2017 n bits 72 index PRIMARY of table `nomswc_db`.`activity_team_figure_history` trx id 252274259 lock_mode X locks rec but not gap
    37. Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
    38.  0: len 8; hex 3436303434313639; asc 46044169;;
    39.  1: len 6; hex 00000f096653; asc     fS;;
    40.  2: len 7; hex de00003c3a0110; asc    <:  ;;
    41.  3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    42.  4: len 4; hex 62da5149; asc b QI;;
    43.  5: len 4; hex 80000000; asc     ;;
    44.  6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
    45.  7: len 4; hex 62da5149; asc b QI;;
    46.  8: len 10; hex 31353330373133323031; asc 1530713201;;
    47.  9: len 12; hex 313530303030333037313332; asc 150000307132;;
    48.  10: len 10; hex 31313030353436393031; asc 1100546901;;
    49.  11: len 12; hex 313130303030303035343639; asc 110000005469;;
    50.  12: len 10; hex 31363034363933393031; asc 1604693901;;
    51.  13: len 12; hex 313630303030303436393339; asc 160000046939;;
    52.  
    53. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    54. RECORD LOCKS space id 61 page no 2001 n bits 144 index PRIMARY of table `nomswc_db`.`activity_team_figure_history` trx id 252274259 lock_mode X insert intention waiting
    55. Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
    56.  0: len 8; hex 73757072656d756d; asc supremum;;
    57.  
    58. *** WE ROLL BACK TRANSACTION (1)

            死锁的描述如下图所示。 

            代码所示，duplicate key error引发的死锁，这个场景主要发生在两个以上的事务同时进行唯一键值（唯一索引）相同的记录插入操作。当并发插入时，出现duplicate异常时，mysql会默认加上S锁，这就是为什么会出现死锁日志里面有个事务加上S锁了，也就同时解释了第二个问题，为什么事务没能提交，因为另一个事务也发生了duplicate异常，同时也对同一个位置加上了S锁，这样就出现了一种情况，多个线程对同一个位置持有S锁，每个线程都去这个位置争抢X锁，S和X锁两者是互斥关系，所以出现循环等待，死锁就此产生。

    4. TRANSACTIONS（事务）

    1. ------------
    2. TRANSACTIONS
    3. ------------
    4. Trx id counter 54476451
    5. Purge done for trx's n:o < 54476451 undo n:o < 0 state: running but idle     
    6. History list length 80
    7. LIST OF TRANSACTIONS FOR EACH SESSION:
    8. ---TRANSACTION 421810410397688, not started
    9. 0 lock struct(s), heap size 1136, 0 row lock(s)
    10. ---TRANSACTION 421810410394304, not started
    11. 0 lock struct(s), heap size 1136, 0 row lock(s)
    12. ---TRANSACTION 421810410386408, not started
    13. 0 lock struct(s), heap size 1136, 0 row lock(s)
    14. ---TRANSACTION 421810410371744, not started
    15. 0 lock struct(s), heap size 1136, 0 row lock(s)
    16. ---TRANSACTION 421810410438296, not started
    17. 0 lock struct(s), heap size 1136, 0 row lock(s)
    名称描述
    Trx id counter

    1. 事务ID计数器；

    2. 每创建一个新事务就会累加

    Purge done for trx's n:o < 54476451

    undo n:o < 0

    state: running but idle 

    1. 小于54476451事务ID的历史数据都被清理掉了；

    2. undo n:o < 0：innodb清理进程正在使用的撤销日志编号，0时说明清理进程处于空闲状态；

    History list length

    1. 记录事务提交的列表长度（未purge）；

    2. 按事务提交的顺序，将undo log进行链接（先提交放在尾端）

    ---TRANSACTION 421810410397688, not started

    1. 每个session的事务情况；

    2. not started：事务已经提交并且没有再发起影响事务的语句

    lock struct(s)多少个锁等待
    heap size堆大小
    row lock(s)锁住多少行数

    5. FILE I/O（文件IO）

    1. --------
    2. FILE I/O
    3. --------
    4. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
    5. I/O thread 1 state: waiting for completed aio requests (log thread)
    6. I/O thread 2 state: waiting for completed aio requests (read thread)
    7. I/O thread 3 state: waiting for completed aio requests (read thread)
    8. I/O thread 4 state: waiting for completed aio requests (read thread)
    9. I/O thread 5 state: waiting for completed aio requests (read thread)
    10. I/O thread 6 state: waiting for completed aio requests (write thread)
    11. I/O thread 7 state: waiting for completed aio requests (write thread)
    12. I/O thread 8 state: waiting for completed aio requests (write thread)
    13. I/O thread 9 state: waiting for completed aio requests (write thread)
    14. Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
    15.  ibuf aio reads:, log i/o's:, sync i/o's:
    16. Pending flushes (fsync) log: 0; buffer pool: 0
    17. 1072970879 OS file reads, 234098005 OS file writes, 67107797 OS fsyncs
    18. 13.06 reads/s, 16384 avg bytes/read, 6.57 writes/s, 3.28 fsyncs/s

            MySQL默认情况共有10个IO线程：1个insert buffert线程，1个log(事务日志)线程，4个read线程，4个write线程。其中读写IO线程，由innodb_read_io_threads、innodb_write_io_threads参数配置，默认值都是4。

    名称描述
    Pending normal aio reads

    1. 挂起的读IO的数量；

    2. 四个值相加

    Pending normal aio writes

    1. 挂起的写IO的数量；

    2. 四个值相加

    ibuf aio reads:, log i/o's:, sync i/o's:insert buffer thread挂起的fsync操作数量
    Pending flushes (fsync) log: 0; buffer pool: 0log thread 挂起的fsync操作数量

    OS file reads,

    OS file writes,

    OS fsyncs

    		读、写以及fsyncs操作的次数
    reads/s, 16384 avg bytes/read, 6.57 writes/s, 3.28 fsyncs/s头部区域“Per second averages calculated from the last 10 seconds ” 的时间段内，平均每秒的执行次数

    6. INSERT BUFFER AND ADAPTIVE HASH INDEX（插入缓冲和自适应哈希索引）

    1. -------------------------------------
    2. INSERT BUFFER AND ADAPTIVE HASH INDEX
    3. -------------------------------------
    4. Ibuf: size 1, free list len 1686, seg size 1688, 305733 merges
    5. merged operations:
    6.  insert 2457103, delete mark 1535765, delete 799296
    7. discarded operations:
    8.  insert 0, delete mark 0, delete 0
    9. Hash table size 34673, node heap has 123 buffer(s)
    10. Hash table size 34673, node heap has 25 buffer(s)
    11. Hash table size 34673, node heap has 37 buffer(s)
    12. Hash table size 34673, node heap has 8 buffer(s)
    13. Hash table size 34673, node heap has 10 buffer(s)
    14. Hash table size 34673, node heap has 4 buffer(s)
    15. Hash table size 34673, node heap has 7 buffer(s)
    16. Hash table size 34673, node heap has 3 buffer(s)
    17. 209.66 hash searches/s, 87.55 non-hash searches/s

            Ibuf描述了插入缓冲的情况：size表示已经合并记录页的数量；free list len表示空闲列表的长度；seg size表示当前Insert Buffer的大小1688*16KB（页默认大小16KB）；merges表示合并Insert Buffer的次数。

            merged operations描述了合并Insert Buffer的情况：insert表示插入合并的记录数；delete mark表示标记删除合并的记录数；delete表示删除合并的记录数。

            discarded operations描述了丢弃的情况（如：表删除，此时发生合并操作，导致无需将记录合并到辅助索引中）：insert表示丢弃的插入记录数；delete mark表示丢弃的标记删除的记录数；delete表示丢弃的删除记录数。

            Hash table size是自适应哈希表的大小；node heap表示节点堆的缓冲数量；hash searches/s表示每秒使用哈希索引查询的次数；non-hash searches/s表示每秒未使用哈希索引查询的次数。注意：通过hash searches : non-hash searches的比值可以大概了解哈希索引的使用效率。

    7. LOG（日志）

    1. ---
    2. LOG
    3. ---
    4. Log sequence number 29631227823
    5. Log flushed up to   29631227823
    6. Pages flushed up to 29631227489
    7. Last checkpoint at  29631227427
    8. Max checkpoint age    80826164
    9. Checkpoint age target 78300347
    10. Modified age          334
    11. Checkpoint age        396
    12. 0 pending log flushes, 0 pending chkp writes
    13. 42232278 log i/o's done, 2.25 log i/o's/second

            Log sequence number表示当前重做日志的LSN（日志序列号）；Log flushed up to表示日志缓冲已经刷新到日志文件logfile的LSN；Pages flushed up to表示脏页已经刷新的LSN。这三个值可能不同，因为一个事务从重做日志缓冲刷新到重做日志文件，并不只是在事务提交时发生，每秒都会有重做日志缓冲刷新到重做日志文件的操作。

            Last checkpoint at表示上次CheckPoint的LSN；Max checkpoint age表示触发同步CheckPoint的最大值（LSN差值）；Checkpoint age target表示触发同步CheckPoint的目标值；Modified age表示未刷新的脏页（Log sequence number 29631227823 - Pages flushed up to 29631227489 = Modified age 334）；Checkpoint age表示未CheckPoint的脏页数（Log sequence number 2963122782 - Last checkpoint at  29631227427 = Checkpoint age 396）。

            pending log flushes表示多少个挂起从重做日志缓冲刷新到重做日志文件；pending chkp writes表示多少个挂起CheckPoint的写。

    名称描述
    Log sequence number当前重做日志（redo log）的LSN
    Log flushed up to日志缓冲已经刷新到日志文件logfile的LSN
    Pages flushed up to脏页已经刷新的LSN
    Last checkpoint at上次CheckPoint的LSN
    Max checkpoint age触发同步CheckPoint的最大值（LSN差值）
    Checkpoint age target触发同步CheckPoint的目标值
    Modified age

    1. 未刷新的脏页；

    2. Log sequence number 29631227823 - Pages flushed up to 29631227489 = Modified age 334

    Checkpoint age

    1. 未CheckPoint的脏页数；

    2.Log sequence number 2963122782 - Last checkpoint at  29631227427 = Checkpoint age 396

    pending log flushes多少个挂起从重做日志缓冲刷新到重做日志文件
    pending chkp writes多少个挂起CheckPoint的写

    8. BUFFER POOL AND MEMORY（缓冲池与内存）

    1. ----------------------
    2. BUFFER POOL AND MEMORY
    3. ----------------------
    4. Total large memory allocated 139722752
    5. Dictionary memory allocated 6567657
    6. Internal hash tables (constant factor + variable factor)
    7.     Adaptive hash index 5808064         (2219072 + 3588992)
    8.     Page hash           139112 (buffer pool 0 only)
    9.     Dictionary cache    7122425         (554768 + 6567657)
    10.     File system         1786152         (812272 + 973880)
    11.     Lock system         421480  (332872 + 88608)
    12.     Recovery system     0       (0 + 0)
    13. Buffer pool size   8191
    14. Buffer pool size, bytes 134201344
    15. Free buffers       1024
    16. Database pages     6950
    17. Old database pages 2545
    18. Modified db pages  3
    19. Pending reads      0
    20. Pending writes: LRU 0, flush list 0, single page 0
    21. Pages made young 702725123, not young 31227621937
    22. 0.00 youngs/s, 0.00 non-youngs/s
    23. Pages read 1072974321, created 83035821, written 184067179
    24. 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    25. Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 48 / 1000
    26. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    27. LRU len: 6950, unzip_LRU len: 0
    28. I/O sum[805]:cur[0], unzip sum[0]:cur[0]
    名称描述
    Total large memory allocated缓冲池分配的总内存，单位字节
    Dictionary memory allocated数据字典分配的总内存，单位字节
    Adaptive hash index自适应哈希索引内存大小，单位字节
    Page hash页哈希内存大小，单位字节
    Dictionary cache数据字典缓存内存大小，单位字节
    File system文件系统内存大小，单位字节
    Lock system锁系统内存大小，单位字节
    Recovery system恢复系统内存大小，单位字节
    Buffer pool size缓存池总页数
    Buffer pool size, bytes缓存池总内存大小，单位字节
    Free buffers空闲页列表（Free List）的页数
    Database pages数据页列表（LRU List）的页数
    Old database pages旧数据页列表（LRU List子列表）的页数
    Modified db pages修改数据的页数（脏页列表的页数 Flush List）
    Pending reads等待读入缓冲池的页数
    Pending writes  LRU从LRU列表（数据页）底部写入的旧脏页数
    Pending writes  flush listCheckpoint期间要刷新的页数，即：刷新的脏页数
    Pending writes single page 挂起的独立页写入数
    Pages made youngLRU列表中变成new页的总数（移动到LRU列表的开头）
    Pages made not youngLRU列表中没有变成new页的总数（没有移动到LRU列表的开头）
    youngs/s每秒操作pages made young的次数
    non-youngs/s每秒操作pages made not young的次数
    Pages read从缓冲池读取的总页数
    Pages created缓冲池中创建的总页数
    Pages written从缓冲池写入的总页数
    reads/s平均每秒读取缓冲池页数
    creates/s平均每秒创建的缓冲池页数
    writes/s每秒平均写入缓冲池页面的次数
    Buffer pool hit rate缓冲池命中率（命中从缓冲池读取的页）
    young-making rate页访问导致页new化的平均命中率（缓冲池内所有的页 _ new + old）
    not (young-making rate)页访问未导致页new化的平均命中率
    Pages read ahead每秒预读操作的平均值
    evicted without access没有从缓冲池访问的情况下被逐出页的每秒平均数
    Random read ahead每秒随机预读操作的平均值
    LRU len缓冲池LRU列表的总页面大小，单位页
    unzip_LRU len缓冲池LRU列表中压缩页的长度，单位页
    I/O sum访问的缓冲池LRU列表页的总数
    I/O cur当前时间间隔内访问的缓冲池LRU列表页的总数
    I/O unzip sum解压缩的缓冲池 unzip_LRU列表页的总数
    I/O unzip cur当前时间间隔内解压的缓冲池 unzip_LRU 列表页总数

    9. ROW OPERATIONS（行操作）

    1. --------------
    2. ROW OPERATIONS
    3. --------------
    4. 0 queries inside InnoDB, 0 queries in queue
    5. 0 read views open inside InnoDB
    6. 0 RW transactions active inside InnoDB
    7. Process ID=981, Main thread ID=140335122142976, state: sleeping
    8. Number of rows inserted 2819363420, updated 30356193, deleted 3622065, read 156168189485
    9. 0.30 inserts/s, 1.79 updates/s, 0.34 deletes/s, 502.56 reads/s
    名称描述
    0 queries inside InnoDB

    1. 当前进入innodb内部的并发线程数；

    2. innodb_thread_concurrency，默认0不限制，0时：0 queries inside InnoDB、0 queries in queue都为0

    0 queries in queue当前进入等待队列FIFO中的线程数
    0 read views open inside InnoDB

    1. 当前InnoDB中打开read view的数量；

    2. MVCC通过read view来确定一致性读时的数据库snapshot；

    3. 可重复读隔离级别下，当事务中的第一个SELECT请求才创建read view

    Process ID=981, Main thread ID=140335122142976, state: sleeping主线程进程ID、线程ID，及当前状态
    Number of rows inserted/updated/deleted/readDB启动以来，所有的insert/update/delete/read数量
    inserts/s, updates/s, deletes/s, reads/sDB启动以来，所有的insert/update/delete/read的每秒平均值

    三、参考资料

    InnoDB 体系结构(上)

    MySQL :: MySQL 5.7 Reference Manual :: 14.5.1 Buffer Pool

    innodb关键特性之自适应哈希索引03 - 站在巨人的肩上Z - 博客园

    MySQL性能突发事件问题的排查技巧有哪些呢 - 数据库 - 亿速云

    InnoDB信息说明 - 网易数帆 - 博客园

    如何查看当前Innodb的并发_sunashe的博客-CSDN博客

    show engine innodb status 输出结果解读 - 孔个个 - 博客园

    MySQL造成更新死锁及插入死锁的几种常见原因_yue_hu的博客-CSDN博客_mysql 插入死锁

    Mysql数据库并发插入死锁问题及处理方式_boonya的博客-CSDN博客_mysql并发插入数据

    [笔记]MySQL 插入导致死锁_ghimi的博客-CSDN博客_mysql 插入死锁

  • 相关阅读:
    第五章：最新版零基础学习 PYTHON 教程—Python 字符串操作指南（第五节 - 在Python中打印转义字符的方法）
    数字孪生扫除智慧城市“盲点”，赋能社会数字发展
    IDEA创建SpringBoot的多模块项目教程
    python html转为为PDF
    flex布局实现左侧宽度固定，右边占满剩余宽度
    遥控器红外解码数码管显示
    军事资讯查询易语言代码
    JVM内存和垃圾回收-12.String Table
    基于微信小程序的校园跑腿小程序，附源码
    git clone拉取项目报错Filename too long warning:Clone succeeded,but checkout failed
  • 原文地址：https://blog.csdn.net/m0_37543627/article/details/125478303