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

71–80 of 91 posts

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

#71

Post by johnchristopher »

miffy900 wrote: post #54
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...

No, it is a Skull Canyon NUC from 2016 (with an i7-6770HQ), see https://www.techradar.com/reviews/pc-mac/pc-mac-desktops/int... for a description (I think this https://www.intel.com/content/www/us/en/products/boards-kits... is the Intel product page).

Intel is using the Canyon word for their range of powerful NUCs (Skull Canyon, Hades Canyon, etc. and the upcoming Ghost Canyon).

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

#72

Post by pritambaral »

codexon wrote: post #60
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).

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.

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

MongoDB claimed to be a replacement for RDBMS-es (which includes OLTP). ClickHouse is explicit about being OLAP-only. MongoDB also hid the fact that they weren't doing fsync, especially when showing off "benchmarks" against OLTP RDBMS-es, while ClickHouse has not tried to show themselves as a replacement for OLTP RDBMS-es.

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

For good reason. It's not a simple matter of choosing one of two options. The choice has consequences: performance.

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

#73

Post by jayleeg »

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?

I've worked with MPP DBs, Hadoop, Spark, ElasticSearch, Druid, kdb, DolphinDB and now ClickHouse and performance wise it's all true - in our case ClickHouse was 10-20x faster than Spark and used 4x less memory. I've seen it outperform the fastest commercial timeseries stores by 2x.

This will make me unpopular but my conclusion is that the file based data lake, splitting data from compute, is not the right approach in many (not all) cases and that Spark was not really that revolutionary. I would go as far to say that the direction data has taken has been a failure and ClickHouse and such come closer to solving the real problem of 'BigData'.

So two things here about 'loading'...

1) ClickHouse table/data files are completely portable (like Parquet) and can be moved from one server to another, copied or cloned etc.. there is even a mechanism to allow remote execution or to pull just the files from a remote server or an S3 store etc.. Just because the CH native file format isn't spoken about in the same circles as Parquet and ORC doesn't mean it can't be treated the same way if thats your thing. The CH native format is far more performant/compressible than Parquet or ORC and the specification is Open Source. Someone could implement a CH native file format serdes for Hive for example.

2) In this instance they were generating the data so no different to running Spark and writing to a Parquet file and running analytics on it later. Spark can't write / generate this amount of data in this amount of time on these resources and write out / compress the data to Parquet or whatever other preferred format. I've tried.

ClickHouse isn't perfect and I'm not affiliated with the Altinity guys but I can tell you this is the real deal.

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

#74

Post by jayleeg »

delusional wrote: post #24
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…

That's what you wanted to show, but what you ended up showing is that if you have different data, then the query performance can be quite good. I get the desire to critique the temperature profile, but completely changing it makes the comparison worthless. From a data perspective it's like saying "if all the sensors just report 1 for temperature every reading, computing the min, max, and average is super fast". No sh…

But they didn't set the temperature reading to anything that would advantage their tests. Without access to the original data they simply generated a dataset as close to the original dataset and volume as possible. The fact they took a few sentences talking about the temperature doesn't equate to invalidating the test.

Looking at this your way - Scylla used an INT, Altinity used a Decimal type with specialized compression (T64). I can tell you that this would have hampered ClickHouse and advantaged Scylla. It's the opposite of what you're saying. They actually performed this benchmark with one arm tied behind their back.

It's a funny benchmark anyway because the two systems have very different use cases but it doesn't invalidate the result.

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

#75

Post by jayleeg »

codexon wrote: post #60
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).

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.

To add to pritambaral comments.

The top commercial high performance timeseries databases, which ClickHouse can usually best, used by banks to make decisions on your money also don't use fsync. You can literally quit the software and watch your transaction data be written out 5 seconds later.

Edit: a word

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

#76

Post by pachicodev »

We have been using ClickHouse in production for some months already and we find it a real game changer. We're running queries over 3B rows for business intelligence purposes.

As a side project, a group of friends and I are working on a simple web analytics project powered by ClickHouse (what it's been originally build for). If anyone wants to contribute, just let me know.

Cheers

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

#77

Post by zX41ZdbW »

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?

You can also use ClickHouse to query files directly - with clickhouse-local tool.

Example: https://www.altinity.com/blog/2019/6/11/clickhouse-local-the...

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

#78

Post by zX41ZdbW »

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…

> 116 billion rows per second at 233.61GB/s

The numbers on sticker are from a cluster of 400 servers and 200 servers were participated in query execution.

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

#80

Post by astral303 »

manigandham wrote: post #37
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.

It doesn't matter. ScyllaDB is a Cassandra clone, an advanced nested key/value database that stores data per-row and requires slow iteration to scan through an entire table. Column-oriented databases will always be much faster at analytical queries because of the difference in physical layout and vectorized processing. Scylla's has very impressive OLTP performance but really shouldn't be compared to OLAP databases at…

Problem is what use cases are strictly OLTP? At this point, I’d consider Scylla/C* to usable for a write-only workload with single-row lookups, or a single-column range lookup.

Same question has to be raised: do you have enough rows to justify a distributed Scylla/C* or could you have used MySQL or Postgres on a giant box?

Post reply on HN