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
  • 1
    @Michael-sqlbot yes, it is T2.micro and credit balance starts dropping fast when this happens but it doesn't reach zero (at least I can't see it on the graphs). – AlexD May 25 at 10:50
  • 1
    Are you getting this issue daily? – SQLadmin May 31 at 6:51
  • 1
    @SQLadmin almost every night but sometimes it can live for 3-4 days without crashing. – AlexD May 31 at 10:51
  • 1
    Question: Are any of the tables MyISAM ??? Please run this query : SELECT IFNULL(table_schema,'Total') db,COUNT(1) table_count FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys','innodb') GROUP BY table_schema WITH ROLLUP;. How many MyISAM tables do you have ??? – RolandoMySQLDBA May 31 at 14:47
  • 1
    @RolandoMySQLDBA There was couple of MyISAM tables but I converted them to InnoDB after the first few crashes. – AlexD Jun 1 at 5:53

All tables in MySQL/MariaDB need to be in InnoDB/XtraDB.

The Amazon RDS documentation states that for MySQL and MariaDB.

If there are any MyISAM or Aria Tables in heavy use during a snapshot, a snapshot would probably have issues. MyISAM and Aria are neither transaction safe nor crash safe.

Unless you can assure that you run FLUSH TABLES WITH READ LOCK; on all your MyISAM/Aria tables (guaranteeing no write traffic and no tables locks) and launch a manual snapshot, you will have snapshot problems and crash scenarios like the one you have been experiencing.

I can see MyISAM's locking scheme (Locking Granularity is Table) interfering with transactions that rollback and well as RDS trying to perform its own locking independent of storage engine behavior. These issues must be addressed for RDS to support transactions and point-in-time recovery.

Hopefully, you should not see any more crashes now that you have converted the few MyISAM tables you have to InnoDB.

  • 1
    I have converted MyISAM tables almost a month ago and RDS console stopped showing appropriate warning about MyISAM tables but crashes still continue. – AlexD Jun 3 at 10:46

(Not an "Answer", but an analysis of the VARIABLES and STATUS.)

Observations:

  • Version: 5.6.39-log
  • 1.00 GB of RAM
  • You are not running on Windows.
  • Running 64-bit version
  • You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Due to having such a small RAM, various settings should be lowered to avoid swapping. On the other hand, if you are sure there is no swapping now, then tese do not need lowering.

key_buffer_size = 8M
innodb_buffer_pool_size = 400M
max_connections = 40
table_open_cache = 300

There are several indications of slow queries. Would you like to find and discuss some of them?

Details and other observations:

( Key_blocks_used * 1024 / key_buffer_size ) = 31 * 1024 / 16M = 0.19% -- Percent of key_buffer used. High-water-mark. -- Lower key_buffer_size to avoid unnecessary memory usage.

( innodb_buffer_pool_size / _ram ) = 596M / 1024M = 58.2% -- % of RAM used for InnoDB buffer_pool

( innodb_buffer_pool_size ) = 596M -- InnoDB Data + Index cache -- 128M (an old default) is woefully small.

( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 198,592 / 660882 = 30.0% -- Write requests that had to hit disk -- Check innodb_buffer_pool_size

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 91,594,240 / (82893 / 3600) / 2 / 128M = 0.0148 -- Ratio -- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 82,893 / 60 * 128M / 91594240 = 2,024 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf. -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 4,774 / 6429 = 0.743 -- Churn -- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( innodb_print_all_deadlocks ) = OFF -- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( max_connections ) = 66 -- Maximum number of connections (threads). Impacts various allocations. -- If max_connections is too high and various memory settings are high, you could run out of RAM.

( min( tmp_table_size, max_heap_table_size ) / _ram ) = min( 16M, 16M ) / 1024M = 1.6% -- Percent of RAM to allocate when needing MEMORY table (per table), or temp table inside a SELECT (per temp table per some SELECTs). Too high may lead to swapping. -- Decrease tmp_table_size and max_heap_table_size to, say, 1% of ram.

( (Com_show_create_table + Com_show_fields) / Questions ) = (225 + 41309) / 2475609 = 1.7% -- Naughty framework -- spending a lot of effort rediscovering the schema. -- Complain to the 3rd party vendor.

( local_infile ) = ON -- local_infile = ON is a potential security issue

( Created_tmp_disk_tables ) = 196,805 / 82893 = 2.4 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs -- increase tmp_table_size and max_heap_table_size. Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM. Better indexes and reformulation of queries are more likely to help.

( Created_tmp_disk_tables / Questions ) = 196,805 / 2475609 = 7.9% -- Pct of queries that needed on-disk tmp table. -- Better indexes / No blobs / etc.

( Created_tmp_disk_tables / Created_tmp_tables ) = 196,805 / 270429 = 72.8% -- Percent of temp tables that spilled to disk -- Maybe increase tmp_table_size and max_heap_table_size; improve indexes; avoid blobs, etc.

( Select_scan ) = 291,700 / 82893 = 3.5 /sec -- full table scans -- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 291,700 / 2200761 = 13.3% -- % of selects doing full table scan. (May be fooled by Stored Routines.) -- Add indexes / optimize queries

( binlog_error_action ) = IGNORE_ERROR -- What to do if the binlog cannot be written. -- IGNORE_ERROR is the default for backwards compatibility, but ABORT_SERVER is recommended.

( binlog_format ) = MIXED -- STATEMENT/ROW/MIXED. ROW is preferred; it may become the default.

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query. -- Suggest 2

( back_log / max_connections ) = 63 / 66 = 95.5%

You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.

Abnormally Large (but I don't have any advice on what to do about them)

Com_create_db = 3.3 /HR
Com_create_table = 64 /HR
Com_flush = 12 /HR
Com_purge = 12 /HR
Com_release_savepoint = 0.043 /HR
Com_rollback_to_savepoint = 9.6 /HR
Com_savepoint = 0.043 /HR
Com_show_binlogs = 12 /HR
Com_show_databases = 6.7 /HR
Com_show_keys = 38 /HR
Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) = 42.1%
Handler_read_next / Handler_read_key = 101
Handler_savepoint = 0.043 /HR
Handler_savepoint_rollback = 9.6 /HR
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 822
Ssl_session_cache_size = 0.0MB
innodb_lru_scan_depth / innodb_io_capacity = 5.12

Abnormal strings:

Slave_heartbeat_period = 0.000
explicit_defaults_for_timestamp = ON
innodb_adaptive_hash_index = OFF
innodb_force_load_corrupted = OFF
log_bin_trust_function_creators = ON
log_output = TABLE
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
secure_file_priv = /tmp/
slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN
  • (This confirms that most (or all) tables are now InnoDB.) – Rick James Jun 8 at 19:53
  • How big is wp_sitemeta? There are certain addons that are flawed in that they add thousands of rows to that table unnecessarily and/or fail to clean up after themselves. Meanwhile, google for wp_sitemeta large.

  • What addons do you have?

  • The queries against wp_sitemeta can be sped up by applying the schema changes similar to those recommended here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

  • And, yes, switch to InnoDB.

  • 1
    wp_sitemeta has about 2K records. There is no performance issues outside of the backup time window. – AlexD Jun 8 at 17:27
  • 1
    If I am not mistaken, the backup will completely lock all MyISAM tables for the duration of the backup. Converting to InnoDB may be the main part of the cure. – Rick James Jun 8 at 17:47
  • 1
    all MyISAM tables were converted to InnoDB a month ago. – AlexD Jun 8 at 19:40

Suggestions to consider for your [mysqld] section of my.cnf-ini

max_connections=50  # from 66 - max_used_connections was 34
thread_cache_size=100  # from 8 to support 69 threads_created+other threads
thread_concurrency= 30  # from 10 for additional concurrency capacity
innodb_io_capacity_max=20000  # from 2000 since you have SSD on your t2.micro
innodb_io_capacity=10000  # from 200 to expedite SSD activity
read_rnd_buffer_size=256K  # from 512K to reduce handler_read_rnd_next RPS
innodb_lru_scan_depth=128  # from 1024 to reduce CPU busy every SECOND
innodb_purge_threads=2  # from 1 to reduce innodb_buffer_pool_pages_dirty
innodb_flushing_avg_loops=10  # from 30 to keep up with MVCC activities
innodb_change_buffer_max_size=15  # from 25 percent for set aside for chgd rows
innodb_buffer_pool_size=500M  # from ~596M since only 1G RAM available
key_buffer_size=8M  # from 16M because MyISAM involvement is minor
innodb_flush_neighbors=0  # from 1 to conserve CPU, SSD has no rotational delay
open_files_limit=10000  # from 65535 for more reasonable limit

for additional assistance my profile has contact information.

Other details to research, com_rollback_to_savepoint AVG 1 every 377 seconds handler_rollback avg 1 every half hour innodb_print_all_deadlocks=1 # from OFF to record in error log, as needed

check ulimit -a for Open Files ulimit -n 20000 more effective limit

2018-06-16 additional Suggestion for your my.cnf-ini [mysqld] section

innodb_adapative_hash_index=1  # from 0 to minimize deadlocks
  • @alexd Were you able to apply any of my suggestions of June 10? Please upvote/accept any helpful suggestions/advice. If there are any remaining issues, it would probably be best to open a new question. This one has many details. – Wilson Hauck Jul 23 at 17:10

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.