title: AWS RDS MySQL Read Replica Lag Issues
body: <p>I run a service that needs to be able to support about 4000+ IOPS and keep replica lag &lt;=1 second to function properly.  </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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 &lt;= 1 second of replica lag.</p>
  
  <p>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):</p>
  
  <pre><code>innodb_flush_log_at_trx_commit=0
  sync_binlog=0
  sync_master_info=0
  sync_relay_log=0
  sync_relay_log_info=0
  </code></pre>
  
  <p>Please read about each of these to understand the impact of the modifications: <a href="http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html">http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html</a> </p>
  
  <p>Other things to make sure you take care of:</p>
  
  <pre><code>Convert any MyISAM tables to InnoDB
  Upgrade from MySQL &lt; 5.6 to MySQL &gt;= 5.6
  Ensure that your provisioned IOPS are &gt; the combined read/write IOPS you require
  Ensure that your read replica instances are &gt;= master instance
  </code></pre>
  
  <p>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.</p>
  
  <p><strong>UPDATE 7-8-2014</strong></p>
  
  <p>To take advantage of Mysql 5.6 multi-thread replication I've set:</p>
  
  <pre><code>slave_parallel_workers=5 (Set it to the number of read replica DBs you have running)
  </code></pre>
  
  <p>I found this in this here:</p>
  
  <p><a href="https://blogs.oracle.com/MySQL/entry/benchmarking_mysql_replication_with_multi">https://blogs.oracle.com/MySQL/entry/benchmarking_mysql_replication_with_multi</a></p>