This site is for tech Q&A. Please keep your posts focused on the subject at hand.

Ask one question at a time. Don't conflate multiple problems into a single question.

Make sure to include all relevant information in your posts. Try to avoid linking to external sites.

Links to documentation are fine, but in addition you should also quote the relevant parts in your posts.

0 votes

I have some thoughts in mind that remote databases cannot handle lots of queries as fast and efficient as local databases (i.e the applications and the databases are on the same server). Am I correct?

in Sysadmin
by (50)
1 7
edit history

Please log in or register to answer this question.

1 Answer

0 votes

Remote database access is not a bad thing per se, and it becomes a necessity when you need (or want) to spread application server load across several instances, since all of your application servers should normally have access to the same data, and multi-master replication gets really complex really fast with SQL servers (think about scenarios like split brain on the network).

The performance of remote database access depends on a lot of factors, though, for instance:

  • Network latency affacts the time packets need to travel accross the network. This generally affects the responsiveness of the application. Higher latency means slower responses.
  • Network bandwidth affects the amount of data that can be obtained per unit of time. The more data an application requests from the database, the slower it will perform.
  • Connection concurrency affects how many parallel connections the database will allow. Usually database administrators will put a hard cap on how many parallel connections the database server will accept, to avoid crashing the database server. In most scenarios it's less troublesome to have a (stateless) webserver crash than a (stateful) database server, because you can easily restart or even re-deploy stateless servers.
  • Network connection overhead is another thing that comes into play when establishing a connection to a remote database. Opening a network connection is expensive compared to localhost connections, and the more connections you open, the more it affects performance.

Latency and available bandwidth are normally something that you can't control as long as you're using a network operated by someone else. What you usually can control, however, is the number of database connection your application establishes, as well as the amount of data the application requests. The former is commonly addressed by a technique called "connection pooling," meaning that something (e.g. a proxy, or a component of your application) pre-establishes a bunch of connections to the database, and your application then requests and uses one or more already established connections from that pool. The latter is implementation-specific to your application, and it's mostly a trade-off: fewer requests means more data per request and vice versa.

To avoid frequently requesting the same data you could implement caching (either in-application or via an external service like memcache), but that'd also require cache management, i.e. when to expire and potentially re-request stale data.

Bottom line, there is no simple answer to your question. Any concrete answer would depend heavily on your application and usage patterns.

by (125)
3 20 38
edit history