MySQL query very slow on Amazon RDS but really fast on my laptop?

Question

I would love to know if anybody knows why this is happening. i've just migrated over to Amazon RDS for our website and our biggest query which takes .2 seconds to execute on my macbook takes 1.3 seconds to execute on the most expensive RDS instance.

Obviously i've disabled query cache (and tested this) on my local computer and both databases are exactly the same. InnoDB, both have the same indexes etc. It's costing us a fortune ($2000 per month) for the fastest RDS instance and i'm losing faith quickly. any ideas?

Solution

Try experimenting with the Provision IOPS that RDS offers. Regardless of instance size, the IO is limited, and you can use the Provision IOPS settings, at an additional cost, to improve IO performance.

You can provision up to 3TB storage and 30,000 IOPS per database instance. For a workload with 50% writes and 50% reads running on an m2.4xlarge instance, you can realize up to 25,000 IOPS for Oracle and 12,500 IOPS for MySQL. However, by provisioning up to 30,000 IOPS, you may be able to achieve lower latency and higher throughput. Your actual realized IOPS may vary from the amount you provisioned based on your database workload, instance type, and database engine choice. Refer to the Factors That Affect Realized IOPS section of the Amazon RDS User Guide to learn more.

You can convert from standard storage to Provisioned IOPS storage and get consistent throughput and low I/O latencies. You will encounter a short availability impact when doing so. You can independently scale IOPS (in increments of 1000) and storage on-the-fly with zero downtime. The ratio of IOPS provisioned to the storage requested (in GB) should be between 3 and 10. For example, for a database instance with 1000 GB of storage, you can provision from 3,000 to 10,000 IOPS. You can scale the IOPS up or down depending on factors such as seasonal variability of traffic to your applications.

AWS RDS Provision IOPS

Comments

[Luc]

Hi eric, it's 1.3 seconds from my EC2 instance using RDS and .2 seconds from my laptop using a local version of MySQL which suprises me!

[Luc]

we were hosting our website in Brisbane, Australia and she was running super quick on some old dell servers, after the move, launching 1 fast EC2 instance, configuring PHP etc the same and running off the RDS in the same region, she's been running very slow. The RDS seems to be the bottleneck, running at about 60% CPU on the most expensive RDS we can get.

[Dan P]

Was this ever resolved? Facing the same issue.

[Luc]

We ended up accepting that different hardware and a different environment contributed to the large difference. RDS is no doubt better for the faster smaller queries. Also, MySQL was optimizing differently on the production server, using the wrong indexes. So, we either forced some indexes in query or we removed some unnecessary indexes.

[Scott]

If you're paying $2000/mo for your db, why not also fork over at least $50/mo for email support. It seems that would be worth it and would likely help with your specifics.

[Luc]

Hi simone, thanks for getting back to me. I'm really worried about this because we've forked out a lot of money to move over to AWS and it's struggling! The RDS and EC2 is in the same region and zone so I don't think i should be getting any latency? Both are in ap-southeast-1b. Is there something I should be doing to check for latency between them?