16 ms·

MySQL performance optimization: 50% more work with 60% less latency variance

82 points by kilimchoi 11y ago

thaumaturgy 11y ago
My jaw landed on the floor when it said that pinterest runs on AWS. AWS is fantastic but I would've expected that the performance-per-dollar ratio is way below having your own colo'd hardware at pinterest scale -- and would leave you spending talent on trying to optimize your software, instead of just throwing more hardware at your operation. Is this common now, that pinterest-sized businesses are running on AWS?
- vgeek 11y ago
  http://www.geekwire.com/2014/heres-startup-dumped-amazon-web-services/ http://www.geekwire.com/2014/heres-startup-dumped-amazon-web...
- kasey_junk 11y ago
  Netflix runs on AWS (at least a lot of it does). Which is a bigger business than Pinterest, and more interestingly to me, in direct competition with Amazon.
  - jandrese 11y ago
    Netflix is a bit unusual in that they just need partners with big pipes. Their servers can probably fill up pretty much any pipe with only a relatively modest amount of CPU and memory. This is as opposed to a big database driven site like Pintrest that has to custom build pages for every view thousands of times every second. They are far more likely to be memory and CPU bound than Netflix.
    - kasey_junk 11y ago
      Sure, but I didn't mean to speak to the technical ramifications of the decision to use AWS, rather I was trying to speak to the business ramifications. That is, AWS has always been a high premium service with the standard story being that you make up for that premium with lower operational costs and more operational sophistication. But when large (and technically sophisticated) companies like Pinterest and Netflix continue to use it you have to question that story (and to me, especially in Netflix's case as they have the added incentive being a direct competitor to Amazon in their main business). My current working conspiracy theory is that implicitly or explicitly, the growth of AWS is tied to some sort of regulatory arbitrage and has virtually nothing to do with hardware performance, software resources, or operational sophistication. That is, AWS allows you to move costs that would have been capital expenditures into other buckets and that is in some way that is obscure to me, very valuable from a business point of view.
  - fweespeech 11y ago
    Netflix's core stuff runs on AWS but they farm out a bunch of boxes to providers and don't pay AWS for bandwidth the way normal people would. http://techblog.netflix.com/2010/12/four-reasons-we-choose-amazons-cloud-as.html http://techblog.netflix.com/2010/12/four-reasons-we-choose-a... > 3. We’re not very good at predicting customer growth or device engagement. That really is a large part of why Netflix runs on AWS. Capacity prediction isn't something they can do reliably due to the nature of their service. That makes sense for a video service. For 99% of websites, you are not going to have trouble with capacity prediction on the scale of 72 hour turn around for additional capacity. Almost any DC can sell you capacity in under 48 hours and if it takes you longer than 24 hours to deploy...yeah...that is on you.
