Web app with remote mysql
When setting up a web app that accesses mysql on another box, it's desireable to have the web & sql servers close together (network-wise). Preferable say in the same building / same network.
But what about when they are in separate locations? How far is probably 'too far' for decent response & performance? For the case I'm looking at here's ping between the two boxes:
10 packets transmitted, 10 received, 0% packet loss, time 9023ms
rtt min/avg/max/mdev = 14.505/14.607/14.978/0.158 ms
The app will see maybe 20-30 users a day (rarely more than 2-3 simulaneous users), each browsing pages for maybe 5-10 minutes. And a page might make 10 queries. So relatively low traffic.
Do you think 15ms between boxes will make a noticeable impact on performance?
You will not know until you try it
Of course, and I will. But sometimes it helps to discuss things first
MySQl and your app will work fine, it will just be slower.
For example, you'll be adding at least 140ms (14ms average latency x 10 queries per page) of latency to your application every time someone loads. That's not counting however many milliseconds it takes for your remote sql server to react, etc.
It would be more like 30 ms per query added, but you can probably work to optimize some of your queries.
Also you could setup a master-slave mysql replication with a local caching slave - send the update queries to the remote master, send the select (read-only) queries to the local slave.
Since your site does not have many visitors, you may also consider running mysql on the same box, just with reduced cache settings to use less memory. It'll be slower than a mysql install with more cache, but I don't know if it'll be faster or slower than latency induced by remote mysql.
I honestly don't see an issue if the servers are 15ms away. But, I would recommend you try and send the queries as early as possible, load the rest of the script, then when the sql is received process the request.
Great feedback, much appreciated. Now I need to think through the options
Probably not, I do remote mysql connections when moving shared hosting sites between LA and Charlotte so both copies of the site are using a single copy of the database, and most of the time these are very mysql heavy sites and for the day or three for DNS to change over you really do not see any performance hit.
This is not ideal in any way, but for a temp solution is fine. Or a very light use app, I don't see a huge problem.
If your worried about page speed you could cache some data in a text file just like the wordpress cache plug ins.