Reducing Writes on Amazon RDS

Recently I transitioned a client from using an ec2 based MySQL server to Amazon RDS. After the migration I noticed a large number of writes to the system which was degrading the performance of MySQL. Inserts and updates were taking multiple seconds to complete, and this was on a larger sized instance than the ec2 solution.

I initially found this article that suggested changing the innodb_flush_log_at_trx_commit variable to either 0 or 2 could help solve the problem. However even after this change the system was still writing extensively to disk.

I dug a little deeper and found in MySQL a large majority of queries where writing temporary tables which would explain the extensive writing to disk. After analysing the previous my.cnf file and comparing to the RDS instance I realized I failed to mirror all the variables I previously had setup. Specifically the tmp_table_size, max_heap_table_size and query_cache_size. Of the 3 the one that had the most dramatic affect on the performance and writs was the query_cache_size. After setting this variable to what the ec2 instance was using the CPU load and system writes drop substantially.

See the following charts:

So if you run into a similar problem try tweaking your query_cache_size and see if that affects your system writes as dramatically as it did for me.