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

41–50 of 91 posts

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

#41

Post by manigandham »

atombender wrote: post #39
manigandham wrote: post #34

Earlier quoted context omitted.

Yes. Clickhouse is a column-oriented relational database among many others like MemSQL, Vertica, Redshift, BigQuery, Snowflake, Greenplum, etc. They're all focused on analytical queries over very large datasets using SQL. An aggregation with several `group by` statements is no challenge and all of these databases also support approximate counting via HyperLogLog for faster results. Clickhouse has some unique features…

Thanks! Looks like the only downside is that, as it returns rows as results, you end up getting a lot of duplicate column data back and need to "nest" the nested buckets yourself. For example, a result like: topic;time;count news;2020-01-01;44 news;2020-01-02;31 Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate throu…

Right, relational databases only return flat tabular results but that seems minor compared to performance increase you gain.

Clickhouse is fast but not as operationally friendly as the others. It's more much work once you go beyond a single node so I'd suggest looking at those other options if you want something easier to operate, or use one of the cloud data warehouses like Bigquery or Snowflake to eliminate ops entirely.

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

#42

Post by caust1c »

codexon wrote: post #25
caust1c wrote: post #21

Earlier quoted context omitted.

It's pretty clearly laid out in the docs. Hopefully anyone seriously considering using Clickhouse reads the docs thoroughly and understands what they're implementing.

What do you mean clearly laid out? This is the only mention of fsync I could find through google or their own search function. https://clickhouse.yandex/docs/en/operations/settings/settin...

The title of the page might be a little snarky, but it's in the introduction that transactional queries are not supported:

https://clickhouse.yandex/docs/en/introduction/features_cons...

Sure it's not specifically about `fsync` but presumably this is what the consumer of the database actually wants to know.

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

#43

Post by caust1c »

FridgeSeal wrote: post #30
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’m not understanding the implication of this, could you explain?

The implication is that clickhouse can't easily support transactional queries. That's why it's an OLAP not OLTP database. (On-Line Analytics Processing vs On-Line Transaction Processing).

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

#44

Post by DasIch »

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/#fsync-metadata
  [2]: https://github.com/ClickHouse/ClickHouse/search?q=fsync&type=Issues
  [3]: https://github.com/ClickHouse/ClickHouse/blob/355b1e5594119e036a2d62988bfa42bc8b1a1687/dbms/src/IO/WriteBufferFromFileDescriptor.cpp#L113
  [4]: https://github.com/ClickHouse/ClickHouse/blob/e765733a26cfc4cecc13c981686560338256a6b1/dbms/src/IO/WriteBufferAIO.cpp#L98

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

#45

Post by DasIch »

FridgeSeal wrote: post #30
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’m not understanding the implication of this, could you explain?

When you write to a file, you generally don't write to physical storage. Instead the writes get buffered in memory and written to physical storage in batches. This substantially improves performance but creates a risk: If there is some sort of outage before the data is flushed to disk, you might lose data.

In order to address that risk, you can explicitly force data to be written to disk by calling fsync. Databases generally do this to ensure durability and only signal success after fsync succeeded and the data is safely stored.

So ClickHouse not calling fsync implies that it might lose data in case of a power outage or a similar event.

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

#46

Post by gautamcgoel »

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.

Yes, I have the same model. I think I bought it for between $650 and $750.

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

#47

Post by tgtweak »

ofek wrote: post #26

Hey, Ofek from Datadog here! I recently implemented our ClickHouse integration [1], so if any of you would like to try it out we would appreciate feedback. I really enjoyed learning about this database, and it has excellent docs :) Oh fun fact, speaking of docs, this was the first integration of ours that we scrape docs for as part of the test suite. So when a new built-in metric is added it will fail our CI until we…

So at datadog, after aggregation with spark and storage into parquet, what is used for serving queries of all the datadog aggregated telemetry data (logs, apm and infra telemetry) to the consumers?

(interestingly, we have a nearly identical data ingestion/ETL stack running on spot instances and saving to parquet/s3)

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

#48

Post by hodgesrm »

atombender wrote: post #39
manigandham wrote: post #34

Earlier quoted context omitted.

Yes. Clickhouse is a column-oriented relational database among many others like MemSQL, Vertica, Redshift, BigQuery, Snowflake, Greenplum, etc. They're all focused on analytical queries over very large datasets using SQL. An aggregation with several `group by` statements is no challenge and all of these databases also support approximate counting via HyperLogLog for faster results. Clickhouse has some unique features…

Thanks! Looks like the only downside is that, as it returns rows as results, you end up getting a lot of duplicate column data back and need to "nest" the nested buckets yourself. For example, a result like: topic;time;count news;2020-01-01;44 news;2020-01-02;31 Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate throu…

> Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate through the flattened results and generate nested structures. This is something Elasticsearch gives you out of the box.

ClickHouse has a number of optimization for solving 'visitor' problems that you describe. Assuming you just want to group in different ways an idiomatic ClickHouse solution is to construct a materialized view that aggregates counts (e.g., of unique users like uniq(user)). You can then select from the materialized view and further aggregate to have larger buckets. ClickHouse can also compute single-level totals using the WITH TOTALS modifier.

If you need to have cascading sub-totals within the same listing as far as I know you'll have to compute the totals yourself. (That feature actually might be an interesting pull request since ClickHouse generates JSON output.)

> Last I looked at Clickhouse, it had master/slave replication only, and if you want shards of data distributed across a cluster it's something you need to manually manage?

ClickHouse replication is multi-master. The model is eventually consistent. Also, ClickHouse can automatically shard INSERTs across a cluster using distributed tables. That said, many users insert directly to local nodes because it's faster and uses fewer resources.

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

#49

Post by ofek »

tgtweak wrote: post #47
ofek wrote: post #26

Hey, Ofek from Datadog here! I recently implemented our ClickHouse integration [1], so if any of you would like to try it out we would appreciate feedback. I really enjoyed learning about this database, and it has excellent docs :) Oh fun fact, speaking of docs, this was the first integration of ours that we scrape docs for as part of the test suite. So when a new built-in metric is added it will fail our CI until we…

So at datadog, after aggregation with spark and storage into parquet, what is used for serving queries of all the datadog aggregated telemetry data (logs, apm and infra telemetry) to the consumers? (interestingly, we have a nearly identical data ingestion/ETL stack running on spot instances and saving to parquet/s3)

Hi there! We actually talked quite a bit about that in a recent podcast https://www.dataengineeringpodcast.com/datadog-timeseries-da...

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

#50

Post by atombender »

hodgesrm wrote: post #48
atombender wrote: post #39

Earlier quoted context omitted.

Thanks! Looks like the only downside is that, as it returns rows as results, you end up getting a lot of duplicate column data back and need to "nest" the nested buckets yourself. For example, a result like: topic;time;count news;2020-01-01;44 news;2020-01-02;31 Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate throu…

> Now you have "news" repeated, and to group this into buckets for rendering summary tables and such (with sub totals at each level), you need to iterate through the flattened results and generate nested structures. This is something Elasticsearch gives you out of the box. ClickHouse has a number of optimization for solving 'visitor' problems that you describe. Assuming you just want to group in different ways an idi…

Sounds great, thank you!
Post reply on HN