*************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 120302 11:38:00 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 7 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 78873 1_second, 78871 sleeps, 7829 10_second, 837 background, 837 flush srv_master_thread log flush and writes: 80380 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 859619, signal count 21621021 Mutex spin waits 21317919, rounds 27408797, OS waits 401483 RW-shared spins 9292521, rounds 30419002, OS waits 216533 RW-excl spins 7199604, rounds 40706180, OS waits 217765 Spin rounds per wait: 1.29 mutex, 3.27 RW-shared, 5.65 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 224237 OS file reads, 7063747 OS file writes, 5911116 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 76.56 writes/s, 14.86 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 4450 merges merged operations: insert 4379, delete mark 42142, delete 291 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 14874907, node heap has 8516 buffer(s) 26088.99 hash searches/s, 11814.60 non-hash searches/s --- LOG --- Log sequence number 550518259052 Log flushed up to 550518259052 Last checkpoint at 550001843022 Max checkpoint age 1738042860 Checkpoint age target 1683729021 Modified age 516416030 Checkpoint age 516416030 0 pending log writes, 0 pending chkp writes 5875604 log i/o's done, 12.86 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 7721713664; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 258529616 (118999256 + 139530360) Page hash 7438216 (buffer pool 0 only) Dictionary cache 44879214 (29751248 + 15127966) File system 83552 (82672 + 880) Lock system 18597168 (18594536 + 2632) Recovery system 0 (0 + 0) Dictionary memory allocated 15127966 Buffer pool size 458751 Buffer pool size, bytes 7516176384 Free buffers 204678 Database pages 245557 Old database pages 90627 Modified db pages 12798 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 2720, not young 0 0.43 youngs/s, 0.00 non-youngs/s Pages read 224218, created 21339, written 1160838 0.00 reads/s, 0.43 creates/s, 62.13 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: 245557, 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 2 read views open inside InnoDB ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 1613267D Read view up limit trx id 1613267C Read view low limit trx id 1613267D Read view individually stored trx ids: Read view trx id 1613267C ----------------- Main thread process no. 10869, id 140627909371648, state: sleeping Number of rows inserted 43405536, updated 108296273, deleted 22936918, read 6113756213 10.71 inserts/s, 43140.69 updates/s, 0.29 deletes/s, 248497.21 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 120302 10:53:16 *** (1) TRANSACTION: TRANSACTION 1606E3BB, ACTIVE 20 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 8386 lock struct(s), heap size 866744, 828664 row lock(s), undo log entries 7304 MySQL thread id 226835, OS thread handle 0x7fe678a16700, query id 60604160 www.local 192.168.1.1 mydatabase preparing UPDATE sales_flat_quote SET trigger_recollect = 1 WHERE entity_id IN ( SELECT DISTINCT quote_id FROM sales_flat_quote_item WHERE product_id IN (SELECT DISTINCT product_id FROM catalogrule_product_price) ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 107163 n bits 152 index `PRIMARY` of table `mydatabase`.`sales_flat_quote` trx id 1606E3BB lock_mode X waiting *** (2) TRANSACTION: TRANSACTION 1607B5C2, ACTIVE 0 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 10 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 5 MySQL thread id 229412, OS thread handle 0x7fe678b3c700, query id 60670349 192.168.1.240 mydatabase update INSERT INTO `sales_flat_quote_item` (`quote_id`, `created_at`, `updated_at`, `product_id`, `store_id`, `is_virtual`, `sku`, `name`, `applied_rule_ids`, `free_shipping`, `is_qty_decimal`, `weight`, `qty`, `price`, `base_price`, `discount_percent`, `discount_amount`, `base_discount_amount`, `tax_percent`, `tax_amount`, `base_tax_amount`, `row_total`, `base_row_total`, `row_weight`, `product_type`, `weee_tax_applied`, `weee_tax_applied_amount`, `weee_tax_applied_row_amount`, `base_weee_tax_applied_amount`, `base_weee_tax_applied_row_amount`, `weee_tax_disposition`, `weee_tax_row_disposition`, `base_weee_tax_disposition`, `base_weee_tax_row_disposition`, `base_cost`, `price_incl_tax`, `base_price_incl_tax`, `row_total_incl_tax`, `base_row_total_incl_tax`) VALUES ('272058', '2012-03-02 08:33:03', '2012-03-02 08:33:03', '23247', '1', '', 'DVATRAVEL55USB', 'DVD Player portabil EBODA TRAVEL55USB, 18 cm, CD, USB', '' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 107163 n bits 152 index `PRIMARY` of table `mydatabase`.`sales_flat_quote` trx id 1607B5C2 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 199228 n bits 1064 index `FK_SALES_QUOTE_ITEM_SALES_QUOTE` of table `mydatabase`.`sales_flat_quote_item` trx id 1607B5C2 lock_mode X insert intention waiting *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 1613C624 Purge done for trx's n:o < 1613268B undo n:o < 0 History list length 1604 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 1613C623, not started MySQL thread id 242505, OS thread handle 0x7fe679098700, query id 63636143 www3.local 192.168.1.241 mydatabase ---TRANSACTION 1613C622, not started MySQL thread id 242504, OS thread handle 0x7fe679005700, query id 63636142 192.168.1.240 mydatabase ---TRANSACTION 1613C581, not started MySQL thread id 242499, OS thread handle 0x7fe67888e700, query id 63635650 192.168.1.240 mydatabase ---TRANSACTION 0, not started MySQL thread id 242413, OS thread handle 0x7fe678332700, query id 63636144 localhost root SHOW ENGINE INNODB STATUS ---TRANSACTION 1613C191, not started MySQL thread id 242351, OS thread handle 0x7fe678458700, query id 63632734 www.local 192.168.1.1 mydatabase Waiting for table metadata lock TRUNCATE TABLE catalogsearch_result ---TRANSACTION 16139ABC, not started MySQL thread id 242141, OS thread handle 0x7fe678bcf700, query id 63596521 www.local 192.168.1.1 mydatabase Waiting for table metadata lock TRUNCATE `catalog_product_index_price_opt_agr_idx` ---TRANSACTION 1613267C, ACTIVE 290 sec mysql tables in use 1, locked 0 MySQL thread id 241211, OS thread handle 0x7fe67826e700, query id 63586880 www3.local 192.168.1.241 mydatabase Sending data SELECT /*!40001 SQL_NO_CACHE */ * FROM `enterprise_logging_event_changes` Trx read view will not see trx with id >= 1613267D, sees < 1613267D ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)