- Someone1234 11y ago
  It actually can make more sense for larger websites than for smaller ones, as Pinterest can scale with demand and thus be running 50% or less VMs during quiet periods (e.g. middle of the night). I'm not exactly sure how you jumped from them using AWS to the assumption that they're "throwing more hardware at [their] problems" rather than optimizing their software. AWS or other cloud services don't really indicate a certain priority as far as internal development, just like first party hardware doesn't (and there are companies that throw additional hardware at problems, just like there are companies that throw extra virtual capacity at it).
  - toomuchtodo 11y ago
    AWS margin is so much higher than your own physical hardware, therefore its cheaper to throw your own hardware at the problem instead of AWS resources (and their built int margin), before finally contemplating spending expensive engineer time on it.
    - merb 11y ago
      it's not. Most people forgetting maintenance costs. They are raising much higher in non Cloud environments. Instead of tracking just your load and buying servers you need to track EVERYTHING. Hardware failure, replace it, etc. Also networking, it's really really really hard to run a Big network. AWS VPC is easy, AWS VPC is predictable. A normal network is not predictable. People forget about a lot of things, especially that it isn't that easy to buy hardware and put it into a datacenter. Also renting the datacenter or buying it? What about the clima? Do you need special coolers etc? What about raising electricity cost? Or higher network costs to your t2, t1 provider? On AWS a box costs x Cent per Hour and most likely these prices will fall. You only need to pay that. We run on AWS and especially due to the networking we pay less than we would pay in a normal datacenter. Mostly cause of the fact that they want money for everything. Even in the small scale, just buying 3-4 AWS instances upfront is really really cheap.
      - toomuchtodo 11y ago
        I do Devops/Infrastructure for a living, I've shown the math to both my day gig and numerous consulting clients that AWS is 30-40% more expensive, even when taking into account reserved instance pricing. External networking expecially is super expensive compared to traditional transit pricing. They're absolutely wonderful for bursty or async workloads, but if you know your load, you're better off on your own gear. EDIT: Source added. https://blog.serverdensity.com/saving-500k-per-month-buying-your-own-hardware-cloud-vs-colocation/ https://blog.serverdensity.com/saving-500k-per-month-buying-... "Saving $500k per month buying your own hardware – cloud vs colocation" The article mentions SoftLayer, but its just as relevant for AWS.
- dabeeeenster 11y ago
  Snapchat is entirely on Google Cloud Platform AFAIK
  - MichaelGG 11y ago
    GCP seems much less expensive. Compared to Azure, for instance, I've found VM to cost from 50% to 10%. (Not to mention the SSD options aren't idiotically lame.) The GCP portal and speed also totally obliterates Azure. AWS had more complicated pricing via that weird reservation system and "reselling" marketplace. But GCP, as far as I could tell, was still a bit less expensive.
    - campers 11y ago
      There's a couple of posts on the GCP blog where they compare equivalent as possible setups, comes out a bit cheaper than AWS. http://googlecloudplatform.blogspot.com.au/2015/01/understanding-cloud-pricing.html http://googlecloudplatform.blogspot.com.au/2015/01/understan... http://googlecloudplatform.blogspot.com.au/2015/04/understanding-cloud-pricing-part-2.html http://googlecloudplatform.blogspot.com.au/2015/04/understan...
- vosper 11y ago
  I think often you'd rather throw money at the problem, and have your engineering talent working on new features. This is one the of the nice things about AWS - oftentimes for the cost of a few hours of engineering time (what you might spend on a meeting to discuss optimisations) you could run a faster machine(s) for a few weeks.
- wahnfrieden 11y ago
  Zynga bet on being able to build out their own datacenter cheaper than what it cost them on AWS. They took this experiment very far and ultimately couldn't beat AWS. There're a lot of hidden costs when you consider AWS-level datacenter management vs. the type of comparisons you might be used to looking at simple dedicated hardware vs. AWS offerings. Random Google result about this: http://readwrite.com/2015/05/12/zynga-data-center-amazon-aws-public-cloud http://readwrite.com/2015/05/12/zynga-data-center-amazon-aws...
  - granto 11y ago
    Zynga's case was more nuanced than that story leads on. Zynga built out its datacenter capacity assuming a base line of users, which in hindsight, was optimistic and left them with significant excess capacity and costs.
    - icelancer 11y ago
      Well... this is kind of a selling point of AWS... isn't it? So it's not just "nuance," it's actually a feature.
- ap22213 11y ago
  I can easily crank up to 15000 transactions per second on AWS RDS for relatively low cost. Why would I want to hire people to manage that?
- mianos 11y ago
  There are not a lot of Pinterest-sized businesses. :) Amazon is documented as giving these guys some serious discounts. Even a cursory analysis of the costs of running at this at scale on AWS shows it's bad value at 'recommended retail' prices.
  - takeda 11y ago
    > Amazon is documented as giving these guys some serious discounts. So do hardware vendors. I think this kind of argument doesn't help, once you are big you get discounts everywhere.
