Improving connection to amazon RDS from EC2

Question

Connecting to a local database on the same EC2 instance has a low latency, but to RDS things get really high. See results below.

time mysql -u root -proot -h localhost -e 'show databases'

+--------------------+
| Database           |
+--------------------+
| information_schema |
| biblical           |
| mysql              |
| performance_schema |
| phpmyadmin         |
| shop               |
+--------------------+

real    0m0.005s
user    0m0.003s
sys 0m0.000s

but connecting to my amazon RDS instance in the same availability zone give me this

+--------------------+
| Database           |
+--------------------+
| information_schema |
| biblical           |
| mysql              |
| performance_schema |
| phpmyadmin         |
| shop               |
+--------------------+

real    0m0.090s
user    0m0.000s
sys 0m0.000s

.005 to 0.090 has a large impact on my application's performance (Magento), and this was for a simple query. If performing a more CPU intensive query, this could get way high. Is there anyway to improve this ??

Answer 1

You are comparing a local database against a network connected node. Simply negotiating a TCP connection, the authentication process and data transfer can easily eat those 90ms in many scenarios. You may mitigate those results by implementing connection pools in the client side, but network latency for data transfer will still apply.

You should also tune your RDS instance for your workload, as RDS default settings are quite generic and do not fit for all use cases. Query caching, connection timeouts, transaction isolation level... all of those can brutally impact your queries every time your target performance is at the single digit ms level.

Remember that RDS is nothing more than standard SQL software running on top of EC2 instances. If you deploy an EC2 instance in your VPC an install MySQL to run your queries against it from your Magento instance, you should expect the same query performance any time you deploy an EC2+EBS setup with comparable resources to your RDS instance. From my experience, RDS and MySQL in EC2 provide the same performance, RDS "simply" automates the typical systems operation tasks.