RDS read-only replication cluster keeps pausing
i have followed this guid to create a read-only Aurora replication cluster to offload intense read queries without impacting the production database: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Replication.html#AuroraMySQL.Replication.MySQL
It is working for the most part. The primary instance is a very busy server, with lots and lots of writes every second. So it was odd to me as I was watching the SHOW SLAVE STATUS
output that it suddenly. stopped with "Waiting for master to send event", the log positions did not change, and "seconds behind master" remained at zero. Then, exactly 1 minute later, the log positions started moving again, and "seconds behind master" jumped to 60 and slowly reduced back to zero; two minutes later, again, it pauses for a minute, and then catches up with the 60 second delay down to zero.
I did a lot of replication setups before my RDS days, this is the first time I've done a manual replication in Aurora. I've never seen anything like this before. Is it possible that there is a setting, maybe on the primary instance, that causes the bin log to pause for a minute every 2 minutes? Or something else I'm missing?
Thanks in advance for any help you all might have!
Hi there I understand that you created a read only-only aurora replica cluster to offload intense queries. It is expected to have replica lag since binlog replication is asynchronous If nothing is happening on the master [1], the replica will wait for until the master to sent event hence the event that you see. The replica is only active when there is changes to apply[2]. Additionally, it is best practice that the size of the replica be equal to the master instance.
I hope the above information is helpful.
References:
[1]https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-high-replica-lag/
[2] https://dev.mysql.com/doc/refman/8.0/en/replica-io-thread-states.html
Relevant questions
Aurora Create cross-region read replica vs Add Region
Accepted Answerasked a year agoDelayed Replication w/ Aurora MySQL
Accepted Answerasked 4 years agoHow to ensure replica is in sync between AWS RDS PSQL primary DB and Aurora RDS PSQL replica cluster?
asked 2 months agomigrate data to new Aurora cluster
asked a month agoSSL replication between Aurora clusters
Accepted Answerasked 3 years agoHow to enable Logical Replication for existing Aurora PG 13 Cluster?
asked 7 months agoRDS Postgresql migrate to Aurora Postgresql. Not showing "Aurora read replica" & "Migrate snapshot" options.
Accepted Answerasked 6 months agoRDS read-only replication cluster keeps pausing
asked 3 months agoMove RDS postgresql database to Aurora Serverless
Accepted Answerasked 3 years agoUse RDS Postgres Replicas as a cluster
Accepted Answerasked 5 months ago