Since you are running MySQL 5.5, have you considering tuning InnoDB for better performance?

There are several variables that have been added to the MySQL 5.1 InnoDB Plugin that are now native to MySQL 5.5. They usually help increase hyperthreading and take advantage of more IOPS if the environment can handled it. In your case, you should be able to.

I have spun RDS models before (See my post : Local database vs Amazon RDS) and can tell you that RDS is not very helpful in tuning InnoDB. You will have to take the bull by the horns on this one.

When you spun up the RDS instance, you probably used the default DB Parameter Group

You should be able to create a new DB Parameter Group for yourself. When you get to this, set the following:

• innodb_read_io_threads : Highest allowed is 64. In RDS, highest allowed is 16. Default is 4.
• innodb_write_io_threads : Highest allowed is 64. In RDS, highest allowed is 16. Default is 4.
• innodb_thread_concurrency : Default is already 0. Never set this value to any other number.

I suggest these because they are default in RDS for innodb_read_io_threads and innodb_write_io_threads are just too low. In addition, your SHOW ENGINE INNODB STATUS\G

--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)

only shows the default of 4 for each class of io threads.

Here is the Bad News: To implement config changes, once you create your own DB Parameter Group, you must export the data from the old RDS instance, create a new instance using your new DB Parameter Group, and reload. In more detail...

• Create a Custom DB Parameter Group (call it MySettings)
• Download RDS CLI and setup a config file with your AWS Credentials
• Execute the following : ./rds-modify-db-parameter-group MySettings --parameters "name=innodb_read_io_threads,value=16,method=immediate"
• Modify using DB Parameter Option List MySettings
• Restart the MySQL RDS Instance

I hope this helps !!!

If you are running LOAD DATA LOCAL INFILE on a Master every 5 minutes, how does that replicate ?

Back on Jan 22, 2012, I wrote an answer to this post : MySql shell command not replicated to slave

In my answer, I explained how LOAD DATA LOCAL INFILE gets replicated:

• Master
  1. Executes LOAD DATA LOCAL INFILE
  2. Copies the contents of entire text file used into the binary logs
  3. The SQL command LOAD DATA LOCAL INFILE is appended to the latest binary log
• Replication ships all of this from the Master's Binary Logs to the Slave's Relay Logs
• Slave
  1. Sees the Text File in the Relay Logs
  2. Reads all the blocks from multiple Relay Logs
  3. The text file get materialized in the /tmp folder
  4. Reads LOAD DATA LOCAL INFILE command from Relay Log
  5. Executes LOAD DATA LOCAL INFILE in the SQL Thread

During Steps 1-4 on the Slave, the IO Thread would have to be in the Reading event from the relay log state, constantly extracting the CSV needed for the next LOAD DATA LOCAL INFILE command. Sometimes, this causes Seconds_Behind_Master to leap hundreds or even thousands of seconds at a time without warning after staying relatively idle.

Running STOP SLAVE; does not help at all. The reason STOP SLAVE; hangs? Steps 1-4 of the Slave will lock the IO Thread until the complete extraction of the CSV file. Even when the extraction has completed, there is the LOAD DATA LOCAL INFILE itself. Running STOP SLAVE; will lock on the SQL Thread running LOAD DATA LOCAL INFILE.

In this crazy paradigm, Replication Lag has to increase steadily. Just look at your Relay_Log_Space. It is 5708184440 (like 5.3 GB). There are multiple LOAD DATA LOCAL INFILE commands just waiting to execute.

Look at the how Seconds_Behind Master increases

• LOAD DATA LOCAL INFILE is executed on the Master
• The CSV needed in loaded into Binary Logs
• The CSV needed in unloaded from Relay Logs
• LOAD DATA LOCAL INFILE is executed on the Slave

If LOAD DATA LOCAL INFILE takes 2 minutes, double that number and add the taken to ship the CSV file through the MySQL Replication process. You may need to come up with a different method of loading data that does not use LOAD DATA LOCAL INFILE.

UPDATE 2013-04-05 15:00 EDT

If MySQL Replication continues falling behind (Seconds_Behind_Master keeps increasing) while the Master keeps getting log-jammed with small LOAD DATA LOCAL INFILE commands, there is only one more thing I could suggest: Most people never touch sync_binlog, which is normally zero. What effect can this have on replicating LOAD DATA LOCAL INFILE ?

According to the Documentation on sync_binlog, this setting can be used to flush binlog changes to disk. Since it is 0 by default, your Master DB Server is at the mercy of the OS because OS is responsible for flusihng binlog changes. When you set sync_binlog to 1, everything may actually change for better. How?

Here is what is probably happening when sync_binlog is 0 on a Master:

• You run LOAD DATA LOCAL INFILE
• mysqld on Master writes the command to the binlog
• mysqld on Master writes the entire CSV file into the binlogs
• mysqld on Master leaves it to the OS to flush binlogs changes
• Slave reads all binlogs info from Master except the last binlog that the Master's OS did not flush
• Slave status shows it is trying to retrieve the remaining info

This is how sync_binlog can hopefully improve things:

• You run SET GLOBAL sync_binlog = 1;
• You run LOAD DATA LOCAL INFILE
• You run SET GLOBAL sync_binlog = 0;
• mysqld on Master writes the command to the binlog
• mysqld on Master writes the entire CSV file into the binlogs
• mysqld on Master flushes every write to the binlogs because sync_binlog = 1
• Slave reads all binlogs info from Master
• Slave status should show it has read every needed binlog

Give it a Try !!!

UPDATE 2013-04-09 11:23 EDT

If you have a low-to-moderate amount of writes (INSERT, UPDATE, DELETE, and ALTER TABLE) in the Master DB Server, leaving sync_binlog at 1 may not be a bad idea. You would then need to do the following:

STEP 01) On the Slave, run STOP SLAVE;

STEP 02) On the Master, add sync_binlog=1 to /etc/my.cnf:

[mysqld]
sync-binlog=1

STEP 03) Run one of the following on the Master:

SET GLOBAL sync_binlog = 1;

or

service mysql restart

STEP 04) On the Slave, run START SLAVE;

Give it a Try !!!