I run out of mysql connections during heavy load.
Below is the innodb status
mysql> show innodb status\G;
*************************** 1. row ***************************
Status:
070125 21:30:39 INNODB MONITOR OUTPUT
Per second averages calculated from the last 54 seconds
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 2105275, signal count 2079336
Mutex spin waits 356003404, rounds 430389184, OS waits 745071
RW-shared spins 7349358, OS waits 656984; RW-excl spins 3829780, OS waits 123067
LATEST FOREIGN KEY ERROR
070125 21:27:30 Transaction:
TRANSACTION 0 1038857271, ACTIVE 0 sec, process no 22616, OS thread id 1389808560 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
10 lock struct(s), heap size 1024, undo log entries 1
MySQL thread id 2926054, query id 1219694394 192.168.3.20 casero update
insert into MEDIACOLLECTION_ITEM (MEDIACOLLECTIONID, MEDIAITEMID) values (275237, 1364537)
Foreign key constraint fails for table GEMINI/MEDIACOLLECTION_ITEM:
,
CONSTRAINT FK1B5F0BD0A32E98CC FOREIGN KEY (MEDIAITEMID) REFERENCES MEDIAITEM (MEDIAITEMID)
Trying to add in child table, in index FK1B5F0BD0A32E98CC tuple:
DATA TUPLE: 2 fields;
0: len 4; hex 8014d239; asc 9;; 1: len 4; hex 80043325; asc 3%;;
But in parent table GEMINI/MEDIAITEM, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 37; compact format; info bits 0
0: len 4; hex 8014d24a; asc J;; 1: len 6; hex 00003bb61711; asc ; ;; 2: len 7; hex 0000003e8c25dc; asc > % ;; 3: len 1; hex 4d; asc M;; 4: len 26; hex 33306231302d31306666366337323537642d67656d696e693031; asc 30b10-10ff6c7257d-gemini01;; 5: len 18; hex 6170706c69636174696f6e2f6d73776f7264; asc application/msword;; 6: len 17; hex 53656374696f6e2034202d2030332d3031; asc Section 4 - 03-01;; 7: len 8; hex 80001240efe8de60; asc @ ;; 8: len 8; hex 80001240efe8de60; asc @ ;; 9: SQL NULL; 10: SQL NULL; 11: len 30; hex 66696c653a2f2f2f73746f726167652f70726f64756374696f6e2f737562 ; asc file:///storage/production/sub;…(truncated); 12: len 1; hex 44; asc D;; 13: len 21; hex 53656374696f6e2034202d2030332d30312e646f63; asc Section 4 - 03-01.doc;; 14: len 17; hex 313136383633373233343736382e646f63; asc 1168637234768.doc;; 15: len 0; hex ; asc ;; 16: len 1; hex 00; asc ;; 17: len 1; hex 00; asc ;; 18: len 1; hex 00; asc ;; 19: len 4; hex 80008200; asc ;; 20: len 1; hex 54; asc T;; 21: len 1; hex 53; asc S;; 22: len 4; hex 804ea079; asc N y;; 23: len 4; hex 800084d8; asc ;; 24: len 30; hex aced0005737200176a6176612e7574696c2e4c696e6b6564486173684d61 ; asc sr java.util.LinkedHashMa;…(truncated); 25: SQL NULL; 26: SQL NULL; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: SQL NULL; 31: SQL NULL; 32: SQL NULL; 33: SQL NULL; 34: SQL NULL; 35: SQL NULL; 36: SQL NULL;
LATEST DETECTED DEADLOCK
070125 21:27:29
*** (1) TRANSACTION:
TRANSACTION 0 1038857248, ACTIVE 0 sec, process no 22616, OS thread id 1334033328 inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024
MySQL thread id 2926081, query id 1219694112 192.168.3.20 casero update
insert into MEDIACOLLECTION_ITEM (MEDIACOLLECTIONID, MEDIAITEMID) values (275237, 1363308)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 294220 n bits 272 index PRIMARY of table GEMINI/MEDIACOLLECTION trx id 0 1038857248 lock mode S locks rec but not gap waiting
Record lock, heap no 204 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80043325; asc 3%;; 1: len 6; hex 00003debb3ff; asc = ;; 2: len 7; hex 0000004b80339b; asc K 3 ;; 3: len 7; hex 4c696272617279; asc Library;; 4: len 7; hex 4c696272617279; asc Library;; 5: SQL NULL; 6: len 8; hex 80001240f0af1648; asc @ H;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 1038857215, ACTIVE 0 sec, process no 22616, OS thread id 1162513328 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 320, undo log entries 57
MySQL thread id 2925879, query id 1219694181 192.168.3.20 casero Updating
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 20:52:56’ where MEDIACOLLECTIONID=275238
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 294220 n bits 272 index PRIMARY of table GEMINI/MEDIACOLLECTION trx id 0 1038857215 lock_mode X locks rec but not gap
Record lock, heap no 204 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80043325; asc 3%;; 1: len 6; hex 00003debb3ff; asc = ;; 2: len 7; hex 0000004b80339b; asc K 3 ;; 3: len 7; hex 4c696272617279; asc Library;; 4: len 7; hex 4c696272617279; asc Library;; 5: SQL NULL; 6: len 8; hex 80001240f0af1648; asc @ H;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 294220 n bits 272 index PRIMARY of table GEMINI/MEDIACOLLECTION trx id 0 1038857215 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80043326; asc 3&;; 1: len 6; hex 00003debb418; asc = ;; 2: len 7; hex 0000004b801c5d; asc K ];; 3: len 7; hex 53686f65626f78; asc Shoebox;; 4: len 7; hex 53686f65626f78; asc Shoebox;; 5: SQL NULL; 6: len 8; hex 80001240f0af1648; asc @ H;; 7: len 8; hex 80001240efbac79f; asc @ ;; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;
*** WE ROLL BACK TRANSACTION (1)
TRANSACTIONS
Trx id counter 0 1038865717
Purge done for trx’s n:o < 0 1038862273 undo n:o < 0 1
History list length 145
Total number of lock structs in row lock hash table 8
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 0 0, not started, process no 22616, OS thread id 1184779184
MySQL thread id 2926288, query id 1219787608 localhost root
show innodb status
—TRANSACTION 0 1038865715, not started, process no 22616, OS thread id 1322253232
MySQL thread id 2926337, query id 1219787595 192.168.3.41 casero
—TRANSACTION 0 1038865716, not started, process no 22616, OS thread id 1596824496
MySQL thread id 2926333, query id 1219787607 192.168.3.40 casero
—TRANSACTION 0 1038865651, not started, process no 22616, OS thread id 1250347952
MySQL thread id 2926326, query id 1219786733 192.168.3.41 casero
—TRANSACTION 0 1038865649, not started, process no 22616, OS thread id 1243409328
MySQL thread id 2926310, query id 1219786686 192.168.3.40 casero
—TRANSACTION 0 0, not started, process no 22616, OS thread id 1290038192
mysql tables in use 1, locked 1
MySQL thread id 2926298, query id 1219769438 192.168.3.20 casero Table lock
insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values (‘Library’, ‘Library’, ‘’, ‘date’, 1, 0, ‘D’, ‘I’, ‘T’, 49570)
—TRANSACTION 0 1038864275, not started, process no 22616, OS thread id 1537239984
mysql tables in use 1, locked 1
MySQL thread id 2926297, query id 1219769456 192.168.3.20 casero Table lock
insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values (‘Library’, ‘Library’, ‘’, ‘date’, 1, 0, ‘D’, ‘I’, ‘T’, 49569)
—TRANSACTION 0 1038864165, not started, process no 22616, OS thread id 1267125168
mysql tables in use 1, locked 1
MySQL thread id 2926267, query id 1219769414 192.168.3.21 casero Table lock
insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values (‘Library’, ‘Library’, ‘’, ‘date’, 1, 0, ‘D’, ‘I’, ‘T’, 49569)
—TRANSACTION 0 1038864268, not started, process no 22616, OS thread id 1180965808
mysql tables in use 1, locked 1
MySQL thread id 2926263, query id 1219769431 192.168.3.20 casero Table lock
insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values (‘Library’, ‘Library’, ‘’, ‘date’, 1, 0, ‘D’, ‘I’, ‘T’, 49568)
—TRANSACTION 0 1038861362, not started, process no 22616, OS thread id 1326447536
mysql tables in use 1, locked 1
MySQL thread id 2926203, query id 1219769303 192.168.3.21 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 21:30:03’ where MEDIACOLLECTIONID=266711
—TRANSACTION 0 1038862247, not started, process no 22616, OS thread id 1334033328
mysql tables in use 1, locked 1
MySQL thread id 2926081, query id 1219769551 192.168.3.20 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=? where MEDIACOLLECTIONID=?
—TRANSACTION 0 1038857290, not started, process no 22616, OS thread id 1351412656
mysql tables in use 1, locked 1
MySQL thread id 2926058, query id 1219769434 192.168.3.20 casero Table lock
insert into MEDIACOLLECTION (NAME, TITLE, DESCRIPTION, SORTFIELD, READONLY, COMMENTS, SORTORDER, CONTENTTYPE, VISIBILITY, CUSTOMERID) values (‘Library’, ‘Library’, ‘’, ‘date’, 1, 0, ‘D’, ‘I’, ‘T’, 49567)
—TRANSACTION 0 1038865713, not started, process no 22616, OS thread id 1250749360
MySQL thread id 2926036, query id 1219787571 192.168.3.21 casero
—TRANSACTION 0 1038807031, not started, process no 22616, OS thread id 1176746928
MySQL thread id 2733303, query id 1219784813 192.168.3.50 casero
—TRANSACTION 0 1038865290, not started, process no 22616, OS thread id 1185381296
MySQL thread id 2733302, query id 1219782096 192.168.3.50 casero
—TRANSACTION 0 1038381180, not started, process no 22616, OS thread id 1288231856
MySQL thread id 2393087, query id 1219787514 192.168.3.51 casero
—TRANSACTION 0 1038865321, not started, process no 22616, OS thread id 1515682736
MySQL thread id 2393086, query id 1219782421 192.168.3.51 casero
—TRANSACTION 0 1038806720, not started, process no 22616, OS thread id 1257040816
MySQL thread id 2129399, query id 1219787355 192.168.3.52 casero
—TRANSACTION 0 1038865283, not started, process no 22616, OS thread id 1258937264
MySQL thread id 2129411, query id 1219782042 192.168.3.52 casero
—TRANSACTION 0 1038547335, not started, process no 22616, OS thread id 1341217712
MySQL thread id 1977835, query id 1219787433 192.168.3.50 casero
—TRANSACTION 0 1038731303, not started, process no 22616, OS thread id 1239415728
MySQL thread id 1977830, query id 1219787532 192.168.3.51 casero
—TRANSACTION 0 1038697607, not started, process no 22616, OS thread id 1522252720
MySQL thread id 1977839, query id 1219787535 192.168.3.51 casero
—TRANSACTION 0 1038713191, not started, process no 22616, OS thread id 1271720880
MySQL thread id 1977848, query id 1219787466 192.168.3.50 casero
—TRANSACTION 0 1038865365, not started, process no 22616, OS thread id 1577307056
MySQL thread id 1977861, query id 1219782974 192.168.3.50 casero
—TRANSACTION 0 1038865327, not started, process no 22616, OS thread id 1534684080
MySQL thread id 1977846, query id 1219782483 192.168.3.51 casero
—TRANSACTION 0 1038865705, not started, process no 22616, OS thread id 1514077104
MySQL thread id 1977842, query id 1219787463 192.168.3.50 casero
—TRANSACTION 0 1038865626, not started, process no 22616, OS thread id 1457658800
MySQL thread id 1977837, query id 1219786368 192.168.3.51 casero
—TRANSACTION 0 1038716303, not started, process no 22616, OS thread id 1516886960
MySQL thread id 332, query id 1219775003 192.168.3.50 casero
—TRANSACTION 0 1038727519, not started, process no 22616, OS thread id 1462221744
MySQL thread id 237, query id 1219782716 192.168.3.52 casero
—TRANSACTION 0 1038749044, not started, process no 22616, OS thread id 1491151792
MySQL thread id 286, query id 1219785405 192.168.3.52 casero
—TRANSACTION 0 1038832667, not started, process no 22616, OS thread id 1459538864
MySQL thread id 232, query id 1219787511 192.168.3.52 casero
—TRANSACTION 0 1038806431, not started, process no 22616, OS thread id 1513675696
MySQL thread id 313, query id 1219772785 192.168.3.51 casero
—TRANSACTION 0 1038745479, not started, process no 22616, OS thread id 1465781168
MySQL thread id 242, query id 1219761604 192.168.3.51 casero
—TRANSACTION 0 1038839293, not started, process no 22616, OS thread id 1468062640
MySQL thread id 275, query id 1219778312 192.168.3.50 casero
—TRANSACTION 0 1038832435, not started, process no 22616, OS thread id 1571613616
MySQL thread id 30, query id 1219787436 192.168.3.51 casero
—TRANSACTION 0 1038666195, not started, process no 22616, OS thread id 1495935920
MySQL thread id 291, query id 1219782533 192.168.3.50 casero
—TRANSACTION 0 1038483004, not started, process no 22616, OS thread id 1482300336
MySQL thread id 264, query id 1219787124 192.168.3.50 casero
—TRANSACTION 0 1038717320, not started, process no 22616, OS thread id 1484381104
MySQL thread id 269, query id 1219783364 192.168.3.51 casero
—TRANSACTION 0 1038856344, not started, process no 22616, OS thread id 1494330288
MySQL thread id 227, query id 1219781030 192.168.3.51 casero
—TRANSACTION 0 1038862736, not started, process no 22616, OS thread id 1539742640
MySQL thread id 668, query id 1219752209 192.168.3.52 casero
—TRANSACTION 0 1038856075, not started, process no 22616, OS thread id 1489071024
MySQL thread id 284, query id 1219785587 192.168.3.52 casero
—TRANSACTION 0 1038858150, not started, process no 22616, OS thread id 1481497520
MySQL thread id 272, query id 1219787478 192.168.3.52 casero
—TRANSACTION 0 1038806259, not started, process no 22616, OS thread id 1455778736
MySQL thread id 229, query id 1219787349 192.168.3.52 casero
—TRANSACTION 0 1038863542, not started, process no 22616, OS thread id 1568066480
MySQL thread id 425, query id 1219760606 192.168.3.52 casero
—TRANSACTION 0 1038739922, not started, process no 22616, OS thread id 1494731696
MySQL thread id 225, query id 1219787565 192.168.3.52 casero
—TRANSACTION 0 1038738836, not started, process no 22616, OS thread id 1456180144
MySQL thread id 222, query id 1219787529 192.168.3.50 casero
—TRANSACTION 0 1038864252, not started, process no 22616, OS thread id 1528622000
MySQL thread id 331, query id 1219769242 192.168.3.50 casero
—TRANSACTION 0 1038863389, not started, process no 22616, OS thread id 1513876400
MySQL thread id 320, query id 1219758839 192.168.3.51 casero
—TRANSACTION 0 1038865182, not started, process no 22616, OS thread id 1495735216
MySQL thread id 292, query id 1219780899 192.168.3.50 casero
—TRANSACTION 0 1038865559, not started, process no 22616, OS thread id 1495333808
MySQL thread id 285, query id 1219785510 192.168.3.52 casero
—TRANSACTION 0 1038864605, not started, process no 22616, OS thread id 1491352496
MySQL thread id 276, query id 1219773310 192.168.3.50 casero
—TRANSACTION 0 1038863650, not started, process no 22616, OS thread id 1482099632
MySQL thread id 274, query id 1219761843 192.168.3.50 casero
—TRANSACTION 0 1038865701, not started, process no 22616, OS thread id 1490951088
MySQL thread id 273, query id 1219787418 192.168.3.52 casero
—TRANSACTION 0 1038864327, not started, process no 22616, OS thread id 1482501040
MySQL thread id 268, query id 1219769982 192.168.3.51 casero
—TRANSACTION 0 1038864651, not started, process no 22616, OS thread id 1467861936
MySQL thread id 248, query id 1219773893 192.168.3.51 casero
—TRANSACTION 0 1038865542, not started, process no 22616, OS thread id 1459739568
MySQL thread id 244, query id 1219785325 192.168.3.52 casero
—TRANSACTION 0 1038863038, not started, process no 22616, OS thread id 1457257392
MySQL thread id 243, query id 1219755432 192.168.3.51 casero
—TRANSACTION 0 1038862041, not started, process no 22616, OS thread id 1599847344
MySQL thread id 231, query id 1219743944 192.168.3.52 casero
—TRANSACTION 0 1038865652, not started, process no 22616, OS thread id 1494932400
MySQL thread id 224, query id 1219786738 192.168.3.52 casero
—TRANSACTION 0 1038865171, not started, process no 22616, OS thread id 1600449456
MySQL thread id 1, query id 1219780761 192.168.3.51 casero
—TRANSACTION 0 1038865105, not started, process no 22616, OS thread id 1600048048
MySQL thread id 3, query id 1219779935 192.168.3.50 casero
—TRANSACTION 0 1038865630, ACTIVE 2 sec, process no 22616, OS thread id 1179761584
5 lock struct(s), heap size 320, undo log entries 7
MySQL thread id 2926294, query id 1219786706 192.168.3.40 casero
Trx read view will not see trx with id >= 0 1038865631, sees < 0 1038857288
—TRANSACTION 0 1038864286, ACTIVE 36 sec, process no 22616, OS thread id 1316006832
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 2924933, query id 1219769624 192.168.3.21 casero Table lock
delete from MEDIACOLLECTION where MEDIACOLLECTIONID=?
Trx read view will not see trx with id >= 0 1038864287, sees < 0 1038857288
—TRANSACTION 0 1038864278, ACTIVE 36 sec, process no 22616, OS thread id 1270918064
mysql tables in use 1, locked 1
MySQL thread id 2926193, query id 1219769461 192.168.3.20 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=? where MEDIACOLLECTIONID=?
Trx read view will not see trx with id >= 0 1038864279, sees < 0 1038857288
—TRANSACTION 0 1038864262, ACTIVE 36 sec, process no 22616, OS thread id 1389808560 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 2926054, query id 1219769295 192.168.3.20 casero Updating
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 21:33:47’ where MEDIACOLLECTIONID=275237
------- TRX HAS BEEN WAITING 36 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 294220 n bits 272 index PRIMARY of table GEMINI/MEDIACOLLECTION trx id 0 1038864262 lock_mode X locks rec but not gap waiting
Record lock, heap no 204 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 4; hex 80043325; asc 3%;; 1: len 6; hex 00003debb448; asc = H;; 2: len 7; hex 0000004b802760; asc K '`;; 3: len 7; hex 4c696272617279; asc Library;; 4: len 7; hex 4c696272617279; asc Library;; 5: SQL NULL; 6: len 8; hex 80001240f0aef70a; asc @ ;; 7: SQL NULL; 8: len 0; hex ; asc ;; 9: len 4; hex 64617465; asc date;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: SQL NULL; 13: len 1; hex 44; asc D;; 14: SQL NULL; 15: len 1; hex 49; asc I;; 16: len 1; hex 54; asc T;; 17: SQL NULL; 18: len 4; hex 8000a619; asc ;;
—TRANSACTION 0 1038862284, ACTIVE 86 sec, process no 22616, OS thread id 1499696048
mysql tables in use 1, locked 1
MySQL thread id 2926227, query id 1219769319 192.168.3.21 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 21:30:29’ where MEDIACOLLECTIONID=90114
Trx read view will not see trx with id >= 0 1038862285, sees < 0 1038857288
—TRANSACTION 0 1038862276, ACTIVE 86 sec, process no 22616, OS thread id 1340214192
mysql tables in use 1, locked 1
MySQL thread id 2926059, query id 1219769312 192.168.3.21 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 21:30:28’ where MEDIACOLLECTIONID=90114
Trx read view will not see trx with id >= 0 1038862277, sees < 0 1038857288
—TRANSACTION 0 1038862258, ACTIVE 87 sec, process no 22616, OS thread id 1283054512
mysql tables in use 1, locked 1
4 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 2925235, query id 1219769310 192.168.3.21 casero Table lock
delete from MEDIACOLLECTION where MEDIACOLLECTIONID=217252
Trx read view will not see trx with id >= 0 1038862259, sees < 0 1038857288
—TRANSACTION 0 1038862251, ACTIVE 87 sec, process no 22616, OS thread id 1517288368
mysql tables in use 1, locked 1
MySQL thread id 2923805, query id 1219769306 192.168.3.20 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 21:34:39’ where MEDIACOLLECTIONID=275237
Trx read view will not see trx with id >= 0 1038862252, sees < 0 1038857288
—TRANSACTION 0 1038857288, ACTIVE 189 sec, process no 22616, OS thread id 1162513328
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 5
MySQL thread id 2925879, query id 1219769328 192.168.3.20 casero Table lock
update MEDIACOLLECTION set DATEUPDATED=‘2007-01-25 20:52:59’ where MEDIACOLLECTIONID=275237
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 (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1755403 OS file reads, 44717711 OS file writes, 5540150 OS fsyncs
0.02 reads/s, 16384 avg bytes/read, 10.56 writes/s, 1.22 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf for space 0: size 1, free list len 12, seg size 14, is empty
Ibuf for space 0: size 1, free list len 12, seg size 14,
376007 inserts, 376007 merged recs, 208778 merges
Hash table size 4980539, used cells 833255, node heap has 895 buffer(s)
321.33 hash searches/s, 597.71 non-hash searches/s
LOG
Log sequence number 4 3914204403
Log flushed up to 4 3914204403
Last checkpoint at 4 3914199168
0 pending log writes, 0 pending chkp writes
23601495 log i/o’s done, 3.87 log i/o’s/second
BUFFER POOL AND MEMORY
Total memory allocated 1375224705; in additional pool allocated 10532352
Buffer pool size 76800
Free buffers 2
Database pages 75903
Modified db pages 43
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 7479502, created 160954, written 22697597
0.02 reads/s, 0.00 creates/s, 7.11 writes/s
Buffer pool hit rate 1000 / 1000
ROW OPERATIONS
0 queries inside InnoDB, 0 queries in queue
8 read views open inside InnoDB
Main thread process no. 22616, id 1644698544, state: sleeping
Number of rows inserted 5701927, updated 8778584, deleted 1611810, read 1599079593
0.61 inserts/s, 1.44 updates/s, 0.26 deletes/s, 19991.07 reads/s
END OF INNODB MONITOR OUTPUT
1 row in set, 1 warning (0.01 sec)
ERROR:
No query specified
mysql>