InnoDB存储引擎状态详解

最新推荐文章于 2024-08-06 21:36:57 发布
最新推荐文章于 2024-08-06 21:36:57 发布
阅读量2.2k 收藏 6
点赞数
分类专栏： mysql 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_37543627/article/details/125478303
版权

目录

一、状态查询

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;

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

2. 查询InnoDB状态

show engine innodb status\G;

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

mysql> show engine innodb status\G;
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2022-06-27 09:33:40 0x7fa1c8a5f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 13285067 srv_active, 0 srv_shutdown, 10606666 srv_idle
srv_master_thread log flush and writes: 23891733
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 57836764
OS WAIT ARRAY INFO: signal count 269276481
RW-shared spins 0, rounds 171890114, OS waits 34656067
RW-excl spins 0, rounds 1245774156, OS waits 18379769
RW-sx spins 80621, rounds 1707842, OS waits 34552
Spin rounds per wait: 171890114.00 RW-shared, 1245774156.00 RW-excl, 21.18 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-07-22 15:27:14 0x7fccb9113700
*** (1) TRANSACTION:
TRANSACTION 252274025, ACTIVE 33 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 3556845, OS thread handle 140505809090304, query id 1181834385 192.168.134.194 zhengkun update
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')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 8; hex 3436303434313639; asc 46044169;;
 1: len 6; hex 00000f096653; asc     fS;;
 2: len 7; hex de00003c3a0110; asc    <:  ;;
 3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 4: len 4; hex 62da5149; asc b QI;;
 5: len 4; hex 80000000; asc     ;;
 6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 7: len 4; hex 62da5149; asc b QI;;
 8: len 10; hex 31353330373133323031; asc 1530713201;;
 9: len 12; hex 313530303030333037313332; asc 150000307132;;
 10: len 10; hex 31313030353436393031; asc 1100546901;;
 11: len 12; hex 313130303030303035343639; asc 110000005469;;
 12: len 10; hex 31363034363933393031; asc 1604693901;;
 13: len 12; hex 313630303030303436393339; asc 160000046939;;

*** (2) TRANSACTION:
TRANSACTION 252274259, ACTIVE 33 sec inserting
mysql tables in use 1, locked 1
14 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2458
MySQL thread id 3556846, OS thread handle 140517254969088, query id 1181837074 192.168.134.194 zhengkun update
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')
*** (2) HOLDS THE LOCK(S):
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
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 8; hex 3436303434313639; asc 46044169;;
 1: len 6; hex 00000f096653; asc     fS;;
 2: len 7; hex de00003c3a0110; asc    <:  ;;
 3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 4: len 4; hex 62da5149; asc b QI;;
 5: len 4; hex 80000000; asc     ;;
 6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 7: len 4; hex 62da5149; asc b QI;;
 8: len 10; hex 31353330373133323031; asc 1530713201;;
 9: len 12; hex 313530303030333037313332; asc 150000307132;;
 10: len 10; hex 31313030353436393031; asc 1100546901;;
 11: len 12; hex 313130303030303035343639; asc 110000005469;;
 12: len 10; hex 31363034363933393031; asc 1604693901;;
 13: len 12; hex 313630303030303436393339; asc 160000046939;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 54476451
Purge done for trx's n:o < 54476451 undo n:o < 0 state: running but idle     
History list length 80
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421810410397688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410394304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410386408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410371744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410438296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1072970879 OS file reads, 234098005 OS file writes, 67107797 OS fsyncs
13.06 reads/s, 16384 avg bytes/read, 6.57 writes/s, 3.28 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1686, seg size 1688, 305733 merges
merged operations:
 insert 2457103, delete mark 1535765, delete 799296
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 123 buffer(s)
Hash table size 34673, node heap has 25 buffer(s)
Hash table size 34673, node heap has 37 buffer(s)
Hash table size 34673, node heap has 8 buffer(s)
Hash table size 34673, node heap has 10 buffer(s)
Hash table size 34673, node heap has 4 buffer(s)
Hash table size 34673, node heap has 7 buffer(s)
Hash table size 34673, node heap has 3 buffer(s)
209.66 hash searches/s, 87.55 non-hash searches/s
---
LOG
---
Log sequence number 29631227823
Log flushed up to   29631227823
Pages flushed up to 29631227489
Last checkpoint at  29631227427
Max checkpoint age    80826164
Checkpoint age target 78300347
Modified age          334
Checkpoint age        396
0 pending log flushes, 0 pending chkp writes
42232278 log i/o's done, 2.25 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 139722752
Dictionary memory allocated 6567657
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 5808064         (2219072 + 3588992)
    Page hash           139112 (buffer pool 0 only)
    Dictionary cache    7122425         (554768 + 6567657)
    File system         1786152         (812272 + 973880)
    Lock system         421480  (332872 + 88608)
    Recovery system     0       (0 + 0)
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       1024
Database pages     6950
Old database pages 2545
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 702725123, not young 31227621937
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1072974321, created 83035821, written 184067179
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 48 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6950, unzip_LRU len: 0
I/O sum[805]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
Process ID=981, Main thread ID=140335122142976, state: sleeping
Number of rows inserted 2819363420, updated 30356193, deleted 3622065, read 156168189485
0.30 inserts/s, 1.79 updates/s, 0.34 deletes/s, 502.56 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

