8 ms·
Show HN: A benchmark for analytical databases (Snowflake, Druid, Redshift)
I created a web page to compare different analytical databases (both self-managed and services, open-source and proprietary) on a realistic dataset.
It contains 20+ databases, each with installation and data loading scripts. And they can be compared to each other on a set of 43 queries, by data load time or by storage size.
There are switches to select different types of databases for comparison - for example, only MySQL compatible or PostgreSQL compatible.
If you play with the switches, many interesting details will be uncovered.
Full description: https://github.com/ClickHouse/ClickBench/blob/main/README.md https://github.com/ClickHouse/ClickBench/blob/main/README.md
- singhrac 4y agoA small suggestion: since the machines aren’t always comparable (eg Redshift) maybe it makes sense to allow the user to make a plot of ($/hr) vs query time? I realize this adds to the complexity, but most people in this space are making a trade off between (maintainability/ease-of-use, long-running costs, performance), and plotting the latter two would probably highlight the Pareto frontier.
- zX41ZdbW 4y agoYes, this is what I want to include - the cost metric. Although it will be tricky for systems with consumption-based pricing: BigQuery and Athena. And Snowflake has almost consumption based pricing - while you pay for warehouse time, it can quickly spin it up and down based on load. Maybe use some assumption like - the price for running queries on consumption based systems is equalized to one hour of run time of other systems...
- ruw1090 4y agoYea, Redshift looks like it was run on an ra3.xl (4 cores) and is comparing against clickhouse on a c6a.4xl (16 cores). I suspect if this were normalized Redshift would have an edge.
- zX41ZdbW 4y agoRedshift configuration was selected to get 16 cores in total, so it is 4 nodes of ra3.xplus 4vCPU to get 16vCPU in total. https://github.com/ClickHouse/ClickBench/tree/main/redshift https://github.com/ClickHouse/ClickBench/tree/main/redshift We can add more results on different configurations easily. There is also Redshift Serverless, which does not have any hardware configuration to tune.
- derN3rd 4y agoMy company recently started to invest more into analytics and we had to find a good solution on which backend/database we want to decide. After some research we settled on ClickHouse and we couldn't be happier. - Super easy to setup - Easy to backup - Fast configuration (documentation could be better at some parts) - Similar SQL dialect as our devs use in MySQL Only negative points I could find so far: - No 'good' management GUI as e.g. phpMyAdmin, pgAdmin, Mysql Workbench - Caching/Batching layers not directly implemented, but through external software As we are a fairly small company all other analytical databases would have cost us a large amount of money/time more. Friends of us recently hired a group of data engineers/analytics who also brought all their AWS knowledge and toolings with them, which basically brought them to the same outcome as us, while we only have 5% of their costs and all our devs are able to either ingest or query some data EDIT: Does anyone have some recommendations on what GUIs I could give our PMs to work with ClickHouse instead of writing queries? All SaaS I found didn't support ClickHouse yet or would cost us a newborn. Also what tools do your devs use when they work with ClickHouse data?
- blinkov 4y agoJust in case, here's built-in batching https://clickhouse.com/docs/en/operations/settings/settings/#async-insert https://clickhouse.com/docs/en/operations/settings/settings/... and a list of mature UIs https://clickhouse.com/docs/en/connect-a-ui https://clickhouse.com/docs/en/connect-a-ui (that all have SaaS offerings I believe) and all third-party UIs https://clickhouse.com/docs/en/interfaces/third-party/gui https://clickhouse.com/docs/en/interfaces/third-party/gui
- derN3rd 4y agoThanks! I remember trying the built-in batching but we had some trouble with it, so we just switched to <https://github.com/nikepan/clickhouse-bulk https://github.com/nikepan/clickhouse-bulk> which works without any issue since then. Will have a look at the UIs listed there
- gbrits 4y agoClickhouse support for Tableplus landed 2 months ago. https://github.com/TablePlus/TablePlus/issues/670 https://github.com/TablePlus/TablePlus/issues/670
- simonw 4y agoAnyone got a succinct explanation for why ClickHouse is doing so well in this kind of benchmark? What are the key tricks they're using to get great analytical performance, and why haven't those same tricks been adapted by other competing systems?
- zX41ZdbW 4y agoI see that other systems often forgot many small details. Imagine you are implementing COUNT(DISTINCT) function. And imagine it is used in GROUP BY something, like GROUP BY region_id. And on realistic datasets, you will have a large number of different region_id and something like power law distribution of their frequency: https://en.wikipedia.org/wiki/Zipf%27s_law https://en.wikipedia.org/wiki/Zipf%27s_law Then you will expect that most of the values of COUNT(DISTINCT) will be small. And the natural optimization will be - using a linear array before using a hash table. Or: place smaller hash tables in something like a memory pool. This is about a ton of optimizations on distributions of real datasets.
- drchaim 4y agoI would say deep optimizations in every aspect of the data cycle. From storing to querying data is heavily optimized. I recall an online meetup where a 10ms gain after a patch was huge celebrated. That kind of things compound over and over and make a really fast data platform for that use cases.
- mdns33 4y ago
- garciasn 4y agoCan BigQuery be added? I'm interested to know how it compares in these sorts of scenarios. We use BQ because I do NOT want to have to deal w/management of any of the infrastructure; paying by query is convenient for our small team. But if there is true value that can be realized by something like ClickHouse, I want to see how it compares.
- zX41ZdbW 4y agoI've added BigQuery and run the benchmark and get the results... but due to their ToS , the benchmark results cannot be published. See https://cube.dev/blog/dewitt-clause-or-can-you-benchmark-a-database https://cube.dev/blog/dewitt-clause-or-can-you-benchmark-a-d... The benchmark instructions for BigQuery are here: https://github.com/ClickHouse/ClickBench/tree/main/bigquery https://github.com/ClickHouse/ClickBench/tree/main/bigquery If you follow them, just add the .json with the results and regenerate the HTML with the generate-results.sh script. You will get the version of the benchmark page with BigQuery included - for your own use. I wish more vendors will get rid of DeWitt Clause sooner. For example, Snowflake has removed it a few months ago (maybe due to pressure from Databricks), SingleStore also removed the clause recently.
- garciasn 4y agoMuch appreciated.
- AdamProut 4y agoIt looks like the queries are all single table queries with group-bys and aggregates over a reasonably small data set (10s of GB)? I'm sure some real workloads look like this, but I don't think it's a very good test case to show the strengths/weaknesses of an analytical databases query processor or query optimizer (no joins, unions, window functions, complex query shapes ?). For example, if there were any queries with some complex joins Clickhouse would likely not do very well right now given its immature query optimizer (Clickhouse blogs always recommend denormalizing data into tables with many columns to avoid joins).
- zX41ZdbW 4y agoThere are many limitations of this benchmark, indeed: https://github.com/ClickHouse/ClickBench/#limitations https://github.com/ClickHouse/ClickBench/#limitations
- qoega 4y agoThere are several existing benchmarks that test query optimisers with a lot of joins. It does not show performance of query engine, but more likely how good is your optimiser was tailored for this queries.
- AdamProut 4y agoI think your missing my point. The page is entitled "a Benchmark For Analytical DBMS" not "A Benchmark for Single Table Query Execution". Most analytical workloads are more complex then single table queries. I didn't say it wasn't useful to test single table columnstore performance on workload that runs best on single host databases, just that this isn't the be-all end-all of Analytical Database performance testing.
- zX41ZdbW 4y agoYou are absolutely right. That's why this benchmark is named "a Benchmark For Analytical DBMS", not "the definitive benchmark for analytical DBMS".
- 4y ago
- arikfr 4y agoThis is great! Will it be possible to include Databricks in the benchmark?
- zX41ZdbW 4y agoDatabricks is in the list of systems I want to add: https://github.com/ClickHouse/ClickBench/#systems-included https://github.com/ClickHouse/ClickBench/#systems-included
- Lucasoato 4y agoYeah, also a comparison with SparkSQL on Kubernetes would be interesting! Let's get in touch, I may help with that.
- ruw1090 4y agoDo you have any benchmark results with Clickhouse in a clustered configuration (and other db's)? Is Clickhouse expected to be run in only single node deployments?
- zX41ZdbW 4y agoClickHouse is typically run on a cluster. There are setups with over 1000 machines and over 100 PB of data. But for this benchmark, the most simple setup is selected. I can add clustered setup for ClickHouse in this benchmark.
- kthejoker2 4y agoSeems like a great ad for c6a.metal ! Without dollars as part of the analysis, I don't find these comparisons useful.
- drchaim 4y agoit's curious there is no available c6a.metal today in eu-west-2, I would say last week there were available in this region.
- lk888 4y ago[dead]
- riku_iki 4y ago> Regarding the joined table queries that are missing in the tests looks like huge issue with this benchmark.
- gianm 4y agoThis is impressive work: it's time consuming to set up and benchmark so many different systems! Impressiveness of the effort notwithstanding, I also want to encourage people to do their own research. As a database author myself (I work on Apache Druid) I have really mixed feelings about publishing benchmarks. They're fun, especially when you win. But I always want to caution people not to put too much stock in them. We published one a few months ago showing Druid being faster than Clickhouse (https://imply.io/blog/druid-nails-cost-efficiency-challenge-against-clickhouse-and-rockset/ https://imply.io/blog/druid-nails-cost-efficiency-challenge-...) on a different workload, but we couldn't resist writing it in a tongue-in-cheek way that poked fun at the whole concept of published benchmarks. It just seems wrong to take them too seriously. I hope most readers took the closing message to heart: benchmarks are just one data point among many. That's why I appreciate the comment "All Benchmarks Are Liars" on the "limitations" section of this benchmark -- something we can agree on :)
- lk888 4y agoVery well said!
- thomoco 4y agoBenchmarks can be quite difficult to interpret when the test parameters vary between tests. However, I think the point in providing the open-source ClickBench benchmark [https://github.com/ClickHouse/ClickBench https://github.com/ClickHouse/ClickBench] is exactly to allow users to do their own research in providing a standardized client and workload across any SQL-based DBMS. Standardized benchmarking is an important technique, for comparing across different applications, or also for comparing the same application across different environments (compute, storage, cloud vs. self-managed, etc.). SPEC [https://www.spec.org https://www.spec.org] used to do a great job in developing and releasing standardized benchmarks, although their activity has waned of late
- AdamProut 4y agoAny idea why Druid performed so poorly though? 100x slower seems odd. I though druid was reasonably good at single table analytics like in this benchmark. Is it the small data size?
- throwawaylala1 4y agoWow Snowflake absolutely crushes these benchmarks. Anyone know why? I don't know much about this space.
- ruw1090 4y agoThe snowflake clusters are several factors bigger (and more expensive) than almost all of the comparison points.
- mdcallag 4y agoREADME.md was interesting to read. I enjoyed learning about the history of the effort and I appreciate how much work got it to this point. I enjoy both benchmarking and benchmarketing. Although benchmarketing is more fun when it isn't about the DBMS I am working on.