gistfile1.txt

=====================================
2015-02-10 16:02:32 2ad28e0c6700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 121502 srv_active, 0 srv_shutdown, 450621 srv_idle
srv_master_thread log flush and writes: 572117
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 294991
OS WAIT ARRAY INFO: signal count 413901
Mutex spin waits 428880, rounds 788908, OS waits 14176
RW-shared spins 298202, rounds 8049625, OS waits 264867
RW-excl spins 6783, rounds 647798, OS waits 14593
Spin rounds per wait: 1.84 mutex, 26.99 RW-shared, 95.50 RW-excl
------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-02-10 15:50:27 2ad28e107700
*** (1) TRANSACTION:
TRANSACTION 464377746, ACTIVE 2 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 1235378, OS thread handle 0x2ad28c478700, query id 109231011 10.0.101.170 admin updating
UPDATE `wp_options` SET `option_value` = '1423669825' WHERE `option_name` = '_transient_timeout_wc_report_sales_by_date'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 1698 n bits 272 index `option_name` of table `ebdb`.`wp_options` trx id 464377746 lock_mode X waiting
Record lock, heap no 65 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 5f7472616e7369656e745f74696d656f75745f77635f7265706f72745f73; asc _transient_timeout_wc_report_s; (total 42 bytes);
 1: len 8; hex 00000000007507e0; asc      u  ;;

*** (2) TRANSACTION:
TRANSACTION 464377312, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 1235237, OS thread handle 0x2ad28e107700, query id 109230550 10.0.102.58 admin updating
UPDATE `wp_options` SET `option_value` = '1423669823' WHERE `option_name` = '_transient_timeout_wc_report_sales_by_date'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 47 page no 1698 n bits 272 index `option_name` of table `ebdb`.`wp_options` trx id 464377312 lock_mode X locks rec but not gap
Record lock, heap no 65 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 5f7472616e7369656e745f74696d656f75745f77635f7265706f72745f73; asc _transient_timeout_wc_report_s; (total 42 bytes);
 1: len 8; hex 00000000007507e0; asc      u  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 1698 n bits 272 index `option_name` of table `ebdb`.`wp_options` trx id 464377312 lock_mode X waiting
Record lock, heap no 65 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 30; hex 5f7472616e7369656e745f74696d656f75745f77635f7265706f72745f73; asc _transient_timeout_wc_report_s; (total 42 bytes);
 1: len 8; hex 00000000007507e0; asc      u  ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 464551334
Purge done for trx's n:o < 464551235 undo n:o < 0 state: running but idle
History list length 1127
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 464551328, not started
MySQL thread id 1237326, OS thread handle 0x2ad28e69d700, query id 109417280 10.0.101.170 admin cleaning up
---TRANSACTION 464551329, not started
MySQL thread id 1237325, OS thread handle 0x2ad28d59a700, query id 109417282 10.0.101.170 admin cleaning up
---TRANSACTION 464551333, not started
MySQL thread id 1237324, OS thread handle 0x2ad28d559700, query id 109417285 10.0.102.58 admin freeing items
SELECT ID, post_name, post_parent, post_type
		FROM wp_posts
		WHERE post_name IN ('login')
		AND post_type IN ('page','attachment')
