| InnoDB | | ===================================== 2015-09-03 10:48:31 7fe3ea2dd700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 330401 srv_active, 0 srv_shutdown, 72 srv_idle srv_master_thread log flush and writes: 330473 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 6416828 OS WAIT ARRAY INFO: signal count 6362125 Mutex spin waits 5427046, rounds 159037348, OS waits 5171812 RW-shared spins 1234234, rounds 36784909, OS waits 1184457 RW-excl spins 71056, rounds 1838380, OS waits 50521 Spin rounds per wait: 29.30 mutex, 29.80 RW-shared, 25.87 RW-excl ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2015-09-01 16:03:06 7fe3ea0de700 *** (1) TRANSACTION: TRANSACTION 197902612, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s) MySQL thread id 556853, OS thread handle 0x7fe3e9a06700, query id 19266942 10.132.15.1 069shop updating DELETE FROM `catalog_product_index_eav_tmp` *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2187 page no 3 n bits 600 index `PRIMARY` of table `069shop`.`catalog_product_index_eav_tmp` trx table locks 1 total table locks 2 trx id 197902612 lock_mode X waiting lock hold time 0 wait time before grant 0 *** (2) TRANSACTION: TRANSACTION 197902607, ACTIVE 0 sec inserting mysql tables in use 4, locked 4 9 lock struct(s), heap size 2936, 50 row lock(s), undo log entries 32 MySQL thread id 556655, OS thread handle 0x7fe3ea0de700, query id 19266946 10.132.15.1 069shop Sending data INSERT INTO `catalog_product_index_eav_tmp` SELECT `pid`.`entity_id`, `pid`.`attribute_id`, `pid`.`store_id`, IFNULL(pis.value, pid.value) AS `value` FROM (SELECT `s`.`store_id`, `s`.`website_id`, `d`.`entity_id`, `d`.`attribute_id`, `d`.`value` FROM `core_store` AS `s` LEFT JOIN `catalog_product_entity_int` AS `d` ON 1 = 1 AND d.store_id = 0 WHERE (s.store_id != 0)) AS `pid` LEFT JOIN `catalog_product_entity_int` AS `pis` ON pis.entity_id = pid.entity_id AND pis.attribute_id = pid.attribute_id AND pis.store_id = pid.store_id WHERE (pid.attribute_id IN('75', '115', '168', '172', '227')) AND (IFNULL(pis.value, pid.value) IS NOT NULL) AND (pid.entity_id IN('1644')) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2187 page no 3 n bits 600 index `PRIMARY` of table `069shop`.`catalog_product_index_eav_tmp` trx table locks 3 total table locks 2 trx id 197902607 lock_mode X lock hold time 0 wait time before grant 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2187 page no 3 n bits 600 index `PRIMARY` of table `069shop`.`catalog_product_index_eav_tmp` trx table locks 3 total table locks 2 trx id 197902607 lock_mode X locks gap before rec insert intention waiting lock hold time 0 wait time before grant 0 *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 205160063 Purge done for trx's n:o < 205160062 undo n:o < 2 state: running but idle History list length 2284 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 1021441, OS thread handle 0x7fe3ea2dd700, query id 36090545 localhost root init show engine innodb status ---TRANSACTION 205160001, not started MySQL thread id 1021451, OS thread handle 0x7fe3ea44a700, query id 36090479 10.132.15.1 heartbeat cleaning up ---TRANSACTION 205159983, not started MySQL thread id 1021450, OS thread handle 0x7fe3e9c05700, query id 36090457 10.132.15.1 heartbeat cleaning up ---TRANSACTION 205114305, not started MySQL thread id 194372, OS thread handle 0x7fe3fcfff700, query id 36089767 10.132.15.1 501config_aut cleaning up ---TRANSACTION 205019693, not started MySQL thread id 203591, OS thread handle 0x7fe3fcf6d700, query id 36089087 10.132.15.1 501config_pub cleaning up ---TRANSACTION 205160038, not started MySQL thread id 55, OS thread handle 0x7fe5f49f1700, query id 36090521 10.132.15.2 f5 cleaning up ---TRANSACTION 205159986, not started MySQL thread id 53, OS thread handle 0x7fe5f495f700, query id 36090464 10.132.15.3 f5 cleaning up ---TRANSACTION 205160061, not started MySQL thread id 4, OS thread handle 0x7fe5f6d2a700, query id 36090551 Slave has read all relay log; waiting for the slave I/O thread to update it ---TRANSACTION 189379588, not started MySQL thread id 3, OS thread handle 0x7fe5f6d73700, query id 0 Waiting for master to send event ---TRANSACTION 189379587, not started MySQL thread id 1, OS thread handle 0x7fe5f6e05700, query id 0 Waiting for background binlog tasks -------- 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 1105240 OS file reads, 7618315 OS file writes, 461311 OS fsyncs 0.05 reads/s, 16384 avg bytes/read, 18.63 writes/s, 0.84 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 1247, seg size 1249, 11612 merges merged operations: insert 5013, delete mark 58768, delete 17579 discarded operations: insert 0, delete mark 0, delete 0 37.68 hash searches/s, 48.26 non-hash searches/s --- LOG --- Log sequence number 91887220464 Log flushed up to 91887220335 Pages flushed up to 91838901835 Last checkpoint at 91838847271 Max checkpoint age 434151015 Checkpoint age target 420583796 Modified age 48318629 Checkpoint age 48373193 0 pending log writes, 0 pending chkp writes 6901311 log i/o's done, 17.21 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 5902336000; in additional pool allocated 0 Total memory allocated by read views 944 Internal hash tables (constant factor + variable factor) Adaptive hash index 112086256 (91307144 + 20779112) Page hash 1142168 (buffer pool 0 only) Dictionary cache 50054551 (22828496 + 27226055) File system 1975984 (812272 + 1163712) Lock system 14271352 (14267752 + 3600) Recovery system 0 (0 + 0) Dictionary memory allocated 27226055 Buffer pool size 351995 Buffer pool size, bytes 5767086080 Free buffers 5120 Database pages 345607 Old database pages 127475 Modified db pages 3092 Percent of dirty pages(LRU & free pages): 0.882 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 466698, not young 25819716 0.00 youngs/s, 0.16 non-youngs/s Pages read 1099266, created 40583, written 536331 0.05 reads/s, 0.00 creates/s, 1.16 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: 345607, unzip_LRU len: 0 I/O sum[270]:cur[35], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 70399 Buffer pool size, bytes 1153417216 Free buffers 1024 Database pages 69128 Old database pages 25497 Modified db pages 597 Percent of dirty pages(LRU & free pages): 0.851 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 124321, not young 6108715 0.00 youngs/s, 0.16 non-youngs/s Pages read 228577, created 8116, written 119843 0.05 reads/s, 0.00 creates/s, 0.21 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: 69128, unzip_LRU len: 0 I/O sum[54]:cur[7], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 70399 Buffer pool size, bytes 1153417216 Free buffers 1024 Database pages 69121 Old database pages 25496 Modified db pages 501 Percent of dirty pages(LRU & free pages): 0.714 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 85552, not young 5133637 0.00 youngs/s, 0.00 non-youngs/s Pages read 217310, created 7803, written 90218 0.00 reads/s, 0.00 creates/s, 0.26 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: 69121, unzip_LRU len: 0 I/O sum[54]:cur[7], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 70399 Buffer pool size, bytes 1153417216 Free buffers 1024 Database pages 69132 Old database pages 25499 Modified db pages 686 Percent of dirty pages(LRU & free pages): 0.978 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 84939, not young 4900696 0.00 youngs/s, 0.00 non-youngs/s Pages read 218270, created 8151, written 101830 0.00 reads/s, 0.00 creates/s, 0.21 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: 69132, unzip_LRU len: 0 I/O sum[54]:cur[7], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 70399 Buffer pool size, bytes 1153417216 Free buffers 1024 Database pages 69117 Old database pages 25493 Modified db pages 627 Percent of dirty pages(LRU & free pages): 0.894 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 85437, not young 4710500 0.00 youngs/s, 0.00 non-youngs/s Pages read 216152, created 8480, written 106701 0.00 reads/s, 0.00 creates/s, 0.11 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: 69117, unzip_LRU len: 0 I/O sum[54]:cur[7], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 70399 Buffer pool size, bytes 1153417216 Free buffers 1024 Database pages 69109 Old database pages 25490 Modified db pages 681 Percent of dirty pages(LRU & free pages): 0.971 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 86449, not young 4966168 0.00 youngs/s, 0.00 non-youngs/s Pages read 218957, created 8033, written 117739 0.00 reads/s, 0.00 creates/s, 0.37 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: 69109, unzip_LRU len: 0 I/O sum[54]:cur[7], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB 0 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 0 out of 1000 descriptors used Main thread process no. 1994, id 140617497696000, state: sleeping Number of rows inserted 1828511, updated 4414571, deleted 1116167, read 1996582414 5.37 inserts/s, 10.21 updates/s, 0.00 deletes/s, 47.31 reads/s Number of system rows inserted 3270430, updated 0, deleted 3270430, read 3270431 9.95 inserts/s, 0.00 updates/s, 9.95 deletes/s, 9.95 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================