It always amazes me how different the rules of the game become when you are dealing with massively scalable data backed applications. According to ComScore, MySpace received 45.4 billion page views for the month of May 2008. That's over 17,000 page views per second. I can't imagine any database system handling that number of simultaneous queries.

Granted, MySpace's Microsoft SQL Server 2005 databases don't handle that many direct queries. MySpace explained how they have used SQL Server Service Broker along with a strong middle tier caching layer to spread the load over 200 separate MSSQL database servers and many more caching servers. While the technical configuration was amazing, what surprised me more were the normal assumptions that went out the window in building this data layer.

First, the one cardinal rule of any computer based system that deals with a user's data is that you can't lose/corrupt any of this data—every step should be taken to ensure that the data is in a consistent state even in the case of hardware failure. If, for example, you're a bank, this is really important. You don't want a transfer of money to die in the milliseconds between when money is subtracted from one account and added to another. However, one of the first things the MySpace dev said was, "We're not a bank. If some 15 year old or pedophile loses a friend it's really not a big deal. He's not gonnna jump in front of a bus and kill himself as a result." Actually though… considering some of the emo kids that use MySpace someone just might….

Anyway, because of this assumption, MySpace has turned off a lot of features such as check constraints on foreign keys simply because they cannot afford the compute cycles to enforce these constraints. If data is inconsistent, that's okay.

MySpace also buys the standard, not enterprise edition of SQL to save money. As a result, they don't have the clustering and availability features offered by enterprise, but for them, this is an acceptable trade off.

They don't have any sort of data mirroring strategy. The MySpace profiles are stored across many different databases, with each database holding about 1 million profiles. The dev explained that for performance and cost reasons, they can't afford to do data mirroring or clustering for the profile data. If a million profiles go offline for a couple of hours due to a server crash, that's acceptable. The dev said that on his third week into the job, he accidentally deleted all of the blog entries on the MySpace Japan site. They were able to restore some of the data from backups, but they had turned off transaction logs for cost/performance reasons. As a result, they had lost about 6 hours of blog entries which amounted to about 20,000 posts. His response: "Whoops. My bad." I don't think I'd be able to show my face anywhere near a T-SQL statement after an incident like that…

I'm fascinated by systems that have to scale to insane loads. I know that MySpace's highly dynamic nature doesn't lend itself well to being bound by stringent processes and laborious load testing. I understand that they have a need to continuously push out new features because they have to offer more compelling features than Facebook and a 3 week QA process is too long. But, I have to wonder if you could design a database that enforces constraints and provides complete transactional integrity that can service the load for a site like MySpace…