#StackBounty: #mysql #mysql-5.6 #amazon-rds RDS MySQL I/O freeze during backup causes RDS instance to crash

Bounty: 50

I have a simple WordPress site using Amazon RDS MySQL 5.6.39 Single-AZ instance. Almost every night during backup window RDS instance crashes and getting recovered causing about 20 minutes of downtime. During the backup window there is almost no traffic, only monitoring systems are hitting the site. All RDS parameters are default. Is there anything I can do about it?

Events in RDS console

May 24, 2018 at 17:31:00 PM UTC+0   Recovery of the DB instance is complete.
May 24, 2018 at 17:30:43 PM UTC+0   DB instance restarted
May 24, 2018 at 17:27:11 PM UTC+0   Recovery of the DB instance has 
started. Recovery time will vary with the amount of data to be recovered.
May 24, 2018 at 17:24:21 PM UTC+0   Backing up DB instance
May 24, 2018 at 17:08:16 PM UTC+0   Backing up DB instance

There is mysql-error.log

InnoDB: Warning: a long semaphore wait:
--Thread 47155048924928 has waited at trx0sys.ic line 103 for 1137.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x2ae2f82536c0 created in file buf0buf.cc line 1069
a writer (thread id 47155048924928) has reserved it in mode  wait exclusive
number of readers 1, waiters flag 0, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.cc line 1056
Last time write locked in file /local/mysql-5.6.39.R1/storage/innobase/include/trx0sys.ic line 103
InnoDB: Warning: a long semaphore wait:
--Thread 47155007514368 has waited at trx0trx.cc line 193 for 1126.00 seconds the semaphore:
Mutex at 0x2ae31e7b9468 created file trx0sys.cc line 597, lock var 1
waiters flag 1

... dozens of the same warnings removed ....

InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

=====================================
2018-05-24 17:27:56 2ae323403700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 18166 srv_active, 0 srv_shutdown, 67861 srv_idle
srv_master_thread log flush and writes: 86026
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21968
OS WAIT ARRAY INFO: signal count 20419
Mutex spin waits 2746, rounds 128132, OS waits 3656
RW-shared spins 17908, rounds 538145, OS waits 12430
RW-excl spins 284, rounds 174171, OS waits 5727
Spin rounds per wait: 46.66 mutex, 30.05 RW-shared, 613.28 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 46791208
Purge done for trx's n:o < 46791128 undo n:o < 0 state: running but idle
History list length 3134
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 25541, OS thread handle 0x2ae32f78d700, query id 1747215 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 25526, OS thread handle 0x2ae32770b700, query id 1747126 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
MySQL thread id 25514, OS thread handle 0x2ae325aa2700, query id 1747061 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25505, OS thread handle 0x2ae327481700, query id 1747010 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25504, OS thread handle 0x2ae3275c6700, query id 1747004 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25503, OS thread handle 0x2ae325ba6700, query id 1746998 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25502, OS thread handle 0x2ae32591c700, query id 1746992 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25501, OS thread handle 0x2ae2dbfe7700, query id 1746986 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25500, OS thread handle 0x2ae3217cf700, query id 1746980 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
MySQL thread id 25499, OS thread handle 0x2ae3276ca700, query id 1746974 10.0.10.251 root init
SHOW /*!50000 ENGINE*/ INNODB STATUS
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25498, OS thread handle 0x2ae323fcf700, query id 1746971 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25497, OS thread handle 0x2ae327544700, query id 1746965 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started estimating records in index range
mysql tables in use 1, locked 0
MySQL thread id 25495, OS thread handle 0x2ae325ae3700, query id 1746953 10.0.11.204 wordpress statistics
SELECT  wp_site.id FROM wp_site  WHERE wp_site.domain IN ( 'www.domain.com.au', 'domain.com.au', 'com.au', 'au' ) AND wp_site.path IN ( '/wp-content/', '/' )  ORDER BY CHAR_LENGTH(wp_site.domain) DESC, CHAR_LENGTH(wp_site.path) DESC
---TRANSACTION 0, not started estimating records in index range
mysql tables in use 1, locked 0
MySQL thread id 25494, OS thread handle 0x2ae3274c2700, query id 1746952 10.0.11.204 wordpress statistics
SELECT  wp_site.id FROM wp_site  WHERE wp_site.domain IN ( 'www.domain.com.au', 'domain.com.au', 'com.au', 'au' ) AND wp_site.path IN ( '/wp-content/', '/' )  ORDER BY CHAR_LENGTH(wp_site.domain) DESC, CHAR_LENGTH(wp_site.path) DESC
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25493, OS thread handle 0x2ae32f440700, query id 1746941 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 10
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25492, OS thread handle 0x2ae327503700, query id 1746935 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25491, OS thread handle 0x2ae32599e700, query id 1746929 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25490, OS thread handle 0x2ae323f4d700, query id 1746923 10.0.11.204 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 10
---TRANSACTION 0, not started estimating records in index range
mysql tables in use 1, locked 0
MySQL thread id 25486, OS thread handle 0x2ae2dbf65700, query id 1746899 10.0.11.204 wordpress statistics
SELECT  wp_site.id FROM wp_site  WHERE wp_site.domain IN ( 'www.domain.com.au', 'domain.com.au', 'com.au', 'au' ) AND wp_site.path IN ( '/wp-content/', '/' )  ORDER BY CHAR_LENGTH(wp_site.domain) DESC, CHAR_LENGTH(wp_site.path) DESC
---TRANSACTION 0, not started
mysql tables in use 1, locked 0
MySQL thread id 25484, OS thread handle 0x2ae323f8e700, query id 1746887 10.0.11.44 wordpress Sending data
SELECT meta_value FROM wp_sitemeta WHERE meta_key = 'site_name' AND site_id = 9
---TRANSACTION 46791168, not started
MySQL thread id 25481, OS thread handle 0x2ae323f0c700, query id 1746869 10.0.11.204 wordpress
---TRANSACTION 46791135, not started
MySQL thread id 25479, OS thread handle 0x2ae323e49700, query id 1746831 10.0.11.204 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791160, not started
MySQL thread id 25480, OS thread handle 0x2ae32170c700, query id 1746856 10.0.11.44 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791132, not started
MySQL thread id 25478, OS thread handle 0x2ae327607700, query id 1746816 10.0.11.44 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791129, not started
MySQL thread id 25477, OS thread handle 0x2ae32778d700, query id 1746807 10.0.11.44 wordpress checking permissions
SHOW FULL COLUMNS FROM `wp_32_options`
---TRANSACTION 46791122, not started
MySQL thread id 1, OS thread handle 0x2ae2d8944700, query id 1747217 localhost 127.0.0.1 rdsadmin init
SELECT count(*) from mysql.rds_history WHERE action = 'disable set master' GROUP BY action_timestamp,called_by_user,action,mysql_version,master_host,master_port,master_user,master_log_file ,master_log_pos,master_ssl ORDER BY action_timestamp LIMIT 1
--------
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
60616 OS file reads, 356399 OS file writes, 165396 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 66 merges
merged operations:
 insert 199, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1236953, node heap has 4034 buffer(s)
