5

I want two same websites to share one database. One server is in Asia, hosting a website and the database. Another server is in the US, hosting the same web via remote database. However, the web in the US responds very slow but when moving the database to the local server(US server), the web responds fast. How to speed up the connection between the server in the US and the database in Asia?

I am using Centos7+Nginx+MySQL.

2
  • 6
    Why do you have application servers in both Asia and US instead of having both in Asia where the DB is? How reliant are the application on the DB?
    – Polygorial
    Jan 1, 2022 at 10:25
  • do ping from the server in US to the server in ASIA and you will see the RTT
    – c4f4t0r
    Jan 5, 2022 at 9:13

6 Answers 6

28

How to speed up the connection between the server in the US and the database in Asia?

You probably can't. US-Asia is a long distance, and there's latency involved. Simply put: the light has a finite speed.

You should probably look into alternatives to querying a remote database, such as caching assets in multiple locations, using a CDN, or using a database replication scheme so that you can have local copies of the data.

4
  • 7
    A replica would be the way to go here, but it depends on how your database is set up as to whether it can be read-write or read-only, I think.
    – shearn89
    Dec 31, 2021 at 14:39
  • 4
    Absolutely. And if it's largely read only, a simple CDN may be the easier path.
    – vidarlo
    Dec 31, 2021 at 15:55
  • maybe a master master replicate can help but the question is what happens if the same values are changed at the same time? ;)
    – djdomi
    Dec 31, 2021 at 17:16
  • 1
    Absolutely. A read only slave may better if the majority of database accesses are read only. Scaling is not without problems.
    – vidarlo
    Dec 31, 2021 at 17:19
14

Does your web application make multiple separate database queries? Does one query depend on the previous query, or are there independent queries that could both be in flight in parallel to overlap their latency?

If the latter, make sure that's actually happening in whatever programming language you used, not unnecessarily serializing your queries.

If that's not sufficient or impossible, you'll need to reduce the round-trip latency between web server and database somehow, either by replicating the DB so you can have a local copy, caching some "hot" parts of it that don't change often, or various other things like caching the final output of the web server or other things a CDN can do (see vidarlo's answer).

1
  • 2
    Upvoted for mentioning looking at the code itself and its DB query patterns. I can easily imagine a series of queries that perform well enough on a local DB, run slow on a high-latency DB, but could be unified/parallelized.
    – Jonathan
    Jan 2, 2022 at 8:46
6

You can't improve on the speed of light. {citation needed}

Instead of querying intercontinentally, you might need to look into whatever replication MySQL offers. You'd need a webserver and a database server in Asia and duplicated in each location (US, EU, Africa, ME, etc). When a webserver writes, the local DB is updated immediately, and that update is then pushed to the remote DB servers by MySQL internally.

Positives:

  • User's sessions could flip from one site to another and their database records would be consistent.
  • You only need to back up one database server to get the lot.
  • Can add redundancy and fault tolerance by having a primary site, and if it goes down then fail over to a remote site.
  • Can scale out the webservers horizontally at each site to allow for downtime maintenance like updates/reboots
  • You can even have a backup DB server locally to each site, again allowing for redundancy without having to fail over

Negatives:

  • Added complexity
  • Increased cost of more resources

Ultimately this is an infrastructure design issue, and not really a webserver thing.

6
  • 3
    "... then pushed to the remote DB servers" No, MySQL databases work in a way that the replication client fetches. Jan 1, 2022 at 20:50
  • @rexkogitans okay you got me - I'm no DBA but I do work with a number of very talented ones. Any decent DB will have some replication system, whether its master/master or master/slave the webserver should be able to get "local" speed out of read requests at a minimum. Writes may be slower or ideally complete at "local" speed.
    – Criggie
    Jan 1, 2022 at 22:30
  • 2
    DB replication over long-distance links is just another can of nasty worms. If you go with synchronous replication then you'll get slow writes and complete downtime in case of network partitioning. If you go with async replication then you'll get data incosistency between replicas due to lag and you won't be able to flip sessions between sites so easily.
    – AlexD
    Jan 3, 2022 at 8:24
  • 2
    User's sessions could flip from one site to another and their database records would be consistent. Eh, no. Not directly after a write. With replication you will have to deal with a user writing to the master, and the application fetching from the slave before the write has been replicated.
    – Abigail
    Jan 3, 2022 at 15:47
  • 1
    Can add redundancy and fault tolerance by having a primary site, and if it goes down then fail over to a remote site. Tricky to get right when the master does down before all the slaves have fetched all the modified data. You don't get this out of the box for free.
    – Abigail
    Jan 3, 2022 at 15:50
3

The usual design of a database-backed website uses few (or much more, like 50 or 100) database queries in order to generate a single web page in response to a browser request.

