4 ms·

(author here) Trying to compare costs between BigQuery and Redshift/Snowflake is really, really hard because of their different pricing models. I tried to be v

(author here) Trying to compare costs between BigQuery and Redshift/Snowflake is really, really hard because of their different pricing models. I tried to be very explicit about what assumptions I am making so people can make their own judgements. You are correct that a single-user sending queries to the warehouse is not realistic, but it should still produce an accurate price comparison. Here's my logic: * Suppose I were to send 10 times as many concurrent queries to the same warehouse. That would drop the effective cost-per-query by 1/10, which makes BigQuery look 10 times worse. * But suppose these queries now take 10 times as long. According to my formula, that makes the effective cost-per-query 10x larger, which makes BigQuery look 10 times better. As long as the performance of the warehouse is linear-ish with respect to number-of-users, these two effects should cancel each other out. RE: PS: Don't worry, I made that one up just for that test cluster, which is now gone :)

boulos 9y ago
I should have been more explicit: it won't be linear-ish. As you fill up any per-box sort of system, you start contending (pretend that you simulate 100 analysts) while the BigQuery results will be closer to linear. It does suck that to benchmark that "accurately", you have to make the queries different enough so that no reuse occurs (unless that's reasonable!). Again, thanks for doing this!
- georgewfraser 9y ago
  We're definitely going to revisit this periodically and I would love to address this in the next iteration. Would you mind creating an issue at https://github.com/fivetran/benchmark https://github.com/fivetran/benchmark describing the concurrency trade-off that we're not accurately capturing?
- ShaunK 9y ago
  When we've looked at BigQuery it seemed that if you prepay you essentially get a similar effect to what you're describing. You're given a certain number of "units" of compute, and if you exceeded your concurrent units available you end up with the same compute resource contention you would with an improperly scaled Snowflake warehouse or Redshift cluster. If you're willing to just pay per gigabyte scanned with BigQuery you can scale near linearly I'm sure (although I haven't actually tried it), but you could accomplish the same thing using Snowflake's API to add warehouses as concurrent query load increases. That's what we do (although we just pre-allocate and suspend the warehouses because you only pay when they're on). Redshift does suffer from this problem because the compute is tied to the data, but Redshift Spectrum is attempting to rectify that as well. I don't know anything about its performance though.
vgt 9y ago
I work at Google and was on the bigquery/dremel team. I've written on this topic in the past at [0]. Turns out analytic SQL workloads are incredibly volatile - we know from operating these big ol Dremel/bq clusters in a multi tenant fashion. In my opinion boulos is correct to point out that the above analysis sets volatility of usage at 0, which is not representing the real world.. Think about it another way. Bigquery on demand pricing only charges you for the resources you use, rather than provision. This guarantees you don't pay for the luxury of deployed resources that sit idle. By definition this also means your "cluster utilization" is 100% with bigquery. It's rare to see provisioned databases get above 50%, even in cloud. Periscope data specifically called out running redshift at 50% utilization as a best practice - in other words it's a best practice to double your bill just for deployment reasons. There is, of course, bigquery flat rate pricing for larger use cases, which is incredibly cost competitive. Folks who migrate to bigquery also specifically call out cost as a major benefit. Some sites that call this out are kabam[1], sharethis[2], Yahoo [3], ny times[3], Motorola[4]. This logic is extrapolatable to any cloud pay-per-use pricing schemas (like queuing, storage, API calls, etc). It's just that in analytics usage tends to be incredibly volatile, and thus this is extra significant. Happy to chat further. [0] https://cloud.google.com/blog/big-data/2016/02/visualizing-the-mechanics-of-on-demand-pricing-in-big-data-technologies https://cloud.google.com/blog/big-data/2016/02/visualizing-t... [1]https://youtu.be/6Nv18xmJirs https://youtu.be/6Nv18xmJirs [2]https://cloud.google.com/customers/sharethis/ https://cloud.google.com/customers/sharethis/ [3]https://youtu.be/TLpfGaYWshw https://youtu.be/TLpfGaYWshw [4]https://cloud.google.com/blog/big-data/2016/07/supersize-it-how-motorola-transformed-its-data-warehousing-and-analytics-with-google-cloud-platform https://cloud.google.com/blog/big-data/2016/07/supersize-it-...

boulos 9y ago

I should have been more explicit: it won't be linear-ish. As you fill up any per-box sort of system, you start contending (pretend that you simulate 100 analysts) while the BigQuery results will be closer to linear. It does suck that to benchmark that "accurately", you have to make the queries different enough so that no reuse occurs (unless that's reasonable!). Again, thanks for doing this!

georgewfraser 9y ago
We're definitely going to revisit this periodically and I would love to address this in the next iteration. Would you mind creating an issue at https://github.com/fivetran/benchmark https://github.com/fivetran/benchmark describing the concurrency trade-off that we're not accurately capturing?
ShaunK 9y ago
When we've looked at BigQuery it seemed that if you prepay you essentially get a similar effect to what you're describing. You're given a certain number of "units" of compute, and if you exceeded your concurrent units available you end up with the same compute resource contention you would with an improperly scaled Snowflake warehouse or Redshift cluster. If you're willing to just pay per gigabyte scanned with BigQuery you can scale near linearly I'm sure (although I haven't actually tried it), but you could accomplish the same thing using Snowflake's API to add warehouses as concurrent query load increases. That's what we do (although we just pre-allocate and suspend the warehouses because you only pay when they're on). Redshift does suffer from this problem because the compute is tied to the data, but Redshift Spectrum is attempting to rectify that as well. I don't know anything about its performance though.

vgt 9y ago

I work at Google and was on the bigquery/dremel team. I've written on this topic in the past at [0]. Turns out analytic SQL workloads are incredibly volatile - we know from operating these big ol Dremel/bq clusters in a multi tenant fashion. In my opinion boulos is correct to point out that the above analysis sets volatility of usage at 0, which is not representing the real world.. Think about it another way. Bigquery on demand pricing only charges you for the resources you use, rather than provision. This guarantees you don't pay for the luxury of deployed resources that sit idle. By definition this also means your "cluster utilization" is 100% with bigquery. It's rare to see provisioned databases get above 50%, even in cloud. Periscope data specifically called out running redshift at 50% utilization as a best practice - in other words it's a best practice to double your bill just for deployment reasons. There is, of course, bigquery flat rate pricing for larger use cases, which is incredibly cost competitive. Folks who migrate to bigquery also specifically call out cost as a major benefit. Some sites that call this out are kabam[1], sharethis[2], Yahoo [3], ny times[3], Motorola[4]. This logic is extrapolatable to any cloud pay-per-use pricing schemas (like queuing, storage, API calls, etc). It's just that in analytics usage tends to be incredibly volatile, and thus this is extra significant. Happy to chat further. [0] https://cloud.google.com/blog/big-data/2016/02/visualizing-the-mechanics-of-on-demand-pricing-in-big-data-technologies https://cloud.google.com/blog/big-data/2016/02/visualizing-t... [1]https://youtu.be/6Nv18xmJirs https://youtu.be/6Nv18xmJirs [2]https://cloud.google.com/customers/sharethis/ https://cloud.google.com/customers/sharethis/ [3]https://youtu.be/TLpfGaYWshw https://youtu.be/TLpfGaYWshw [4]https://cloud.google.com/blog/big-data/2016/07/supersize-it-how-motorola-transformed-its-data-warehousing-and-analytics-with-google-cloud-platform https://cloud.google.com/blog/big-data/2016/07/supersize-it-...