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.825Read 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.871Replication for the Read Replica resumed
2019-01-24 13:37:21.827Read 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.814Replication for the Read Replica resumed
The pattern is always the same:
T: Replication error
T + 1min: Replication resumed
T + 10min: Replication error
T + 11min: Replication resumed
SHOW SLAVE STATUS has been returning the same
Exec_Master_Log_Pos value from the very beginning.
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)
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