Blog Post

Facebook trapped in MySQL ‘fate worse than death’

Stay on Top of Enterprise Technology Trends

Get updates impacting your industry from our GigaOm Research Community
Join the Community!

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 (s orcl) 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.

177 Responses to “Facebook trapped in MySQL ‘fate worse than death’”

  1. From the article: “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.’”

    This is *so* disconnected from reality.

    I work for a $20 billion dollar company, which is growing 20% year over year, and run entirely on MySQL.

    Is this ideal? No. Do we have problems? Yes. But would consider rewrite everything? Never! *That* would be a fate worse than death.

    ” 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”

    The article talks about open source as if it was derogatory.

    I can tell you that my company wouldn’t be possible without several open source projects (OS, database, programming language, etc).

    Heck: great companies like Facebook, Twitter, and Google, wouldn’t exist without open source software.

    Now they are huge companies, and they could afford proprietary licenses; but they still choose free — not because of the cost, but because of the freedom.

  2. bhcompy

    Err, there are valid “NoSQL” solutions that have been around forever. PICK(marketed under names like Reality), for instance, is extremely fast and very reliable. It’s also very old and has very little overhead.

  3. I haven’t read a more astute observation of the situation China is currently facing with it’s inevitable financial meltdown. Stonebraker is actually a brilliant Sino economist and doesn’t even know it! The same ACID trip principals certainly apply.

  4. FockYou

    The best Dbase I’ve ever worked with is ms-access. It is super scalable and super fast. Also, I could run the entire fb site on a laptop and it would still out perform any of your systems or other dbases out there. No need for multiple Dbase servers or memcached servers.

    My system can also run wireless connections from here to the moon, and I’ve just signed a contract with NASA to automate 98.67849% of the international space station using my new integrated crystal microchip processors. I can store more data in one of my crystal chips than all of fb, combined!

    • Forrest Gump

      I used MySQL to organize wav files of my various farts. It worked well for me. I encourage Facebook to allow users to upload wav files of their farts too!

  5. mySQL is open source. OPEN. OPEN. OPEN!!! Wouldn’t it just be more straightforward to alter mySQL to meet its needs? Of course it would. what ever is wrong with their mySQL can be fixed by fiddling with the mySQL source. With that said, doing DB processing for 600M users on <13K machines should be considered efficient and relatively troublefree. That's 45K users per db server. While there is room for improvement, that's not too shabby.

  6. Chilly8

    The problem with MySQL is phpMyAdmin, the most commonly used backup program. It stops backing up data at around 12 or 13 megabytes, which is a real headache if you want to have a very large database.

    • WTF? what a weird comment!
      phpMyAdmin is not a backup program, although it can. It’s useful for sites that don’t have an ssh shell or (tunneled) port access.
      I suspect the script timeout in php.ini is set way too low.

      If you have a website of any importance, then you must also have ssh access for all the servers.

      mysqldump is THE backup program, takes a minute or two to backup 5Gb of social network data every day from a slave, then compress and compare with previous day’s backups and link yesterday’s backup to today’s if any tables have not changed.

  7. OmNomNom

    Typical academic. Yeah it’s not perfect. But Facebook operates in the real world and the availability, price, etc may be what they do want…

  8. Johan

    Yeah, obviously this guy has experience with way heavier and more advanced setups than that of Facebook. VoltDB has really been in the foreground of the whole development of database scalability for a long time. It was also available at the time Facebook made it’s choices. Furthermore it has really proven that it can handle the webs biggest applications under real load and real scenarios … Or maybe it’s just ordinary internet trolling …

  9. You are clueless and haven’t pinpointed a specific problem with MySQL at Facebook. Have you read how Facebook is actually using MySQL? Facebook has a massive Memcached cluster that caches over 25tb of data. The whole site is practically cached. MySQL is not a bottleneck and never has been. As for outdated technologies, what does VoltDB run on? Linux? Based on Unix? 30 years old? By your mentality, we have to rewrite Linux, TCP/IP, the database and everything else in between.

  10. Tarak

    I was wondering why facebook is slow while fetching data.
    I got the answer now, its MySQL. Its unimaginable how such a big and successful application is running on MySQL. Every body knows the limitations of MySQL. After all it’s free.
    I would also like to add that we should be careful in using facebook and always keep a backup of data on facebook account .

  11. sibidiba

    Comments here really explain why this is an unfortunate statement.

    http://news.slashdot.org/story/11/07/09/150211/Sony-Announces-End-For-MiniDisc-Walkman?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+Slashdot%2Fslashdot+%28Slashdot%29&utm_content=Google+Reader

    While relational DBs are really not the future,
    1) Facebook already has achieved scalability! The fact show that it works very very well. Their biggest concerns was actually PHP speed, not DB bottleneck.
    2) In any application with an architecture, the DB is abstracted away as a repository service, and you mostly talk to the cache.
    3) Architecture enables you to replace the implementation behind an interface easily, i.e. you can switch to any other repository/DB without “rewriting everything”.

  12. Note: this is from someone who a) loathes Facebook and b) loves PostgreSQL.

    Derrick, shame on you for writing this piece of trash. There are enough people in the world spewing unsubstantiated self-serving garbage without your help. What’s worse is to read your pathetic rejoinders in the comment thread.

    If there was a killfile for the web, I’d surely add you to it.

  13. graham hart

    When databases were first invented hardware was incredibly expensive so they were designed to be able to dynamically grow with the users needs to minimise the hardware cost.
    The bigger the database is the more difficult it is for the database to be able to grow.
    Hardware is now cheap and going to become virtually free.
    There is no longer any need for databases to be able to grow. Database structures can be fixed.

  14. Derrick Harris

    I just want to reiterate that Stonebraker wasn’t insulting Facebook. Rather, he was pointing out the incredible amount of work and skill required to make MySQL fit its purposes.

    Is VoltDB the answer? Maybe, maybe not. FB’s transactional DB might be too big to run completely in-memory. Also, FB has the engineering talent and is so far along that it might be easier maintaining the status quo rather than rearchitecting completely.

    But it’s not too crazy to suggest that life would be easier if it was built initially on something designed for its purposes.

    So, if there is a class of DBs available — VoltDB, Clustrix, ScaleBase, or whatever — that’s designed to handle the transaction and data volumes of today’s web, isn’t it prudent to at least give them a look? Especially if you’re building something from scratch?

    • Derrick,

      I don’t think assessment like ‘fate worse than death’ is a fair one for analytical article, if no insider knowledge is presented. There’re various presentations available online about state of FB database deployment – and what kind of problems are there and how they’re solved.

      It is difficult to define today’s web, but there’re not enough public numbers about big boys and their datasets nowadays, and to get your place in the web you have to do something smarter and more efficiently than others.

      You ‘class of DBs available’ without actually looking more at them. VoltDB is in-memory, so ‘data volumes of today’s web’ is already somewhat odd qualifier for it.

      ScaleBase is “transparent sharding” with regular MySQL servers in the back. I’d bet that implementing sharding in the application gives way more visibility of data access costs to developers (so that cross-shard workloads are well understood and optimized). Same applies to VoltDB, I guess ;-)

      Does NimbusDB product exist yet, I miss that part on their website.

      I’m yet to understand how GenieDB provides ‘wide geography multi master’ – for now it talks about in-memory consistency layer with only one coordinator for a range of data, it seems.

      Xeround is up to 50GB, not much of a web scale.

      etc

      I can do lots of hand waving and pick my random list of products that are future of the web, and make headlines, but that may not make responsible reporting or analysis.

      Anyway, whatever solutions people propose, all those solutions will still need to be partitioned at large enough scales. If not at a terabyte scale, then at a petabyte. If not at a petabyte, then at exabyte. If not at exabyte, then at zettabyte, … ;-)

      • BarryVMorris

        NimbusDB is in Beta release.

        – SQL, ACID
        – elastically scalable (add/delete nodes dynamically)
        – multi-tenant (DB’s can share machines arbitrarily)
        – no single point of failure
        – resilient to node failure
        – active/active geo-distributed
        – no sharding or partitioning
        – very fast on single transaction node, scaling linearly in tests to date
        -redundant storage nodes (as many live copies of the DB as you want)
        – very low DBA requirement

        We are always keen to talk to people that are facing SQL-in-the-cloud challenges.

        Barry Morris, NimbusDB Inc.
        – no masters, slaves or supervisors (100% peer to peer)
        – free for small systems

    • Jack Colby

      If your point was to suggest that it’s appropriate in some instances to use a specific tool for a specific job (“built initially on something designed for its purpose”), then it’s irresponsible to include quotes like, “old SQL (as he calls it) is good for nothing” (Stonebreaker). Nothing in your original article implied anything about it being a decision between SQL and NewSQL based on project requirements. It was all posed in the form of “New” SQL being a replacement for “Old” SQL.

    • jgarry

      FB doesn’t need transactions. It has a window of recent posts, anything other than that is “too old,” and the amount that is too old doesn’t matter, sometimes you see nothing at all. As a database guy, to me the data is everything. FB data is transient, it is not important enough to worry about. So it, in fact, doesn’t actually need ACID, it doesn’t actually need a database for anything besides user info. Any CS student could write a pointer list to unix style /etc/passwd to scale to a billion users, even dumbass java programmers. All the stuff users use is cached.

      What it needs is a better front end, but that’s another story.

  15. Mr Harris: When you attribute statements like “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.” include references to the hard proof. Pushing logic from the repository to the application does not make anything faster or slower. Cumulative work is just shuffled around. Moving logic from server to client can improve the perceived speed as you have effectively added massively to CPU and RAM capacity. So, if you have a relational data model the use SQL. If your data model is different then use a custom repository. (The different NoSQL repositories fill this custom need. Just as, for example, Focus fitted the custom need for hierarchical data in the 80s!) As to web scale issues, the solutions are all the same no matter how your repository models data.

  16. Nonsense. MySQL has an enterprise cluster product specifically built for massive throughput and redundancy. The problem is probably that they are using something very table type specific like innodb or myisam with fulltext search keys that the ndbcluster engine can’t handle. MySQL Cluster is built to handle telco level realtime transactions and does so for some really big sites… not easy to implement but extremely powerful. They could have 100 self replicating clusters which would be built with the sole purpose of expanding outward. Just cause you’re big doesn’t mean you are smart.

  17. Too many companies get sucked into the sales-guy promoting fear. A big smile with shiny glasses is a Technology evangelist and once you get sucked in, you’ll pay anything to feel safer.

    Facebook can use any technology out there and make it work, that is the difference – who cares what technology they use, it will work. It’s about walking the walk. Looks like Stonebraker needs to build something similar to a Facebook… then talk the talk.

  18. “Until, that is, it, too, becomes a relic of the Web 3.0 era.”

    The author’s use, or rather overuse, as it were, of the comma, is, I feel, commendable.

  19. IBM has build systems that has SQL built into the core OS that a perfectly suited for millions of transactions a second. Banks and large corporations uses these systems every day. They are midrange computers. Why do people always think inside the box. Talk to the only tech company that has been around for a hundred years.

  20. systems guy here. no, not a database expert, but I’ve built my own web presence. You can talk all you want about software issues, strengths and weaknesses. At the end of the day, you can not deny that facebook is trying to create its own “decentralized network” or its own “little internet”. This is all part of the evolution process. The internet was built to be decentralized, not centralized. Simply put, it will eventually implode under its own weight. But me thinks all the other social networking clients, like diaspora, or appleseed, friendika, etc will take facebook down to a sizable web service. again. natural evolution: decentralized….

  21. Liran

    To be clear – I work for another company that builds a newSQL solution. It’s name and product are irrelevant here – as I don’t do this kind of product pitches.
    This article is a joke, and it’s a shame that such a product pitch is given here. Only the last paragraph mentions the interests Dr. Stonebraker has, and you can’t present yourself as an academic figure when you just want to push your product. It’s misleading and unethical and it’s a shame gigaom gave Dr. Stonebraker this publicity.
    The article is filled with in inaccuracies, and the competition he presents is such that is most fitting for him.
    Shameful.

  22. Terry Greenlaw

    Stonebraker’s trolling for business has become quite irritating. While the rest of the world has come to the realization that transactional technology only works when it spans all aspects of your application, not just the database tier, he’s still clinging to the 1970s. Of course, he still thinks SQL makes sense, too, because the relational database is the center of the universe and deserves its own semi-functional, buffer-loving, designed for ad-hoc query language. Michael, step into the light, step into this century, and stop trying to pawn the snake oil !