Created
February 10, 2015 16:02
-
-
Save janewang/bb79267fb74034107e52 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
===================================== | |
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 | |
============================ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,
Were you able to resolve or at least check this?
Thanks.
Best,
Greg