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

51–60 of 91 posts

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

#51

Post by imtringued »

kresten wrote: post #3

What’s the tldr on why it is fast?

Because it front loaded all operations so that they happen outside of the benchmark. Depending on what you want to do it makes sense but the original intention of the benchmark was a brute force query benchmark.

Have you seen the sticker on the NUC? 116 billion rows per second at 233.61GB/s. If you spend even a single second thinking about how absurd that number is you would start to see that the two benchmarks measure completely different things. Even with a quad channel Xeon CPU you won't see significantly more than 100GB/s memory bandwidth. Those 116 billion queries didn't actually happen. It's just a synthetic number. The result of the query was calculated during insertion of the temperature record before the benchmark has even started and then they just calculated the theoretical number of queries you would have to do for an equivalent result and slapped that fictional number on their NUC.

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

#52

Post by FridgeSeal »

manigandham wrote: post #35
FridgeSeal wrote: post #32

Earlier quoted context omitted.

For what it’s worth, I’ve used Clickhouse and Snowflake and I strongly prefer Clickhouse. Performance was superior, client libraries and built-in HTTP interface was a god-send, it supported geospatial queries. I had perpetual issues with getting Snowflake to properly escape strings in CSV, handle JSON in anything approaching a sensible way, there’s claims that it integrates properly with Kafka as a consumer, but it m…

What were the issues with JSON? Snowflake is the best cloud data warehouse when it comes to support for unstructured data and far better than Redshift, Bigquery or the others. Snowflake also has geo support. It doesn't support streaming data though so things like Kafka aren't a good fit yet. They have a connector but it's basically a little app that automates consuming from Kafka, writing files to S3, then loading th…

Getting it to export JSON was needlessly complicated, those Variant type columns are the worst.

Does it have geospatial support now? Because they definitely didn’t have it when I used it.

Ultimately though, I don’t see enough benefits of SF over CH: feature set overlaps but with no real winner features. observed performance was worse, the client libraries are few and far between and not great, and no HTTP interface, and no real streaming support. On top of being hideously expensive, and closed source.

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

#53

Post by FridgeSeal »

caust1c wrote: post #43
FridgeSeal wrote: post #30

Earlier quoted context omitted.

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).

Oh that’s not too bad, they’re very explicit about not having transaction support, thanks for explaining.

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

#54

Post by miffy900 »

johnchristopher wrote: post #28

Note: not your standard $250 NUC, it's a canyon something with much more oomphs.

It does have more oomph than most NUC's, but it's not a Canyon-something NUC; as stated in the article, the CPU model is Intel i7-6770HQ, which was released in Q1 2016 on Intel's 14nm node, so it's Skylake.

https://ark.intel.com/content/www/us/en/ark/products/93341/i...

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

#55

Post by hodgesrm »

imtringued wrote: post #51
kresten wrote: post #3

What’s the tldr on why it is fast?

Because it front loaded all operations so that they happen outside of the benchmark. Depending on what you want to do it makes sense but the original intention of the benchmark was a brute force query benchmark. Have you seen the sticker on the NUC? 116 billion rows per second at 233.61GB/s. If you spend even a single second thinking about how absurd that number is you would start to see that the two benchmarks measu…

That's a sticker from a ClickHouse community event, not related to the benchmark. We tend to stick them on anything flat. My ancient Dell XPS-13 has one. It's definitely not that fast.

That said, the sticker is from a real performance test. I assume it was a cluster but don't have details. ClickHouse query performance is outstanding--it's not hard to scan billions of rows per second on relatively modest hosts. These are brute force queries on source data, no optimization using materialized views or indexes.

For instance, I have an Amazon md5.2xlarge with 8 vcpus, 32 GB of RAM, and EBS GP2 storage rated at 100 iops. I can compute average passengers on the benchmark NYC taxi cab dataset [1] in .551 seconds using direct I/O. The throughput is 2.37B rows/sec.

ClickHouse is so fast on raw scans that many production users don't even use materialized views. I mostly use them to get responses down to small numbers of milliseconds for demos.

[1] https://tech.marksblogg.com/benchmarks.html

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

#56

Post by somurzakov »

DasIch wrote: post #45
FridgeSeal wrote: post #30

Earlier quoted context omitted.

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 g…

is this really that important, thought, since all servers feed power from uninterruptible power supply and most data centers have multiple power sources.

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

#57

Post by manigandham »

FridgeSeal wrote: post #52
manigandham wrote: post #35

Earlier quoted context omitted.

What were the issues with JSON? Snowflake is the best cloud data warehouse when it comes to support for unstructured data and far better than Redshift, Bigquery or the others. Snowflake also has geo support. It doesn't support streaming data though so things like Kafka aren't a good fit yet. They have a connector but it's basically a little app that automates consuming from Kafka, writing files to S3, then loading th…

Getting it to export JSON was needlessly complicated, those Variant type columns are the worst. Does it have geospatial support now? Because they definitely didn’t have it when I used it. Ultimately though, I don’t see enough benefits of SF over CH: feature set overlaps but with no real winner features. observed performance was worse, the client libraries are few and far between and not great, and no HTTP interface,…

Yes it has geospatial support. Variant columns are better than every other database so far. Redshift and Bigquery just have a text field and require far more verbose commands to operate and cast. It sounds like most of your issues are with importing and exporting data rather than querying it?

Snowflake is basically EC2 servers reading files from S3 so you get more bandwidth with a larger warehouse size but it's fundamental limit and will have much higher latency compared to running on a local SSD with clickhouse. Lack of streaming is a known problem. They actually do have an HTTP interface, you just don't see it but that's how all the ODBC drivers are implemented (with HTTP calls and JSON data in the background).

If your data fits or you don't mind the operational overhead of running your own data warehouse then it's almost always a cheaper and faster option.

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

#58

Post by codexon »

caust1c wrote: post #42
codexon wrote: post #25

Earlier quoted context omitted.

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.

Fsync is not synonymous with transactions. Not using fsync anywhere means there's a wide window that can be over 10 minutes long when data can be lost when a server gets an unplanned shutdown.

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

#59

Post by [deleted] »

FridgeSeal wrote: post #53
caust1c wrote: post #43

Earlier quoted context omitted.

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).

Oh that’s not too bad, they’re very explicit about not having transaction support, thanks for explaining.

[deleted]

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

#60

Post by codexon »

caust1c wrote: post #43
FridgeSeal wrote: post #30

Earlier quoted context omitted.

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).

This is not the implication at all.

Clickhouse can easily add fsync, they just choose not to do it.

Mongodb also did not use fsync and was ridiculed for it, yet no one mentions this about clickhouse.

Post reply on HN