Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

In at least the web development field, web servers are everywhere but are backed by very few database servers. As web servers get hit with requests they execute large queries on the database, putting the server under heavy load. While web servers are very easy to scale, db servers are much harder (at least from what I know), making them a precious resource.

One way I've heard from some answers here to take load off the database is to offload the work to the web server. Easy, until you realize that now you've got a ton of traffic internally as a web server tries to run SELECT TOP 3000 (presumably to crunch the results on its own), which still slows things down.

What are other ways to take load off the database?

share|improve this question
add comment

5 Answers

Here is a list of standard options.

  1. Optimize the access to the database to only do what you need, efficiently. A good DBA can help here a lot. This is a basic step that most companies do.

  2. Cache data away from the database using something like memcached. This is usually done at the application layer, and is highly effective. Virtually every competent website should do this.

  3. More ambitiously, maintain read-only copies of the database, and direct queries there when possible. On the database side the necessary technology is called "replication" and the read-only copies are often also backups for failover from the main database. If you're doing a million dynamic pages per hour, odds are that you are doing this, or have thought about it.

  4. Buy really, really expensive hardware for the database. I know that PayPal did this as of 4 years ago, and changing their architecture would have been difficult so they possibly still are.

  5. Shard the database into multiple pieces with ranges of data. This is a very intrusive change into application design. A well-known example of a company that does this is eBay.

  6. Try to use a database that scales onto multiple machines. Oracle RAC scales onto clusters, but doesn't let you distribute data widely. Other offerings exist that are supposed to be easier to distribute, including Microsoft's SQL Azure and FathomDB. I have not used those offerings and don't know how well they work. I suspect better than nothing, but I doubt they scale horizontally that well.

  7. Relational databases generally try to provide ACID guarantees. But the CAP theorem makes it very difficult to do that in a distributed system, particularly while letting you do things like join data. Therefore people have come up with many NoSQL alternatives that explicitly offer weaker guarantees and avoid problematic operations in return for fully distributed scalability. Well-known examples of companies that use scalable NoSQL data stores include Google, Facebook and Twitter.

share|improve this answer
add comment

90% of the problem is optimizing your database queries and configuring your server correctly. Make sure you have proper indexes, that the queries arent doing row scans, etc. Use whatever profiler tools your database offers. You'd be suprised how much this can affect a server. A lot of companies will rush out and buy a new database server when all they really need is a couple extra indexes to reduce load.

Once you get the queries themselves as optimized as you can and your db server is still overloaded, use database replication to distribute the workload. Instead of a single overworked database server, split it into 2 or more, with the others replicating the master. Then make the heavy read-only queries (reports, etc) work against the replicated databases, and only hit the master database for simple queries and inserts/updates.

share|improve this answer
2  
after database optimization and before database replication you should try aggressive caching. –  Javier Mar 21 '11 at 4:23
add comment

How do you reduce the database's workload?

  • Cache data on the web servers so that the business layer doesn't make redundant calls to the database. [ORM frameworks like Hibernate can help with this.]

Easy, until you realize that now you've got a ton of traffic internally as a web server tries to run SELECT TOP 3000 (presumably to crunch the results on its own), which still slows things down.

  • Schedule jobs which anticipate the need to do heavy calculation and aggregation and store the results in the application cache (accessible to all requests).
share|improve this answer
add comment

One way I've heard from some answers here to take load off the database is to offload the work to the web server. Easy, until you realize that now you've got a ton of traffic internally as a web server tries to run SELECT TOP 3000 (presumably to crunch the results on its own), which still slows things down.

I think you might be taking this a bit too literally. It's not about having the web server(s) perform the same calculation as the database. In most applications there are opportunities to cache not just the result set from the database, but actually to cache the end result of whatever the result set is being used for (typically to generate some content on a page).

As an example, let's say you have a "most popular blog posts" list on a blog that is relatively expensive to query from the database. Without caching, if your blog suddenly gets a surge of heavy traffic, your blog application will repeatedly query the database to generate the "most popular posts" list for every page load and quickly fail under load. Now let's say you just cache that list of popular posts in memory for 15 minutes. This way even if you get a million hits in an hour your database will execute that query only four times.

By doing this you are making a tradeoff for the most popular blog post list to be stale for up to 15 minutes but gaining significantly decreased load on the database (and increased scalability).

share|improve this answer
add comment

Disclaimer: I'm not an expert on web servers, DB queries etc (not my field) :)

That being said I really loved the concept demoed in this presentation on Node.js.

(Related article)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.