二、状态详解

1. BACKGROUND THREAD（后台线程）

-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 13285067 srv_active, 0 srv_shutdown, 10606666 srv_idle
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（信号量）

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 57836764
OS WAIT ARRAY INFO: signal count 269276481
RW-shared spins 0, rounds 171890114, OS waits 34656067
RW-excl spins 0, rounds 1245774156, OS waits 18379769
RW-sx spins 80621, rounds 1707842, OS waits 34552
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（检测到的死锁）

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-07-22 15:27:14 0x7fccb9113700
*** (1) TRANSACTION:
TRANSACTION 252274025, ACTIVE 33 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 13 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 3556845, OS thread handle 140505809090304, query id 1181834385 192.168.134.194 zhengkun update
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')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 8; hex 3436303434313639; asc 46044169;;
 1: len 6; hex 00000f096653; asc     fS;;
 2: len 7; hex de00003c3a0110; asc    <:  ;;
 3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 4: len 4; hex 62da5149; asc b QI;;
 5: len 4; hex 80000000; asc     ;;
 6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 7: len 4; hex 62da5149; asc b QI;;
 8: len 10; hex 31353330373133323031; asc 1530713201;;
 9: len 12; hex 313530303030333037313332; asc 150000307132;;
 10: len 10; hex 31313030353436393031; asc 1100546901;;
 11: len 12; hex 313130303030303035343639; asc 110000005469;;
 12: len 10; hex 31363034363933393031; asc 1604693901;;
 13: len 12; hex 313630303030303436393339; asc 160000046939;;

*** (2) TRANSACTION:
TRANSACTION 252274259, ACTIVE 33 sec inserting
mysql tables in use 1, locked 1
14 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 2458
MySQL thread id 3556846, OS thread handle 140517254969088, query id 1181837074 192.168.134.194 zhengkun update
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')
*** (2) HOLDS THE LOCK(S):
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
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 8; hex 3436303434313639; asc 46044169;;
 1: len 6; hex 00000f096653; asc     fS;;
 2: len 7; hex de00003c3a0110; asc    <:  ;;
 3: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 4: len 4; hex 62da5149; asc b QI;;
 5: len 4; hex 80000000; asc     ;;
 6: len 17; hex 4f7065726174696f6e506c6174666f726d; asc OperationPlatform;;
 7: len 4; hex 62da5149; asc b QI;;
 8: len 10; hex 31353330373133323031; asc 1530713201;;
 9: len 12; hex 313530303030333037313332; asc 150000307132;;
 10: len 10; hex 31313030353436393031; asc 1100546901;;
 11: len 12; hex 313130303030303035343639; asc 110000005469;;
 12: len 10; hex 31363034363933393031; asc 1604693901;;
 13: len 12; hex 313630303030303436393339; asc 160000046939;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

        死锁的描述如下图所示。 

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

4. TRANSACTIONS（事务）

------------
TRANSACTIONS
------------
Trx id counter 54476451
Purge done for trx's n:o < 54476451 undo n:o < 0 state: running but idle     
History list length 80
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421810410397688, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410394304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410386408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410371744, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421810410438296, not started
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）