---TRANSACTION 464551330, not started
MySQL thread id 1237321, OS thread handle 0x2ad28c478700, query id 109417283 10.0.102.58 admin cleaning up
---TRANSACTION 464551331, not started
MySQL thread id 1237323, OS thread handle 0x2ad28d9aa700, query id 109417284 10.0.102.58 admin cleaning up
---TRANSACTION 464551327, not started
MySQL thread id 1237320, OS thread handle 0x2ad28eaee700, query id 109417279 10.0.101.170 admin cleaning up
---TRANSACTION 464551332, not started
MySQL thread id 1237322, OS thread handle 0x2ad28edfa700, query id 109417281 10.0.101.170 admin cleaning up
---TRANSACTION 464511716, not started
MySQL thread id 1236895, OS thread handle 0x2ad28ca28700, query id 109374771 10.0.102.58 admin cleaning up
---TRANSACTION 0, not started
MySQL thread id 1233504, OS thread handle 0x2ad28e0c6700, query id 109417286 10.0.1.191 admin init
SHOW ENGINE INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 1233513, OS thread handle 0x2ad28d2cf700, query id 109415733 10.0.1.191 admin cleaning up
---TRANSACTION 0, not started
MySQL thread id 1221859, OS thread handle 0x2ad28d518700, query id 109415297 10.0.1.118 admin cleaning up
---TRANSACTION 464238113, not started
MySQL thread id 1221851, OS thread handle 0x2ad28cf41700, query id 109415856 10.0.1.118 admin cleaning up
---TRANSACTION 464550878, not started
MySQL thread id 3277, OS thread handle 0x2ad28c4b9700, query id 109416810 localhost 127.0.0.1 rdsadmin cleaning up
---TRANSACTION 464551288, ACTIVE (PREPARED) 1 sec flushing log
mysql tables in use 1, locked 1
1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
MySQL thread id 1236992, OS thread handle 0x2ad28dc75700, query id 109417239 10.0.101.170 admin query end
INSERT INTO `wp_commentmeta` (`comment_id`,`meta_key`,`meta_value`) VALUES ('29242','is_customer_note','0')
---TRANSACTION 464551066, ACTIVE 2 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 29 lock struct(s), heap size 6544, 86 row lock(s)
MySQL thread id 1236899, OS thread handle 0x2ad28f106700, query id 109417006 10.0.102.58 admin updating
update wp_postmeta set meta_value='68' where meta_key='_stock' and post_id='10942'
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 49 page no 361 n bits 344 index `PRIMARY` of table `ebdb`.`wp_postmeta` trx id 464551066 lock_mode X locks rec but not gap waiting
Record lock, heap no 166 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 8; hex 000000000000a30c; asc         ;;
 1: len 6; hex 000000d9eef4; asc       ;;
 2: len 7; hex 73000001ff1dc5; asc s      ;;
 3: len 8; hex 0000000000002a4b; asc       *K;;
 4: len 6; hex 5f73746f636b; asc _stock;;
 5: len 3; hex 353431; asc 541;;

------------------
---TRANSACTION 464551063, ACTIVE (PREPARED) 2 sec flushing log
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1237135, OS thread handle 0x2ad28e107700, query id 109417004 10.0.101.170 admin query end
UPDATE `wp_posts` SET `comment_count` = 2 WHERE `ID` = 62816
---TRANSACTION 464550947, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1237314, OS thread handle 0x2ad28db30700, query id 109416879 10.0.101.170 admin update
INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_is_multi_author', '0', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 47 page no 1697 n bits 344 index `option_name` of table `ebdb`.`wp_options` trx id 464550947 lock_mode X waiting
Record lock, heap no 272 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 26; hex 5f7472616e7369656e745f69735f6d756c74695f617574686f72; asc _transient_is_multi_author;;
 1: len 8; hex 0000000000750844; asc      u D;;

