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, specially select statement, is performing very slow. 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 worked for me - performance didn't increase.

So, can someone please help me with this issue?

Answer 1

RDS MySQL performance can be increased in following ways assuming the system has more read ratio:

1. Use Larger instance types, they come with better NW bandwidth. Example AWS Quadruple EXL comes with 1,000 Mbps bandwidth.
2. Use PIOPS storage you can extract 12,500 IOPS of 16KB from MySQL DB
3. If lots of read is performed, add one or more Read Replica's to increase read performance
4. Apply standard practices like: Tune the queries, apply the indexes etc

Answer 2

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)

Answer 3

You can check where the query is taking time by making use of profiling. Use the below query:

1. set profiling=1
2. execute your select query
3. show profile

This will tell you about the status of the query and where the query is spending its time. If the sum of all the time returned by the profiling is less than the actual execution time of the query, then maybe other factors like Network bandwidth may be the cause of it.

Answer 4

For me, it was nothing to do with MySQL but rather the instance type I was on t2.medium. The problem is I ran out of CPU credits because the load on the DB was too high and the balance kept going down until finally, I was getting far fewer credits hourly then where needed.

Here is what I saw in RDS CloudWatch under CPU Credit Usage:

If you have the same problem it may be time to switch to a different instance. Here is the list of instance types:

https://aws.amazon.com/rds/instance-types/

Hope this helps.

Answer 5

First i highly recommend to look over these queries using

SHOW FULL PROCESSLIST

You can read more about it on SHOW FULL PROCESSLIST

This will show you the time each query take.

Then you can use

EXPLAIN

You can read more about it on EXPLAIN

This will show you if you need some enhancement on your queries

Answer 6

It is important to have your RDS and EC2 instances not in the same region but in the same availability zone to minimize the latency.

I had an API hosted in Ireland on EC2 and moved the Database to a MySQL cluster in Virginia USA that we had set up for another project and the round trip on every SQL query made the API unusable.