--------
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] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1072970879 OS file reads, 234098005 OS file writes, 67107797 OS fsyncs
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（插入缓冲和自适应哈希索引）

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 1686, seg size 1688, 305733 merges
merged operations:
 insert 2457103, delete mark 1535765, delete 799296
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 123 buffer(s)
Hash table size 34673, node heap has 25 buffer(s)
Hash table size 34673, node heap has 37 buffer(s)
Hash table size 34673, node heap has 8 buffer(s)
Hash table size 34673, node heap has 10 buffer(s)
Hash table size 34673, node heap has 4 buffer(s)
Hash table size 34673, node heap has 7 buffer(s)
Hash table size 34673, node heap has 3 buffer(s)
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（日志）

---
LOG
---
Log sequence number 29631227823
Log flushed up to   29631227823
Pages flushed up to 29631227489
Last checkpoint at  29631227427
Max checkpoint age    80826164
Checkpoint age target 78300347
Modified age          334
Checkpoint age        396
0 pending log flushes, 0 pending chkp writes
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（缓冲池与内存）

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 139722752
Dictionary memory allocated 6567657
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 5808064         (2219072 + 3588992)
    Page hash           139112 (buffer pool 0 only)
    Dictionary cache    7122425         (554768 + 6567657)
    File system         1786152         (812272 + 973880)
    Lock system         421480  (332872 + 88608)
    Recovery system     0       (0 + 0)
Buffer pool size   8191
Buffer pool size, bytes 134201344
Free buffers       1024
Database pages     6950
Old database pages 2545
Modified db pages  3
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 702725123, not young 31227621937
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1072974321, created 83035821, written 184067179
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 986 / 1000, young-making rate 0 / 1000 not 48 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6950, unzip_LRU len: 0
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（行操作）

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
Process ID=981, Main thread ID=140335122142976, state: sleeping
Number of rows inserted 2819363420, updated 30356193, deleted 3622065, read 156168189485
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 插入死锁

