I have a db.t2.small read replica, which crashes every time I run mysqldump.
The issues started after I made some table changes - my main suspicion is the use of InnoDB.
This replica is used as a backup (incase the main server was to fail); and allowed me to run mysqldump every night, so I can create an off-site backup without effecting the main server.
AWS now goes though the recovery process when I use mysqldump.
01:53:13 - Recovery of the DB instance has started. Recovery time will vary with the amount of data to be recovered.
01:57:11 - DB instance restarted
01:57:36 - Recovery of the DB instance is complete.
Annoying the error logs are pretty much empty, so I have no idea what's causing it to fail.
The largest tables are:
788 MB = report_data
410 MB = report_data_log
418 MB = email_log_boy
The report_data table used to contain 36 TEXT fields (an example of more fields being added over time), and used MyISAM.
I changed this to use a simpler 1 TEXT field per row (an report_id, field_name, field_value, created_date, author_id), and it's now setup to use InnoDB.
The overall table size has decreased a bit.
The report_data_log table is basically the same table structure, but with a date to record when each value was replaced (as in, on editing, the record is copied from the report_data table, so I have a history of what's changed in the last 3 months).
I export each table individually with:
/usr/bin/mysqldump --defaults-extra-file=/backup/database.cnf --skip-extended-insert --skip-dump-date db report_data_log
I have tried adding --single-transaction, but that hasn't changed anything.
The config file just contains the username, password, host, ssl-ca, and sets ssl-verify-server-cert to true.
The /etc/mysql/conf.d/mysqldump.cnf file contains:
[mysqldump]
quick
quote-names
max_allowed_packet = 1024M
The server configuration hasn't been changed recently, where it includes:
max_allowed_packet: 1073741824
net_read_timeout: 3600
net_write_timeout: 3600
Oddly, it's able to export the 788MB report_data table, but crashes after exporting ~200MB of the 410MB report_data_log table.
Using db.t2.medium will probably fix this issue, but it doubles the cost - which seems a little silly considering the main server is fine, and both are idle most of the time (never appears to exceed 25% CPU, average 1.5%; or running low on Freeable Memory).
I'm tempted to switch back to MyISAM, but was wondering if there are any better AWS RDS logs to find out what's happened, or if there is something different about InnoDB that I'm missing?
As an interesting aside, the "db.t2.small" MariaDB instance provides 2GB of RAM.
The parameter group sets "innodb_buffer_pool_size" to "{DBInstanceClassMemory*3/4}".
But when I run:
SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_size";
It returns 2147483648... so it's being set to 2GB, not 1.5GB.