- spotman 11y ago
  Yes. Pinterest is big but it's not Amazon, Google or Facebook big. With things like reserved instance pricing, and not having to spend as much money on a physical ops team that can manage real hardware, it leaves room to optimize software and cloud architecture. without knowing the specifics of their workload I would bet that it's probably a draw at their scale. they may be big enough to start considering this sort of transition... but, it also takes a significant amount of time, energy, people and energy to make that transition, so they probably won't do it until it's a no brainer. Curious if the numbers in the article are their entire db workload, 20-30k qps. If so they have a while to go before they are ready to graduate from the cloud in my opinion. (having no idea of the real meaty specifics, but imagining as it is similar in function to many that I have worked on).
dsheth 11y ago
Would be interesting to see how this compares to RDS--i.e. perhaps all these optimizations are already in place, or perhaps it makes sense to not use RDS, and optimize mysql for your own workload.
acd 11y ago
A question, with such high load, why not run it on dedicated hardware with PCI express SSDs? You are paying around $1000-2500 per month for the instance type for that you can get dedicated machine and lessen the total number of servers.
- MichaelGG 11y ago
  Or use Google Cloud which will stick a 375GB PCIe SSD in your VM for $80 a month.
  - campers 11y ago
    Yeah their SSD pricing example seemed to blow AWS away http://googlecloudplatform.blogspot.com.au/2015/04/understanding-cloud-pricing-part-2.html http://googlecloudplatform.blogspot.com.au/2015/04/understan...
    - MichaelGG 11y ago
      Awesome.. As much as I dislike Google as a business, I love their engineering and despite being totally biased coming on to GCP, they won me entirely in a day. We're so, so very much happier dealing with GCP. No RAID to get perf - just ask for more. No complexity. Simple, damn fast, and cheap. I cannot imagine how Azure or AWS compete (esp Azure - even without making a cheap comment about their terrible new portal) when it comes to IaaS. GCP is just flat out better.
- hermanradtke 11y ago
  We have been using FusionIO cards to get effect.
Thaxll 11y ago
When I see the prices / perf that AWS offers compare to high end hosting like Rackspace, its' crazy. I don't know why people keep running MySQL on AWS.
- pbz 11y ago
  Rackspace is on the high end as far as price. You can find DCs for much much less (multiple times less).
thrownaway2424 11y ago
It's amazing to me that anyone still tries to run mysql with the glibc allocator. That allocator is not suitable for any kind of multithreaded workload. Use tcmalloc or jemalloc for everything.
rastem 11y ago
This article doesn't talk about the optimizations, it just mentions they were done. Clickbait, I'm afraid.
- seany 11y ago
  There is the slide deck to a related talk. http://www.slideshare.net/denshikarasu/all-your-iops-are-belong-to-us-a-pinteresting-case-study-in-mysql-performance-optimization http://www.slideshare.net/denshikarasu/all-your-iops-are-bel...
- dragonne 11y ago
  The link at the end points to this slide deck, which has more specific details: http://www.slideshare.net/denshikarasu/all-your-iops-are-belong-to-us-a-pinteresting-case-study-in-mysql-performance-optimization http://www.slideshare.net/denshikarasu/all-your-iops-are-bel... I recommend taking a look at it.
Kassandry 11y ago
It sounds like they really ran afoul of the stable page writes bugs that exist in Linux 3.2.31 to 3.9. Thread discussing the bug and a test program to show the issue. https://lkml.org/lkml/2012/10/9/210 https://lkml.org/lkml/2012/10/9/210 Patch introducing the bug: https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.git/patch/?id=3d08bcc887a1c8d12be8d81f747ffa2e8a44b67b https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.... Patch fixing the bug: https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.git/patch/?id=1d1d1a767206fbe5d4c69493b7e6d2a8d08cc0a0 https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.... It causes a huge dropoff in latency and throughput by causing all filesystems to block even when they don't need to. In short, 3.2 is lethally bad in terms of database workloads.

