*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 090506 11:22:16 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 11 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3523802, signal count 3010012 Mutex spin waits 0, rounds 489625034, OS waits 1540087 RW-shared spins 6792995, OS waits 761832; RW-excl spins 18022902, OS waits 542627 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 090506 9:29:52 Transaction: TRANSACTION 0 381927089, ACTIVE 0 sec, process no 21566, OS thread id 1183189312 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 106477, query id 92015059 p8.fizzback.net 66.132.221.56 phs update insert into JBPM_BYTEBLOCK (PROCESSFILE_, INDEX_, BYTES_) values (5439370, 18, x'00BC71007E00B971007E00BD71007E00BE7571007E005300000005740012416D65726963612F53745F56696E63656E7471007E00BC71007E00B971007E00BD71007E00BE7571007E005300000005740015416D65726963612F53776966745F43757272656E7471007E009B71007E009C71007E009D71007E009E7571007E005300000005740013416D65726963612F54656775636967616C706171007E009B71007E009C71007E009D71007E009E7571007E00530000000574000D416D65726963612F5468756C6571007E00BC71007E00B971007E00BD71007E00BE7571007E005300000005740013416D65726963612F5468756E6465725F42617971007E00A271007 Foreign key constraint fails for table `phs_jbpm`.`JBPM_BYTEBLOCK`: , CONSTRAINT `FK_BYTEBLOCK_FILE` FOREIGN KEY (`PROCESSFILE_`) REFERENCES `JBPM_BYTEARRAY` (`ID_`) Trying to add in child table, in index `PRIMARY` tuple: DATA TUPLE: 5 fields; 0: len 8; hex 800000000052ff8a; asc R ;; 1: len 4; hex 80000012; asc ;; 2: len 6; hex 000016c3beb1; asc ;; 3: len 7; hex 80001c511c064a; asc Q J;; 4: len 1000; hex 00bc71007e00b971007e00bd71007e00be7571007e005300000005740012416d65726963612f53745f56696e63656e7471007e00bc71007e00b971007e00bd71007e00be7571007e005300000005740015416d65726963612f53776966745f43757272656e7471007e009b71007e009c71007e009d71007e009e7571007e005300000005740013416d65726963612f54656775636967616c706171007e009b71007e009c71007e009d71007e009e7571007e00530000000574000d416d65726963612f5468756c6571007e00bc71007e00b971007e00bd71007e00be7571007e005300000005740013416d65726963612f5468756e6465725f42617971007e00a271007e00a371007e00a471007e00a57571007e00530000000574000f416d65726963612f54696a75616e6171007e008971007e008a71007e008b71007e008c7571007e00530000000574000f416d65726963612f546f726f6e746f71007e00a271007e00a371007e00a471007e00a57571007e00530000000574000f416d65726963612f546f72746f6c6171007e00bc71007e00b971007e00bd71007e00be7571007e005300000005740011416d65726963612f56616e636f7576657271007e008971007e008a71007e008b71007e008c7571007e00530000000574000e416d65726963612f56697267696e71007e00bc71007e00b971007e00bd71007e00be7571007e005300000005740012416d65726963612f5768697465686f72736571007e008971007e008a71007e008b71007e008c7571007e005300000005740010416d65726963612f57696e6e6970656771007e009b71007e009c71007e009d71007e009e7571007e00530000000574000f416d65726963612f59616b7574617471007e00b471007e00b571007e00b671007e00b77571007e005300000005740013416d65726963612f59656c6c6f776b6e69666571007e009071007e009171007e009271007e00937571007e005300000005740010416e74617263746963612f43617365797400215765737465726e205374616e646172642054696d6520284175737472616c69612974000357535474001f5765737465726e2053756d6d65722054696d6520284175737472616c69612971007e02e97571007e005300000005740010416e74617263746963612f446176697374000a44617669732054696d657400044441565474001144617669732053756d6d65722054696d6574000544415653547571007e005300000005740019416e74617263746963612f44756d6f6e7444557276696c6c6574001544756d6f6e742d6427557276696c6c652054696d657400044444555474001c44756d6f6e742d6427557276696c6c652053756d6d65722054696d65; asc q ~ q ~ q ~ uq ~ S t America/St_Vincentq ~ q ~ q ~ q ~ uq ~ S t America/Swift_Currentq ~ q ~ q ~ q ~ uq ~ S t America/Tegucigalpaq ~ q ~ q ~ q ~ uq ~ S t America/Thuleq ~ q ~ q ~ q ~ uq ~ S t America/Thunder_Bayq ~ q ~ q ~ q ~ uq ~ S t America/Tijuanaq ~ q ~ q ~ q ~ uq ~ S t America/Torontoq ~ q ~ q ~ q ~ uq ~ S t America/Tortolaq ~ q ~ q ~ q ~ uq ~ S t America/Vancouverq ~ q ~ q ~ q ~ uq ~ S t America/Virginq ~ q ~ q ~ q ~ uq ~ S t America/Whitehorseq ~ q ~ q ~ q ~ uq ~ S t America/Winnipegq ~ q ~ q ~ q ~ uq ~ S t America/Yakutatq ~ q ~ q ~ q ~ uq ~ S t America/Yellowknifeq ~ q ~ q ~ q ~ uq ~ S t Antarctica/Caseyt !Western Standard Time (Australia)t WSTt Western Summer Time (Australia)q ~ uq ~ S t Antarctica/Davist Davis Timet DAVTt Davis Summer Timet DAVSTuq ~ S t Antarctica/DumontDUrvillet Dumont-d'Urville Timet DDUTt Dumont-d'Urville Summer Time;(total 1024 bytes); But in parent table `phs_jbpm`.`JBPM_BYTEARRAY`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 8; hex 800000000052ff8d; asc R ;; 1: len 6; hex 000016c3b503; asc ;; 2: len 7; hex 80003c00160121; asc < !;; 3: SQL NULL; 4: SQL NULL; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 090506 7:07:37 *** (1) TRANSACTION: TRANSACTION 0 381882002, ACTIVE 0 sec, process no 21566, OS thread id 1165084992 starting index read mysql tables in use 2, locked 2 LOCK WAIT 90 lock struct(s), heap size 14320, 14380 row lock(s) MySQL thread id 100095, query id 85920102 office.fizzback.net 79.123.32.19 phs preparing delete from JBPM_RUNTIMEACTION where PROCESSINSTANCE_ in (select ID_ from phs_jbpm.JBPM_PROCESSINSTANCE where END_ is not null) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 458790 n bits 328 index `PRIMARY` of table `phs_jbpm`.`JBPM_PROCESSINSTANCE` trx id 0 381882002 lock_mode X locks rec but not gap waiting Record lock, heap no 249 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 80000000001c4dbf; asc M ;; 1: len 6; hex 000016c30e87; asc ;; 2: len 7; hex 0000378014016a; asc 7 j;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 5: len 8; hex 80001245af7a4ad1; asc E zJ ;; 6: SQL NULL; 7: len 1; hex 00; asc ;; 8: len 8; hex 80000000000000fb; asc ;; 9: len 8; hex 80000000001c4df6; asc M ;; 10: SQL NULL; *** (2) TRANSACTION: TRANSACTION 0 381881998, ACTIVE 0 sec, process no 21566, OS thread id 1182656832 starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 23 lock struct(s), heap size 3024, 33 row lock(s), undo log entries 37 MySQL thread id 100086, query id 85920129 p8.fizzback.net 66.132.221.56 phs Updating update JBPM_PROCESSINSTANCE set VERSION_=2, KEY_=null, START_='2009-05-06 07:07:37', END_='2009-05-06 07:07:37', ISSUSPENDED_=0, PROCESSDEFINITION_=251, ROOTTOKEN_=1854966, SUPERPROCESSTOKEN_=null where ID_=1854911 and VERSION_=1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 458790 n bits 328 index `PRIMARY` of table `phs_jbpm`.`JBPM_PROCESSINSTANCE` trx id 0 381881998 lock mode S locks rec but not gap Record lock, heap no 249 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 80000000001c4dbf; asc M ;; 1: len 6; hex 000016c30e87; asc ;; 2: len 7; hex 0000378014016a; asc 7 j;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 5: len 8; hex 80001245af7a4ad1; asc E zJ ;; 6: SQL NULL; 7: len 1; hex 00; asc ;; 8: len 8; hex 80000000000000fb; asc ;; 9: len 8; hex 80000000001c4df6; asc M ;; 10: SQL NULL; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 458790 n bits 328 index `PRIMARY` of table `phs_jbpm`.`JBPM_PROCESSINSTANCE` trx id 0 381881998 lock_mode X locks rec but not gap waiting Record lock, heap no 249 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 80000000001c4dbf; asc M ;; 1: len 6; hex 000016c30e87; asc ;; 2: len 7; hex 0000378014016a; asc 7 j;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 5: len 8; hex 80001245af7a4ad1; asc E zJ ;; 6: SQL NULL; 7: len 1; hex 00; asc ;; 8: len 8; hex 80000000000000fb; asc ;; 9: len 8; hex 80000000001c4df6; asc M ;; 10: SQL NULL; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 0 381998764 Purge done for trx's n:o < 0 381998664 undo n:o < 0 0 History list length 34 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 21566, OS thread id 1178929472 MySQL thread id 109582, query id 92792259 localhost root show engine innodb status ---TRANSACTION 0 381998718, not started, process no 21566, OS thread id 1186384192 MySQL thread id 109564, query id 92791912 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998712, not started, process no 21566, OS thread id 1163753792 MySQL thread id 109575, query id 92791951 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998711, not started, process no 21566, OS thread id 1162156352 MySQL thread id 109572, query id 92791950 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998714, not started, process no 21566, OS thread id 1181325632 MySQL thread id 109563, query id 92791936 p8.fizzback.net 66.132.221.56 jboss ---TRANSACTION 0 381998354, not started, process no 21566, OS thread id 1076410688 MySQL thread id 109552, query id 92788196 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998353, not started, process no 21566, OS thread id 1075435840 MySQL thread id 109550, query id 92788195 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998724, not started, process no 21566, OS thread id 1178396992 MySQL thread id 109530, query id 92791949 p8.fizzback.net 66.132.221.56 jboss ---TRANSACTION 0 381997748, not started, process no 21566, OS thread id 1174669632 MySQL thread id 109535, query id 92781175 peer1-13.fizzback.net 66.132.221.12 web ---TRANSACTION 0 381998757, not started, process no 21566, OS thread id 1183988032 MySQL thread id 109484, query id 92792257 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998370, not started, process no 21566, OS thread id 1174137152 MySQL thread id 109436, query id 92788194 p8.fizzback.net 66.132.221.56 jboss ---TRANSACTION 0 381998367, not started, process no 21566, OS thread id 1170942272 MySQL thread id 109384, query id 92788172 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381998758, not started, process no 21566, OS thread id 1185052992 MySQL thread id 109358, query id 92792256 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381987290, not started, process no 21566, OS thread id 1175468352 MySQL thread id 109055, query id 92666511 peer1-13.fizzback.net 66.132.221.12 web ---TRANSACTION 0 0, not started, process no 21566, OS thread id 1171208512 MySQL thread id 108467, query id 92792247 localhost 127.0.0.1 root ---TRANSACTION 0 381962369, not started, process no 21566, OS thread id 1165617472 MySQL thread id 107888, query id 92414373 peer1-13.fizzback.net 66.132.221.12 web ---TRANSACTION 0 381971407, not started, process no 21566, OS thread id 1193838912 MySQL thread id 107868, query id 92495895 peer1-13.fizzback.net 66.132.221.12 web ---TRANSACTION 0 0, not started, process no 21566, OS thread id 1168013632 MySQL thread id 107521, query id 92245888 localhost 127.0.0.1 prem ---TRANSACTION 0 381950164, not started, process no 21566, OS thread id 1074882880 MySQL thread id 107520, query id 92345433 localhost 127.0.0.1 prem ---TRANSACTION 0 381966991, not started, process no 21566, OS thread id 1194903872 MySQL thread id 107007, query id 92455656 localhost 127.0.0.1 prem ---TRANSACTION 0 381966967, not started, process no 21566, OS thread id 1196235072 MySQL thread id 107005, query id 92470474 localhost 127.0.0.1 prem ---TRANSACTION 0 381927053, not started, process no 21566, OS thread id 1177332032 MySQL thread id 106869, query id 92013763 peer1-13.fizzback.net 66.132.221.12 web ---TRANSACTION 0 381976117, not started, process no 21566, OS thread id 1178130752 MySQL thread id 106809, query id 92546183 peer1-13.fizzback.net 66.132.221.12 web ---TRANSACTION 0 381924525, not started, process no 21566, OS thread id 1188514112 MySQL thread id 106747, query id 91764049 p8.fizzback.net 66.132.221.56 jboss ---TRANSACTION 0 381998762, not started, process no 21566, OS thread id 1191708992 MySQL thread id 105689, query id 92792246 p1.fizzback.net 66.132.217.110 pusher ---TRANSACTION 0 381998530, not started, process no 21566, OS thread id 1164552512 MySQL thread id 86632, query id 92792249 goofy.fizzback.net 82.197.83.41 ure ---TRANSACTION 0 381998260, not started, process no 21566, OS thread id 1184520512 MySQL thread id 86630, query id 92786705 goofy.fizzback.net 82.197.83.41 ure ---TRANSACTION 0 381600792, not started, process no 21566, OS thread id 1166149952 MySQL thread id 19, query id 92766501 p3.fizzback.net 66.132.217.122 mancor ---TRANSACTION 0 381998763, ACTIVE 0 sec, process no 21566, OS thread id 1170143552 fetching rows, thread declared inside InnoDB 137 mysql tables in use 3, locked 0 MySQL thread id 106401, query id 92792248 p1.fizzback.net 66.132.217.110 pusher Sorting result SELECT feed_id, target_id, customer_name, customer_address, extractvalue(feed_xml, '/feed/@transaction_type') as transaction_type FROM feeds WHERE 1 AND push_time < NOW() AND sent_time IS NULL AND target_id = 1472 AND feed_status = 'pending' ORDER BY push_time ASC LIMIT 10 Trx read view will not see trx with id >= 0 381998764, sees < 0 381921175 ---TRANSACTION 0 381998749, ACTIVE 2 sec, process no 21566, OS thread id 1198098752 fetching rows, thread declared inside InnoDB 488 mysql tables in use 1, locked 0 MySQL thread id 106400, query id 92792100 p1.fizzback.net 66.132.217.110 pusher Sorting result SELECT feed_id, customer_address FROM feeds WHERE 1 AND push_time < NOW() AND target_id = 1469 AND sent_time < DATE_ADD(NOW(), INTERVAL 1 MINUTE) AND feed_status = 'in progress' ORDER BY push_time ASC LIMIT 350 Trx read view will not see trx with id >= 0 381998750, sees < 0 381921175 ---TRANSACTION 0 381924584, ACTIVE 7188 sec, process no 21566, OS thread id 1189046592 4 lock struct(s), heap size 3024, 3 row lock(s), undo log entries 1 MySQL thread id 106763, query id 91764233 p8.fizzback.net 66.132.221.56 jboss ---TRANSACTION 0 381923865, ACTIVE (PREPARED) 7290 sec, process no 21566, OS thread id 1175734592 4 lock struct(s), heap size 342000, 2 row lock(s), undo log entries 1 MySQL thread id 106680, query id 91764037 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381921177, ACTIVE (PREPARED) 7693 sec, process no 21566, OS thread id 1168546112 11 lock struct(s), heap size 3024, 5 row lock(s), undo log entries 6 MySQL thread id 105765, query id 91764000 p8.fizzback.net 66.132.221.56 phs ---TRANSACTION 0 381921175, ACTIVE (PREPARED) 7694 sec, process no 21566, OS thread id 1169877312 29 lock struct(s), heap size 6752, 49 row lock(s), undo log entries 173 MySQL thread id 105802, query id 91763984 p8.fizzback.net 66.132.221.56 phs -------- 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: 1 2509212 OS file reads, 4871865 OS file writes, 3218569 OS fsyncs 0.27 reads/s, 152917 avg bytes/read, 14.09 writes/s, 7.64 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 70359 inserts, 70359 merged recs, 22994 merges Hash table size 5312557, node heap has 6239 buffer(s) 116566.40 hash searches/s, 5629.31 non-hash searches/s --- LOG --- Log sequence number 207 1317018759 Log flushed up to 207 1317018759 Last checkpoint at 207 1315910005 0 pending log writes, 0 pending chkp writes 3044242 log i/o's done, 7.18 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2988009274; in additional pool allocated 1048576 Dictionary memory allocated 1034024 Buffer pool size 163840 Free buffers 2 Database pages 157579 Modified db pages 313 Pending reads 0 Pending writes: LRU 0, flush list 1, single page 0 Pages read 8246113, created 2587894, written 5107993 2.55 reads/s, 0.91 creates/s, 13.91 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 2 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB Main thread process no. 21566, id 1161623872, state: flushing buffer pool pages Number of rows inserted 37470107, updated 25322015, deleted 10309437, read 77887579621 21.27 inserts/s, 18.91 updates/s, 0.45 deletes/s, 569952.10 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================