Facebook's use of MySQL

Discussion in 'Off-topic Zone' started by Sam I Am, Dec 8, 2011.

  1. Sam I Am

    Sam I Am Unfriendly and Aloof!

    37,577 Messages
    5,520 Likes Received
    For you techies out there.

    60 million queries and 4 million row updates per second! Good stuff! Our MySQL database only averages around say 3000 queries per second and probably maxing around 9000-10000 when things really get moving.


    When you’re storing every transaction for 800 million users and handling more than 60 million queries per second, your database environment had better be something special. Many readers might see these numbers and think NoSQL, but Facebook held a Tech Talk on Monday night explaining how it built a MySQL environment capable of handling everything the company needs in terms of scale, performance and availability.

    Over the summer, I reported on Michael Stonebraker’s stance that Facebook is trapped in a MySQL “fate worse than death” because of its reliance on an outdated database paired with a complex sharding and caching strategy (read the comments and this follow-up post for a bevy of opinions on the validity of Stonebraker’s stance on SQL). Facebook declined an official comment at the time, but last night’s night talk proved to me that Stonebraker (and I) might have been wrong.

    Complete Story
  2. Meat-O-Rama

    Meat-O-Rama Vegetarians are so stupid.

    2,478 Messages
    329 Likes Received
    Very interesting indeed. I would not have thought it possible to handle Facebook levels of traffic with MySQL.
  3. Kangaroo

    Kangaroo Active Member

    9,893 Messages
    0 Likes Received
    Hell of a lot cheaper than Oracle or Microsoft SQL

    They probably started on it because of cost and then grew it from there which is quite cool and is something start up companies like facebook (start up at one time) tend to accomplish. Then have refined it and moved it forward which is always cool to read about,

    The big guys are going to play it safe and do what has been tested and to make sure they have a job because if it doe snot work quite right you have industry standards you followed
  4. Phoenix

    Phoenix Well-Known Member

    6,686 Messages
    1,120 Likes Received

    Indeed. Thought something more like Teradata would be used.
  5. Reality

    Reality Administrator Staff Member

    14,433 Messages
    9,293 Likes Received
    I didn't read the article other than the quoted part but no matter what it says, it is highly unlikely that mysql is the main driving force behind their information retention and delivery system. Most of their information traffic is going to be reads (still a ton of writes, just mostly reads) which means that data is being cached. With traditional sites, even some mid-traffic traffic sites, you update the database and then cache the data (write-and-cache) for future queries while low traffic sites usually use the read-and-cache system.

    Since WRITE's to hard drives (and databases) carry a much high cost in terms of performance and also affect READ performance as well due to delays, micro-updating a database in a high traffic/query environment is not only not efficient but in some cases not even an option.

    Given the volume of traffic it receives, I expect that Facebook is using the cache everything approach and then periodically using dedicated servers batch updating database records as needed. In fact I wouldn't be surprised if they don't even update the database in many cases if the data is either temporary or low priority.

    Using a caching system such as memcached takes the load off the database servers and even better, you can configure server farms of memory only data storage which will only be limited primarily by memory speed and internal network bandwidth.

    Basically, the actual hard drive (mysql) database becomes the first level of backup.

    One thing they may do though I have never tried it is using mysql on virtual memory drives. I would love to see some detailed performance results for that.

  6. Cowboy Brian

    Cowboy Brian Augmented. Zone Supporter

    12,390 Messages
    522 Likes Received
    If FB can operate on MySql how do Oracle or MsSql get any business
  7. Sam I Am

    Sam I Am Unfriendly and Aloof!

    37,577 Messages
    5,520 Likes Received
    Actually, the article notes that 90% of queries hit cache and only about 10% actually hit the database.

    They use memcached and some homebrew caching utilities also. Of course, their MySQL servers are heavily hacked too as they have their own MySQL developers department.

    On our server, we don't cache anything except what is cached as Java objects in JBoss. Our server (yep, a single MySQL server) handles all the queries without issue. Of course 99.999% of the actively queried / written data exists in memory.

    MySQL is extremely fast if you spec it to what you're doing. As I noted. Our server on average accepts 3,000 to 5,000 queries a second. (though they are small queries, returning small data)

    Here is a snap I just took of mytop showing over 6,400 queries per second.

  8. Sam I Am

    Sam I Am Unfriendly and Aloof!

    37,577 Messages
    5,520 Likes Received
    There are reasons though many people who pay for Oracle don't actually need it. Oracle supports a lot more things than MySQL does. You can shard in MySQL, but you can't cluster like Oracle RAC. (well, there is MySQL Cluster, but MySQL and MySQL cluster are two different beasts)
  9. Sam I Am

    Sam I Am Unfriendly and Aloof!

    37,577 Messages
    5,520 Likes Received
    Ha! I didn't even notice that. Look at that uptime! :laugh2:

    453 days and 22 hours! :p:

    Lets see Windows do that! :laugh2:

Share This Page