177 Comments

Summary:

According to database pioneer Michael Stonebraker, Facebook is operating a huge, complex MySQL implementation equivalent to “a fate worse than death.” It’s actually a predicament all too common among web startups, for which the solution might be a class of databases referred to as NewSQL.

Mysql

According to database pioneer Michael Stonebraker, Facebook is operating a huge, complex MySQL implementation equivalent to “a fate worse than death,” and the only way out is “bite the bullet and rewrite everything.”

Not that it’s necessarily Facebook’s fault, though. Stonebraker says the social network’s predicament is all too common among web startups that start small and grow to epic proportions.

During an interview this week, Stonebraker explained to me that Facebook has split its MySQL database into 4,000 shards in order to handle the site’s massive data volume, and is running 9,000 instances of memcached in order to keep up with the number of transactions the database must serve. I’m checking with Facebook to verify the accuracy of those numbers, but Facebook’s history with MySQL is no mystery.

The oft-quoted statistic from 2008 is that the site had 1,800 servers dedicated to MySQL and 805 servers dedicated to memcached, although multiple MySQL shards and memcached instances can run on a single server. Facebook even maintains a MySQL at Facebook page dedicated to updating readers on the progress of its extensive work to make the database scale along with the site.

The widely accepted problem with MySQL is that it wasn’t built for webscale applications or those that must handle excessive transaction volumes. Stonebraker said the problem with MySQL and other SQL databases is that they consume too many resources for overhead tasks (e.g., maintaining ACID compliance and handling multithreading) and relatively few on actually finding and serving data. This might be fine for a small application with a small data set, but it quickly becomes too much to handle as data and transaction volumes grow.

This is a problem for a company like Facebook because it has so much user data, and because every user clicking “Like,” updating his status, joining a new group or otherwise interacting with the site constitutes a transaction its MySQL database has to process. Every second a user has to wait while a Facebook service calls the database is time that user might spend wondering if it’s worth the wait.

Not just a Facebook problem

In Stonebraker’s opinion, “old SQL (as he calls it) is good for nothing” and needs to be “sent to the home for retired software.” After all, he explained, SQL was created decades ago before the web, mobile devices and sensors forever changed how and how often databases are accessed.

But products such as MySQL are also open-source and free, and SQL skills aren’t hard to come by. This means, Stonebraker says, that when web startups decide they need to build a product in a hurry, MySQL is natural choice. But then they hit that hockey-stick-like growth rate like Facebook did, and they don’t really have the time to re-engineer the service from the database up. Instead, he said, they end up applying Band-Aid fixes that solve problems as they occur, but that never really fix the underlying problem of an inadequate data-management strategy.

There have been various attempts to overcome SQL’s performance and scalability problems, including the buzzworthy NoSQL movement that burst onto the scene a couple of years ago. However, it was quickly discovered that while NoSQL might be faster and scale better, it did so at the expense of ACID consistency. As I explained in a post earlier this year about Citrusleaf, a NoSQL provider claiming to maintain ACID properties:

ACID is an acronym for “Atomicity, Consistency, Isolation, Durability” — a relatively complicated way of saying transactions are performed reliably and accurately, which can be very important in situations like e-commerce, where every transaction relies on the accuracy of the data set.

Stonebraker thinks sacrificing ACID is a “terrible idea,” and, he noted, NoSQL databases end up only being marginally faster because they require writing certain consistency and other functions into the application’s business logic.

Stonebraker added, though, that NoSQL is a fine option for storing and serving unstructured or semi-structured data such as documents, which aren’t really suitable for relational databases. Facebook, for example, created Cassandra for certain tasks and also uses the Hadoop-based HBase heavily, but it’s still a MySQL shop for much of its core needs.

Is ‘NewSQL’ the cure?

But Stonebraker — an entrepreneur as much as a computer scientist — has an answer for the shortcoming of both “old SQL” and NoSQL. It’s called NewSQL (a term coined by 451 Group analyst Matthew Aslett) or scalable SQL, as I’ve referred to it in the past. Pushed by companies such as Xeround, Clustrix, NimbusDB, GenieDB and Stonebraker’s own VoltDB, NewSQL products maintain ACID properties while eliminating most of the other functions that slow legacy SQL performance. VoltDB, an online-transaction processing (OLTP) database, utilizes a number of methods to improve speed, including by running entirely in-memory instead of on disk.

