Facebook's use of MySQL

[YosemiteSam]

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

 

[Meat-O-Rama]

Very interesting indeed. I would not have thought it possible to handle Facebook levels of traffic with MySQL.

 

[Kangaroo]

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

 

[Phoenix]

Very interesting indeed. I would not have thought it possible to handle Facebook levels of traffic with MySQL.

Indeed. Thought something more like Teradata would be used.

 

[Reality]

Very interesting indeed. I would not have thought it possible to handle Facebook levels of traffic with MySQL.

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.

#reality

 

[Cowboy Brian]

If FB can operate on MySql how do Oracle or MsSql get any business

 

[YosemiteSam]

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.

https://lh6.***BROKEN***/-HbHLp5l87gc/TuEKCk8aDmI/AAAAAAAAA9E/aih5z587Ow0/s640/queries.jpg

 

[YosemiteSam]

If FB can operate on MySql how do Oracle or MsSql get any business

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)

 

[YosemiteSam]

https://lh6.***BROKEN***/-HbHLp5l87gc/TuEKCk8aDmI/AAAAAAAAA9E/aih5z587Ow0/s640/queries.jpg

Ha! I didn't even notice that. Look at that uptime! :laugh2:

453 days and 22 hours! :

Lets see Windows do that! :laugh2: