I'm using Amazon RDS as a slave for my dedicated MySQL server (running outside of AWS).
Replication has been working alright for the past few months, until yesterday when I updated the structure of tables with 100+ million rows on the master.
My main database server has NVMe SSD drives rated up to 26000 write IOPS, while my RDS storage of 365 GB should have a baseline speed of 1095 IOPS according to the docs.
I ran several consecutive ALTER TABLE
statements, that may have taken around one hour in total to complete on the master.
Due to the figures above, I was expecting some delay in replication, but it's now been 24+ hours and it looks like something is broken somewhere.
First, I have been receiving these email notifications from RDS every 10 minutes since yesterday:
2019-01-24 13:27:21.825
Read Replica Replication Error - SQLError: 1205, reason: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.2019-01-24 13:28:21.871
Replication for the Read Replica resumed2019-01-24 13:37:21.827
Read Replica Replication Error - SQLError: 1205, reason: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable.2019-01-24 13:38:21.814
Replication for the Read Replica resumed- ...
The pattern is always the same:
T
: Replication errorT + 1min
: Replication resumedT + 10min
: Replication errorT + 11min
: Replication resumed- ...
SHOW SLAVE STATUS
has been returning the same Exec_Master_Log_Pos
value from the very beginning. Slave_SQL_Running_State
says Applying batch of row changes (write)
.
SHOW PROCESSLIST
on the slave shows a single query, running for 90000+ seconds:
Applying batch of row changes (write)
The CPU usage and write IOPS metrics of the slave show peaks every 5 minutes: (—edit— this seems to be caused by a FLUSH LOGS
issued by RDS every 5 minutes)
CPU
Write IOPS
SHOW CREATE TABLE
on the slave shows an updated table structure, so it looks to me that the changes have started to replicate.
The average write IOPS on the slave is ~300, average read IOPS ~40, which alone is weird as the total is way below the baseline of 1095 IOPS the storage should provide.
Given the average write IOPS, it may just be a matter of waiting a bit more for replication to complete, but I'm really starting to wonder if it will ever complete successfully.
Q: What do these "Replication Error" / "Replication Resumed" messages mean?
The message says that it's giving up, then that it's resuming, which is confusing me.
Moreover, I don't understand how a transaction could be failing: the slave should be executing only one transaction at a time, so there should be no deadlock or lock wait timeout occurring. If it's not a concurrency issue, and only one transaction is running, why would it give it up after only 10 minutes?
Is my replication failing? Or are these error messages due to some kind of system query issued by RDS, that could not complete due to the long running transaction?
If there is a permanent error here, how do I fix it?
Edit: more information as requested:
SHOW CREATE TABLE
:
CREATE TABLE `IsbnLookup` (
`country` enum('us','ca','gb','de','it','es','fr') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`isbn` bigint(13) unsigned zerofill NOT NULL,
`time` int(10) unsigned NOT NULL,
PRIMARY KEY (`country`,`isbn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC
CREATE TABLE `IsbnLookupQueue` (
`country` enum('us','ca','gb','de','it','es','fr') CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`isbn` bigint(13) unsigned zerofill NOT NULL,
`pid` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`country`,`isbn`),
KEY `IsbnLookupQueue_PID` (`country`,`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC
SHOW ENGINE INNODB STATUS
: https://pastebin.com/raw/34prH8weSHOW GLOBAL STATUS
: https://pastebin.com/raw/XevBvYvbSHOW GLOBAL VARIABLES
: https://pastebin.com/raw/dPbgEppg
db.t2.small
instance with 2GB RAM.