82 points by kilimchoi 11y ago

My jaw landed on the floor when it said that pinterest runs on AWS. AWS is fantastic but I would've expected that the performance-per-dollar ratio is way below having your own colo'd hardware at pinterest scale -- and would leave you spending talent on trying to optimize your software, instead of just throwing more hardware at your operation. Is this common now, that pinterest-sized businesses are running on AWS?

vgeek 11y ago
http://www.geekwire.com/2014/heres-startup-dumped-amazon-web-services/ http://www.geekwire.com/2014/heres-startup-dumped-amazon-web...
kasey_junk 11y ago
Netflix runs on AWS (at least a lot of it does). Which is a bigger business than Pinterest, and more interestingly to me, in direct competition with Amazon.
- jandrese 11y ago
  Netflix is a bit unusual in that they just need partners with big pipes. Their servers can probably fill up pretty much any pipe with only a relatively modest amount of CPU and memory. This is as opposed to a big database driven site like Pintrest that has to custom build pages for every view thousands of times every second. They are far more likely to be memory and CPU bound than Netflix.
  - kasey_junk 11y ago
    Sure, but I didn't mean to speak to the technical ramifications of the decision to use AWS, rather I was trying to speak to the business ramifications. That is, AWS has always been a high premium service with the standard story being that you make up for that premium with lower operational costs and more operational sophistication. But when large (and technically sophisticated) companies like Pinterest and Netflix continue to use it you have to question that story (and to me, especially in Netflix's case as they have the added incentive being a direct competitor to Amazon in their main business). My current working conspiracy theory is that implicitly or explicitly, the growth of AWS is tied to some sort of regulatory arbitrage and has virtually nothing to do with hardware performance, software resources, or operational sophistication. That is, AWS allows you to move costs that would have been capital expenditures into other buckets and that is in some way that is obscure to me, very valuable from a business point of view.
- fweespeech 11y ago
  Netflix's core stuff runs on AWS but they farm out a bunch of boxes to providers and don't pay AWS for bandwidth the way normal people would. http://techblog.netflix.com/2010/12/four-reasons-we-choose-amazons-cloud-as.html http://techblog.netflix.com/2010/12/four-reasons-we-choose-a... > 3. We’re not very good at predicting customer growth or device engagement. That really is a large part of why Netflix runs on AWS. Capacity prediction isn't something they can do reliably due to the nature of their service. That makes sense for a video service. For 99% of websites, you are not going to have trouble with capacity prediction on the scale of 72 hour turn around for additional capacity. Almost any DC can sell you capacity in under 48 hours and if it takes you longer than 24 hours to deploy...yeah...that is on you.
Someone1234 11y ago
It actually can make more sense for larger websites than for smaller ones, as Pinterest can scale with demand and thus be running 50% or less VMs during quiet periods (e.g. middle of the night). I'm not exactly sure how you jumped from them using AWS to the assumption that they're "throwing more hardware at [their] problems" rather than optimizing their software. AWS or other cloud services don't really indicate a certain priority as far as internal development, just like first party hardware doesn't (and there are companies that throw additional hardware at problems, just like there are companies that throw extra virtual capacity at it).
- toomuchtodo 11y ago
  AWS margin is so much higher than your own physical hardware, therefore its cheaper to throw your own hardware at the problem instead of AWS resources (and their built int margin), before finally contemplating spending expensive engineer time on it.
  - merb 11y ago
    it's not. Most people forgetting maintenance costs. They are raising much higher in non Cloud environments. Instead of tracking just your load and buying servers you need to track EVERYTHING. Hardware failure, replace it, etc. Also networking, it's really really really hard to run a Big network. AWS VPC is easy, AWS VPC is predictable. A normal network is not predictable. People forget about a lot of things, especially that it isn't that easy to buy hardware and put it into a datacenter. Also renting the datacenter or buying it? What about the clima? Do you need special coolers etc? What about raising electricity cost? Or higher network costs to your t2, t1 provider? On AWS a box costs x Cent per Hour and most likely these prices will fall. You only need to pay that. We run on AWS and especially due to the networking we pay less than we would pay in a normal datacenter. Mostly cause of the fact that they want money for everything. Even in the small scale, just buying 3-4 AWS instances upfront is really really cheap.
    - toomuchtodo 11y ago
      I do Devops/Infrastructure for a living, I've shown the math to both my day gig and numerous consulting clients that AWS is 30-40% more expensive, even when taking into account reserved instance pricing. External networking expecially is super expensive compared to traditional transit pricing. They're absolutely wonderful for bursty or async workloads, but if you know your load, you're better off on your own gear. EDIT: Source added. https://blog.serverdensity.com/saving-500k-per-month-buying-your-own-hardware-cloud-vs-colocation/ https://blog.serverdensity.com/saving-500k-per-month-buying-... "Saving $500k per month buying your own hardware – cloud vs colocation" The article mentions SoftLayer, but its just as relevant for AWS.
