3

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 ??

Phil
  • 143
  • 1
  • 8
  • 1
    `If performing a more CPU intensive query, this could get way high`: This assumption is not necessarily correct, as you don't know what is causing the higher latency. This can very well be a constant time factor that is completely independent of the complexity of the query, e.g. overhead in the authorization phase. Remember, your `time` command times everything, from name resolution to establishing the network connection, authorization, query time and result transmission time. – Sven Feb 10 '17 at 10:48
  • I ran the command over a certain period and the results varied. From 0.090 - 0.289. – Phil Feb 10 '17 at 10:50
  • 0.090 was the minimum i got, and that was only once – Phil Feb 10 '17 at 10:51
  • 1
    Benchmark actual query time. If you have a connection overhead, but fast queries, that can be overcome by persistent connections... – Sven Feb 10 '17 at 10:52
  • I'll do that and get back to you – Phil Feb 10 '17 at 10:54

1 Answers1

4

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.

ma.tome
  • 1,179
  • 8
  • 15
  • I did some improvement and configuration on rds and was able to get this down to what i was getting on a local server Also, moving it to an AZ closer to my location made it all the more better. Now TTFB is about 1.99 secs – Phil Mar 24 '17 at 16:55