Amazon RDS MySQL instance performs very slow

Question

I have published my website on Amazon EC2 (Singapore region) and I have used MySQL RDS instance for the data storage. Everything is working very fine except performance.

I seems that, my all queries, especially the select statement, is performing very slowly. If I check this issue on my local PC, there it is working very well. But when I am trying to get data from RDS instance, it is very slow. Some of the select statements takes 2-3 seconds to fetch data.

I have properly tuned up all table indexes, and normalized/de-normalized as required. I have made all necessary settings on RDS custom parameter group (eg. max_connection, buffer etc). I don't know if I am missing something, but it didn't work for me - performance didn't increase.

So, can someone please help me with this issue?

Answer 1

It is worth noting that, for whatever reason, MySQL query cache is OFF by default in RDS. We learned that the hard way ourselves this week.

This won't help performance of your initial query, but it may speed things up in general.

To re-enable query cache:

1. Log in to the RDS Console
2. Click on your RDS instance to view it's details
3. Edit the Database Parameter Group
4. Be sure to set both query_cache_size and query_cache_type

(Disclaimer: I am not a DBA so there may be additional things I'm missing here)