Home / server / Questions / 315889
In Process
Luc
Asked: 2011-09-28 02:24:48 +0800 CST

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

  • 772

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?

mysql amazon-web-services rds amazon-rds

4 Answers

  1. 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

    • 2

  2. 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.

    • 0

  3. 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

  4. 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.

    • 0