查看当前打开的表
SHOW OPEN TABLES WHERE In_use > 0;
查看当前的事务和锁等待
SELECT
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_id,
b.trx_mysql_thread_id blocking_thread,
b.trx_query blocking_query
FROM
INFORMATION_SCHEMA.INNODB_LOCK_WAITS w
JOIN
INFORMATION_SCHEMA.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN
INFORMATION_SCHEMA.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;查看详细锁信息
SHOW ENGINE INNODB STATUS;
=====================================
2024-11-27 17:44:54 0x7f43300c6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 16 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7630103 srv_active, 0 srv_shutdown, 2931759 srv_idle
srv_master_thread log flush and writes: 10561862
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 114411551
OS WAIT ARRAY INFO: signal count 325890031
RW-shared spins 0, rounds 783271127, OS waits 37494057
RW-excl spins 0, rounds 3159451548, OS waits 16480981
RW-sx spins 37235468, rounds 546861857, OS waits 3719358
Spin rounds per wait: 783271127.00 RW-shared, 3159451548.00 RW-excl, 14.69 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-10-30 14:10:23 0x7f43317b8700
*** (1) TRANSACTION:
TRANSACTION 280032300, ACTIVE 14 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 11 lock struct(s), heap size 1136, 171 row lock(s), undo log entries 1
MySQL thread id 75501, OS thread handle 139926815233792, query id 2457463579 10.12.54.92 root updating
update device_token set is_disabled = 1 where user_id = 5 and token = 'B459D64EAA753EBBF2ABC5F0849A6E989AEB462D250B0135BB42AC5EDAFB9189'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 318 page no 30 n bits 112 index PRIMARY of table `growth_user_main_db`.`device_token` trx id 280032300 lock_mode X locks rec but not gap waiting
Record lock, heap no 20 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80001614; asc ;;
1: len 6; hex 000010b0b5c4; asc ;;
2: len 7; hex 250000023a2a0b; asc % :* ;;
3: len 4; hex 80000005; asc ;;
4: len 30; hex 344432413241344536434431433741413944363546363036443934323945; asc 4D2A2A4E6CD1C7AA9D65F606D9429E; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4b0b0ae; asc ;;
*** (2) TRANSACTION:
TRANSACTION 280016324, ACTIVE 31790 sec starting index read
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1136, 183 row lock(s), undo log entries 1
MySQL thread id 75090, OS thread handle 139926569715456, query id 2457463669 10.12.54.92 root updating
delete from device_token where token = 'B459D64EAA753EBBF2ABC5F0849A6E989AEB462D250B0135BB42AC5EDAFB9189'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 318 page no 30 n bits 112 index PRIMARY of table `growth_user_main_db`.`device_token` trx id 280016324 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80001602; asc ;;
1: len 6; hex 000010a19cfc; asc ;;
2: len 7; hex a4000013370110; asc 7 ;;
3: len 4; hex 80000408; asc ;;
4: len 30; hex 304533423843413644313735324146313734434633313244313832454246; asc 0E3B8CA6D1752AF174CF312D182EBF; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4af0c67; asc g;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80001603; asc ;;
1: len 6; hex 000010a19cff; asc ;;
2: len 7; hex a70000023b0110; asc ; ;;
3: len 4; hex 80000407; asc ;;
4: len 30; hex 304533423843413644313735324146313734434633313244313832454246; asc 0E3B8CA6D1752AF174CF312D182EBF; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4af0c67; asc g;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80001604; asc ;;
1: len 6; hex 000010a19d02; asc ;;
2: len 7; hex aa000001730110; asc s ;;
3: len 4; hex 80000409; asc ;;
4: len 30; hex 304533423843413644313735324146313734434633313244313832454246; asc 0E3B8CA6D1752AF174CF312D182EBF; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4af0c67; asc g;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80001607; asc ;;
1: len 6; hex 000010a1b3cf; asc ;;
2: len 7; hex f40000011d0110; asc ;;
3: len 4; hex 8000040a; asc ;;
4: len 30; hex 304533423843413644313735324146313734434633313244313832454246; asc 0E3B8CA6D1752AF174CF312D182EBF; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4af1776; asc v;;
Record lock, heap no 20 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80001614; asc ;;
1: len 6; hex 000010b0b5c4; asc ;;
2: len 7; hex 250000023a2a0b; asc % :* ;;
3: len 4; hex 80000005; asc ;;
4: len 30; hex 344432413241344536434431433741413944363546363036443934323945; asc 4D2A2A4E6CD1C7AA9D65F606D9429E; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4b0b0ae; asc ;;
Record lock, heap no 29 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8000161e; asc ;;
1: len 6; hex 000010a4333d; asc 3=;;
2: len 7; hex c000000c850110; asc ;;
3: len 4; hex 8000041f; asc ;;
4: len 30; hex 304533423843413644313735324146313734434633313244313832454246; asc 0E3B8CA6D1752AF174CF312D182EBF; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4b103e3; asc ;;
Record lock, heap no 30 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8000161f; asc ;;
1: len 6; hex 000010a43340; asc 3@;;
2: len 7; hex c300000c9d0110; asc ;;
3: len 4; hex 8000041e; asc ;;
4: len 30; hex 304533423843413644313735324146313734434633313244313832454246; asc 0E3B8CA6D1752AF174CF312D182EBF; (total 64 bytes);
5: len 1; hex 80; asc ;;
6: len 5; hex 99b4b103e3; asc ;;
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 8000162f; asc /;;
1: len 6; hex 000010b0cf46; asc F;;
2: len 7; hex 6f000010d415c8; asc o ;;
3: len 4; hex 8000045b; asc [;;
4: len 30; hex 344432413241344536434431433741413944363546363036443934323945; asc 4D2A2A4E6CD1C7AA9D65F606D9429E; (total 64 bytes);
5: len 1; hex 81; asc ;;
6: len 5; hex 99b4bcae4f; asc O;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 318 page no 15 n bits 200 index idx_device_token_token of table `growth_user_main_db`.`device_token` trx id 280016324 lock_mode X waiting
Record lock, heap no 126 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 423435394436344541413735334542424632414243354630383439413645; asc B459D64EAA753EBBF2ABC5F0849A6E; (total 64 bytes);
1: len 4; hex 80001600; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 283555746
Purge done for trx's n:o < 283555745 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421402209802144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209800320, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209799408, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209797584, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209796672, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209791200, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209790288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209761104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209793024, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209787552, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209786640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209783904, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209782992, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209781168, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209779344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209778432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209776608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209775696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209773872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209772960, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209772048, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209769312, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209768400, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209842272, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209777520, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209838624, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209837712, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209829504, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209828592, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209827680, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209826768, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209798496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209801232, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209793936, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209788464, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209792112, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209785728, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209784816, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209814912, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209814000, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209813088, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209782080, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209806704, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209764752, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209771136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209794848, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209774784, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209762016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209789376, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209807616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209795760, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209780256, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209770224, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209765664, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209767488, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209766576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209763840, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421402209762928, 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
92657230442 OS file reads, 181741954 OS file writes, 88742509 OS fsyncs
364.16 reads/s, 16384 avg bytes/read, 1.37 writes/s, 1.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 381, seg size 383, 466336 merges
merged operations:
insert 1149487, delete mark 10295650, delete 225513
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 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
Hash table size 34673, node heap has 188 buffer(s)
Hash table size 34673, node heap has 1 buffer(s)
0.56 hash searches/s, 3.25 non-hash searches/s
---
LOG
---
Log sequence number 187350438707
Log flushed up to 187350438707
Pages flushed up to 187350438707
Last checkpoint at 187350438698
0 pending log flushes, 0 pending chkp writes
72081322 log i/o's done, 0.67 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 4546806
Buffer pool size 8191
Free buffers 0
Database pages 7996
Old database pages 2931
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1419324208, not young 434222906057
0.00 youngs/s, 0.00 non-youngs/s
Pages read 92657623252, created 3204356, written 102403088
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 877 / 1000, young-making rate 111 / 1000 not 463 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 7996, unzip_LRU len: 0
I/O sum[35671]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=139926863599360, state: sleeping
Number of rows inserted 380289343, updated 28279140, deleted 379903301, read 2379098669376
0.00 inserts/s, 0.06 updates/s, 0.00 deletes/s, 16856.45 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
