mysql > show engine innodb status\G;
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2021-05-10 10:41:58 0x7febcc273700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 29 seconds
-----------------
BACKGROUND THREAD ----压力情况
-----------------
srv_master_thread loops: 943651 srv_active, 0 srv_shutdown, 40128262 srv_idle
srv_master_thread log flush and writes: 41071913
----------
SEMAPHORES -----信号等待信息。当等待信号量非常大的时候，可能需要禁用并发线程检测设置innodb_thread_concurrency=0.
----------
OS WAIT ARRAY INFO: reservation count 1502227
OS WAIT ARRAY INFO: signal count 1517917
RW-shared spins 0, rounds 3035222, OS waits 1497468
RW-excl spins 0, rounds 105732, OS waits 618
RW-sx spins 2992, rounds 36171, OS waits 301
Spin rounds per wait: 3035222.00 RW-shared, 105732.00 RW-excl, 12.09 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-07-20 10:08:49 0x7febd43b8700
*** (1) TRANSACTION:
TRANSACTION 278597, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 7 lock struct(s), heap size 1136, 181 row lock(s), undo log entries 1
MySQL thread id 923, OS thread handle 140651109988096, query id 3871952 localhost 127.0.0.1 root Sending data
update xwb_content set like_inc=(select count(*) from xwb_content_like where content_id=41) where content_id=41
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 356 page no 5 n bits 264 index PRIMARY of table `xlife_product`.`xwb_content_like` trx id 278597 lock mode S waiting
Record lock, heap no 197 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 28; hex 6f704d6d4c3553417468756c5679656941455451523968333274786f; asc opMmL5SAthulVyeiAETQR9h32txo;;
1: len 4; hex 80000027; asc ';;
2: len 6; hex 000000044046; asc @F;;
3: len 7; hex bc000001330110; asc 3 ;;
4: len 5; hex 99a6e8a231; asc 1;;
*** (2) TRANSACTION:
TRANSACTION 278598, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
8 lock struct(s), heap size 1136, 182 row lock(s), undo log entries 1
MySQL thread id 937, OS thread handle 140650854713088, query id 3871954 localhost 127.0.0.1 root Sending data
update xwb_content set like_inc=(select count(*) from xwb_content_like where content_id=39) where content_id=39
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 356 page no 5 n bits 264 index PRIMARY of table `xlife_product`.`xwb_content_like` trx id 278598 lock_mode X locks rec but not gap
Record lock, heap no 197 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 28; hex 6f704d6d4c3553417468756c5679656941455451523968333274786f; asc opMmL5SAthulVyeiAETQR9h32txo;;
1: len 4; hex 80000027; asc ';;
2: len 6; hex 000000044046; asc @F;;
3: len 7; hex bc000001330110; asc 3 ;;
4: len 5; hex 99a6e8a231; asc 1;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 356 page no 5 n bits 264 index PRIMARY of table `xlife_product`.`xwb_content_like` trx id 278598 lock mode S waiting
Record lock, heap no 196 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 28; hex 6f704d6d4c3553417468756c5679656941455451523968333274786f; asc opMmL5SAthulVyeiAETQR9h32txo;;
1: len 4; hex 80000029; asc );;
2: len 6; hex 000000044045; asc @E;;
3: len 7; hex bb000001310110; asc 1 ;;
4: len 5; hex 99a6e8a231; asc 1;;
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS ----锁等待信息和当前活动事务信息。通过这部分输出，可以追踪死锁的详细信息。
------------
Trx id counter 5403601
Purge done for trx's n:o < 5403599 undo n:o < 0 state: running but idle
History list length 22
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422126263025264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263032560, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263031648, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263030736, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263024352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263029824, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263026176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263027088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263028000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422126263023440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O ----IO等待信息
--------
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
703837 OS file reads, 14642493 OS file writes, 9550263 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX ----显示插入缓存当前状态信息以及自适应Hash Index的状态。
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 8916 merges
merged operations:
insert 7034, delete mark 1850, delete 8
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 4 buffer(s)
Hash table size 34673, node heap has 31 buffer(s)
Hash table size 34673, node heap has 8 buffer(s)
Hash table size 34673, node heap has 39 buffer(s)
Hash table size 34673, node heap has 13 buffer(s)
Hash table size 34673, node heap has 37 buffer(s)
Hash table size 34673, node heap has 4 buffer(s)
0.00 hash searches/s, 0.10 non-hash searches/s
---
LOG -----事务日志相关信息，当前序列号，已经刷新同步序列号，check到哪个序列号。从启动到现在已经做了多少次check point。
---
Log sequence number 1478125911
Log flushed up to 1478125911
Pages flushed up to 1478125911
Last checkpoint at 1478125902
0 pending log flushes, 0 pending chkp writes
6058759 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY ----显示innodb buffer pool相关的各种统计信息，以及其他一些内存使用信息。
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 1629898
Buffer pool size 8191
Free buffers 1024
Database pages 7030
Old database pages 2575
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1225685, not young 11425256
0.00 youngs/s, 0.00 non-youngs/s
Pages read 703608, created 30387, written 7711320
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: 7030, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS ----客户端的请求query
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1928, Main thread ID=140650886448896, state: sleeping
Number of rows inserted 4805556, updated 2350785, deleted 86699, read 95968291572
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
create table innodb_monitor(a int) engine=innodb;来开通15秒输出到表里。 drop table innodb_monitor。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
