Amazon RDS MySQL performance issues

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.

It seems that all queries, but especially select statements, are performing very slow. If I check this issue on my local PC, it is working very well. But when I try to get data from RDS instance, it is 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. (don't know if I am something missing), but it has not helped. Can someone help me figure out the issue?

Answer 1

When you create an RDS instance, you pick an instance size, a database size, and optionally determine if you need specific IOPS.

I would start by looking at the RDS monitors, and see if there are any parameters that are hitting limits. You may need to increase the instance size or IOPS to increase performance.

Answer 2

You can profile your MySQL queries using Show Profile

SET PROFILING = 1; 
SET profiling_history_size = 0;
SET profiling_history_size = 15;

YOUR QUERY;

SHOW PROFILES;

SELECT state, ROUND(SUM(duration),5) AS `duration (summed) in sec`
FROM information_schema.profiling 
WHERE query_id = <Id of a query> 
GROUP BY state 
ORDER BY `duration (summed) in sec` DESC;

SET PROFILING = 0;

Answer 3

Make sure your security group (firewall) only allow connections from your IPs to MySQL port.

In your AWS control panel check monitoring tab for RDS CPU, Reads, writes etc.

Make sure you are using InnoDB (because it has better locking-level and concurrency) which could be your issue (compared to your lonely local development environment).