7

I've been working on performance-sensitive features. I've been developing locally, running a MySQL server on my Mac. One key query runs in about 1.2 seconds on my machine, which is in the acceptable range for me. Everything was running speedy enough, so I decided to move it onto an AWS RDS Aurora database so that I could start using the new system in production.

But once I started connecting to RDS instead of my local server, the queries started to take more than twice as long. And this is comparing the time it takes the actual query to run, unaffected by networking speed. This is how I'm measuring.

I've bumped up the instance that RDS is using to db.r3.4xlarge, which has 122 GB of RAM, an Intel Xeon E5-2670 v2 (Ivy Bridge), and 16 vCPUs. My local machine has 32 GB of ram, and a 4 GHz Intel Core i7.

I don't know much about this stuff, but it sure seems like the database in the cloud is running on more powerful hardware no matter what metric you're looking at.

Main question: What else can I look into to get the cloud database running as quickly as my local machine?

Things that seemed like plausible causes but don't appear to be:

  • Using explain at the start of my queries results in the exact same index plan on both DBs.
  • The DB running on AWS has fewer rows than the local one, as I've loaded less data into it.
  • The hardware in the cloud is more powerful than my local machine, unless I'm overlooking something.
  • Network performance is not part of what I'm measuring.
  • It isn't isolated to a certain query -- almost every query is running 2 or 3 times slower.
  • I'm comparing just running the queries plain, so my application code doesn't come into it.

Things that could be factors, but I really don't know:

  • The AWS database is running Aurora mimicking MySQL 5.6.10, whereas locally I'm running MySQL 5.6.43.
  • Maybe another chip or component of my computer is effecting this, other than my RAM or CPU.
  • Could the cloud one still be building indexes or something? I think MySQL indexes are built as data is inserted, though.

I'm really at a bit of a loss here. If anyone has any ideas or advice, it would be very much appreciated!

Improve this question
4
  • 2
    EC2 / RDS use EBS network storage, which must increase latency over computers with local storage. Few ideas 1) Ask AWS support, who are excellent if you pay for support 2) Try running MySQL rather than Aurora just to see the difference 3) You can try MySQL on EC2.
    – Tim
    Jul 9, 2019 at 18:56
  • Chatted with AWS, they pointed me to the solution. Thanks for the advice, I never knew their chat support was so good!
    – fnsjdnfksjdb
    Jul 9, 2019 at 20:02
  • Their support is pretty great. If I can't work something AWS related out in 30-60 minutes I just jump on chat and ask them. It does cost 10% of the AWS bill, but given the costs of people's time that is easily earned back given our current deployment in the account we have support in.
    – Tim
    Jul 9, 2019 at 20:38
  • How far away is the Cloud server? Remember that network latency may be a factor.
    – Rick James
    Dec 20, 2019 at 4:20

2 Answers 2

Reset to default
5

I took the advice from Tim in the comments and signed up for AWS Support so I could chat with someone about this. The explanation was:

Aurora is built and tailored for Highly concurrent workloads, Single-threaded are unlikely to result in the impressive results as also the concurrency levels in your benchmarks need to be verified.

So if we are in the works of 1 query, its highly unlikely that Aurora would perform as fast as your local machine

A bit more chatting back and forth, then I retried with RDS MySQL instead of Aurora, and it works! Almost exactly as fast as my local machine, with much cheaper hardware.

Improve this answer
1
  • Good that you got that worked out. The "much cheaper hardware" bit though... the db.r3.4xlarge is $9000 per year when paid in advance, or $1500 per month. The smaller and newer db.r5.large (2 cores / 16GB RAM) is $175/month or $1100 per year. Both require AWS support at $100 per month / $1200 per year. So local resources can be a lot cheaper, but you have to buy, administer, and maintain them. Use the newest (m5/r5 series are general purpose / ram optimsed) and smallest (large) that you gives you adequate performance.
    – Tim
    Jul 9, 2019 at 20:45
1

In my case I noticed that the database indexes are not migrated to RDS. I added indexes and it’s working fast. Also MySQL query cache is OFF by default in RDS. This won't help the performance of your initial query, but it may speed things up in general. You can set query_cache_type to 1 and define a value for query_cache_size. I also changed the thread_cache_size from 8 to 24 and innodb_io_capacity from 200 to 1900 don't know if it helps you. (After adding indexes I turned off the query cache) Also creating AWS DB Parameter Groups helped me a lot with configuring and tuning DB variables. Here you can read more: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .