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

11–20 of 91 posts

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

#11

Post by PeterZaitsev »

chupasaurus wrote: post #8
jgrahamc wrote: post #2

We use ClickHouse extensively and it's been great: https://blog.cloudflare.com/http-analytics-for-6m-requests-p...

You've lost a Russian IT meme in translation. We use the verb " to use brakes " to describe that something works slowly. There was a long story of threads about Java performance, until the meme was solidified after the news from 2005 DARPA Grand Challenge (racing competition for autonomous robotic self-driven cars): a car named Tommy by Jefferson Team which was running Java under Linux haven't used breaks before a tu…

I think this is poor translation. In Russian "Тормозить" may mean to "use brakes" when applied to a car or just "be slow" when applied to a program (or a person). "MySQL сегодня тормозит" would mean MySQL is acting slow today, not what it is using brakes. So that meme I think is best translated as "Clickhouse is never slow" or "Clickhouse never acts slow"

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

#12

Post by chupasaurus »

nkrisc wrote: post #10
chupasaurus wrote: post #8

Earlier quoted context omitted.

You've lost a Russian IT meme in translation. We use the verb " to use brakes " to describe that something works slowly. There was a long story of threads about Java performance, until the meme was solidified after the news from 2005 DARPA Grand Challenge (racing competition for autonomous robotic self-driven cars): a car named Tommy by Jefferson Team which was running Java under Linux haven't used breaks before a tu…

I think you mean "brakes," as I thought you meant "breaks" like in an iterator. Not that familiar with Java though, just assuming based on the reference to a vehicle failing to brake before a turn. I thought maybe it meant using break statements in your loops slows things down or something.

Yeah, my bad.

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

#14

Post by hodgesrm »

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.

Mat views are great as the article showed. I use them to get query response down to milliseconds, as they vastly reduce the amount of data ClickHouse must scan.

That said, there are a lot of other tools: column storage, vectorwise query, efficient compression including column codecs, and skip indexes to name a few. If you only have a few billion rows it's still possible to get sub-second query results using brute force scans.

Disclaimer: I work for Altinity, who wrote this article.

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

#15

Post by patelh »

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.

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

#16

Post by sheeshkebab »

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.

I bet results would be roughly the same even for the exact same dataset - Scylla and other K/v data stores can’t compete with columnar databases that are purpose built for complex analytics queries. the many orders of magnitude query performance differences (not to count storage, compute overhead) show it enough.

It was kind of a crummy use case for Scylla anyway (it’s a transactional write store, not an analytics engine)

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

#17

Post by hodgesrm »

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 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 essentially the same no matter how you generate and store data because the materialized view down-samples temperature max/min to daily aggregates. The data are vastly smaller no matter how you generate them.

The article illustrates that if you really had such an IoT app and designed it properly you could run analytics with surprisingly few resources. I think that's a significant point.

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

#18

Post by hodgesrm »

hodgesrm wrote: post #14
SloopJon wrote: post #7

Earlier quoted context omitted.

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.

Mat views are great as the article showed. I use them to get query response down to milliseconds, as they vastly reduce the amount of data ClickHouse must scan. That said, there are a lot of other tools: column storage, vectorwise query, efficient compression including column codecs, and skip indexes to name a few. If you only have a few billion rows it's still possible to get sub-second query results using brute for…

p.s. Loading the view is low-cost compared to loading the source data. On the NUC it's 40-60 minutes, so worst case it's something like 1h / 17.5h = 5.71%. Also, you can still query the source data. That is fast for individual sensors as the examples showed.

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

#19

Post by patelh »

hodgesrm wrote: post #17
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 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.
Post reply on HN