AWS RDS MySQL Read Replica Lag Issues

Question

I run a service that needs to be able to support about 4000+ IOPS and keep replica lag <=1 second to function properly.

I am using AWS RDS MySQL instances and have 2 read replica's. My service was experiencing giant replica lag spikes on the read replica's so I was in contact with AWS support for a week trying to understand why I was experiencing the lag--I had 6000 IOPS provisioned and my instances were very powerful. They gave me all kinds of reasons.

After changing instance types, upgrading to MySQL 5.6 from 5.5 to take advantage of multi-threading, and them replacing underlying hardware I was still seeing significant replica lag randomly.

Eventually I decided to start tinkering with the parameter groups changing my configs for just the read replica's on anything I could find that was involved in the replication process and am now finally experiencing <= 1 second of replica lag.

Here are the settings I changed and their values that appear to be successful (I copied the default mysql 5.6 param group and changed these values applying the updated paramater group to just the read replicas):

innodb_flush_log_at_trx_commit=0
sync_binlog=0
sync_master_info=0
sync_relay_log=0
sync_relay_log_info=0

Please read about each of these to understand the impact of the modifications: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

Other things to make sure you take care of:

Convert any MyISAM tables to InnoDB
Upgrade from MySQL < 5.6 to MySQL >= 5.6
Ensure that your provisioned IOPS are > the combined read/write IOPS you require
Ensure that your read replica instances are >= master instance

If anyone else has any additional parameters that could be modified on the read replica's or master DB to get the best replication performance I'd love to hear more.

UPDATE 7-8-2014

To take advantage of Mysql 5.6 multi-thread replication I've set:

slave_parallel_workers=5 (Set it to the number of read replica DBs you have running)

I found this in this here:

https://blogs.oracle.com/MySQL/entry/benchmarking_mysql_replication_with_multi

Melancholic

Can you clarify "Ensure that your read replica instances are >= master instance" - do you mean in terms of the instance class? – Lundeen

Yes, ensure the instance class is at least the same if not greater – Melancholic

Answer 1

Mysql replication executes all the transactions on a single database in order , and master - can execute those transactions in parallel.

You probably have most of the updates executed on a single DA, and that is what not allowing you to get advantage of multithreaded replication.

Check the iostat on your replica server. Most of the time those problem occurs because of high IO on the machine.

In order to decrease the IO on a machine - there are several additional changes that you can do:

• Increase innodb_buffer_pool_size - this is the first thing you should change from default. If this instance runs only mysql - you can allocate about 80% of your available the memory here.

• Verify also the following parameters:

   log_slave_updates = false
   binlog_format = STATEMENT
  

  (if you have MIXED or ROW binlog_format configured - verify that you understand what does that means from here http://dev.mysql.com/doc/refman/5.6/en/binary-log-setting.html

• If you have a lot of data that is being changed for several times - increasing

  innodb_max_dirty_pages_pct to 90 or 95% can be worth checking.

Affinal

Answer 2

I had extreme lag that came out of nowhere after years of consistent database load. To solve it, along with the settings in the original post, I had to add:

binlog_format=ROW
slave_rows_search_algorithms=TABLE_SCAN,INDEX_SCAN

Those are recommedned in this repost article. After a reboot the lag quickly disappeared

Nuri