9

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?

6
  • 1.3 seconds from an EC2 instance, or 1.3 seconds from your laptop? I'd suspect latency between you and RDS, which is why you really should be using RDS to back EC2 instances.
    – ceejayoz
    Sep 27, 2011 at 12:42
  • 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
    Sep 28, 2011 at 6:17
  • 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.
    – Luc
    Sep 28, 2011 at 6:19
  • 1
    Was this ever resolved? Facing the same issue.
    – Dan P
    Jul 31, 2013 at 11:28
  • 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.
    – Luc
    Jul 31, 2013 at 21:50

4 Answers 4

2

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

0

The main reason should be latency between your computer and the Amazon RDS instance. Where are you based? If you're in the US, make sure you use RDS in either US-East or US-West. If you're in Europe or Japan or Asia, there are regions there as well.

2
  • 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?
    – Luc
    Sep 28, 2011 at 6:14
  • P.S. - Is this Simone from the amazon cloud tour at all?
    – Luc
    Sep 28, 2011 at 6:14
0

I had a similar issue on a Rackspace server instance. Queries took ages to execute, even with Innodb configured to the best of my efforts. I ended up moving to the dedicated database product offered by Rackspace. My guess is that disk intensive services such as databases does not thrive that well on virtualised servers where disks are shared between multiple instances. In general, it appears to be agreed upon on that mysql works best on bare metal or other infrastructure optimised for databases. I might be wrong, but this is the conclusion i came to.

0

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, but I don't know if it helps you.

Also creating AWS DB Parameter Groups helped me a lot with configuring and tuning DB variables.

You must log in to answer this question.

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