RDS Read Replica Lag
I have an db.m5.8xlarge RDS MySQL database in the us-east-2 region with 15k Max IOPS and a read replica of the same size and specifications in us-east-1.
When our business day starts our replica lag starts. It generally reached about 6000-8000 second on any given business day.
While this is a high transaction database, this level of lag makes using the read replica for anything other than a straight back up impossible.
I have spent hours researching and trying to tune the MySQL settings to get this replica lag under control.
Does anyone else have any experience solving this problem?
I am running out of ideas.
Thank you.
Have you looked into configuring parallel apply on the read replica? By default MySQL replication uses single threaded apply. So if your workload on the source database uses high concurrent connections, these can be serialized on the read replica unless you configure parallel apply. All workloads do not necessarily tend to parallelize as well as others so your mileage might vary, but that is something you can try.
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html see: replica-parallel-workers
I might have found it:
slave_parallel_workers
EDIT: The fact AWS warns me for using a term I copied from their own website is deplorable.
This didn't actually fix the issue. I'm not sure why we get a huge replica lag spike every day at the same time.
Backups are not turned on for the replica.
Relevant questions
RDS - Read/Write Replica Loadbalancing
asked 2 months agoUpgrading Aurora MySQL 5.6 to 5.7 on cross region replica stuck in pre-upgrade checks and reboots every 30 minutes
asked 3 months agoHow to ensure replica is in sync between AWS RDS PSQL primary DB and Aurora RDS PSQL replica cluster?
asked 2 months agoPostgres Read Replica (in a different region) Stuck in Deleting State
asked 3 years agoRDS Postgresql migrate to Aurora Postgresql. Not showing "Aurora read replica" & "Migrate snapshot" options.
Accepted Answerasked 6 months agoDMS between MySQL RDS in 2 different accounts
asked 2 months agoUnable to enter create read replica page for a MySQL RDS instance inside classic EC2
Accepted Answerasked 5 months agoRDS read-only replication cluster keeps pausing
asked 3 months agoRDS Postgres + MySQL low read iops
asked 2 months agoRDS Read Replica Lag
asked a month ago
This is interesting. Thank you for the post. Are you familiar with a similar setting for MySQL 5.7? I am digging through the docs now.