mysql> show engine innodb status \G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 130830 12:14:56 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 30 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 357307 1_second, 357299 sleeps, 34915 10_second, 8595 background, 8595 flush srv_master_thread log flush and writes: 472498 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 36267327, signal count 488398537 Mutex spin waits 1523500124, rounds 1218141480, OS waits 442964 RW-shared spins 552357205, rounds 3068395064, OS waits 30276535 RW-excl spins 12630037, rounds 733634534, OS waits 1850626 Spin rounds per wait: 0.80 mutex, 5.56 RW-shared, 58.09 RW-excl -------- 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, 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 363813 OS file reads, 8562416 OS file writes, 3132247 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 38.47 writes/s, 16.50 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 4620 merges merged operations: insert 3206, delete mark 1740, delete 87 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 53124499, node heap has 27699 buffer(s) 792197.26 hash searches/s, 79102.03 non-hash searches/s --- LOG --- Log sequence number 92029791417 Log flushed up to 92029788007 Last checkpoint at 92028228512 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1562905 Checkpoint age 1562905 0 pending log writes, 0 pending chkp writes 2973733 log i/o's done, 15.90 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35751149218; in additional pool allocated 8388608 Internal hash tables (constant factor + variable factor) Adaptive hash index 878824576 (424995992 + 453828584) Page hash 26563016 (buffer pool 0 only) Dictionary cache 108648546 (106250416 + 2398130) File system 83536 (82672 + 864) Lock system 73922848 (73919464 + 3384) Recovery system 0 (0 + 0) Dictionary memory allocated 2398130 Buffer pool size 1638399 Buffer pool size, bytes 26843529216 Free buffers 1235725 Database pages 374975 Old database pages 138398 Modified db pages 759 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 1196, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 363802, created 11173, written 5461389 0.00 reads/s, 0.00 creates/s, 22.07 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: 374975, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 218F0EB2 Read view up limit trx id 218F0A17 Read view low limit trx id 218F0EB2 Read view individually stored trx ids: Read view trx id 218F0EB1 Read view trx id 218F0A17 ----------------- Main thread process no. 3129, id 139767517439744, state: flushing log Number of rows inserted 19358577, updated 1641829, deleted 1257962, read 316626495127 179.53 inserts/s, 7.03 updates/s, 2.00 deletes/s, 1245939.04 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 130830 2:32:22 *** (1) TRANSACTION: TRANSACTION 2165AE64, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1248, 3 row lock(s) MySQL thread id 29910626, OS thread handle 0x7f1b098a4700, query id 308313802 10.222.1.201 root update insert into vtiger_crmentity (crmid,smcreatorid,smownerid,setype,description,createdtime,modifiedtime) values('10700185',0,'232','Leads','','2013-08-30 02:32:22','2013-08-30 02:32:22') *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 336398 n bits 200 index `PRIMARY` of table `newcrmdb`.`vtiger_crmentity` trx id 2165AE64 lock_mode X insert intention waiting *** (2) TRANSACTION: TRANSACTION 2165AE65, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 3 row lock(s) MySQL thread id 29910628, OS thread handle 0x7f1e084fb700, query id 308313803 10.222.1.201 root update insert into vtiger_crmentity (crmid,smcreatorid,smownerid,setype,description,createdtime,modifiedtime) values('10700185',0,'232','Leads','','2013-08-30 02:32:22','2013-08-30 02:32:22') *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 336398 n bits 200 index `PRIMARY` of table `newcrmdb`.`vtiger_crmentity` trx id 2165AE65 lock mode S *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 336398 n bits 200 index `PRIMARY` of table `newcrmdb`.`vtiger_crmentity` trx id 2165AE65 lock_mode X insert intention waiting *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 218F1060 Purge done for trx's n:o < 218F0EDC undo n:o < 0 History list length 1488 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 31566870, OS thread handle 0x7f1b0a6dc700, query id 325737969 10.222.1.201 root ---TRANSACTION 218F105F, not started MySQL thread id 31566838, OS thread handle 0x7f1e0898d700, query id 325737975 10.222.1.201 root ---TRANSACTION 218F1044, not started MySQL thread id 31566863, OS thread handle 0x7f1e09027700, query id 325737924 10.222.1.201 root ---TRANSACTION 0, not started MySQL thread id 31566821, OS thread handle 0x7f24e0e7e700, query id 325737970 10.222.1.201 root ---TRANSACTION 218F1054, not started MySQL thread id 31566839, OS thread handle 0x7f24e230f700, query id 325737927 10.222.1.201 root ---TRANSACTION 218F0413, not started MySQL thread id 31565013, OS thread handle 0x7f24e224c700, query id 325737899 10.222.1.242 root ---TRANSACTION 0, not started MySQL thread id 31559235, OS thread handle 0x7f1b0a9e8700, query id 325737974 localhost root show engine innodb status ---TRANSACTION 218F105E, ACTIVE 0 sec mysql tables in use 1, locked 0 MySQL thread id 31566875, OS thread handle 0x7f1e084fb700, query id 325737958 10.222.1.201 root Sending data SELECT count(*) from vtiger_tracker WHERE user_id='851' LIMIT 1 Trx read view will not see trx with id >= 218F105F, sees < 218F0EB1 ---TRANSACTION 218F0EB1, ACTIVE 1 sec mysql tables in use 7, locked 0 , holds adaptive hash latch MySQL thread id 31565651, OS thread handle 0x7f24e08e8700, query id 325735358 10.222.1.201 root Sending data select vtiger_leaddetails.lead_no,vtiger_leaddetails.lastname,vtiger_leaddetails.firstname,vtiger_leaddetails.company,vtiger_leadaddress.phone,vtiger_leadsubdetails.website,vtiger_leaddetails.email,case when (vtiger_users.user_name not like '') then vtiger_users.user_name else vtiger_groups.groupname end as user_name ,vtiger_crmentity.crmid FROM vtiger_leaddetails INNER JOIN vtiger_crmentity ON vtiger_crmentity.crmid = vtiger_leaddetails.leadid INNER JOIN vtiger_leadsubdetails ON vtiger_leadsubdetails.leadsubscriptionid = vtiger_leaddetails.leadid INNER JOIN vtiger_leadaddress ON vtiger_leadad Trx read view will not see trx with id >= 218F0EB2, sees < 218F0A17 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)