5.7.19
mysql> show engine innodb status;
| Type | Name | Status | InnoDB | | #头部信息
=====================================
2018-03-15 09:20:38 0x7f845bb36700 INNODB MONITOR OUTPUT #第二行是当前日期和时间
=====================================
Per second averages calculated from the last 1 seconds #第四行显示的是计算出这一平均值的时间间隔，即自上次输出以来的时间，或者是距上次内部复位的时长
-----------------
BACKGROUND THREAD #线程
-----------------
srv_master_thread loops: 215618 srv_active, 0 srv_shutdown, 16771756 srv_idle
srv_master_thread log flush and writes: 16987374
----------
SEMAPHORES #信号
----------
OS WAIT ARRAY INFO: reservation count 161412 #os wait 的信息：reservation count：表示InnoDB产生了多少次OS WAIT
OS WAIT ARRAY INFO: signal count 199120 #进行OS WAIT线程，接收到多少次信号(single)被唤醒
如果这个single数值越大，几十万或者几百万，可能是很多I/0等待或者是InnoDB争用问题(关于争用问题可能与OS调度有关，可以尝试减少innodb_thread_concurrency参数)
RW-shared spins 0, rounds 368745, OS waits 147551 #Mutex spin线程无法获取锁而进入Spin wait ,rounds是spin wait进行轮询检查mutextes的次数，os wait 线程放弃spin-wait 进入挂起状态
RW-excl spins 0, rounds 720848, OS waits 5581 #RW-shared 共享锁
RW-sx spins 21765, rounds 391404, OS waits 4700 #RW-excl 排他锁
Spin rounds per wait: 368745.00 RW-shared, 720848.00 RW-excl, 17.98 RW-sx
备注：要明白Innodb如何处理互斥量(Mutexes)，以及什么是两步获得锁(two-step approach)。首先进程，
试图获得一个锁，如果此锁被它人占用。它就会执行所谓的spin wait,即所谓循环的查询”锁被释放了吗？”。
如果在循环过程中，一直未得到锁释放的信息，则其转入OS WAIT，即所谓线程进入挂起(suspended)状态。
直到锁被释放后，通过信号(singal)唤醒线程
Spin wait的消耗远小于OS waits。Spinwait利用cpu的空闲时间，检查锁的状态，
OS Wait会有所谓content switch，从CPU内核中换出当前执行线程以供其它线程使用。
你可以通过innodb_sync_spin_loops参数来平衡spin wait和os wait
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-01 13:56:55 0x7f8484d67700 #死锁发生的时间
*** (1) TRANSACTION: #事务1的状态
TRANSACTION 22137, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2077, OS thread handle 140207263028992, query id 389659 localhost root updating
UPDATE sbtest set c=? where id=? #显示第一个死锁的的第一个事务
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 120 page no 840 n bits 144 index PRIMARY of table `mysql`.`sbtest` trx id 22137 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
#以上表示死锁发生时事务1等待的锁，事务想获得sbtest表的PRIMARY索引对应的X排他锁(Innodb的锁是与索引相关)
0: len 4; hex 0000c1c6; asc ;;
1: len 6; hex 000000005675; asc Vu;;
2: len 7; hex 210000016e1598; asc ! n ;;
3: len 4; hex 00000000; asc ;;
4: len 30; hex 3834323436313331392d39323836313938372d313035353230373638352d; asc 842461319-92861987-1055207685-; (total 120 bytes);
5: len 30; hex 616161616161616161616666666666666666666672727272727272727272; asc aaaaaaaaaaffffffffffrrrrrrrrrr; (total 60 bytes);
*** (2) TRANSACTION: #事务2的状态
TRANSACTION 22133, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 3
MySQL thread id 2081, OS thread handle 140207141058304, query id 389671 localhost root update
INSERT INTO sbtest values(?,0,' ','aaaaaaaaaaffffffffffrrrrrrrrrreeeeeeeeeeyyyyyyyyyy')
*** (2) HOLDS THE LOCK(S): #事务2获得的锁
RECORD LOCKS space id 120 page no 840 n bits 144 index PRIMARY of table `mysql`.`sbtest` trx id 22133 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 0000c1c6; asc ;;
1: len 6; hex 000000005675; asc Vu;;
2: len 7; hex 210000016e1598; asc ! n ;;
3: len 4; hex 00000000; asc ;;
4: len 30; hex 3834323436313331392d39323836313938372d313035353230373638352d; asc 842461319-92861987-1055207685-; (total 120 bytes);
5: len 30; hex 616161616161616161616666666666666666666672727272727272727272; asc aaaaaaaaaaffffffffffrrrrrrrrrr; (total 60 bytes);
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: #事务2等待的锁
RECORD LOCKS space id 120 page no 840 n bits 144 index PRIMARY of table `mysql`.`sbtest` trx id 22133 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 0000c1c6; asc ;;
1: len 6; hex 000000005675; asc Vu;;
2: len 7; hex 210000016e1598; asc ! n ;;
3: len 4; hex 00000000; asc ;;
4: len 30; hex 3834323436313331392d39323836313938372d313035353230373638352d; asc 842461319-92861987-1055207685-; (total 120 bytes);
5: len 30; hex 616161616161616161616666666666666666666672727272727272727272; asc aaaaaaaaaaffffffffffrrrrrrrrrr; (total 60 bytes);
*** WE ROLL BACK TRANSACTION (1) #表示选择了哪个事务回滚，避免无限期死锁等待
备注：innodb有一个内在的死锁检测工具，当死锁超过一定时间后，会回滚其中一个事务,innodb_lock_wait_timeout 可配置死锁等待超时时间
------------
TRANSACTIONS #包含了InnoDB事务(transaction)的统计信息
------------
Trx id counter 883585 #当前的transaction id ,这是个系统变量，随着每次新的transaction产生而增加
Purge done for trx's n:o < 883584 undo n:o < 0 state: running but idle #正在进行清空的操作操作的transaction ID
History list length 32 #记录了undo spaces 内unpurged 的事务个数（Purge的原则就是记录没有被其它事务继续使用了）
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421783105715856, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105723152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105708560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105721328, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105720416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105719504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105712208, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105717680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105716768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105706736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105714032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105711296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105710384, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105709472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105702176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105707648, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105704912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105698528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105704000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105713120, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105697616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105722240, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105701264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105700352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105718592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105703088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105705824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105699440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105724064, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105728624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421783105714944, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O #显示了I/O Helper thread d的状态，包含一些统计信息
--------
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)
#以上显示了I/O Helper 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
#显示各个I/O Helper thread的pending operations,pending的log和buffer pool thread的fsync()调用
745 OS file reads, 3884270 OS file writes, 2637830 OS fsyncs
#显示了reads writes fsync() 调用次数
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 8 merges
#seg size 表示当前插入缓冲的大小为3608*16KB，大约为57728KB。free list len代表了空闲列表的长度，merges 表示合并次数
merged operations:
insert 0, delete mark 0, delete 0
#insert 插入的记录数，delete mark 打上的标记,delete 删除的次数
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 23903167, node heap has 70 buffer(s)
Hash table size 23903167, node heap has 3 buffer(s)
Hash table size 23903167, node heap has 2 buffer(s)
Hash table size 23903167, node heap has 4 buffer(s)
Hash table size 23903167, node heap has 2 buffer(s)
Hash table size 23903167, node heap has 2 buffer(s)
Hash table size 23903167, node heap has 1 buffer(s)
Hash table size 23903167, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG #记录了transaction log 子系统的信息
---
Log sequence number 572522770 #显示当前log sequence number表示有多少字节写入到log文件内
Log flushed up to 572522753 #显示已经被flushed(写入磁盘)的logs
Pages flushed up to 572519298
Last checkpoint at 572519289 #显示最后一个checkpoint 的logs
0 pending log flushes, 0 pending chkp writes
1071368 log i/o's done, 0.00 log i/o's/second #显示pending log 的统计信息
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 98948874240 #显示分配给innodb 的内存大小（以及additional pool 使用的大小 (0表示没有使用)）
Dictionary memory allocated 353114
Buffer pool size 5897520
#buffer pool size > database pages 因为buffer pool size 还会存放lock index hash index 等一些其他系统信息
Free buffers 5883344
Database pages 14090
Old database pages 4996
Modified db pages 23
Pending reads 0 #显示了pending的reads 和writes
Pending writes: LRU 0, flush list 0, single page 0 #显示InnoDB读写和创建的页面(pages)
Pages made young 2238, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 656, created 13450, written 2205522
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 #显示buffer pool 的命中率
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14090, unzip_LRU len: 0
I/O sum[1000]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 589752
Free buffers 588257
Database pages 1487
Old database pages 528
Modified db pages 6
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 66, created 1421, written 335233
0.00 reads/s, 0.00 creates/s, 0.00 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: 1487, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 589752
Free buffers 588345
Database pages 1398
Old database pages 496
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 32, created 1366, written 69907
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1398, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 589752
Free buffers 588518
Database pages 1223
Old database pages 431
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 35, created 1188, written 117885
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1223, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 589752
Free buffers 588417
Database pages 1326
Old database pages 469
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 94, created 1232, written 316886
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1326, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 589752
Free buffers 588457
Database pages 1287
Old database pages 455
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1494, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 91, created 1204, written 273765
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1287, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 589752
Free buffers 588310
Database pages 1433
Old database pages 508
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 469, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 97, created 1340, written 281935
0.00 reads/s, 0.00 creates/s, 0.00 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: 1433, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 589752
Free buffers 588304
Database pages 1439
Old database pages 511
Modified db pages 3
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 77, created 1362, written 241590
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1439, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 589752
Free buffers 588118
Database pages 1625
Old database pages 579
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 26, created 1599, written 236382
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1625, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 8
Buffer pool size 589752
Free buffers 588349
Database pages 1395
Old database pages 494
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 73, created 1322, written 218407
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1395, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 9
Buffer pool size 589752
Free buffers 588269
Database pages 1477
Old database pages 525
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 275, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 65, created 1416, written 113532
0.00 reads/s, 0.00 creates/s, 0.00 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: 1477, unzip_LRU len: 0
I/O sum[100]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS #显示了row operations 及其他一些统计信息
--------------
0 queries inside InnoDB, 0 queries in queue #显示了有多少个线程在InnoDB内核
0 read views open inside InnoDB #有多少个read view 被打开，一个read view 是一致性保证MVCC "snapshot"
Process ID=32249, Main thread ID=140207409276672, state: sleeping #显示内核main thread的状态信息。
Number of rows inserted 997078, updated 292987, deleted 101690, read 55691282017
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1869.13 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
相关文章
- show engine innodb status输出说明
- MySQL5.7-show engine innodb status
- MySQL中show engine innodb status的详解
- mysql 查看存储引擎的状态 show engine innodb status 详解
- mysql中engine=innodb和engine=myisam的区别
- mysql中engine=innodb和engine=myisam的区别
- mysql中 的 ENGINE = innodb; 意思
- mysql中engine=innodb和engine=myisam的区别
- mysql中engine=innodb和engine=myisam的区别
- 我的mysql版本是5.1.57.log，为什么没有innodb_status_file这个参数