Website responds very slow using remote database

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.

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.

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).

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.

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.

2. 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.

3. 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.

4. 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.

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.

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.