MySQL at Facebook • Hacker News Board

Re: MySQL at Facebook

#11

Post by davidamcclain » Thu, Nov 04, 2010, 6:20 PM UTC

thevivekpandey wrote: post #6

"It is OK if a query is slow as long as it is always slow" I find this enlightening. I think it is just one instance of difference in emphasis between Google and Facebook. Google, a technology oriented company, would minimize the average response time, while Facebook, a people oriented company would minimize the unpredictability (by minimizing the variance)

>> "It is OK if a query is slow as long as it is always slow"

I'm having trouble understanding the motivation. If a slow query is always slow, then I'm always going to be kept waiting for that page/data. It seems logical to worry about the queries that 100% of the time keeps users waiting rather than the queries that keep users waiting Does anyone care to explain why this is a good idea (for Facebook at least)?

Re: MySQL at Facebook

#12

Post by kmavm » Thu, Nov 04, 2010, 6:25 PM UTC

thevivekpandey wrote: post #6

"It is OK if a query is slow as long as it is always slow" I find this enlightening. I think it is just one instance of difference in emphasis between Google and Facebook. Google, a technology oriented company, would minimize the average response time, while Facebook, a people oriented company would minimize the unpredictability (by minimizing the variance)

For what it's worth, Jeff Dean of Google has famously emphasized 95th and 99th percentile performance in preference to average or median performance over the years. The realization that edge cases more powerfully determine user perception of performance than average cases is a deep one, and is not original to Facebook or Google; Dean and colleagues were making some of the same points when they were at DEC's CRC pre-Google.

Re: MySQL at Facebook

#13

Post by kmavm » Thu, Nov 04, 2010, 6:27 PM UTC

davidamcclain wrote: post #11

thevivekpandey wrote: post #6

"It is OK if a query is slow as long as it is always slow" I find this enlightening. I think it is just one instance of difference in emphasis between Google and Facebook. Google, a technology oriented company, would minimize the average response time, while Facebook, a people oriented company would minimize the unpredictability (by minimizing the variance)

>> "It is OK if a query is slow as long as it is always slow" I'm having trouble understanding the motivation. If a slow query is always slow, then I'm always going to be kept waiting for that page/data. It seems logical to worry about the queries that 100% of the time keeps users waiting rather than the queries that keep users waiting Does anyone care to explain why this is a good idea (for Facebook at least)?

A query that is always slow will not end up on home.php. A query that is slow 1% of the time might.

Re: MySQL at Facebook

#14

Post by adamesque » Thu, Nov 04, 2010, 6:32 PM UTC

davidamcclain wrote: post #11

thevivekpandey wrote: post #6

"It is OK if a query is slow as long as it is always slow" I find this enlightening. I think it is just one instance of difference in emphasis between Google and Facebook. Google, a technology oriented company, would minimize the average response time, while Facebook, a people oriented company would minimize the unpredictability (by minimizing the variance)

>> "It is OK if a query is slow as long as it is always slow" I'm having trouble understanding the motivation. If a slow query is always slow, then I'm always going to be kept waiting for that page/data. It seems logical to worry about the queries that 100% of the time keeps users waiting rather than the queries that keep users waiting Does anyone care to explain why this is a good idea (for Facebook at least)?

The parent hints at this by talking about minimizing unpredictability and describing Facebook as "people oriented" - it ultimately boils down to user experience.

Think of it this way: if you know a certain function will reliably take a little while to complete, you can justify the effort of adding progress indicators and other feedback to let the user know.

But if query performance is unpredictable, even planning the UI design becomes difficult - not to mention the end user's experience.

Re: MySQL at Facebook

#15

Post by bialecki » Thu, Nov 04, 2010, 6:34 PM UTC

davidamcclain wrote: post #11

thevivekpandey wrote: post #6

"It is OK if a query is slow as long as it is always slow" I find this enlightening. I think it is just one instance of difference in emphasis between Google and Facebook. Google, a technology oriented company, would minimize the average response time, while Facebook, a people oriented company would minimize the unpredictability (by minimizing the variance)

