*************************** 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)