dabeeeenster 11y ago
Snapchat is entirely on Google Cloud Platform AFAIK
- MichaelGG 11y ago
  GCP seems much less expensive. Compared to Azure, for instance, I've found VM to cost from 50% to 10%. (Not to mention the SSD options aren't idiotically lame.) The GCP portal and speed also totally obliterates Azure. AWS had more complicated pricing via that weird reservation system and "reselling" marketplace. But GCP, as far as I could tell, was still a bit less expensive.
  - campers 11y ago
    There's a couple of posts on the GCP blog where they compare equivalent as possible setups, comes out a bit cheaper than AWS. http://googlecloudplatform.blogspot.com.au/2015/01/understanding-cloud-pricing.html http://googlecloudplatform.blogspot.com.au/2015/01/understan... http://googlecloudplatform.blogspot.com.au/2015/04/understanding-cloud-pricing-part-2.html http://googlecloudplatform.blogspot.com.au/2015/04/understan...
vosper 11y ago
I think often you'd rather throw money at the problem, and have your engineering talent working on new features. This is one the of the nice things about AWS - oftentimes for the cost of a few hours of engineering time (what you might spend on a meeting to discuss optimisations) you could run a faster machine(s) for a few weeks.
wahnfrieden 11y ago
Zynga bet on being able to build out their own datacenter cheaper than what it cost them on AWS. They took this experiment very far and ultimately couldn't beat AWS. There're a lot of hidden costs when you consider AWS-level datacenter management vs. the type of comparisons you might be used to looking at simple dedicated hardware vs. AWS offerings. Random Google result about this: http://readwrite.com/2015/05/12/zynga-data-center-amazon-aws-public-cloud http://readwrite.com/2015/05/12/zynga-data-center-amazon-aws...
- granto 11y ago
  Zynga's case was more nuanced than that story leads on. Zynga built out its datacenter capacity assuming a base line of users, which in hindsight, was optimistic and left them with significant excess capacity and costs.
  - icelancer 11y ago
    Well... this is kind of a selling point of AWS... isn't it? So it's not just "nuance," it's actually a feature.
ap22213 11y ago
I can easily crank up to 15000 transactions per second on AWS RDS for relatively low cost. Why would I want to hire people to manage that?
mianos 11y ago
There are not a lot of Pinterest-sized businesses. :) Amazon is documented as giving these guys some serious discounts. Even a cursory analysis of the costs of running at this at scale on AWS shows it's bad value at 'recommended retail' prices.
- takeda 11y ago
  > Amazon is documented as giving these guys some serious discounts. So do hardware vendors. I think this kind of argument doesn't help, once you are big you get discounts everywhere.
