Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Amazon RDS MySQL instance performs very slow

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?

like image 577
Manish Sapkal Avatar asked Apr 16 '13 11:04

Manish Sapkal


People also ask

Why is RDS so slow?

Your Amazon Relational Database Service (Amazon RDS) system resources are over utilized. This can happen because of high CPU, low memory, or a workload that exceeds what your DB instance type can handle. The database is locking and the resulting wait events are causing SELECT queries to perform poorly.

How do I improve my AWS RDS performance?

An Amazon RDS performance best practice is to allocate enough RAM so that your working set resides almost completely in memory. The working set is the data and indexes that are frequently in use on your instance. The more you use the DB instance, the more the working set will grow.

Why is my MySQL query running slow?

Queries can become slow for various reasons ranging from improper index usage to bugs in the storage engine itself. However, in most cases, queries become slow because developers or MySQL database administrators neglect to monitor them and keep an eye on their performance.

How do I find slow queries in MySQL RDS?

For more information, see Monitoring DB load with Performance Insights on Amazon RDS. You can also use the slow query log (enabled in your custom parameter group) to identify slow running queries. You can then use the Amazon CloudWatch metrics to check if the amount of work done on your instance has increased.


1 Answers

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)

like image 104
DOOManiac Avatar answered Oct 19 '22 13:10

DOOManiac