0.00 hash searches/s, 0.55 non-hash searches/s
---
LOG
---
Log sequence number 8176212174
Log flushed up to   8176212174
Pages flushed up to 8176212174
Last checkpoint at  8176212174
0 pending log writes, 0 pending chkp writes
129212 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 639598592; in additional pool allocated 0
Dictionary memory allocated 1612053
Buffer pool size   38144
Free buffers       1024
Database pages     33086
Old database pages 12193
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 223420, not young 5361756
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59989, created 131, written 218511
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: 33086, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
19 read views open inside InnoDB
Main thread process no. 3409, id 47155039454976, state: sleeping
Number of rows inserted 11395, updated 88770, deleted 4851, read 3252049111
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

=====================================
2018-05-24 17:28:02 2ae323403700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 2 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 18166 srv_active, 0 srv_shutdown, 67866 srv_idle
srv_master_thread log flush and writes: 86030
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 21977
OS WAIT ARRAY INFO: signal count 20424
Mutex spin waits 2790, rounds 128767, OS waits 3665
RW-shared spins 17908, rounds 538145, OS waits 12430
RW-excl spins 284, rounds 174171, OS waits 5727
Spin rounds per wait: 46.15 mutex, 30.05 RW-shared, 613.28 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 46791224
Purge done for trx's n:o < 46791128 undo n:o < 0 state: running but idle
History list length 3134
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 46791202, not started
MySQL thread id 25536, OS thread handle 0x2ae32f648700, query id 1747187 10.0.11.44 wordpress
---TRANSACTION 46791169, not started
MySQL thread id 25537, OS thread handle 0x2ae32f689700, query id 1747193 10.0.11.204 wordpress
---TRANSACTION 46791172, not started
MySQL thread id 25538, OS thread handle 0x2ae32f6ca700, query id 1747199 10.0.11.204 wordpress
---TRANSACTION 46791186, not started
MySQL thread id 25540, OS thread handle 0x2ae32f74c700, query id 1747211 10.0.11.44 wordpress
---TRANSACTION 46791180, not started
MySQL thread id 25539, OS thread handle 0x2ae32f70b700, query id 1747205 10.0.11.204 wordpress
---TRANSACTION 46791195, not started
MySQL thread id 25535, OS thread handle 0x2ae32f607700, query id 1747181 10.0.11.204 wordpress
---TRANSACTION 46791207, not started
MySQL thread id 25534, OS thread handle 0x2ae32f5c6700, query id 1747175 10.0.11.44 wordpress
---TRANSACTION 46791171, not started
MySQL thread id 25533, OS thread handle 0x2ae32f585700, query id 1747169 10.0.11.44 wordpress
---TRANSACTION 46791174, not started
MySQL thread id 25532, OS thread handle 0x2ae32f544700, query id 1747163 10.0.11.204 wordpress
---TRANSACTION 46791187, not started
MySQL thread id 25531, OS thread handle 0x2ae32f503700, query id 1747157 10.0.11.204 wordpress
---TRANSACTION 46791176, not started
MySQL thread id 25530, OS thread handle 0x2ae32f4c2700, query id 1747150 10.0.11.44 wordpress
---TRANSACTION 46791201, not started
MySQL thread id 25529, OS thread handle 0x2ae2d89c7700, query id 1747144 10.0.11.44 wordpress
---TRANSACTION 46791205, not started
MySQL thread id 25528, OS thread handle 0x2ae325859700, query id 1747138 10.0.11.44 wordpress
---TRANSACTION 46791204, not started
MySQL thread id 25527, OS thread handle 0x2ae3216cb700, query id 1747132 10.0.11.44 wordpress
---TRANSACTION 46791203, not started
MySQL thread id 25525, OS thread handle 0x2ae325b65700, query id 1747123 10.0.11.204 wordpress
---TRANSACTION 46791200, not started
MySQL thread id 25524, OS thread handle 0x2ae2dbfa6700, query id 1747116 10.0.11.204 wordpress
---TRANSACTION 46791182, not started
MySQL thread id 25523, OS thread handle 0x2ae3258db700, query id 1747110 10.0.11.204 wordpress
---TRANSACTION 46791173, not started
MySQL thread id 25522, OS thread handle 0x2ae32774c700, query id 1747104 10.0.11.44 wordpress
---TRANSACTION 46791181, not started
MySQL thread id 25521, OS thread handle 0x2ae2d8a49700, query id 1747098 10.0.11.44 wordpress
---TRANSACTION 46791177, not started
MySQL thread id 25520, OS thread handle 0x2ae323ecb700, query id 1747092 10.0.11.44 wordpress
---TRANSACTION 46791191, not started
MySQL thread id 25519, OS thread handle 0x2ae3277ce700, query id 1747086 10.0.11.44 wordpress
---TRANSACTION 46791189, not started
MySQL thread id 25518, OS thread handle 0x2ae321649700, query id 1747080 10.0.11.204 wordpress
---TRANSACTION 46791199, not started
MySQL thread id 25516, OS thread handle 0x2ae325be7700, query id 1747073 10.0.11.204 wordpress
---TRANSACTION 46791185, not started
MySQL thread id 25515, OS thread handle 0x2ae327440700, query id 1747067 10.0.11.204 wordpress
---TRANSACTION 46791194, not started
MySQL thread id 25513, OS thread handle 0x2ae32174d700, query id 1747058 10.0.11.44 wordpress
---TRANSACTION 46791206, not started
MySQL thread id 25512, OS thread handle 0x2ae32168a700, query id 1747057 10.0.11.44 wordpress
---TRANSACTION 46791175, not started
MySQL thread id 25511, OS thread handle 0x2ae325a61700, query id 1747046 10.0.11.204 wordpress
---TRANSACTION 46791183, not started
MySQL thread id 25510, OS thread handle 0x2ae32589a700, query id 1747040 10.0.11.204 wordpress
---TRANSACTION 46791190, not started
MySQL thread id 25508, OS thread handle 0x2ae3259df700, query id 1747034 10.0.11.44 wordpress
---TRANSACTION 46791178, not started
MySQL thread id 25509, OS thread handle 0x2ae32f481700, query id 1747033 10.0.11.44 wordpress
---TRANSACTION 46791196, not started
MySQL thread id 25507, OS thread handle 0x2ae327689700, query id 1747022 10.0.11.204 wordpress
---TRANSACTION 46791193, not started
MySQL thread id 25506, OS thread handle 0x2ae323e8a700, query id 1747016 10.0.11.204 wordpress
---TRANSACTION 46791210, not started
MySQL thread id 25505, OS thread handle 0x2ae327481700, query id 1747010 10.0.11.204 wordpress
---TRANSACTION 46791213, not started
MySQL thread id 25504, OS thread handle 0x2ae3275c6700, query id 1747004 10.0.11.44 wordpress
---TRANSACTION 46791215, not started
MySQL thread id 25503, OS thread handle 0x2ae325ba6700, query id 1746998 10.0.11.44 wordpress
---TRANSACTION 46791208, not started
MySQL thread id 25502, OS thread handle 0x2ae32591c700, query id 1746992 10.0.11.204 wordpress
---TRANSACTION 46791214, not started
MySQL thread id 25501, OS thread handle 0x2ae2dbfe7700, query id 1746986 10.0.11.204 wordpress
---TRANSACTION 46791216, not started
MySQL thread id 25500, OS thread handle 0x2ae3217cf700, query id 1746980 10.0.11.44 wordpress
---TRANSACTION 0, not started
MySQL thread id 25499, OS thread handle 0x2ae3276ca700, query id 1746974 10.0.10.251 root
---TRANSACTION 46791218, not started
MySQL thread id 25498, OS thread handle 0x2ae323fcf700, query id 1746971 10.0.11.204 wordpress
---TRANSACTION 46791219, not started
MySQL thread id 25497, OS thread handle 0x2ae327544700, query id 1746965 10.0.11.44 wordpress
---TRANSACTION 46791197, not started
MySQL thread id 25496, OS thread handle 0x2ae2d8985700, query id 1746959 10.0.11.44 wordpress
---TRANSACTION 46791212, not started
MySQL thread id 25493, OS thread handle 0x2ae32f440700, query id 1746941 10.0.11.204 wordpress
---TRANSACTION 46791209, not started
MySQL thread id 25492, OS thread handle 0x2ae327503700, query id 1746935 10.0.11.44 wordpress
---TRANSACTION 46791220, not started
MySQL thread id 25491, OS thread handle 0x2ae32599e700, query id 1746929 10.0.11.44 wordpress
---TRANSACTION 46791211, not started
MySQL thread id 25490, OS thread handle 0x2ae323f4d700, query id 1746923 10.0.11.204 wordpress
---TRANSACTION 46791198, not started
MySQL thread id 25489, OS thread handle 0x2ae32595d700, query id 1746917 10.0.11.44 wordpress
---TRANSACTION 46791184, not started
MySQL thread id 25488, OS thread handle 0x2ae327648700, query id 1746916 10.0.11.44 wordpress
---TRANSACTION 46791170, not started
MySQL thread id 25487, OS thread handle 0x2ae327585700, query id 1746905 10.0.11.204 wordpress
---TRANSACTION 46791179, not started
MySQL thread id 25485, OS thread handle 0x2ae2d8a08700, query id 1746893 10.0.11.204 wordpress
---TRANSACTION 46791217, not started
MySQL thread id 25484, OS thread handle 0x2ae323f8e700, query id 1746887 10.0.11.44 wordpress
---TRANSACTION 46791192, not started
MySQL thread id 25483, OS thread handle 0x2ae325a20700, query id 1746881 10.0.11.204 wordpress
---TRANSACTION 46791188, not started
MySQL thread id 25482, OS thread handle 0x2ae32178e700, query id 1746875 10.0.11.44 wordpress
---TRANSACTION 46791168, not started
MySQL thread id 25481, OS thread handle 0x2ae323f0c700, query id 1746869 10.0.11.204 wordpress
---TRANSACTION 46791135, not started
MySQL thread id 25479, OS thread handle 0x2ae323e49700, query id 1746831 10.0.11.204 wordpress
---TRANSACTION 46791160, not started
MySQL thread id 25480, OS thread handle 0x2ae32170c700, query id 1746856 10.0.11.44 wordpress
---TRANSACTION 46791132, not started
MySQL thread id 25478, OS thread handle 0x2ae327607700, query id 1746816 10.0.11.44 wordpress
---TRANSACTION 46791129, not started
MySQL thread id 25477, OS thread handle 0x2ae32778d700, query id 1746807 10.0.11.44 wordpress
---TRANSACTION 46791122, not started
MySQL thread id 1, OS thread handle 0x2ae2d8944700, query id 1747218 localhost 127.0.0.1 rdsadmin init
SELECT 1
--------
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
60616 OS file reads, 356399 OS file writes, 165396 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 66 merges
merged operations:
 insert 199, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 1236953, node heap has 4034 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 8176212174