------------------
---TRANSACTION 464550899, ACTIVE (PREPARED) 2 sec
mysql tables in use 1, locked 1
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1237317, OS thread handle 0x2ad28eaad700, query id 109416831 10.0.101.170 admin query end
INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_is_multi_author', '0', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)
---TRANSACTION 464550119, ACTIVE (PREPARED) 7 sec flushing log
mysql tables in use 1, locked 3
14 lock struct(s), heap size 2936, 170 row lock(s), undo log entries 1
MySQL thread id 1237250, OS thread handle 0x2ad28cfc3700, query id 109415955 10.0.102.58 admin query end
UPDATE `wp_postmeta` SET `meta_value` = 'a:44:{i:0;i:21459;i:1;i:21463;i:2;i:19467;i:3;i:21528;i:4;i:21547;i:5;i:21689;i:6;i:21712;i:7;i:21418;i:8;i:21417;i:9;i:21434;i:10;i:21438;i:11;i:21423;i:12;i:21499;i:13;i:71;i:14;i:9281;i:15;i:21546;i:16;i:21551;i:17;i:19339;i:18;i:21669;i:19;i:16274;i:20;i:21952;i:21;i:22051;i:22;i:22057;i:23;i:22106;i:24;i:20919;i:25;i:22179;i:26;i:22189;i:27;i:22193;i:28;i:22219;i:29;i:22230;i:30;i:22236;i:31;i:22245;i:32;i:22273;i:33;i:22359;i:34;i:22384;i:35;i:11872;i:36;i:22438;i:37;i:22475;i:38;i:22525;i:39;i:18430;i:40;i:22578;i:41;i:22636;i:42;i:22644;i:43;i:2
--------
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: 1; buffer pool: 0
10623 OS file reads, 3105103 OS file writes, 2126067 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 5.71 writes/s, 2.57 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 24 merges
merged operations:
 insert 25, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 23009359, node heap has 1598 buffer(s)
2650.34 hash searches/s, 373.52 non-hash searches/s
---
LOG
---
Log sequence number 3901450877
Log flushed up to   3901448273
Pages flushed up to 3901419071
Last checkpoint at  3901415441
1 pending log writes, 0 pending chkp writes
883432 log i/o's done, 0.86 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 11899109376; in additional pool allocated 0
Dictionary memory allocated 296321
Buffer pool size   709624
Free buffers       695932
Database pages     12094
Old database pages 4334
Modified db pages  75
Pending reads 0
Pending writes: LRU 0, flush list 21, single page 0
Pages made young 78, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 10399, created 1695, written 1656498
0.00 reads/s, 0.00 creates/s, 4.71 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: 12094, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   88703
Free buffers       86816
Database pages     1681
Old database pages 600
Modified db pages  12
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1344, created 337, written 359406
0.00 reads/s, 0.00 creates/s, 0.00 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: 1681, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   88703
Free buffers       86938
Database pages     1562
Old database pages 556
Modified db pages  10
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1259, created 303, written 544805
0.00 reads/s, 0.00 creates/s, 0.00 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: 1562, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   88703
Free buffers       87165
Database pages     1340
Old database pages 474
Modified db pages  11
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1151, created 189, written 33507
0.00 reads/s, 0.00 creates/s, 0.00 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: 1340, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   88703
Free buffers       87030
Database pages     1480
Old database pages 546
Modified db pages  1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 10, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1333, created 147, written 129476
0.00 reads/s, 0.00 creates/s, 0.00 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: 1480, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   88703
Free buffers       86897
Database pages     1597
Old database pages 582
Modified db pages  8
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1430, created 167, written 128764
0.00 reads/s, 0.00 creates/s, 0.00 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: 1597, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   88703
Free buffers       86953
Database pages     1555
Old database pages 554
Modified db pages  3
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1429, created 126, written 61272
0.00 reads/s, 0.00 creates/s, 1.43 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: 1555, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   88703
Free buffers       86993
Database pages     1516
Old database pages 539
Modified db pages  4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 6, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1302, created 214, written 119419
0.00 reads/s, 0.00 creates/s, 3.29 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: 1516, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   88703
Free buffers       87140
Database pages     1363
Old database pages 483
Modified db pages  26
Pending reads 0
Pending writes: LRU 0, flush list 21, single page 0
Pages made young 23, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1151, created 212, written 279849
0.00 reads/s, 0.00 creates/s, 0.00 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: 1363, 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
0 read views open inside InnoDB
Main thread process no. 16125, id 47083954534144, state: sleeping
Number of rows inserted 114713, updated 209584, deleted 9317, read 2548808882
0.29 inserts/s, 0.57 updates/s, 0.00 deletes/s, 2909.73 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Hi,

Were you able to resolve or at least check this?

Thanks.

Best,
Greg