Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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 '11 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 '11 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 '11 at 6:19
    
Was this ever resolved? Facing the same issue. –  Dan P Jul 31 '13 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 '13 at 21:50
add comment

3 Answers 3

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

share|improve this answer
add comment

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.

share|improve this answer
    
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 '11 at 6:14
    
P.S. - Is this Simone from the amazon cloud tour at all? –  Luc Sep 28 '11 at 6:14
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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