The app i'm working on has users from all over the world and due to the nature of the app, i need to minimize all delays. My application servers are located in different data centers across the world and load balancing is in place. The load of the DB isn't that high, so that's not an issue, the issue is simply that i need to minimize delays across long distances somehow.

Currently, there's a single DB server and it's located in Europe and the issue is when my app server in South-East Asia requests data from the DB in Europe, the complete duration for the query easily increases tenfold (from 50ms to 500ms).

My initial idea is to add another DB server in Asia, what kind of replication should i set in that case? Or is this even a good way to go?

Any guidelines and suggestions for this scenario are welcome.

  • 2
    How often do you write to the DB? – Philᵀᴹ May 11 '19 at 16:30
  • Pretty often, without going into to much details - people submit answers to some questions (write), then they get more questions (read), so pretty much there's equal amount of read and write operations. – IanDess May 12 '19 at 16:21

If you only want read access, then Master-Slave will work nicely. Anything written to the Master will incur a replication delay getting to Asia, but after that, clients in Asia can get the data with very little delay from the Asia slave.

For fast write access everywhere in the world, Master-Master might be viable. The long delay should not be a problem since the traffic is low. There are many potential problems with M-M, mostly having to do with primary/unique keys, but perhaps you can resolve that.

NDB Cluster is another possibility. However, many things are different with it, and the setup is more complex. It relies on "eventual consistency" wherein you provide rules for what to do when conflicting data is written to different Masters.

In your current setup...

If the operations you perform involve multiple queries (say, a few selects, then an update), there is a simple speed-up. Combine the queries into a Stored Procedure and perform one CALL. This cuts the trans-oceanic hops down from "multiple" to "one". I suspect the ping time from Asia to Europe is about 200ms? That is how much additional latency for each SQL statement.

| improve this answer | |
  • Great idea about combining multiple queries, thanks a lot! – IanDess May 12 '19 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.