Amazon RDS Performance Tuning Settings

gistfile1.sh

rds-modify-db-parameter-group {param-group-name} \
--parameters="name=character_set_server, value=utf8, method=pending-reboot" \
--parameters="name=collation_server, value=utf8_general_ci, method=pending-reboot" \
--parameters="name=tmp_table_size, value={DBInstanceClassMemory/16}, method=pending-reboot" \
--parameters="name=max_heap_table_size, value={DBInstanceClassMemory/16}, method=pending-reboot" \
--parameters="name=query_cache_type, value=1, method=pending-reboot" \
--parameters="name=query_cache_size, value=131072, method=pending-reboot" \
--parameters="name=table_open_cache, value=2500, method=pending-reboot" \
--parameters="name=join_buffer_size, value={DBInstanceClassMemory/64}, method=pending-reboot" \
--parameters="name=thread_cache_size, value={DBInstanceClassMemory/12582880}, method=pending-reboot" \
--parameters="name=sort_buffer_size, value={DBInstanceClassMemory/64}, method=pending-reboot" \
--parameters="name=innodb_flush_log_at_trx_commit, value=0, method=pending-reboot" \
--parameters="name=innodb_additional_mem_pool_size, value={DBInstanceClassMemory/64}, method=pending-reboot" \
--parameters="name=innodb_max_dirty_pages_pct, value=90, method=pending-reboot" \
--parameters="name=max_allowed_packet, value=33554432, method=pending-reboot" \
--parameters="name=tx_isolation, value=READ-COMMITTED, method=pending-reboot"

Setting "query_cache_size" to 128M in KB per http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/.

m1.xlarge instances (the 64-bit/15GB you mention) get a NIC all to themselves and therefore have the best network (and by extension, EBS) performance. With striping you can sustain 1500 disk iops, as oppossed to the standard 100 iops.

When used in RDS, you will see highest iops by setting your storage size to at least 300GB, which triggers back-end striping.

If you need more RAM or CPU, only the c1.xlarge and m2.4xlarge instance types have the full NIC to themselves- they would also required 300GB stoarge allocation to take advantage of it.

Reference http://stackoverflow.com/questions/6986568/which-rds-instance-type-has-better-io-extra-large-db-instance-or-high-memory-ex.

I would also increase innodb_read/write_io_threads to 16.

And would mention that innodb_flush_log_at_trx_commit at 0 decreases data durability in case of power loss, that should be taken very seriously.

Is the parameter query_cache_size specified in bytes or in kilobytes? A google search have reported mixed results.

If it indeed is in bytes, then the value is to low.