Log flushed up to   8176212174
Pages flushed up to 8176212174
Last checkpoint at  8176212174
0 pending log writes, 0 pending chkp writes
129212 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 639598592; in additional pool allocated 0
Dictionary memory allocated 1612053
Buffer pool size   38144
Free buffers       1024
Database pages     33086
Old database pages 12193
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 223420, not young 5361756
0.00 youngs/s, 0.00 non-youngs/s
Pages read 59989, created 131, written 218511
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: 33086, unzip_LRU len: 0
I/O sum[0]:cur[1], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
3 read views open inside InnoDB
Main thread process no. 3409, id 47155039454976, state: sleeping
Number of rows inserted 11395, updated 88770, deleted 4851, read 3252049226
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 6.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB: ###### Diagnostic info printed to the standard error stream
2018-05-24 17:28:18 3409 [Note] /rdsdbbin/mysql/bin/mysqld: Normal shutdown

2018-05-24 17:28:18 3409 [Note] Giving 62 client threads a chance to die gracefully
2018-05-24 17:28:18 3409 [Note] Event Scheduler: Purging the queue. 0 events
2018-05-24 17:28:18 3409 [Note] Shutting down slave threads
2018-05-24 17:30:28 3460 [Note] Plugin 'FEDERATED' is disabled.
2018-05-24 17:30:28 3460 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-24 17:30:28 3460 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-24 17:30:28 3460 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-24 17:30:28 3460 [Note] InnoDB: Memory barrier is not used
2018-05-24 17:30:28 3460 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-24 17:30:28 3460 [Note] InnoDB: Using Linux native AIO
2018-05-24 17:30:28 3460 [Note] InnoDB: Using CPU crc32 instructions
2018-05-24 17:30:28 3460 [Note] InnoDB: Initializing buffer pool, size = 596.0M
2018-05-24 17:30:28 3460 [Note] InnoDB: Completed initialization of buffer pool
2018-05-24 17:30:28 3460 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-24 17:30:28 3460 [Note] InnoDB: The log sequence numbers 8084612454 and 8084612454 in ibdata files do not match the log sequence number 8176212174 in the ib_logfiles!
2018-05-24 17:30:28 3460 [Note] InnoDB: Database was not shutdown normally!
2018-05-24 17:30:28 3460 [Note] InnoDB: Starting crash recovery.
2018-05-24 17:30:28 3460 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-05-24 17:30:29 3460 [Note] InnoDB: Restoring possible half-written data pages 
2018-05-24 17:30:29 3460 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 367, file name mysql-bin-changelog.007566
2018-05-24 17:30:34 3460 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-24 17:30:34 3460 [Note] InnoDB: Waiting for purge to start
2018-05-24 17:30:34 3460 [Note] InnoDB: 5.6.39 started; log sequence number 8176212174
2018-05-24 17:30:34 3460 [Note] Recovering after a crash using /rdsdbdata/log/binlog/mysql-bin-changelog
2018-05-24 17:30:34 3460 [Note] Starting crash recovery...
2018-05-24 17:30:34 3460 [Note] Crash recovery finished.
2018-05-24 17:30:34 3460 [Note] RSA private key file not found: /rdsdbdata/db//private_key.pem. Some authentication plugins will not work.
2018-05-24 17:30:34 3460 [Note] RSA public key file not found: /rdsdbdata/db//public_key.pem. Some authentication plugins will not work.
2018-05-24 17:30:34 3460 [Note] Server hostname (bind-address): '*'; port: 3306
2018-05-24 17:30:34 3460 [Note] IPv6 is available.
2018-05-24 17:30:34 3460 [Note]   - '::' resolves to '::';
2018-05-24 17:30:34 3460 [Note] Server socket created on IP: '::'.
2018-05-24 17:30:34 3460 [Note] Event Scheduler: Loaded 0 events
2018-05-24 17:30:34 3460 [Note] /rdsdbbin/mysql/bin/mysqld: ready for connections.
Version: '5.6.39-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2018-05-24 17:31:54 3460 [Warning] IP address '10.0.11.44' could not be resolved: Name or service not known
2018-05-24 17:33:31 3460 [Warning] IP address '10.0.11.204' could not be resolved: Name or service not known
2018-05-24 17:35:01 3460 [Warning] IP address '10.0.10.251' could not be resolved: Name or service not known


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.