show engine innodb status发现deadlock

最新推荐文章于 2024-06-30 14:28:30 发布
最新推荐文章于 2024-06-30 14:28:30 发布
阅读量2k 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/opsdba/article/details/32721269
版权

show engine innodb status发现deadlock



mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2014-06-20 15:07:49 7f975bb5c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 43 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 6060983 srv_active, 0 srv_shutdown, 26993 srv_idle
srv_master_thread log flush and writes: 6085668
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 8720007498
OS WAIT ARRAY INFO: signal count 8347597945
Mutex spin waits 231539924988, rounds 2719968232096, OS waits 4633791667
RW-shared spins 1593278807, rounds 27590420428, OS waits 804786677
RW-excl spins 202775387, rounds 3052976458, OS waits 30388091
Spin rounds per wait: -7004.65 mutex, 17.32 RW-shared, 15.06 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2014-05-27 18:29:00 7f958ae39700
*** (1) TRANSACTION:
TRANSACTION 603580589, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 174548012, OS thread handle 0x7f9553d75700, query id 871733995 192.168.30.5 golo update
INSERT INTO `trip_record_tmp_b`(`uid`,`s_lat`,`s_lon`,`e_lat`,`e_lon`,`serial_no`,`sdate`,`edate`,`data`,`travel_id`,`issynchro`) VALUES('84bc2f352552a5241a600f661432bc2e','','','','','967790023150','2014-05-27 17:55:27','2014-05-27 18:15:59','null','3caae5f2-4c0f-461f-a7be-3fe9070732d6','1')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15172 page no 237737 n bits 80 index `PRIMARY` of table `db_application`.`trip_record_tmp_b` trx id 603580589 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
 0: len 30; hex 383462633266333532353532613532343161363030663636313433326263; asc 84bc2f352552a5241a600f661432bc; (total 32 bytes);
 1: len 6; hex 000023f9e8a7; asc   #   ;;
 2: len 7; hex 46000028081c52; asc F  (  R;;
 3: len 4; hex 6e756c6c; asc null;;
 4: len 30; hex 33636161653566322d346330662d343631662d613762652d336665393037; asc 3caae5f2-4c0f-461f-a7be-3fe907; (total 36 bytes);
 5: len 0; hex ; asc ;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 0; hex ; asc ;;
 9: len 5; hex 9992f71ddb; asc      ;;
 10: len 5; hex 9992f723fb; asc    # ;;
 11: len 12; hex 393637373930303233313530; asc 967790023150;;
 12: len 4; hex 00000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 603580587, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s)