spotman 11y ago
Yes. Pinterest is big but it's not Amazon, Google or Facebook big. With things like reserved instance pricing, and not having to spend as much money on a physical ops team that can manage real hardware, it leaves room to optimize software and cloud architecture. without knowing the specifics of their workload I would bet that it's probably a draw at their scale. they may be big enough to start considering this sort of transition... but, it also takes a significant amount of time, energy, people and energy to make that transition, so they probably won't do it until it's a no brainer. Curious if the numbers in the article are their entire db workload, 20-30k qps. If so they have a while to go before they are ready to graduate from the cloud in my opinion. (having no idea of the real meaty specifics, but imagining as it is similar in function to many that I have worked on).

dsheth 11y ago

Would be interesting to see how this compares to RDS--i.e. perhaps all these optimizations are already in place, or perhaps it makes sense to not use RDS, and optimize mysql for your own workload.

acd 11y ago

A question, with such high load, why not run it on dedicated hardware with PCI express SSDs? You are paying around $1000-2500 per month for the instance type for that you can get dedicated machine and lessen the total number of servers.

MichaelGG 11y ago
Or use Google Cloud which will stick a 375GB PCIe SSD in your VM for $80 a month.
- campers 11y ago
  Yeah their SSD pricing example seemed to blow AWS away http://googlecloudplatform.blogspot.com.au/2015/04/understanding-cloud-pricing-part-2.html http://googlecloudplatform.blogspot.com.au/2015/04/understan...
  - MichaelGG 11y ago
    Awesome.. As much as I dislike Google as a business, I love their engineering and despite being totally biased coming on to GCP, they won me entirely in a day. We're so, so very much happier dealing with GCP. No RAID to get perf - just ask for more. No complexity. Simple, damn fast, and cheap. I cannot imagine how Azure or AWS compete (esp Azure - even without making a cheap comment about their terrible new portal) when it comes to IaaS. GCP is just flat out better.
hermanradtke 11y ago
We have been using FusionIO cards to get effect.

Thaxll 11y ago

When I see the prices / perf that AWS offers compare to high end hosting like Rackspace, its' crazy. I don't know why people keep running MySQL on AWS.

pbz 11y ago
Rackspace is on the high end as far as price. You can find DCs for much much less (multiple times less).

thrownaway2424 11y ago

It's amazing to me that anyone still tries to run mysql with the glibc allocator. That allocator is not suitable for any kind of multithreaded workload. Use tcmalloc or jemalloc for everything.

rastem 11y ago

This article doesn't talk about the optimizations, it just mentions they were done. Clickbait, I'm afraid.

seany 11y ago
There is the slide deck to a related talk. http://www.slideshare.net/denshikarasu/all-your-iops-are-belong-to-us-a-pinteresting-case-study-in-mysql-performance-optimization http://www.slideshare.net/denshikarasu/all-your-iops-are-bel...
dragonne 11y ago
The link at the end points to this slide deck, which has more specific details: http://www.slideshare.net/denshikarasu/all-your-iops-are-belong-to-us-a-pinteresting-case-study-in-mysql-performance-optimization http://www.slideshare.net/denshikarasu/all-your-iops-are-bel... I recommend taking a look at it.

Kassandry 11y ago

It sounds like they really ran afoul of the stable page writes bugs that exist in Linux 3.2.31 to 3.9. Thread discussing the bug and a test program to show the issue. https://lkml.org/lkml/2012/10/9/210 https://lkml.org/lkml/2012/10/9/210 Patch introducing the bug: https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.git/patch/?id=3d08bcc887a1c8d12be8d81f747ffa2e8a44b67b https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.... Patch fixing the bug: https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.git/patch/?id=1d1d1a767206fbe5d4c69493b7e6d2a8d08cc0a0 https://git.kernel.org/cgit/linux/kernel/git/torvalds/linux.... It causes a huge dropoff in latency and throughput by causing all filesystems to block even when they don't need to. In short, 3.2 is lethally bad in terms of database workloads.