Geo-distributed database/sharding?

Question

MySQL

Colleagues, I want a little strange.
there is some kind of authentication service. Now the server is exclusively in Europe, there is a desire to drag the second server to Asia.
the data is in Postgre, but this is not a dogma. Users who come to the European server will not immediately come to the Asian one, but potentially they can.
what can be done "on the forehead" - asynchronous replication. It seems like it's not difficult and even should work, but. In some perspective, there may be MANY users, but at the same time migrating between servers of a fraction of a percent. Sinking everyone is somehow not very effective.
What you want is some perverted sharding (?). Those. We write European users to the European server, Asian users to the Asian one, but you need to read everything (ours quickly, "strangers" - well, how it goes). Is it real at all?

Answer 1

Set a reverse proxy, and based on the IP address to throw to different servers?
Is it possible to do this with a database...

Answer 2

I would raise db links between servers

Answer 3

What you want is some perverted sharding (?). Those. We write European users to the European server, Asian users to the Asian one, but you need to read everything (ours quickly, "strangers" - well, how it goes). Is it real at all?

Users of "their region" let them quickly log in to the local database, and those few who are unlucky - to another. For the latter, it will be a little longer, but in general, probably, it's okay.
In case of problems with the channel to the "remote" base, you can probably periodically synchronize them with each other, in order to slip not the most recent details in the last resort. But passwords are usually changed quite rarely, in most cases it should fail.

Answer 4

The European server
has its own base
, the Asian one has its own
Accordingly, the requests from the European one
1. European - a quick response
2. Asian - a slow response and adding to the European database
3. Nothing - two requests, but it happened
The Asian is the opposite

Answer 5

between Europe and Asia RTT approximately 200ms (Germany-Singapore)
https legacy requires = 3-4 RTT (600-800ms) + DNS
if you have TLS v 1.3 configured + 0-RTT = 2-3 RTT (400-600 ) + DNS
details here https://blog.cloudflare.com/introducing-0-rtt/
The application and the database must be in the same data center (region) otherwise each http request will be processed as a number of sql requests * RTT 200 ms = long)
respectively, if your asian traffic lands only on a single server in europe, users from asia spend more on connection, 600-800 ms more.
if you buy a CDN service to speed up a dynamic site (or set up your own servers) and your https will be terminated in Asia, while the application itself and the database will remain in Europe, the remaining delay will be reduced to 1 RTT = 200 ms, you
also fasten a fast anycast to this DNS, for example Cloudflare (only DNS, without CDN) or AWS route 53 (it is also protected from ddos ​​level 3.4)
here are the statistics of DNS services by latency https://www.dnsperf.com/
if you watch CDN, choose here: https ://www.cdnperf.com/
but not everyone has the dynamics acceleration service
if you want your own - you can use AWS Global Accelerator (it gives anycast ip) this will give:
- TCP termination close to the user
- priority network with lower latency
- protection against Ddos level 3.4
- fast switching to a backup data center / region if the main one fell
+ a couple of cheap regional EC2 for Https termination
as an analogue, see Azure Front Door
How to reduce the remaining 200 ms:
1) If you use synchronous replication between continents, for example, like mysql galera / percona xtradb cluster - read speeds will be local, but the number of write transactions per second will be very slow, as it will run into RTT, and if it is 200 ms = 5 transactions / second
2) If you do sharding users by continent, then there will be problems if you have cross-shard operations or there may be resharding (the user moves to another continent).
There is CockroachDB which claims that they can solve all this "out of the box" - in practice, xs.
4) If eventual consistency suits you and split brain will not break your application for a short time - you can try databases that will use Consistency in the end using asynchronous replication
But is it critical to reduce these remaining 200 ms?
Is it worth it to use new unrolled databases in production with which there is no experience in solving problems for the sake of the remaining 200 ms?