Three MySpace executives met with SQL Server Magazine to provide a candid look at their decision to use SQL Server for MySpace’s enterprise database platform and how the implementation has unfolded over time. SQL Server Magazine Technical Director Michael Otey, and Editors Sheila Molnar and Jeff James interviewed the leaders who spearhead the MySpace data effort—Hala Al-Adwan, vice president for data, Christa Stelzmuller, chief data architect, and George Tevelde, director of database administration—about how they chose SQL Server for rapid development, what it’s like to run a massive SQL Server implementation, and what tips and techniques they can pass along to organizations of any size.
SQL Server Magazine: We’re interested in how MySpace made the decision to go with SQL Server over other enterprise database platforms.
Al-Adwan: I always get asked, "Why SQL Server?" MySpace chose to go with a Windows platform and SQL Server because it provides for a rapid development environment. That's something that doesn't get much attention, but compared to the open-source competitors, it's really easy to get up and running and developing rapidly on SQL Server. So that was why MySpace launched on a Microsoft platform, specifically on SQL Server 2000.
When we started in 2003, we had one instance of SQL Server running on one server. Everything was fine until we experienced more and more growth. Our first approach to scaling was a master/slave model, using transactional replication. We replicated our master read/write node to all of the slaves, which worked fine for us until we reached about two million users.
Around our two million user mark, we experienced latency issues with replication. That caused problems with the data being presented to users. We were still a two-tier architecture; we had web servers hitting the database directly. That was a key consideration when we talked about scaling.
We went with a vertical partitioning approach: We separated our features onto different servers; that worked until we hit the four million user mark. Then we had issues with users on multiple servers: How do you combine them when you're trying to build a single user experience? So we asked, "What’s the ideal way to combine everything we’ve learned using replication and vertical partitioning to come up with a more scalable model that allows us to keep a single user view on a single server?" We went with a feature-level partitioning model across all of our servers within MySpace, but within each one of those features, we try to be very thoughtful about the best way to scale horizontally.
The decision to scale horizontally happened early on, around the two million user range, when we decided to go to a model where we can add servers horizontally, rather than trying to scale out to a really large server. When you look at our user base, we have profiles and we have messaging and communication. We partition those functionally into different groups, and within each of those groups we partition again by user ranges. So we have databases for every million users, and we add more databases for every new set of million users. The application tier is aware of that and routes activity to the appropriate database depending on the user that is requesting it. So that, in a nutshell, is the evolution of SQL Server in the MySpace environment.
Stelzmuller: Today we either implement things as a federation (horizontally, partitioned across servers), or we implement them as load-balanced clusters, which are master/slave configurations, and we have standalone servers—many of our more critical features are running on high-availability clusters. So that's kind of how it maps out after all these years.
SQL Server Magazine: You started on SQL Server 2000—over the years, which versions of SQL Server have you moved to?
Al-Adwan: We're currently running on SQL Server 2005 and are planning a migration to SQL Server 2008 Enterprise Edition.
Stelzmuller: We're currently on Standard Edition for SQL Server 2005, but our high-availability clusters are on Enterprise Edition.
SQL Server Magazine: You mentioned that you have about a database per million users. So how many databases and servers are you using?
Al-Adwan: Our server footprint is about 450 servers. When you add up our user profile databases, with all the other databases that we have that support our various features sets, the load balance databases, and our messaging infrastructure databases, we're looking at over 1,200 databases. These are total generated accounts. Our monthly active user base is about 130 million. And we have about 250 or 270 million active user accounts.
SQL Server Magazine: Are you using virtualization?
Al-Adwan: Our databases are not virtualized. We use virtualization very heavily in our development and staging environments. Currently, we don't have virtualization in production.
I worked in the SQL Server world for many years prior to MySpace—I'm naturally a data head—and a lot of issues we faced in smaller implementations are similar to the challenges we face at MySpace. We talk about our scale and it's pretty huge, but in my previous job, managing messaging and transactional integrity across multiple servers was a challenge, and we had a small number of servers. We still have the same issue even with 450 servers, a single user profile, and trying to manage the integrity of the transaction across all those systems. That affected us pretty heavily when we went to the federated model, and we tried to work around it by creating some level of data duplication, but that didn't help very much. We tried to move to a place in the middle tier where we did some rudimentary transaction-level management, specifically on the user level because they touch so many databases. We have a much more elegant solution right now, utilizing Service Broker within the database.