Debunking Misleading Benchmarks Of Redshift vs BigQuery
aws.amazon.com
aws.amazon.com
Well, yeah. That's 28,108.80 a month if you're running queries on demand and don't want a delay/coordination in Amazon instance creation/destruction.
BQ may or may not be as fast, but it's truly a managed service; I give it our data and it just works. I don't have to worry about instances, boot up time, maintenance, hourly costs, etc. It's silly to focus just on query speed when there's a whole layer of management and cost that comes with it.
maybe i'm completely out of touch, but i'm really wondering who can afford this kind of stuff without $20M series A money in the bank. and i'm also wondering what they're going to do when they run out of that money and have zero database expertise in-house because they outsourced everything to amazon.
I dont think it's insane to believe that a profitable mature data informed business should be expected to spend 1/3 of a million a year for the ability to have all that data live and available without deep latency.
I can also agree that this is quite a bit for a startup, but if the unit economics of the startup require that this kind of data be available for interactive exploring, there's going to be a deep challenge down the road in scaling it I think?
To your point, if a startup runs through 20 million and their biggest problem is paying AWS bills, then they are probably a failed startup and folks should move on towards whatever next exciting thing is on the horizon.
On the other hand the Redshift cluster doesn't run itself, despite what Amazon says. You still need at least a part time DBA guy.
And as long as you were not into GPUs you could get some really decent HP servers at somewhere around $2000 - $7000 depending on your storage needs.
full-time DBA's work for either enormous corporations, or database consulting firms. it's very rare you will see some random small-medium company with even one DBA on payroll.
Startups on the other side, rarely have the amount of data that could not be hosted on anything for more than 1000-2000 USD/month.
I don't work for or have any relationship with either, but I do use Redshift.
Is this a typo? A single DC1.8XL is $4.80/hour. 8, like the article, would be $38.40/hour.
Numbers on https://aws.amazon.com/redshift/pricing/ used.
dc1.8xlarge - $4.800 per Hour
That's four point eight dollars, not four thousand eight hundred.
8 instances of DC1.8XL = $38.4 which means you can run the redshift cluster for 28 hours (edit: for $1100, which is the cost of scanning 220TB of data with BQ, used in previous comment).
Redshift does win on price when you are continuously running queries and scanning entire datasets, although there is enterprise pricing available for BQ if you hit a certain amount.
That is right. We had ~100K / month with on prem Hadoop (energy, networking, OPS, enterprise support, etc. included) that could have been moved to Redshift to save money. However, when we did the comparison a different cloud based solution came out as the winer for our DWH needs. The point is, showing a single number without showing how much it would be with other solutions is pointless. There are several companies that can easily afford a ~29K/month DWH in the cloud.
If you can't (or don't want to!) afford that kind of money for data analytics, please consider giving the FOSS alternative EventQL [0] a try some time.
It's super simple to set up and tries to be efficient on commodity hardware, so you can run large clusters (>100TB scale) for a couple hundred dollars a month.
DISC: I'm one of the EventQL authors
What kind of hardware would I need to do that interactively? Or consistently sub-10 second, with 100s of queries per minute.
I have built a thing on Redshift that can do some of this, but it has been new territory for me and I am not sure I've done it "right". Constantly looking for alternatives.
Your biggest issue with aggregation / group by is going to be the memory bandwidth to/from a hash table to store all the results. Once the hash table no longer fits in L3, its access time will rise dramatically, so your query's performance will depend heavily on how many buckets you need (if you're grouping by 2 columns, maybe not that many; if you're grouping by 20, you'll probably have almost no buckets with more than one entry). Another potential issue is going to be having to look up the values for that column in a hash table if they are highly compressed (since you need to perform aggregation on them, presumably something like sum; if it's count, this doesn't matter).
If you can find a total attribute order such that the columns you group by are always to the left of the columns you aggregate by, you can sort the rows to enable efficient delta encoding; you can also then perform aggregation in the same order as your scan, which eliminates the hash table lookup problem for output (not input, though). You can also pre-materialize the results for common subsets.
To do this with multiple queries at once, you'd probably need to batch query execution (because of the memory bandwidth issue I alluded to earlier). While the data access requirements would remain similar on read, they'd get worse and worse on write (again, depending on how many buckets you had and how cleverly sorted your data were).
Alternately, you could get a bunch of Power8s (or commodity machines, but to maximize bang for your hardware buck you really want stuff with tons of memory bandwidth) and give each of them a slice of the data (but still apply all the above optimizations). The commodity version of this is the Redshift solution. If you went this route, you could also look at specialized solutions like GPUs with NVLink or the KNL Xeon Phis, which have "fast" memory with tons of extra bandwidth which help mitigate the aforementioned hash table / query result access costs.
I still haven't talked about how you're supposed to actually get the results back. If you're trying to do ethernet through anything commodity you're going to be very limited in terms of data rate. Even 100 Gbps Infiniband only gets you 12.5 GB/s out, and even if you hook two of those up to each machine you're still only at 25 GB/s out. So either you get even more machines, your clients process the data on the machine, or you have to limit the output somehow... and if you're thinking "we'll sort it!" guess what that's going to be bound by (unless you can store the input data presorted)? Probably memory bandwidth (assuming radix sort)!
tl;dr One way or another, you're paying out the nose to satisfy the requirements you just outlined. Also worth noting that you're paying for this read performance on write.
Using a column store and doing a bunch of pre-aggregation is the only way we've been able to to come close to these requirements, but I keep hoping there is a system that will do what we've done without having to write code. Trading space for computation is the only thing that has worked reliably.
We also have the benefit that most queries just want a small subset of time and have things sorted to take advantage of that. But occasionally people want to do something over a large range and if isn't a set of group-bys we've pre-aggregated then they just have to be patient.
You can also look at MemSQL for a distributed relational database with a columnstore. Run enough nodes and you might be able to hit your performance goals.
The flat rate may improve things dramatically, of course, but the documentation's ambiguity about what you get with a "slot" makes it hard to say. And none of this is taking required bandwidth into account, because AFAICT there are no promises made on query response time... so I don't know if any of this would satisfy the 10 second requirement.
Either way, no matter how Google, MemSQL, or anyone else might try to satisfy these requirements, they can't get around the required hardware costs. At best, they can amortize them by buying in bulk and partitioning all their clients across lots of servers.
I don't work for any of these database providers, BTW, or use any of their products; I have no skin in this game. And I'm not even saying that paying $60 for that kind of query is necessarily a bad deal (when you consider what may be required under the hood). I'm just saying if you're looking for a cheap solution here you're not going to find it.
That's a given. I think in this context the user was asking how to meet performance goals with cost possibly secondary or not a concern... and in that case BQ has proven to be incredible at churning through large datasets within seconds.
While I might try it out myself to see how well it performs, it would be nice if some figures were readily available.
I've been keeping an eye on Postgres-XL and CitusDB for distributed SQL. Would be interesting to compare.
I have at least the assurance that such a thing cannot happen with Postgres (there are a multitude of for-profit companies working on it full-time, in addition to pro-bono community contributors), or with Apache Cassandra; and it's a similar assurance that keeps users of RethinkDB able to continue to operate despite the company being bought and folded.
----
Other advantages of open source: the very freedom to inspect and modify the software we run. This freedom is how OpenResty was born. In fact, Postgres itself comes from another GPLed database.
On a minor note: this freedom is what allowed us at my last job to create a patch for an internally required function in Nginx, in ten minutes. Had Nginx been closed source, we'd have had to request the makers, wait days/weeks/months, and if accepted they'd include it.
Could you please talk a bit more about your use cases? what domain, volume of data, how you manage updates etc, if it is possible for you to share?
I really think it's a good thing that AWS and GCP are punching each other in the cloud data warehousing market. It means that the market is maturing, and we are all benefiting from their arms race against each other.
I am of the opinion that Redshift and BigQuery are philosophically different enough that performance differences, while important, shouldn't be the deciding factor. I've written about this in a blog post awhile back, and it might be relevant for folks weighing their options
https://blog.treasuredata.com/blog/2016/06/09/redshift-bigqu...
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
Disclosures: I wrote the parent article.
BigQuery just introduced flat-rate pricing for this exact reason: https://cloud.google.com/bigquery/pricing#flat_rate_pricing
Disclosure: I work for Google Cloud
Again, I expect this "talk to sales" part to change in the future as BQ matures, but I imagine this kind of CIO friendly thinking to be new (if not de-prioritized) at Google.
If you're downvoting do you mind leaving a comment on why I'm wrong? I'm interested in knowing more. The documentation for BigQuery doesn't really explain how slots are allocated and what they're capable of.
Disclosures: I don't disclose things when it makes no sense to.
Don't shit on people for being polite.
Edit: That said, it works a lot better on reddit with flairs.
Nonsense. In what way does this help the reader? Facts are facts, whether they come from a Google employee or a clown. He/she didn't share an opinion that needs to be taken with a grain of salt.
BigQuery's no-ops model makes a huge difference. It saves a lot of effort when all you have to do is just load up your data and run queries, no other cluster starting, sizing, tweaking or maintenance necessary.
It has real-time streaming input, nested/repeated records, powerful RegEx support and UDFs so you can run some really complex queries that you can't do with traditional SQL. It also has the cheapest storage at $0.01/gb after 90 days. They even have DML statements in beta now to support update/delete statements.
If you have lots of data to store (TBs to PBs), which can archived by time or other dimension as it gets older, but also needs to be available at any time, and have a decent amount of queries or but also need to run massive scans, and don't need sub-second response times, and want really complex query logic, BQ wins.
Not having to manage a redshift cluster and just let BQ do all the work for us is worth the query time; even then, most of our complex queries run sub 30 seconds over hundreds of GB. If your data can be queried using a date-range then use date-partitioned tables. There is a 1000 table query limit so there are some pit-falls but BQ makes it easy for you to aggregate data into weekly and monthly tables to query against.
Disclosures:
1. I don't work at either.
2. My employer, however, partners with both.
Having said all this, my company is a fully-managed data pipeline that supports both data warehouses, and we find they both work extremely well for our customers real datasets.
Another option absolutely worth checking out is Snowflake, which compromises between the approach of BQ and Redshift in a very interesting way.
Well you can if that test matches the use-case of the database. Big Data databases are not like transnational databases, tend to perform column oriented analytics and tend to not join very well (they may lack indexes altogether).
I find this post to be more misleading than Google - he seems to be conceitedly ignoring the fact that many specialised databases exist that do certain things really well - and selecting tests that match their use-case is entirely appropriate.
Also I think you meant transactional not transnational
I understand what your purpose was, but the blog entry would have felt more balanced if you'd said something like:
It's true that for a small subset of queries, BigQuery out-performed RedShift. If your workload is similar to those queries then BigQuery may be a good match for you, but our testing shows that for general purpose datawarehousing / analytics workloads, RedShift is the better performing database and would be more suitable for most customers.
With BQ you pay for the amount of data that flows through the system, as opposed to how many instances are running with Redshift.
We also don't really need Redshift running all the time so something like BQ or Azure SQL Datawarehouse where you can pause and scale up/down fits our usage patterns and budget much better.
I will note I am enjoying reading these comments especially the ones that have real data points, real implementations and considerations of database structures/architectures on both of the products vs. gripes of the brands.
In order to run the benchmark they had to modify the SQL in it. I much prefer it when a benchmarking exercise is sufficiently transparent for another party to come along an replicate the result.
That implies that they should provide details on all SQL changes that they made. Some of the phrasing was a little petty - BigQuery in “Legacy SQL” mode supports this but then joins don’t really work - but being clear about what they had to change seems totally appropriate to me.
I think in term of EDW solutions, it is really not a one size fits all situation. I am happy for the people (especially small companies who can't afford running Redshift 24/7) who went with BigQuery, but I am also happy that we chose Redshift and so far, it has been great.
Well, it's impossible to know the context in which this comparison was made. BigQuery is very different from the rest of the data warehouse solutions, I wouldn't think a standard benchmark suite would be appropriate for it.
Is Redshift worth it? Yes, it's a powerful EDW system. That said, it's going to cost you more than BQ because of always-on nodes.
Is it better than BQ? It really depends on your analytics workload.
But I can't say the same about RedShift, it's a complete black box. (Other than that it's a mod of an ancient version of Postgres)
See point 9
http://www.exasol.com/en/newsroom/blog/10-questions-the-tpc-...
Note: Exasol has been dominating TPC-H for a while but it's kind of werid they don't publish much about what's under the hood (unlike Actian Vectorwise, for example, who has a huge library of amazing papers)
Because Vectorwise is Marcin Zukowski's brainchild and started with his PhD theses. He's an academic through and through, looks like!
Any chance you could sure a link? I couldn't find this on Google. Thanks!
I don't have a pony in this race since either one at large enough scale will cost an arm and a leg and both seem to be within same order of magnitude with a constant factor here and there.
http://www.zdnet.com/article/will-snowflake-spark-a-cloud-da...
if you do only occaisonal queries on mostly static data bigquery is probably fine but redshift is a fraction of the cost for more frequent use
Indeed it is, I wonder if that benchmark claim is being taken out of context of that private talk.