Every one of these queries, one after another requires a round trip time between the web server and the database server. This can quickly add up.

In contrast, the connection between the web browser and the website is established after two times the round trip between the browser and the server and then the data is pretty much pipelined to the browser as fast as the connection allows.

See the difference? 2 user-server round trips vs multiple (likely tens) of server-server round trips. The connection between the servers may be faster, but not to a great extent.

This is why your setup is generally pointless.

What can be done, then?

  1. Use a single web server near the database. The times when having geographically distributed servers was important in order to improve the user experience are long gone for anything less than a global e-commerce or news hub.

The user experience today is dominated by the user's connection properties in the first place and barely dependent on the web server location.

Yes, there are times when a major global backbone connection is down and the connections between e.g. China an Europe become a real pain, but if such unfortunate event happens, the connection between your web server and your db server will be equally affected. And this is actually worse than slowing down the connection between your web server and your users, see above.

There are CDN tricks that may improve your response times even with a single server by looking like you have multiple servers at different locations. Major CDN providers like Cloudflare or Akamai have really powerful tools.

  1. Use database replication and maintain a database near both web servers. This may require deep rethinking of the application design, as well as much wider skill set and/or more expensive DB licenses.

  2. Check your database connection properties at both ends (db server and web server).

  • Extensive logging, complex authentication and reverse dns on the db server side can pretty much lag the results of each db query.
  • Using persistent db connections on the web server side can reduce the db query round trip 2-5 fold.
  1. Redesign your application to use fewer db queries per page. This, on the other hand, may result in the queries becoming more complex, slower and harder to maintain. Your mileage may vary.
2
  • 1
    I disagree with that the webserver location is not important; rather the opposite. Today most users have <50ms latency to local content, e.g. same and neighbouring countries, but 2-300ms to remote countries. CDN's and distribution is cheaper and easier than ever before, so more sites do it, and stuff feels faster. But overall, the experience using e.g. a chinese site from Europe still sucks, compared to a local site.
    – vidarlo
    Jan 1, 2022 at 15:53
  • 2
    @vidarlo in this regard, the world is generally divided between China and everyone else. But if you work in China you put a server there and a separate database there. The regulative framework forces you to keep China related stuff in China and everything else outside China.
    – fraxinus
    Jan 1, 2022 at 18:31
2

distance doesn't make the database any slower, it just increases latency.

mitigations for latency include

  • runing multiple queries in parallel
  • reducing the number of queries needed
  • caching results locally
  • keeping a read-only copy of the database locally

Some SQL wrapper layers like make a large number of extra queries to determine the database structure, you might have to redesign to use native SQL instead.

Where possible use joins instead of query loops. if needed it is possible to do multi-row updates and inserts, but I don't know the best syntax to use for that in mysql.

1

Others have already noted the options of replication or caching to give you a local copy of the data, reducing the number of queries, or to always use the web server near the database server. These are good options that you should consider but not the only options.

Another alternative is to proxy the query. With a proxy, you can set up a US web server to relay to an Asian web server local to the database. Apache can definitely do this, and I believe that Nginx can as well (but am much less familiar with Nginx configuration). This will fix the problems with repeated round trips between the web server and the database being slow at the cost of adding the proxy communication on top of it. But the proxy communication can be a single request/response pair, which will generally be faster.

You might do this if replication, caching, or reducing queries is too difficult for some reason. Proxy set up is comparatively simple and does not require you to change your application logic (as caching and query reduction do). I would also find it more likely to be effective than simply reducing queries. Because a proxy essentially reduces to a single remote query, whereas you are likely to still have multiple queries to the database.

Proxying also avoids having to replicate in both directions. If both the local (US) and remote (Asian) web servers perform write operations, it can be easier for all the write operations to be made directly to the authoritative database server. This avoids the problem of conflicting edits that each try to overwrite each other at a distance. But now we're potentially back to making multiple round trip remote database requests (albeit fewer). With the proxy, you are guaranteed only one request/response per page load.

It also provides some benefit relative to always using the remote (Asian) web server directly. If some of the content is dynamic and you only proxy that content, your local (US) server can still provide all the static content. Of course, you might be better off with a CDN for the static content and direct client access to the Asian web server.

2
  • This sounds like the best option for me. Thanks!
    – LeoZ
    Jan 2, 2022 at 13:59
  • If the goal of the server in the US is to be closer to end users to deliver pages faster, then proxying all requests without any form of caching will not bring much, if anything, compared to having all customers query the server in Asia directly. If the connections from proxy to the original server can be kept open (persistent connections), there can be a slight gain in the establishment of the connections from end-user to proxy (especially if using SSL/TLS), but on the other hand users will in most cases make a slight "detour" via the proxy.
    – jcaron
    Jan 3, 2022 at 18:15

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.