MySQL thread id 174940322, OS thread handle 0x7f958ae39700, query id 871733992 192.168.30.5 golo update
INSERT INTO `trip_record_tmp_b`(`uid`,`s_lat`,`s_lon`,`e_lat`,`e_lon`,`serial_no`,`sdate`,`edate`,`data`,`travel_id`,`issynchro`) VALUES('84bc2f352552a5241a600f661432bc2e','22.563603','113.89169','22.584672','113.86702','967790023150','2014-05-27 17:55:27','2014-05-27 18:15:59','[{\"Latitude\":\"22.563603\",\"Longitude\":\"113.89169\"},{\"Latitude\":\"22.563213\",\"Longitude\":\"113.89181\"},{\"Latitude\":\"22.563020\",\"Longitude\":\"113.89158\"},{\"Latitude\":\"22.563082\",\"Longitude\":\"113.89128\"},{\"Latitude\":\"22.563298\",\"Longitude\":\"113.89098\"},{\"Latitude\":\"22.563590\",\"Longitude\":\"113.89104\"},{\"Latitude\":\"22.563842\",\"Longitude\":\"113.89083\"},{\"Latitude\":\"22.564328\",\"Longitude\":\"113.89016\"},{\"Latitude\":\"22.564692\",\"Longitude\":\"113.88978\"},{\"Latitude\":\"22.565124\",\"Longitude\":\"113.88929\"},{\"Latitude\":\"22.565547\",\"Longitude\":\"113.88887\"},{\"Latitude
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 15172 page no 237737 n bits 80 index `PRIMARY` of table `db_application`.`trip_record_tmp_b` trx id 603580587 lock mode S locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
 0: len 30; hex 383462633266333532353532613532343161363030663636313433326263; asc 84bc2f352552a5241a600f661432bc; (total 32 bytes);
 1: len 6; hex 000023f9e8a7; asc   #   ;;
 2: len 7; hex 46000028081c52; asc F  (  R;;
 3: len 4; hex 6e756c6c; asc null;;
 4: len 30; hex 33636161653566322d346330662d343631662d613762652d336665393037; asc 3caae5f2-4c0f-461f-a7be-3fe907; (total 36 bytes);
 5: len 0; hex ; asc ;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 0; hex ; asc ;;
 9: len 5; hex 9992f71ddb; asc      ;;
 10: len 5; hex 9992f723fb; asc    # ;;
 11: len 12; hex 393637373930303233313530; asc 967790023150;;
 12: len 4; hex 00000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 15172 page no 237737 n bits 80 index `PRIMARY` of table `db_application`.`trip_record_tmp_b` trx id 603580587 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 13; compact format; info bits 32
 0: len 30; hex 383462633266333532353532613532343161363030663636313433326263; asc 84bc2f352552a5241a600f661432bc; (total 32 bytes);
 1: len 6; hex 000023f9e8a7; asc   #   ;;
 2: len 7; hex 46000028081c52; asc F  (  R;;
 3: len 4; hex 6e756c6c; asc null;;
 4: len 30; hex 33636161653566322d346330662d343631662d613762652d336665393037; asc 3caae5f2-4c0f-461f-a7be-3fe907; (total 36 bytes);
 5: len 0; hex ; asc ;;
 6: len 0; hex ; asc ;;
 7: len 0; hex ; asc ;;
 8: len 0; hex ; asc ;;
 9: len 5; hex 9992f71ddb; asc      ;;
 10: len 5; hex 9992f723fb; asc    # ;;
 11: len 12; hex 393637373930303233313530; asc 967790023150;;
 12: len 4; hex 00000001; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1070470523
Purge done for trx's n:o < 1070470521 undo n:o < 0 state: running but idle
History list length 2311
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 324245045, OS thread handle 0x7f9552bf0700, query id 1601328902 192.168.30.2 golo cleaning up
---TRANSACTION 0, not started
MySQL thread id 323103860, OS thread handle 0x7f975bb5c700, query id 1601328900 localhost root init
show engine innodb status
---TRANSACTION 1070440054, not started
MySQL thread id 324188163, OS thread handle 0x7f9552cf4700, query id 1601280049 192.168.30.83 golo cleaning up
---TRANSACTION 1070440060, not started
MySQL thread id 324188149, OS thread handle 0x7f975814c700, query id 1601280080 192.168.30.83 golo cleaning up
---TRANSACTION 1070467755, not started
MySQL thread id 324169376, OS thread handle 0x7f9751e20700, query id 1601323957 192.168.30.81 golo cleaning up
---TRANSACTION 1070460753, not started
MySQL thread id 324158238, OS thread handle 0x7f975294c700, query id 1601314632 192.168.30.81 golo cleaning up
---TRANSACTION 1070468351, not started
MySQL thread id 324145850, OS thread handle 0x7f95881ca700, query id 1601325052 192.168.30.81 golo cleaning up
---TRANSACTION 1070464368, not started
MySQL thread id 324066670, OS thread handle 0x7f97580ca700, query id 1601319626 192.168.30.80 golo cleaning up
---TRANSACTION 1070462151, not started
MySQL thread id 324064818, OS thread handle 0x7f9589c73700, query id 1601317045 192.168.30.80 golo cleaning up
---TRANSACTION 1070469584, not started
MySQL thread id 324048910, OS thread handle 0x7f955328a700, query id 1601327292 192.168.30.81 golo cleaning up
---TRANSACTION 1070461303, not started
MySQL thread id 324044580, OS thread handle 0x7f955a34e700, query id 1601315602 192.168.30.81 golo cleaning up
---TRANSACTION 1070469095, not started
MySQL thread id 324040723, OS thread handle 0x7f958a24a700, query id 1601326457 192.168.30.81 golo cleaning up
---TRANSACTION 1070424669, not started
MySQL thread id 324026147, OS thread handle 0x7f9589d77700, query id 1601270601 192.168.30.80 golo cleaning up
---TRANSACTION 1070462337, not started
MySQL thread id 324025340, OS thread handle 0x7f958aebb700, query id 1601317353 192.168.30.80 golo cleaning up
---TRANSACTION 1070392027, not started
MySQL thread id 324021109, OS thread handle 0x7f95589a9700, query id 1601231292 192.168.30.81 golo cleaning up
---TRANSACTION 1070317177, not started
MySQL thread id 324019747, OS thread handle 0x7f9588c33700, query id 1601086454 192.168.30.81 golo cleaning up
---TRANSACTION 1070464317, not started
MySQL thread id 324019274, OS thread handle 0x7f9588f80700, query id 1601319591 192.168.30.80 golo cleaning up
---TRANSACTION 1070464407, not started
MySQL thread id 324019261, OS thread handle 0x7f958ba28700, query id 1601319654 192.168.30.80 golo cleaning up
---TRANSACTION 1070458274, not started
MySQL thread id 324019214, OS thread handle 0x7f9588db9700, query id 1601310253 192.168.30.81 golo cleaning up
---TRANSACTION 1070469575, not started
MySQL thread id 324018903, OS thread handle 0x7f95596dd700, query id 1601327279 192.168.30.81 golo cleaning up
---TRANSACTION 1070430739, not started
MySQL thread id 324018585, OS thread handle 0x7f955a8e4700, query id 1601279466 192.168.30.80 golo cleaning up
---TRANSACTION 1070469211, not started
MySQL thread id 324018544, OS thread handle 0x7f9550b2f700, query id 1601326672 192.168.30.80 golo cleaning up
---TRANSACTION 1070469583, not started
MySQL thread id 324018422, OS thread handle 0x7f958abf0700, query id 1601327282 192.168.30.81 golo cleaning up
---TRANSACTION 1070462360, not started
MySQL thread id 324018244, OS thread handle 0x7f95585da700, query id 1601317364 192.168.30.80 golo cleaning up
---TRANSACTION 1070459911, not started
MySQL thread id 324018088, OS thread handle 0x7f9551598700, query id 1601313158 192.168.30.81 golo cleaning up
---TRANSACTION 1070462363, not started
MySQL thread id 324017553, OS thread handle 0x7f955bbef700, query id 1601317388 192.168.30.80 golo cleaning up
---TRANSACTION 1070469079, not started
MySQL thread id 323037712, OS thread handle 0x7f955161a700, query id 1601326435 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070446154, not started
MySQL thread id 322754879, OS thread handle 0x7f955b4d3700, query id 1601290918 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070456817, not started
MySQL thread id 322593679, OS thread handle 0x7f958a75e700, query id 1601307649 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070444417, not started
MySQL thread id 322593678, OS thread handle 0x7f975bada700, query id 1601288051 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070469841, not started
MySQL thread id 322111135, OS thread handle 0x7f9752a91700, query id 1601327742 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070388741, not started
MySQL thread id 321995454, OS thread handle 0x7f97518cb700, query id 1601199830 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070469814, not started
MySQL thread id 320882470, OS thread handle 0x7f955aaec700, query id 1601327705 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461058, not started
MySQL thread id 320882469, OS thread handle 0x7f9753270700, query id 1601315215 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461102, not started
MySQL thread id 320882468, OS thread handle 0x7f9558aad700, query id 1601315292 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469263, not started
MySQL thread id 320882471, OS thread handle 0x7f958bcf3700, query id 1601326747 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070463623, not started
MySQL thread id 320882466, OS thread handle 0x7f975bb1b700, query id 1601319054 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469637, not started
MySQL thread id 320882467, OS thread handle 0x7f9751272700, query id 1601327385 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070464268, not started
MySQL thread id 320882465, OS thread handle 0x7f9558c33700, query id 1601319551 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070463931, not started
MySQL thread id 320882438, OS thread handle 0x7f9551147700, query id 1601319300 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070459670, not started
MySQL thread id 320882436, OS thread handle 0x7f9559c32700, query id 1601312775 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070463601, not started
MySQL thread id 320882437, OS thread handle 0x7f958aa29700, query id 1601319034 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070463600, not started
MySQL thread id 320882434, OS thread handle 0x7f955334d700, query id 1601319033 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461193, not started
MySQL thread id 320882435, OS thread handle 0x7f958b186700, query id 1601315440 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461259, not started
MySQL thread id 320882433, OS thread handle 0x7f975006b700, query id 1601315539 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070462569, not started
MySQL thread id 320882420, OS thread handle 0x7f9752334700, query id 1601317755 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469631, not started
MySQL thread id 320882419, OS thread handle 0x7f9750d5e700, query id 1601327369 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469160, not started
MySQL thread id 320882417, OS thread handle 0x7f95531c7700, query id 1601326577 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469193, not started
MySQL thread id 320882415, OS thread handle 0x7f9559aac700, query id 1601326642 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469630, not started
MySQL thread id 320882416, OS thread handle 0x7f9553e38700, query id 1601327368 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469232, not started
MySQL thread id 320882418, OS thread handle 0x7f9829c02700, query id 1601326699 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070464585, not started
MySQL thread id 320882394, OS thread handle 0x7f958bb2c700, query id 1601319804 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070460833, not started
MySQL thread id 320882392, OS thread handle 0x7f9558599700, query id 1601314788 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070464651, not started
MySQL thread id 320882391, OS thread handle 0x7f9559f3e700, query id 1601319880 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070464741, not started
MySQL thread id 320882393, OS thread handle 0x7f9753374700, query id 1601319993 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070462494, not started
MySQL thread id 320882390, OS thread handle 0x7f975157e700, query id 1601317645 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070460844, not started
MySQL thread id 320882389, OS thread handle 0x7f958ae39700, query id 1601314824 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461240, not started
MySQL thread id 320388300, OS thread handle 0x7f95883d2700, query id 1601315514 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070455246, not started
MySQL thread id 318289563, OS thread handle 0x7f975bb9d700, query id 1601304755 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070431949, not started
MySQL thread id 318289562, OS thread handle 0x7f95528e4700, query id 1601268805 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070457142, not started
MySQL thread id 318289561, OS thread handle 0x7f955b38e700, query id 1601308187 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070466912, not started
MySQL thread id 316814334, OS thread handle 0x7f9553d34700, query id 1601322496 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070460149, not started
MySQL thread id 316814333, OS thread handle 0x7f9552b6e700, query id 1601313606 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070457957, not started
MySQL thread id 316814332, OS thread handle 0x7f9829bc1700, query id 1601309665 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070458005, not started
MySQL thread id 316814331, OS thread handle 0x7f955b410700, query id 1601309737 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070398229, not started
MySQL thread id 316292792, OS thread handle 0x7f97521ae700, query id 1601214382 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070367049, not started
MySQL thread id 315542217, OS thread handle 0x7f9751b96700, query id 1601163050 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070324250, not started
MySQL thread id 315542216, OS thread handle 0x7f955a3d0700, query id 1601095693 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470240, not started
MySQL thread id 314143414, OS thread handle 0x7f955269b700, query id 1601328422 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070463956, not started
MySQL thread id 314143415, OS thread handle 0x7f9589d36700, query id 1601319318 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070463621, not started
MySQL thread id 314143412, OS thread handle 0x7f97511f0700, query id 1601319053 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070459646, not started
MySQL thread id 314143391, OS thread handle 0x7f958a69b700, query id 1601312741 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461192, not started
MySQL thread id 314143390, OS thread handle 0x7f95890c5700, query id 1601315439 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070461230, not started
MySQL thread id 314143392, OS thread handle 0x7f958abaf700, query id 1601315501 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070462519, not started
MySQL thread id 314143367, OS thread handle 0x7f9751ee3700, query id 1601317684 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070460834, not started
MySQL thread id 314143334, OS thread handle 0x7f955b9a6700, query id 1601314789 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070462446, not started
MySQL thread id 314143336, OS thread handle 0x7f9552597700, query id 1601317565 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070393828, not started
MySQL thread id 314056482, OS thread handle 0x7f9551926700, query id 1601206090 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070433350, not started
MySQL thread id 314056479, OS thread handle 0x7f97530ea700, query id 1601271249 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070468574, not started
MySQL thread id 314056483, OS thread handle 0x7f958a515700, query id 1601325441 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070457875, not started
MySQL thread id 314056480, OS thread handle 0x7f9559b6f700, query id 1601309538 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070462447, not started
MySQL thread id 313953810, OS thread handle 0x7f9558efe700, query id 1601317566 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469722, not started
MySQL thread id 313953808, OS thread handle 0x7f95582ce700, query id 1601327536 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070466875, not started
MySQL thread id 312868188, OS thread handle 0x7f95518a4700, query id 1601322384 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070460107, not started
MySQL thread id 312868189, OS thread handle 0x7f975147a700, query id 1601313521 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070469111, not started
MySQL thread id 312868187, OS thread handle 0x7f9751231700, query id 1601326498 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070462225, not started
MySQL thread id 312868186, OS thread handle 0x7f9588aee700, query id 1601317176 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070469161, not started
MySQL thread id 311706786, OS thread handle 0x7f955a083700, query id 1601326578 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070462182, not started
MySQL thread id 310850324, OS thread handle 0x7f958befb700, query id 1601317100 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070399001, not started
MySQL thread id 310801655, OS thread handle 0x7f9589390700, query id 1601215738 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070428532, not started
MySQL thread id 310801651, OS thread handle 0x7f9588bf2700, query id 1601262685 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070269398, not started
MySQL thread id 310801650, OS thread handle 0x7f9552f7e700, query id 1601010851 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070442617, not started
MySQL thread id 310801649, OS thread handle 0x7f958b145700, query id 1601284732 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070383279, not started
MySQL thread id 310708348, OS thread handle 0x7f955279f700, query id 1601189444 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070464247, not started
MySQL thread id 309183152, OS thread handle 0x7f9552aec700, query id 1601319537 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070469672, not started
MySQL thread id 309182757, OS thread handle 0x7f975b954700, query id 1601327443 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070464586, not started
MySQL thread id 307726396, OS thread handle 0x7f9589cb4700, query id 1601319805 192.168.30.61 xmpp cleaning up
---TRANSACTION 1070385559, not started
MySQL thread id 306492572, OS thread handle 0x7f955aaab700, query id 1601193423 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070343148, not started
MySQL thread id 294691150, OS thread handle 0x7f98282a6700, query id 1601125614 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070467716, not started
MySQL thread id 145183823, OS thread handle 0x7f95889a9700, query id 1601323904 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070469255, not started
MySQL thread id 145183822, OS thread handle 0x7f97532b1700, query id 1601326734 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070417764, not started
MySQL thread id 145183821, OS thread handle 0x7f955a1c8700, query id 1601246688 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070462284, not started
MySQL thread id 145183820, OS thread handle 0x7f9558f80700, query id 1601317280 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070383441, not started
MySQL thread id 145183819, OS thread handle 0x7f958be38700, query id 1601189753 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070396195, not started
MySQL thread id 145183818, OS thread handle 0x7f958a042700, query id 1601210722 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070429671, not started
MySQL thread id 145183817, OS thread handle 0x7f9588dfa700, query id 1601264736 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070430793, not started
MySQL thread id 145175054, OS thread handle 0x7f9828120700, query id 1601266760 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070407812, not started
MySQL thread id 145175052, OS thread handle 0x7f958be79700, query id 1601228850 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070416589, not started
MySQL thread id 145174943, OS thread handle 0x7f9751ddf700, query id 1601244964 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070452547, not started
MySQL thread id 145162074, OS thread handle 0x7f95535d7700, query id 1601300135 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070462453, not started
MySQL thread id 145127933, OS thread handle 0x7f9751f24700, query id 1601317581 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470517, not started
MySQL thread id 145124571, OS thread handle 0x7f9553b6d700, query id 1601328895 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470122, not started
MySQL thread id 145124570, OS thread handle 0x7f958b6db700, query id 1601328241 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470081, not started
MySQL thread id 145124569, OS thread handle 0x7f958b38e700, query id 1601328170 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470388, not started
MySQL thread id 145124568, OS thread handle 0x7f9829f4f700, query id 1601328696 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470417, not started
MySQL thread id 145124567, OS thread handle 0x7f9750b97700, query id 1601328743 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470367, not started
MySQL thread id 145124563, OS thread handle 0x7f975b78d700, query id 1601328666 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470341, not started
MySQL thread id 145111664, OS thread handle 0x7f9553410700, query id 1601328606 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470434, not started
MySQL thread id 145111663, OS thread handle 0x7f97512f4700, query id 1601328771 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070469944, not started
MySQL thread id 145106910, OS thread handle 0x7f955b2cb700, query id 1601327945 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470229, not started
MySQL thread id 145056839, OS thread handle 0x7f9558085700, query id 1601328403 192.168.30.60 xmpp cleaning up
---TRANSACTION 1070470521, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
MySQL thread id 322593677, OS thread handle 0x7f9558391700, query id 1601328903 192.168.30.60 xmpp update
insert into connection_log values(null, "1037855", "online", "192.168.30.60:10207", "android", "address:::ffff:121.33.207.162 user-agent:golo/3.1.2/android/19", "2014-06-20 15:07:49")
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
519478111 OS file reads, 475571232 OS file writes, 405080434 OS fsyncs
25.65 reads/s, 16384 avg bytes/read, 156.16 writes/s, 156.16 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 7778, seg size 7780, 20096289 merges
merged operations:
 insert 21258675, delete mark 4422649, delete 378053
discarded operations:
 insert 5, delete mark 0, delete 0
Hash table size 4730647, node heap has 955 buffer(s)
3196.53 hash searches/s, 746.75 non-hash searches/s
---
LOG
---
Log sequence number 224036232063
Log flushed up to   224036231846
Pages flushed up to 224029757156
Last checkpoint at  224029757156
0 pending log writes, 0 pending chkp writes
341386815 log i/o's done, 156.16 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2197815296; in additional pool allocated 0
Dictionary memory allocated 9119775
Buffer pool size   131064
Free buffers       8188
Database pages     121921
Old database pages 44843
Modified db pages  3048
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5805713280, not young 3709436704
22.95 youngs/s, 697.59 non-youngs/s
Pages read 542477273, created 3579928, written 159627746
25.65 reads/s, 0.98 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 13 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 121921, unzip_LRU len: 0
I/O sum[9304]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   16383
Free buffers       1022
Database pages     15240
Old database pages 5606
Modified db pages  500
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 746573182, not young 454977557
2.60 youngs/s, 48.53 non-youngs/s
Pages read 67686047, created 498875, written 23889464
2.60 reads/s, 0.05 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 5 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15240, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   16383
Free buffers       1024
Database pages     15239
Old database pages 5605
Modified db pages  385
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 745312523, not young 484035409
4.65 youngs/s, 158.09 non-youngs/s
Pages read 66102931, created 479874, written 17872945
4.09 reads/s, 0.12 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 29 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15239, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   16383
Free buffers       1024
Database pages     15246
Old database pages 5607
Modified db pages  265
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 738031948, not young 535011002
2.26 youngs/s, 3.42 non-youngs/s
Pages read 71570303, created 365905, written 11929674
1.93 reads/s, 0.02 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: 15246, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   16383
Free buffers       1023
Database pages     15240
Old database pages 5606
Modified db pages  372
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 747680440, not young 499979987
2.95 youngs/s, 138.51 non-youngs/s
Pages read 74678009, created 452183, written 29956008
3.88 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 18 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15240, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   16383
Free buffers       1024
Database pages     15245
Old database pages 5607
Modified db pages  385
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 712059936, not young 425299410
2.05 youngs/s, 1.93 non-youngs/s
Pages read 67580487, created 416983, written 24324871
1.30 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: 15245, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   16383
Free buffers       1023
Database pages     15240
Old database pages 5605
Modified db pages  279
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 668418980, not young 411016765
2.58 youngs/s, 171.48 non-youngs/s
Pages read 62837956, created 391469, written 14684447
5.09 reads/s, 0.30 creates/s, 0.00 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 47 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15240, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   16383
Free buffers       1024
Database pages     15238
Old database pages 5604
Modified db pages  311
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 728371220, not young 446412100
1.72 youngs/s, 66.86 non-youngs/s
Pages read 65762204, created 454944, written 16726579
3.16 reads/s, 0.02 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 15 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15238, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   16383
Free buffers       1024
Database pages     15233
Old database pages 5603
Modified db pages  551
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 719265051, not young 452704474
4.14 youngs/s, 108.76 non-youngs/s
Pages read 66259336, created 519695, written 20243758
3.58 reads/s, 0.47 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 10 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 15233, unzip_LRU len: 0
I/O sum[1163]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 4565, id 140291497711360, state: sleeping
Number of rows inserted 80799106, updated 263372036, deleted 16848113, read 1598736248861
16.02 inserts/s, 140.18 updates/s, 0.07 deletes/s, 54663.38 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.04 sec)

mysql>




jack2tom
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
show innodb status 的实际拓展
MyISDB--Focus On Database
11-21 1224
结合这上一篇官方文档的翻译，接下来，我们拿实际的case来讲解。 摘抄： 这个比较详细！不错 很多人让我来阐述一下 SHOW INNODB STATUS 的输出信息, 了解 SHOW INNODB STATUS 都输出了些什么信息，并且我们能从这些信息中获取什么资讯，得以提高 MySQL 性能。 首先，让我们来了解一下 SHOW INNODB STATUS 输出的基础，它打印了
mysql-5.7 show engine innodb status 详解
weixin_34315189的博客
09-10 6540
一、show engine innodb status 简介： 　　show engine innodb status 是mysql提供的一个用于查看innodb引擎时间信息的工具，就目前来说有两处比较 　　常用的地方一、死锁分析 二、innodb内存使用情况   二、通过show engine innodb status 查看内存使用情况： 　　1、show variables like 'in...
【mysql死锁】示例 和讨论 “SHOW ENGINE INNODB STATUS“
最新发布
SHI_IHS的博客
06-30 366
根据信息可以查看到 曾经发生锁死锁。有个一事务被回滚了。我们复制出 Status信息进行查看。在第一个命令行执行 会报错提示死锁。使用navicat 来做示例。窗口2才能自行第一句成功。
show engine innodb status 详细介绍
weixin_33831673的博客
03-19 383
ContentsHeader1SEMAPHORES. 1LATEST DETECTED DEADLOCK. 3TRANSACTIONS. 5什么是purge操作... 5FILE I/O.. 7INSERT BUFFER AND ADAPTIVE HASH INDEX. 7LOG. 8BUFFER POOL AND MEMORY. 8ROW OPERATIONS. 9SHOW...
MySQL show engine innodb status命令：显示InnoDB存储引擎的当前状态信息，用于诊断性能问题、死锁、内存使用情况以及事务处理等方面的问题
学亮编程手记
05-26 856
是一个非常重要的管理命令，用于显示InnoDB存储引擎的当前状态信息。这个命令能够提供大量有关InnoDB内部运作的详细信息，对于诊断性能问题、死锁、内存使用情况以及事务处理等方面的问题极其有用。可能会因为输出信息过多，特别是当存在大量死锁历史时，导致部分信息被截断。这种情况下，可以通过升级MySQL服务器到较新的版本或者调整MySQL客户端的输出限制来解决。由于输出内容非常多且详细，通常需要根据具体的问题场景去分析特定的部分。例如，如果遇到死锁问题，重点关注。需要注意的是，在早期版本中，
mysql---show engine innodb status
Accpwo的专栏
02-17 282
[color=blue]show engine innodb status[/color] [color=green]===================================== 2017-02-17 18:26:07 7f14a2411700 INNODB MONITOR OUTPUT ===================================== Per ...
show engine innodb status显示信息不全如何解决
12-15
问题： 执行 show engine innodb status\G 时，显示的信息不全，DEADLOCK相关信息太多，后面的都没了 原因： 这是mysql客户端的一个bug：BUG#19825，交互式客户端限制了输出信息最大为 64KB，因此更多的信息无法显示...
mysql innodb死锁问题详解.docx
01-15
5. 利用InnoDB监控：通过`SHOW ENGINE INNODB STATUS`和`INNODB_LOCKS`、`INNODB_LOCK_WAITS`等表进行深入分析，找出引发死锁的具体原因，如唯一索引冲突或特定类型的锁导致的阻塞。 在分析过程中，需要注意的是： ...
InnoDB数据库死锁问题处理
12-16
4. 使用死锁检测工具，如`SHOW ENGINE INNODB STATUS`，定期检查并分析死锁情况。 了解和掌握InnoDB的锁机制以及如何处理死锁是每个数据库管理员和开发人员必备的技能，因为即使在精心设计的系统中，死锁也可能偶尔...
详解MySQL（InnoDB）是如何处理死锁的
09-09
1. 利用`SHOW ENGINE INNODB STATUS`命令来检查死锁原因，或者在调试阶段开启`innodb_print_all_deadlocks`收集死锁日志。 2. 尽量避免使用`LOCK TABLES`，而是使用事务来管理操作。 3. 避免长时间运行的事务，确保...
show engine innodb status
duxingxia356的专栏
03-11 416
注意：以下使用mysql5.5.24版本做解读，mysql5.6.x和5.7.x输出内容有些地方有调整。 1.第一段是头部信息，它仅仅声明了输出的开始，其内容包括当前的日期和时间，以及自上次输出以来经过的时长。 ===================================== 16012912:07:26INNODBMONITOROUTPUT#第二行是当前日期和时...
MySQL5.7-show engine innodb status
KAJSA_WANG的博客
03-15 1193
5.7.19mysql&gt; show engine innodb status;| Type   | Name | Status | InnoDB |      |                     #头部信息=====================================2018-03-15 09:20:38 0x7f845bb36700 INNODB MONITOR OUT...
【mysql】show engine innodb status命令
weixin_44580764的博客
03-18 176
执行结果： 'InnoDB', '', ' ===================================== 2020-03-18 10:47:11 3994 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds...
show engine innodb status分析
weixin_34234829的博客
11-26 190
2019独角兽企业重金招聘Python工程师标准>>> ...
show engine innodb status输出
longxibendi的专栏
01-13 3027
前言： show innodb status 网上的资料很多。自认为，这个是比较全面的。其中的示例取自不同的show innodb status的输出结果。 关于mysql的几个问题： 1.mysql并发低，是因为io的问题么？ 2. 为什么相同硬件，oracle比mysql的qps高？ 3. 如果一次物理io,耗时500nm，那会怎样？ 4.buffe
mysql死锁分析工具show engine innodb status
热门推荐
CRUD工程师
08-29 1万+
​在之前的文章（《mysql运维脚本与个人理解》）中介绍了Mysql运维的几个简单脚本。 但最后的死锁根因分析因为缺少实践经验与分析文章搁置了，这里来补上。
mysql查看引擎状态_mysql 查看存储引擎的状态 show engine innodb status 详解
weixin_42290401的博客
01-19 688
首先，让我们来了解一下 SHOW INNODB STATUS 输出的基础，它打印了很多关于 InnoDB 内部性能相关的计数器、统计、事务处理信息等。在 MySQL 5 中，InnoDB 的性能统计结果也在SHOW STATUS结果中显示了。大部分和 SHOW INNODB STATUS 的其他信息相同，在旧版本中还没有这个功能。SHOW INNODB STATUS 中的很多统计值都是每秒更新...
SHOW ENGINE INNODB STATUS如何找到线程所在的事务 ID
06-01
要找到 `SHOW ENGINE INNODB STATUS` 命令输出的 InnoDB 存储引擎状态信息中某个线程所在的事务 ID，可以按照以下步骤进行： 1. 执行 `SHOW ENGINE INNODB STATUS\G` 命令，将 InnoDB 存储引擎状态信息以详细的方式输出到终端或客户端。 2. 在输出的状态信息中，找到 `LATEST FOREIGN KEY ERROR` 或 `LATEST DETECTED DEADLOCK` 部分。在这个部分中，通常会列出最近发生的死锁或外键错误的相关信息。 3. 在这个部分中，找到被称为“TRANSACTION”的部分。该部分会列出当前正在运行或等待的事务的详细信息，包括事务 ID、事务状态、锁定的对象等。 4. 在事务列表中找到您要查找的线程所在的事务。您可以根据线程 ID 在列表中查找到该事务，并在该事务的信息中找到事务 ID。 例如，以下是 `SHOW ENGINE INNODB STATUS\G` 命令输出的状态信息中事务列表的示例： ``` ------------ TRANSACTIONS ------------ Trx id counter 0 123456 Purge done for trx's n:o < 0 123455 undo n:o < 0 0 History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 123455, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 123, OS thread handle 0x7fb3b5c1d700, query id 1234 localhost root updating UPDATE `test` SET `value` = '123' WHERE `id` = 1 ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1234 page no 1 n bits 72 index `PRIMARY` of table `test` trx id 0 123455 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 ``` 在这个例子中，事务列表中包含了当前正在运行的事务和等待锁定的事务。您可以根据线程 ID 查找到对应的事务，例如这里线程 ID 为 `123`，则可以在事务列表中找到该线程所在的事务，该事务 ID 为 `0 123455`。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值