1

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!

  • 1
    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 '19 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 '19 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 '19 at 20:38
  • How far away is the Cloud server? Remember that network latency may be a factor. – Rick James Dec 20 '19 at 4:20
2

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.

  • 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 '19 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.