Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

altinity.com

Post reply on HN
Print view View original

61–70 of 91 posts

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#61

Post by codexon »

DasIch wrote: post #44
codexon wrote: post #20

One thing I haven't seen anyone note about clickhouse though which would be really important to many for data durability, is that it does not use fsync anywhere at all.

I can't find anything about this in the docs except[1]. I also can't find any issues in their bug tracker related to clickhouse not using fsync[2]. I can however find code that actually calls fsync[3][4]. To be fair I haven't read enough to determine how this (doesn't) affect durability. Nevertheless I'm wondering do you have a source for this claim? [1]: https://clickhouse.yandex/docs/en/operations/settings/settings…

As I mentioned, there's only 1 place where it says anything about fsync, and in that page, it says that is only for creating .sql files.

https://groups.google.com/d/msg/clickhouse/cjJ6v8uzu0Q/jGV59...

> The reason is because CH does not use fsync (for performance)

https://www.linkedin.com/in/dzhuravlev/

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#63

Post by tumanian »

bdcravens wrote: post #36
brobinson wrote: post #13

Earlier quoted context omitted.

Yeah, this post could have been titled "Why Materialized Views are Awesome"

Clickhouse is also crazy fast without materialized views - I've only done some PoC's against it, but in loading a largish data set of raw invoice CSVs, I was very impressed with the performance compared to our standard RDBMS.

that sounds like a non-canonical use of clickhouse. Wouldnt a good RDBMS be a better fit for invoice data? This is on the surface, of course, really interested in what is this invoice data like, and what queries are you trying to run on them.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#64

Post by xs83 »

I love stuff like this but I am massively put off by any database system that I need to "Load" into. There are so many amazing file formats now that can be queried directly as files - a 17H load time simply isnt feasible for much of this work.

Does anyone know what format it stores it internally? Is there a way to simply have it query in-place?

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#65

Post by xs83 »

xs83 wrote: post #64

I love stuff like this but I am massively put off by any database system that I need to "Load" into. There are so many amazing file formats now that can be queried directly as files - a 17H load time simply isnt feasible for much of this work. Does anyone know what format it stores it internally? Is there a way to simply have it query in-place?

https://github.com/ClickHouse/ClickHouse/pull/8430

Just to answer my own question - this looks good - I might have to try it out!

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#66

Post by shaklee3 »

patelh wrote: post #15

Not exactly a good comparison if you don't generate the data the same way for the test setup. Your generated data is more compressible by clickhouse, that skews the comparison. Would have been better to not change the test data if you wanted to do a comparison.

The compression is a property of the table and done on the fly, transparently to the user. If the difference was compressing/decompressing as part of the user task, I'd agree. But this is something that comes for free by a few extra characters in the schema.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#67

Post by subhajeet2107 »

We use Clickhouse extensive at work and boy is it better than anything i have used in column oriented databases so far, documentation is good, http query interface and features such as builtin url parsing are amazing, we also tested Druid and found Clickhouse to be better than Druid, it is easier to setup and maintain as well

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#68

Post by lmeyerov »

SloopJon wrote: post #7
kresten wrote: post #3

What’s the tldr on why it is fast?

I'd say the materialized view is the main thing: > Thus, we add the following materialized view ... At the end we should have 1440 times fewer rows in the aggregate than the source table. The cost of populating that view is amortized over the 17.5 hours it took to load the data.

Yeah I was confused, where I couldn't tell what was precomputed stats (col min/max/count), view calcs, and what's actual perf -- even legacy SQL vendors do all those. That's apples/oranges, more of a statement against the other db vs for clickhouse. Likewise, the db comparison I'd like to see if _other_columnar_stores_.

I know some folks running one of the larger clickhouse instances out there... but this article made me trust the community less, not more.

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#69

Post by lmeyerov »

PeterZaitsev wrote: post #29
endymi0n wrote: post #27

Earlier quoted context omitted.

I'd argue on ClickHouse not even being that fast (compared to comparable technology like Snowflake, Redshift or BigQuery) but actually the ScyllaDB example being completely misleading. Scylla is probably one of the fastest OLTP datastores, yet they're benchmarking an analytics query — which is pretty easy to crack by any columnar datastore. The actual point here is that you can execute millions of (different!) indivi…

Actually while ClickHouse does not have all features of RedShift, BigQuery etc it usually is much faster than them. It can be slower on some workloads on GPU powered systems, when all data fits in GPU memory but it is not the use case it targets. ScyllaDB is amazing when it comes to OLTP performance but not in the Analytical ones. I think they took pretty mediocre Analytical Workload results and shared them as someth…

The restriction to a tiny GPU workload is increasingly wrong for assessments.

GPU compute stacks are increasingly geared towards multi-gpu/multi-node & streaming, esp. given the crazy bandwidth they're now built for (2TB/s for a dgx2 node?). Likewise, per-GPU memory and per-GPU-node memory is going up nicely each year (16-24GB/GPU, and 100GB-512GB/node with TBs connected same-node). Network is more likely to become the bottleneck if you saturate that, not your DB :)

Though I like to do mostly single gpu streaming in practice b/c I like not having to think about multinode and they're pretty cheap now :)

Re: ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC

#70

Post by manigandham »

patelh wrote: post #19
hodgesrm wrote: post #17

Earlier quoted context omitted.

The important difference is that we used a more realistic temperature profile, which as you say does affect compression for that column . Schema design (including sort order, compression, and codecs) for the remaining columns is just good ClickHouse practice. Much of the storage and I/O savings is in the date, time, and sensor_id and columns. It's also useful to note that the materialized view results would be essent…

Then you should provide results for both test datasets to make the point of using a more realistic approach. Materialized views are not news, nor is properly designed analytics applications. For me the importance is how click house is better and why.

A column-store will be magnitudes faster at analytical queries than any rowstore system. This is fundamental architecture and the data used makes little to no difference. You could use the exact ScyllaDB dataset duplicated to trillions of rows and still arrive at the same relative performance figures.
Post reply on HN