>> "It is OK if a query is slow as long as it is always slow" I'm having trouble understanding the motivation. If a slow query is always slow, then I'm always going to be kept waiting for that page/data. It seems logical to worry about the queries that 100% of the time keeps users waiting rather than the queries that keep users waiting Does anyone care to explain why this is a good idea (for Facebook at least)?

I'm sure the rationale here is that if a given query takes 100ms, they don't focus on getting it down to 50ms even if that's several times what the average is because they know they can. Certainly someone should focus on making that query faster, but it's more straightforward.

The harder problem is figuring out why that 20ms query suddenly balloons to 200ms. You can say, "no big deal, it only happens 1% of the time," but if you don't know why, you could make changes to the system that cause it to happen much more frequently and eventually bring the whole system down.

Also, there's a bit of UX here. People are much more frustrated by things they don't understand and/or aren't used. There are parts of GMail that are always slow (archiving a lot of messages). I know this so I know I have to wait 5-10 seconds. What if sometimes it took 1 second and sometimes it took 20 seconds? What if it took 20 seconds 5% of the time. I'd probably always click again and think something was broken. If it's always slow, I want it to be faster, but at least I know what to expect.

Re: MySQL at Facebook

#16

Post by codypo » Thu, Nov 04, 2010, 6:42 PM UTC

I enjoyed the section on creating quality, not quantity, and its emphasis on minimizing variance. I can see how these heuristics could be applied to most startups.

The section on diagnosing should be taken with a grain of salt, though. If your company ever gets to the point where you need to monitor everything at subsecond level to catch problems or analyze and understand every layer of your stack to see how it performs, you've already won. That amount of attention to scalability means your company has a huge base of users. Not only that, it means you have the large and impressive engineering resources to devote to that problem.

That's definitely not my startup, and so the tools described, while definitely useful (and probably fun to build!), aren't anything approaching a priority for me. In the words of the stereotypical Yiddish grandmother, you should be so lucky to have those sorts of problems!

Re: MySQL at Facebook

#17

Post by matwood » Thu, Nov 04, 2010, 6:52 PM UTC

qjz wrote: post #5

"They figure out why the response time for the worst query is bad and then fix it." This can reap huge benefits and doesn't need to be difficult. Just enable the slow query log in MySQL, use the EXPLAIN command to analyze the results, then add indexes where appropriate. I was able to fix poorly indexed tables in a vendor's application with dramatic results. In one case, a twenty-minute(!) query was reduced to less th…

While your premise is correct it's not always that simple. More indexes can slow down updates and inserts. A tables overall usage pattern needs to be examined before just adding more indexes. You might have fixed the 20 minute report query while slowing down some other more critical query that loaded or updated data.

Re: MySQL at Facebook

#18

Post by preek » Thu, Nov 04, 2010, 6:52 PM UTC

Does anyone know the througput of the largest MSSQL installation? I'm searching the web to show off a little information at work, but I can't find anything that compares.

Re: MySQL at Facebook

#19

Post by ora600 » Thu, Nov 04, 2010, 7:07 PM UTC

qjz wrote: post #5

"They figure out why the response time for the worst query is bad and then fix it." This can reap huge benefits and doesn't need to be difficult. Just enable the slow query log in MySQL, use the EXPLAIN command to analyze the results, then add indexes where appropriate. I was able to fix poorly indexed tables in a vendor's application with dramatic results. In one case, a twenty-minute(!) query was reduced to less th…

I believe that Facebook subscribe to this methodology because MySQL makes this easy and other methods of finding issues very difficult.

When all you have is a hammer...

Re: MySQL at Facebook

#20

Post by ora600 » Thu, Nov 04, 2010, 7:10 PM UTC

known wrote: post #9

Does PayPal use MySQL?

Everyone is using MySQL. But I attended a talk by one of their DBAs where he said that they large OLTP server, the one that processes the payments is Oracle. Single-instance, because Oracle RAC can't give them the low latency they needed.

They switch the the biggest machine IBM can give them every few month.