Suppose I had a HUGE website (which I don't, but let's suppose), the common MySQL on a single server wouldn't work, because the MySQL server wouldn't be able to handle thousands of requests per second fast enough. How can you have several database servers that link to the same data but work independently of each other? What are the softwares available?
4 Answers
Wikipedia probably counts as having a "huge website", even if it's still tiny compared to, say, Google. You can find a description of their server architecture here.
Basically, their SQL backend consists of a bunch of clusters, each one consisting of a single MySQL master server whose data is replicated to a bunch of read-only slave servers. Also, images and revision text, which make up the bulk of the data and are essentially immutable once written, are stored separately.
Each of the various Wikimedia sites runs on a specific cluster. English Wikipedia, the largest of them by far, has a separate cluster all for itself, while most of the other sites store their data on shared clusters.
In addition to within-cluster replication, the databases are also replicated to servers running in remote data centers, although I believe that this is currently mainly used for live offsite backups and for the Wikimedia Toolserver (a separate set of servers for community-developed tools) which is hosted in Amsterdam, on the other side of the Atlantic from the main DB clusters in the U.S.
Here's a diagram of the Wikimedia server layout from 2010:
For more up-to-date information, see this presentation from last month.
A notable feature of the server layout is that the database servers run behind a veritable army of load-balanced web servers, which themselves are hidden behind a whole bunch of caching proxies. Thus, most requests from random visitors browsing Wikipedia never even hit the webservers, much less the database.
The use of "cloud computing" is growing for these tasks now. Large web applications -- such as Facebook, for example -- are distributed, meaning that it's not on any one single server at a time.
One approach to this has been messaging (CQRS) -- where, when the user initiates some action, a message is sent to a queue, as a type of command that the system responds to as soon as it is able. This way, if a certain facet of the system is down, the user can still see "Okay, your post has been created" (for example), even though it's still in the queue.
Designs of distributed systems vary (a Google search on this, or Wikipedia, would be most insightful here), but I know of some where a master copy of a database is kept, processing messages from the queue pretty much constantly, while read-only replications of it (or even just pieces of it) are being made across different systems so that the one single database isn't being hit thousands and thousands of times. In other words, one DB is being written to, but many are being read. When it's not mission-critical for it to be the exact, up-to-the-second data, then this is a good solution.
For more info:
Whichever package you use, I think the concept is the same. An example would be:
1 Update server - This is where the writes get sent.
1 General Read server - This is where the site gets its page info, etc.
1 Search server - User searches and the like.
1 reporting server - If you allow of heavy duty reporting in your app, you might want to think about this one.
A similar setup has been used at several places I know, servicing thousands of concurrent users, with several million line databases. Seems to work pretty good.
-
whoops! just realized it's kind of like what Matt said: 1 write server, and several read servers. You could even geographically disperse the read servers, say east-coast/west-coast?– RefreshJan 15, 2012 at 15:57
I use Windows Azure, create stateless website, you can increase instance anytime you want
Save data in Azure Table Storage