What's the maximum acceptable latency if using a remote MySQL DB server? Aside from single server optimization, I am making evaluation in splitting up an existing web application to separate WEB and DB servers.

I understand network latency is an important factor when using a remote database server.

Ideally I would place a dedicated DB server in the same DC as the WEB server or even with a private network.

However, if I really need to put it on a different DC, what's the maximum acceptable network latency in order to get a responsive connectivity?

Update:

In the end, I want to ask, if I absolutely need to deploy remote database, what's the best practice and things to consider when it comes to choosing the datacenter and infrastructure?
There is no single correct answer to this question.  The MySQL client/server protocol has no limitations on toleration of round trip time, but responsiveness of your application can be dramatically affected.

Fundamentally, though, you can't look at any number and know intuitively that it will work, without intimate familiarity with your application.

I recently had to migrate a legacy application piecemeal from on-premise to cloud -- database first, then the app tiers.  The round-trip time between the two sites was 23ms.  "Cake," I thought... "23 ms is no problem."

Unfortunately, it was a problem, because the original developers had succumbed to one of the fallacies of distributed computing: "The latency is zero."

It was a 3-tiered app, and investigation of multi-second page loads revealed that every method call to the application tier on each page on the site triggered a minimum of 13 round trips to the database.  GetUserAttributes, GetAccountBalance, GetProductListings... you get the idea... so here's a simple page making 39 largely unnecessary database requests and requiring ~750ms just in transit time, instead of just a few.  (Why so many? They historically used two different ORMs and were still setting up both for each method, redundantly calling USE database and SET autocommit, etc.)  This is an extreme example, but on a LAN, this is the kind of thing you get away with, but that causes your performance to be impacted exponentially as RTT increases.

Where you can achieve hundreds of round trips per second on a LAN, that number drops to about 40 with the 23 ms added in.  That could easily be a factor of 10 or more, even though by human reckoning, 23 ms seems very quick.  Even single-digit round-trips can have a counter-intuitively significant impact on performance.

Fortunately, there's a fairly easy way to simulate latency to a MySQL server in an absolutely meaningful way -- by not simulating it, but actually injecting physical latency: configure a transparent TCP proxy in a remote data center.  For example, HAProxy.  Simple sample config:

listen mysql
  mode tcp
  bind *:3306
  server mysql-server 203.0.113.1:3306
  timeout tunnel 28800s


This overlooks features you'd want to use in a permanent deployment, like health-checks and ACLs, but if you set this up on a test machine in the remote data center, connect to it on port 3306, it would hairpin the connection back to the MySQL server at 203.0.113.1.  HAProxy is payload agnostic in TCP mode, and it's just forwarding packet payload (using splice where available, to avoid much time in userspace) so it will contribute no meaningful latency itself (and can handle a surprising amount of traffic on tiny machines).  If your app and db are local and this proxy is remote, this will perfectly model what your application behavior will be like with double the round trip time between the two data centers (because app to db, db to app traverses the entire path twice, not once)... the simulation is a worse than worst case scenario.  

If this performance proves acceptable, your proposed round trip time is no problem.  If it isn't, you found the problem before it was too late.