It’s hard to have scalable, reliable databases on the Web
21st February 2006
Putting databases on the Web is hard. It’s hard if you do it right, anyway.
Dare Obasanjo collects a set of posts about the challenges of using databases on the Web. These are written for people who have already tried to build systems for millions of users. Let me explain the problems for those of you who haven’t done so but, for some odd reason, are still interested in learning about it.
The people designing and supporting online systems hear this all the time from the people running the company: we want no down time — keep this thing available 24 hours a day, every day, now and evermore.*
There are basically two different ways of sharing the data in a database on the Web: the users of the Web site can read the data and query it, but they can’t change it (this is read-only); or they not only read the data, but they make changes to it as well (this is read-write).
Let’s start with a read-only system. Some examples? Imagine a “knowledge base” for a product, where customers look for answers to problems. They’re not changing the answers, they’re just finding them and reading them. Or even a search engine like Google. Sure, there are the “robots” that grab the data from all the pages on the Web and need to write it into the search engine database, but for all the people who use the search engine, it’s a read-only kind of deal.
The simplest setup (though not a very reliable one) is to have one database server for the Web site. It’s easy to set up, but there are all kinds of limitations. And I’m not even going to talk about scale here — a system needs to be able to scale when the number of users goes from a few hundred to several thousand, or from several thousand to several million. So what are some of the limitations of a single server? What happens when a hard drive fails? Or when the server needs to be taken offline to apply a security patch? What happens when the company needs to update its knowledge base — how does it apply the updates without breaking the content for its users while the updates are being loaded?**
There’s a pretty easy solution here: set up multiple databases that all have the same information, and put them behind a load balancer. This is hardware (or software in some cases) that distributes incoming requests to servers in a group. If one machine fails or needs to be taken offline, the load balancer just stops sending requests to it and the rest of the machines carry the load.
This design can provide a lot of resilience for read-only databases. In fact, with some fancy network setups, it’s possible for these servers to be distributed all around the world. If one datacenter goes offline thanks to a back hoe through a fiber optic cable, the requests are routed to other databases in another part of the world. The customers don’t notice.
Read-only databases are easy. What about read-write? What does a place like Amazon do? They don’t just let you browse their inventory, they let you buy it. They let you create an account, a profile, a wish list — they let you write to their database.
The design for a read-only database doesn’t work when users need to change the data. For a load-balanced pool of read-only databases, each server has the same content. It doesn’t matter which one serves the request: the result will be the same. But as soon as users can write to the database, throw synchronized data out the window. There are naive solutions to this problem, such as having the clients (the Web servers) duplicate their write requests across each database server. There’s no way to guarantee that the request could be completed across all the servers. Remember that this is the Web. There may be thousands of users who want to write to the database during the same second. Even if there are only a few — even if there is simply more than one user — there’s a risk that data would get out of sync or would even be in conflict when users make changes to the same data, but on different servers.
Solutions get a lot tougher here, but one technique separates the read-only parts of the system from those that need to be written to. Imagine an online magazine. Each article has an option for users to rate it — tell the author whether they loved it, hated it, or didn’t care. The articles could be served from a read-only database, while the rating section could be hooked up to a separate database system. This offers some additional flexibility and a chance to clarify the “always on, never down” requirement. Perhaps the articles must always be online, but it’s OK for the rating section of the page to sometimes be offline.
There are ways to make writable databases reliable and highly available (online 24 hours a day) but they’re complicated to deploy and even more complicated to explain, especially if no one out there is interested in hearing me do it. Have a look at this article about SQL Server Clusters (or lack of them) at Microsoft.com. I think Brad LeRoss is the author. For more about the things the Microsoft.com Ops team does, there’s a whole track at Windows Connections 2006 if you’re going.
After reading this, and the other articles, if you still have even the least bit of curiosity about databases and the Web, leave a comment and tell me what you would like to know.
*Well, they hear that request until they tell the management how much it will cost.
**After all, broken content is just as bad to the users as a server that’s down. Too bad so many people who support Web sites don’t see it that way. If the server is up, they reason, things are good. It’s easy to set up a system that simply pings a server to see if its alive. It’s much harder to design and maintain a monitor that can tell that the content is valid.
March 2nd, 2006 at 9:42
Thanx for your article. Only thing is: When it really got interesting (talking about the multi-database-write-solution), you stopped. Could you provide me with links where I can find information like this?
You are right: It is not that I NEED it. It’s only interest. My company and my clients are too small to need things like this.
My knowledge: Well, I am going to be MCDBA, but things like not only pinging but validating content of a multiple database solution didn’t come to my mind… …for me very inspiring. Again: Thanx :-)
Sincerely
Jens-Peter
March 2nd, 2006 at 12:28
Thanks for you interest and your comment.
The article from MSCOM Ops that I refer to in the post above talks about one solution for writable databases.
If that article doesn’t answer it clearly enough, I’ll be glad to try to expand on it.