Key behind RDS MySQL’s Cross-Region Replicas

RDS uses native MySQL binary log based replication to create read replicas. There are mainly two threads involved in MySQL replication setup i.e., IO thread and SQL thread. IO thread is responsible to connect to master and fetch the binary log events and save them as relay log files on the slave instance. SQL thread will read those events from relay logs and execute them on the slave.

The main draw back with this approach is that all the queries executed on the master by multiple connections in parallel need to be executed by single thread (SQL_thread) on the replication instance i.e., MySQL replication is single threaded by default.

In recent versions of MySQL we have feature to enable multiple threads by modifying slave_parallel_workers variable to improve the replication performance. However the time required to sync the replica depends on the write workload present on the master. Even after replica is in sync with master, If workload changes i.e., many write operations or large transactions modifying number of rows executed on master then replication lag will increase.

Replication lag is calculated from “Seconds_behind_master” value from show slave status and Seconds_Behind_Master parameter shows a huge delay in seconds. However, this can be misleading, because it only measures the difference between the timestamps of the relay log most recently executed, versus the relay log entry most recently downloaded by the IO_THREAD.

Hope this helps you in planning an efficient solution for your RDS MySQL’s Cross-Region Read-Replicas.

Happy Learning!

Cheers,
Aman Kharbanda

Advertisements

Author: sqlserverposts

Database Consultant by profession. I am a geek and a avid sports lover too. "Progress informed by the past, inspired by future".

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s