Mysql – Why are database queries running so much slower on AWS RDS

amazon-rdsamazon-web-servicesMySQLperformance

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!

Best Answer

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.

Related Topic