Remote mysql server speed

Question

I develop websites, often on Wordpress using MAMP. Using a database running on localhost, the site runs really fast. Sometimes I need to use a shared database running on a dev server we host at DigitalOcean, so that multiple developers can work on the site. When switching to this remote database the site is much slower.

I can ping the server in 30ms, and we have a fast Internet connection.

Are there any tweaks we can do to make the remote setup work better? And how can I best benchmark the speed difference?

Answer 1

30ms is added to every SQL query performed. It may be that WP is issuing a lot of unnecessary queries. (Packets, network bandwidth, etc, are less relevant than the 30ms overhead.)

Turn on the "General log" for a short while; post it here; then we can discuss things.

It may be that moving your client application to the same Digital Ocean server, or to another server in that datacenter, would be the quickest solution. Then the 30ms would be at a different part of the data path, possibly less frequently used. For example, if 1 WP command leads to 5 SQL commands, then you are changing from 150ms down to 30ms of overhead.

Answer 2

When your database is local, there is no network latency, but while your database exist remotely, there is a network latency which can vary depending on the network activity happening on the server.

Also your ping uses very small packet of data, so it will be fast, but when you query your database, latency depends on how much data is returned by that particular query and load on the server at that moment.

Also I/O operation can also be a limited factor.

I would advise you to get the high speed network database server.

In AWS they have the options to choose various servers, which varies on their I/O and network capability. You can try some of these servers with your benchmark and choose which suits your need.One of the advantage of using the AWS servers is that you don't have to benchmark network and I/O of the server as its already published by them.

Answer 3

Having a database on remote server generally has the problem you mentioned, it could be network, it also could be low I/o speed on hosting provider(though digital ocean hosts on SSD). Try this: Connect to database remote host via ssh Install iotop (apt-get install iotop) Install bmon (apt-get install bmon) Try to use these programs to see your bottleneck. Iotop shows storage load and bmon shows network load.