It would be easy to accuse Stonebraker of tooting his own horn, but NewSQL vendors have been garnering lots of attention, investment and customers over the past year. There’s no guarantee they’re the solution for Facebook’s MySQL woes — the complexity of Facebook’s architecture and the company’s penchant for open source being among the reasons — but perhaps NewSQL will help the next generation of web startups avoid falling into the pitfalls of their predecessors. Until, that is, it, too, becomes a relic of the Web 3.0 era.

Feature image courtesy of Flickr user jimw; error image courtesy of Flickr user rubenerd.

You’re subscribed! If you like, you can update your settings

  1. Dennis Forbes Thursday, July 7, 2011

    It is tough to accept Stonebraker as much more than a successful troll at this point. He makes outlandishly unsubstantiated claims about systems that he has no stated inside knowledge on, all to garner attention for his own products.

    If he actually could point to some failure on the part of Facebook (performance, keeping up with growth, etc), perhaps there would be some footing for his observations, but as is they seem baseless and a bit ridiculous.

    1. Facebook’s implementation of MySql is “a fate worse than death”? OldSQL is “good for nothing”? How does this clown have any credibility at all with ridiculous statements like that? Is it just me, or does Facebook seem to be doing OK? Sure, they may have to rework some aspects of their current implementation, but in the mean time, they’re freaking taking over the world. Somewhat less than a fate worse than death, I’d say.

      1. http://en.wikipedia.org/wiki/Michael_Stonebraker

        Yea – he really sounds like the kind of type that would need to do that. Its just a product. Its no becoming to freak out when your pet product gets ripped. Even MySQL’s founder opening acknowledges its shortcomings.

      2. @Scott –

        Stonebraker is a well respected legend, and has made incredible contributions to the database field. However over the past couple of years his attention-seeking rhetoric has simply gotten out of hand. And it only seems to be getting worse.

        He wants some attention for his various contenders-to-the-throne, and it seems like only absurd hysterics will get it now. No thanks.

    2. It’s “glory days” syndrome (cue Springsteen song) and it seems particularly acute among database pioneers like Stonebraker and Starkey. These are people who really did make great contributions Back In The Day, they remember what it felt like, and when they see those contributions becoming less relevant they become absolutely *desperate* to recapture that feeling. Some of them still have the chops to do it with technology, or at least hire others to do it for them. Failing that, they resort to shameless pimping of their latest brain-farts along with bashing everything that might keep it irrelevant. Sad, really.

      1. :-(

        Really sad.

  2. Nothing more than an advertisement disguised as an article.

    1. I agree its just an ad in disguise. And the more we comment the better the advert runs, quite ingenius! Well done.

  3. So Facebook should just throw everything in the bit and start again. Just like the Bolsheviks in 1917.

    Maybe not… I hear Fusion-io aren’t doing that bad as a result.

    ;)

  4. What Dennis said. Why is it that when a fringe case comes up, and sites like Facebook are fringe cases in terms of software development, it negates the use of things people have been using for decades? I’ve yet to work on a project that was too big for SQL, and I probably never will. Most of us never will. And I’ve worked on some pretty big stuff (like parts of MSDN on a “little” site like microsoft.com).

    One of the problems with self-labeled computer science people is that they tend to want to solve all kinds of problems before they’re problems. That’s a waste of time, especially for the business signing your checks. That the “noSQL” movement is often referred to as a movement should tell you something. The objective of business is not usually to start a religion or social upheaval, it’s to make money by solving problems.

    1. While I understand what you are saying, in the datawarehousing world, it is not uncommon to come across data sets that are too large for relational databases like oracle and sql server to handle. I know of several current examples where sql server and even oracle were unable to scale up to handle the load. That’s why companies like terradata exist (still relational, but different semantics). Of course, that also makes the case against the in memory part :).

    2. Jeff Putz, you say:

      “One of the problems with self-labeled computer science people…”

      I have to point out that Stonebraker isn’t “a self-styled computer scientist, he actually *is* a computer scientist. According to wikipedia, and some other sources, Stonebraker:

      …has a PhD in Computer Information and Control Engineering from the University of Michigan.

      …received the IEEE John von Neumann Medal and the first SIGMOD Edgar F. Codd Innovations Award; was inducted as a Fellow of the Association for Computing Machinery; and was elected a member of the National Academy of Engineering.

      …was a Professor of Computer Science at University of California, Berkeley, for twenty-nine years. He is currently an adjunct professor at MIT.

      Of course, none of that means that he’s right in this case, or means that the whole thing isn’t self-serving. All his accomplishments make it all the more disappointing if he is just trying to get attention for his own products. He’s made real contributions to the field. With his background, why not either keep contributing, teach (which is also a real contribution), or just rest on your laurels?

      1. Perhaps, contribution is not fiscally rewarding and many academics come to realize this eventually. Why should all the morons get the rewards? Are you saying that someone with a PhD and an academic should not also be able to get financially rewarded? I don’t see why an academic doesn’t deserve a share of the fiscal pie while CEO’s steal all the money based off other people’s creations. I say screw the CEO’s and go academics.

        Welcome to the world created by greedy business that likes to steal everything and pretend to have created it. Stop blaming academia and start blaming gold-digging moron CEOs.

  5. Jonathan Allen Thursday, July 7, 2011

    This guy clearly has no idea how Facebook uses MySQL. Watch any of the presentations that Facebook has given at QCon and you’ll quickly see they are far more sophisticated than Stonebraker gives them credit for.

    More information: http://www.infoq.com/facebook/

    1. who cares how exactly Facebook is using MySQL. His point is valid one. We’ve been using stonge age technology to solve problems that didn’t exist 30 years ago. A new approach is long overdue, and NoSQL or Hadoop ain’t it either.

      1. After NoSQL, there’s Opa:
        http://opalang.org

      2. I hear Facebook is still using transistors! Those idiots should upgrade from that ancient tech to light-based computing.

  6. Oleg Kozlovsky Thursday, July 7, 2011

    Shards:
    What if the combined size of MySQL databases in Facebook was 100TB?
    Would you run 1 Oracle database on that data?

    Number of systems:
    Google utilizes 4000 servers for Google map. So what? They are not running a small mom ‘n pop shop.

    I believe FB has 4 DBAs, I know 1000 shops that have more Oracle DBAs. What if FB DBAs hate managing/administering appliances (Clustrix) and Java (VoltDB)? Xeround is a cloud database, why does it even get mentioned in this context is fascinating.

    At FB’s scale, extreme familiarity with software systems that are critical to business is required. If there is a bug or a workaround, their personnel need to find and fix it ASAP. Or is VoltDB without bugs?

    VoltDB has nothing unique, there was a failed start-up “ANTs software” that got digested into 4Js and Sybase, which boasted of a non-blocking database engine 10 years ago.

    So what is new: Many people claim that Ingres was better than Oracle in the 80s and early 90s, but who is the market leader today?

    All in all it sounds like someone (MySQL hater) is looking for attention.

  7. I recommend that you use GeneXus for the migration.

  8. I agree with all three previous comments. In addition, I found the following line in the article ridiculous:

    “The widely accepted problem with MySQL is that it wasn’t built for webscale applications or those that must handle excessive transaction volumes.”

    Huh? If it was widely accepted that MySQL wasn’t built for web applications, then why do so many startups keep using it? They couldn’t all be stupid, right? In addition to Facebook, Craigslist, Wikipedia, and TicketMaster use MySQL. And those are not low traffic, nor low transaction sites. YouTube (pre-Google) also used MySQL (and maybe they still do).

    By the way, I noticed GigaOm.com uses WordPress. Guess what database WordPress uses? MySQL. So either (1) GigaOm is a using a crap database or (2) MySQL is probably a pretty good solution for the web. It’s the latter.

    1. It would be difficult for me to imagine a database so huge that it’s truly beyond SQL-type databases. Walmart and the federal government have enormous databases and often use SQL based relational databases; I’ve thrown huge data pulls at Oracle, MySql, Sybase, etc, and if the queries are well programmed and the dba’s have it set up well there’s usually no long waits, bottlenecks, or crashes.

  9. >> The widely accepted problem with MySQL is that it wasn’t built for
    >> webscale applications or those that must handle excessive transaction volumes.

    Really? I guess Facebook, Craiglist, and Wikipedia aren’t webscale applications and must have low traffic. YouTube (pre-Google) also used MySQL (they still might). Lastly, I noticed GigaOm.com uses WordPress. And guess what database WordPress uses? MySQL.

    1. Facebook has a massive layer of Memcached servers that are used as an in-memory database, since the MySQL servers can’t take the read load of the live site. Facebook basically lives in memory chips. Also, for the inbox, the part that takes the bigger load, it uses HBase/Hadoop. The database is just a “safety vault”.

      Craiglist migrated to MongoDB, a NoSQL solution, a few months ago. (The same thing happened with Foursquare btw)

      Youtube (and the whole Google websites, with exception) work on top of Google Bigtable, something similar to a NoSQL database that’s built on top of GFS.

      Wikipedia is another heavy user of Memcached, since too many concurrent reads and writes would simply block the database. The database is just a “safety vault”.

      Twitter, another heavy MySQL user, uses a homegrown sharding server, but most reads happen on memcached. HBase and Hadoop are used for the search, just like Facebook. Again, the database is just a “safety vault”.

      From your examples, one could say that it’s next to impossible to run a site having only MySQL as a backend.

      1. I don’t think people using a cache negates the argument that mysql scales. Obviously a cache will be used in addition to the db.

    2. I think many of you are right about him trying to sell something, but the same people who are right are so, so wrong in other ways. He’s dropping the Facebook bomb because it’s something everyone can relate to. The whole point of what he’s trying to say is that IT IS TOO LATE FOR FACEBOOK TO SWITCH. MySQL works for them because they have plenty of resources to make it so.

      The exact thing he’s describing happened to my company. A lot of enterprise software nowadays isn’t provided as a web service by the software vendor–you buy the product and install it in-house on your own server’s instance of the DBMS it was written for. What, you’re going to give a start-up all of your data to handle on their servers? Yeah, OK. Anyway, we chose MySQL.

      As a start-up, you expect that your software might have a thousand end-users, maybe two tops, and MySQL is a perfect choice. You can’t just sell your product for crazy amounts out of the gate, so you want a DBMS that is easy to install and manage on the customer side, and more importantly, easy to code for on the dev side. MySQL is extremely well-documented; it’s not at all a black box. Being community-supported to a large extent is ideal for start-ups.

      When you distribute your software, and it actually catches-on, soon enough larger business want to use it. Now you have to support 20-100k end-users per installation, out of the box, in order to work your way into larger accounts and sales.

      If you started with MySQL, you’re kinda screwed until you rewrite a lot of code. DBMS isn’t easy to just switch when you have hundreds of customers each with their own installation. It’s practically impossible. We had to do our best with what we had and optimize the crap out of it, like Facebook is doing, but obviously to a much lesser extent. And once you rewrite, you have to keep supporting the legacy systems, or it pisses people off. It costs money to continue maintaining old code and support teams to keep existing installations limping along. If you don’t, then you instantly alienate the customers that created extra demand in the first place, as well as the ones who don’t necessarily need the extra scalability. It just costs a lot of money to start over with a new approach.

      The bottom line is that he’s completely right in at least one aspect (the most important one for this topic): if you aren’t scalable to start, you have to basically make a next-generation version of your product from scratch which actually uses a highly-scalable DBMS. The problem is that there isn’t as much demand for that, so there’s more risk to investing in it. This is a big reason why enterprises are stuck with slow/unstable software unless they hire a bunch of DBA’s to take their database systems seriously. You’d be surprised how many companies just have one or two MySQL or MSSQL instances to work with, and not more than one DBA designated to managing them. Otherwise, they’d have the IT wherewithal and budget to write basically your product in-house from the ground-up with their expected load in-mind from the start.

      There’s not a lot of middle-ground in IT today–either your business model is data-heavy and you already have the ability to write the software with the right DBMS you need/already have, or your electronic data and IT needs are growing and scalability needs to be built-in.

      I hope I made it a little bit clearer why starting scalable would be amazing for start-ups. It’s just not realistic at all right now because of cost–that’s where academia comes in. Lowering the entry barriers of running ridiculously-sized databases would eliminate so many problems in getting applications started that it would seriously make the world a much better place for software/IT innovation, not just for Facebook. They’re totally invested with MySQL, and hey, they can actually make it work for them. More power to em.

  10. I’ve worked as a DBA for some 25 years, starting out working on Unify, as data volumes grew we had to convert to Oracle. I spent 8 years at Sun Microsystem where I was exposed to MySQL. I’m currently a MySQL DBA for IBM and think MySQL is very scalable IF it is tuned properly. I think if you have performance issues, and exhausted all your options, then I’d look into the alternatives listed in the article. But remember, propriety code may not work good with Database Upgrades, and I have seen a lot of Applications break when an upgrade occurs. Oracle was on the ball when they bought all those middleware companies in the 90’s, oh and now they have MySQL.

Comments have been disabled for this post