爱我所爱0505
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
show innodb status 的实际拓展
MyISDB--Focus On Database
11-21 1220
结合这上一篇官方文档的翻译，接下来，我们拿实际的case来讲解。 摘抄： 这个比较详细！不错 很多人让我来阐述一下 SHOW INNODB STATUS 的输出信息, 了解 SHOW INNODB STATUS 都输出了些什么信息，并且我们能从这些信息中获取什么资讯，得以提高 MySQL 性能。 首先，让我们来了解一下 SHOW INNODB STATUS 输出的基础，它打印了
Innodb存储引擎浅析—事务系统
12-12
### Innodb存储引擎浅析—事务系统 #### 学述 在MySQL的众多存储引擎中，InnoDB无疑是最为重要且被广泛使用的之一。本文旨在深入解析InnoDB存储引擎中的事务处理机制及其背后的设计原理。 #### 存储引擎介绍 在...
InnoDB存储引擎Log详解.pdf
10-14
MySQL数据库的InnoDB存储引擎是关系型数据库管理系统的基石，其强大的事务处理能力和高可用性在很大程度上得益于其内部的Log机制，主要包括Undo Log和Redo Log。这两者是实现事务ACID特性的关键，同时也是优化数据库...
MySQL show engine innodb status命令：显示InnoDB存储引擎的当前状态信息，用于诊断性能问题、死锁、内存使用情况以及事务处理等方面的问题
学亮编程手记
05-26 822
是一个非常重要的管理命令，用于显示InnoDB存储引擎的当前状态信息。这个命令能够提供大量有关InnoDB内部运作的详细信息，对于诊断性能问题、死锁、内存使用情况以及事务处理等方面的问题极其有用。可能会因为输出信息过多，特别是当存在大量死锁历史时，导致部分信息被截断。这种情况下，可以通过升级MySQL服务器到较新的版本或者调整MySQL客户端的输出限制来解决。由于输出内容非常多且详细，通常需要根据具体的问题场景去分析特定的部分。例如，如果遇到死锁问题，重点关注。需要注意的是，在早期版本中，
InnoDB存储引擎Log详解收集.pdf
11-24
MySQL数据库的InnoDB存储引擎是其核心特性之一，它提供了强大的事务处理能力，这得益于其对Undo Log和Redo Log的高效使用。Undo Log和Redo Log是事务管理的重要组成部分，它们确保了事务的原子性（Atomicity）和持久...
innodb存储说明1
08-08
InnoDB存储引擎是MySQL数据库中的一个重要组成部分，它以其事务安全性和高并发性能而闻名。本文将深入解析InnoDB的存储机制，包括页的组织、页头与页尾的结构以及数据页的管理。 首先，InnoDB的存储是以页为基本...
认识MySQL
m0_51821434的博客
08-06 315
这篇文章可以让你了解数据库，不在懵懵懂懂！
MYSQL主库切换binlog模式后主从同步错误
weixin_45626288的博客
08-06 493
在使用FlinkSQL的mysql-cdc连接器监听MySQL数据库时，确保binlog模式为ROW是必要的。但在切换binlog模式时，需要特别注意主从库的数据一致性和同步状态。
尚硅谷谷粒商城项目笔记——六、使用navciat连接docker中的mysql容器【电脑CPU：AMD】
B6665X的博客
08-06 156
尚硅谷谷粒商城项目笔记——六、使用navciat连接docker中的mysql容器【电脑CPU：AMD】
2024.8.2（MySQL）
weixin_70751278的博客
08-02 1495
2024-08-02T01:49:19.435048Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: dQ1o<YO&YNyX 密码。语法：create table user( id int primarykey,username varchar，password varchar );#insert into 表名 (字段名列表) values (字段值列表)2.5.2 修改列名。
源码编译安装LAMP（HTTP服务，MYSQL ,PHP，以及bbs论坛）
YCyjs的博客
08-06 586
可在生产环境使下载在下面两个插件是httpd2.4以后的版本所需要虽然xf通常足够，但使用与压缩算法对应的选项可以提供更好的控制。接着编译-j开几用几个线程，如果在虚拟机可以开设置多一些内存和线程优化配置文件路径，并把httpd服务的可执行程序文件放入路径环境变量的目录中便于系统识别#加入系统服务管理有两种方式方法一：便于service管理将链接apachectl 复制到系统启动目录下并命名为httpd。
Java面试题:MySQL高频面试题
qq_55630615的博客
08-06 164
MySQL为什么选择B+树来存储索引？层数低,可以存放的数据多。
MySQL——表的约束（三）唯一约束
PAP
08-06 207
MySQL——表的约束（三）唯一约束
【学习总结】MySQL篇
qq_43813182的博客
08-06 330
事务是不可分割的最小单元，一个事务的若干sql操作。要么统一成功，要么统一失败。（redoLog）
MySQL(六）：mysql 约束
SSuperwoman的博客
08-06 275
基本介绍：约束用于确保数据库的数据满足特定的商业规则，约束包括：not null、unique、primary key、foreign key 、check五种。一、主键的使用（primary key)用于唯一的表示表行的数据，当定义主键约束后，该列不能重复。
MySQL2 DML和SQL约束
最新发布
Stringzhua的博客
08-06 656
DML和SQL约束
MySQL之delete 、truncate与drop区别
心有谦谦结
08-03 319
drop：‌删除表及其所有数据，‌包括表结构、‌索引、‌触发器等，‌是最彻底的删除方式。‌ truncate：‌仅删除表中的所有数据，‌但保留表结构、‌索引、‌触发器等，‌常用于快速清空表数据。‌ delete：‌删除表中的特定行数据，‌可以指定条件删除，‌支持事务回滚
【MySQL】全面剖析索引失效、回表查询与索引下推
k123456kah的博客
08-03 690
回表查询是指在使用辅助索引（二级索引）进行查询时，由于辅助索引中不包含查询所需的所有列数据，数据库必须通过索引找到对应的数据行位置，再去实际的数据表（即“回表”）中读取完整的数据行。这种操作会增加额外的 I/O 开销，因此回表查询通常比直接从索引中获取数据的查询更慢。
【Mysql】第二章 库操作（增删改查+备份恢复+编码校验）
YQ20210216的博客
08-06 242
在root模式下（不是在mysql中），备份helloworld数据库为mytest.sql，输入下面命令后输入密码。创建一个名为helloworld的数据库，设置字符编码为gbk，直接在后面加上。注意：数据库名字加反引号``，是为了防止使用的数据库名刚好是关键字。创建一个名为helloworld的数据库，设置校验编码，直接加上。创建一个名为helloworld的数据库，相当于建立一个目录。改变数据库helloworld的编码为utf8。需要先进入对应的数据库，本质是创建文件。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值