35

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?

Shark Lasers
  • 441
  • 6
  • 15
Manish Sapkal
  • 5,591
  • 8
  • 45
  • 74
  • Just for understanding.. Which instance you are using? – Nikolay Fominyh Oct 30 '15 at 23:23
  • I have the same problem. I tried setting up RDS with a Digital Ocean -- page load (relied on DB) went from average of 1.8s to 5.9 seconds. So, recreated the whole setup in EC2 & RDS in the same region/cluster and same time zone. I setup caching as well. – Kerry Jones Nov 06 '16 at 08:00

8 Answers8

37

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)

DOOManiac
  • 6,066
  • 8
  • 44
  • 67
  • 5
    For InnoDB tables you should keep it OFF. Reference: https://serverfault.com/questions/604315/is-enabling-mysql-query-cache-size-innodb-lock-safe – Lavi Avigdor Aug 02 '17 at 13:55
  • setting `query_cache_type` to 1 solved my problem with the speed too. I also changed the `thread_cache_size` from 8 to 24 and `innodb_io_capacity` from 200 to 1900. – Fariman Kashani Aug 26 '21 at 14:13
  • 1
    query_cache_type is no longer available in mysql 8: https://dev.mysql.com/blog-archive/mysql-8-0-retiring-support-for-the-query-cache/ – apricity Jul 11 '22 at 19:13
12

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 than were needed.

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

enter image description here

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.

radtek
  • 34,210
  • 11
  • 144
  • 111
  • How did you get to this screen? – Seph Reed Apr 17 '19 at 15:46
  • Its under "Monitoring" – radtek Apr 17 '19 at 15:50
  • RDS > Databases > [YOUR_DB_NAME], bottom of the page choose "Monitoring," click any graph, then navigate the dropdown in the top left to CPU Credit. Make sure to zoom the period out as far back as when you last remember it working well. – Seph Reed Apr 17 '19 at 15:56
  • had to upgrade from db.t2.micro to db.t3.micro instance: ...While Amazon RDS T2 instances are restricted to baseline performance once the CPU Credit balance is drawn down to zero, Amazon RDS T3 instances can burst above the baseline even when its CPU Credit balance is zero. ... source: https://aws.amazon.com/rds/instance-types/ – xhafan Sep 07 '20 at 19:33
9

It is important to have your RDS and EC2 instances not just in the same region but also 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.

Chris Sattinger
  • 4,446
  • 3
  • 30
  • 29
Roovian
  • 99
  • 2
  • 5
  • 13
    Having a RDS and EC2 on same availability zone implies they are on the same region – dvdvck Aug 05 '15 at 19:29
  • 3
    you just made my month – Vladimir Kovalchuk May 26 '17 at 17:47
  • I don't understand how did this answer manage to gain upvotes with no remarks about using Firewall between API and DB, and they also must be connected through a VPN tunnel over the Tor network - for better security — optimize your stack hard! – maxkoryukov Sep 04 '21 at 20:49
3

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
Brent Matzelle
  • 4,073
  • 3
  • 28
  • 27
Harish Ganesan
  • 743
  • 5
  • 4
2

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

Hiyasat
  • 8,601
  • 7
  • 33
  • 59
  • the question is about the difference between execution queries locally and in AWS, not about _slow queries_ – maxkoryukov Sep 05 '21 at 05:59
  • I have a set of "migration" scripts with DDL for creating the DB structure. And a list of files with "seed" data. I run them locally and in the AWS, as a result, I have two _identical_ (tables, indexes, ... and table items) databases. I run "EXPLAIN" in both databases - whoaaa same explanations, but in AWS the request (REST + SQL) takes 900ms, and locally (in a very limited system with two Docker images - DB + nginx) it takes 70ms. – maxkoryukov Sep 05 '21 at 05:59
2

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.

0

Always should deploy source and rds in the same AWS availability zone for lower network latency and Should create a private endpoint link in VPC for RDS to connect RDS endpoint through the internal network instead of routing through the internet.

Reference: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/vpc-interface-endpoints.html

Piyush Sonigra
  • 1,423
  • 11
  • 10
0

I found that after migrating to RDS all my database Indexes are gone! They weren't migrated along with the schema and data. Make sure you're indexes are there.

Fariman Kashani
  • 856
  • 1
  • 16
  • 29