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

81–90 of 91 posts

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

#81

Post by thekozmo »

ClickHouse achieved good (great) results. However, it's a bad comparison. Clickhouse is an analytics DB while Scylla is a realtime, random access one. /me am Scylla co-founder.

We could stack 100k rows in a single partition and be 1000x faster in this use case than the performance we demonstrate but we wanted to keep it real. Actually the use case we wanted to show is a single row per partition which would require more machines but surprisingly we couldn't provision that many on AWS.

The presented usecase by ClickHouse is 100x slower on writes (8M row/s) as they report. It doesn't matter since it's just a completely different use case. Use Clickhouse for analytics (I wonder why stop in SSE and not to go all the way to the GPU like SqreamDB) and use Scylla for OLTP

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

#82

Post by [deleted] »

pritambaral wrote: post #72
codexon wrote: post #60

Earlier quoted context omitted.

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

[deleted]

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

#83

Post by DasIch »

somurzakov wrote: post #56
DasIch wrote: post #45

Earlier quoted context omitted.

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.

It’s a significant deviation from what I would expect from a disk oriented database. So I would definitely expect it to be well documented, along with the reason for it, why the developers believe it is a reasonable (or even safe) choice, what assumptions went into that (such as availability of uninterruptible power supply) etc.

Additionally keep in mind that with EBS most people probably use network attached storage and fsync involves the network. Outage doesn’t just mean power outage, it could also be a network issue.

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

#84

Post by codexon »

pritambaral wrote: post #72
codexon wrote: post #60

Earlier quoted context omitted.

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

I can't find any evidence showing that OLAP means it is okay to lose data from unexpected shutdowns. How can you have correct analytics without a complete set of data?

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

It is a simple matter though. They can choose to sacrifice performance for data durability which I suspect would not be impacted very much since clickhouse acts like an append log. It just seems that Yandex doesn't care much for durability since they are just using the database to store people's web traffic. They wouldn't care if some of that data is lost so they don't use fsync.

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

#85

Post by valyala »

VictoriaMetrics core developer here.

The performance numbers from ClickHouse running on Intel NUC are impressive! We are going to publish VictoriaMetrics performance numbers for the original Billy benchmark from ScyllaDB [1] running on the same hardware from packet.com . Initial results are quite promising [2], [3].

[1] https://www.scylladb.com/2019/12/12/how-scylla-scaled-to-one...

[2] https://mobile.twitter.com/MetricsVictoria/status/1209116702...

[3] https://mobile.twitter.com/MetricsVictoria/status/1209186575...

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

#86

Post by valyala »

codexon wrote: post #58
caust1c wrote: post #42

Earlier quoted context omitted.

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.

Btw, it should be easy adding fsync to ClickHouse. For instance VictoriaMetrics uses similar file format as ClickHouse, and it issues proper fsyncs at least every second, so it may lose only the last second of data on unclean shutdown such as OOM, hardware reset or `kill -9`. [1], [2].

[1] https://medium.com/@valyala/wal-usage-looks-broken-in-modern...

[2] https://medium.com/@valyala/how-victoriametrics-makes-instan...

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

#87

Post by jayleeg »

thekozmo wrote: post #81

ClickHouse achieved good (great) results. However, it's a bad comparison. Clickhouse is an analytics DB while Scylla is a realtime, random access one. /me am Scylla co-founder. We could stack 100k rows in a single partition and be 1000x faster in this use case than the performance we demonstrate but we wanted to keep it real. Actually the use case we wanted to show is a single row per partition which would require mo…

The ScyllaDB one is a bit funny anyway as it doesn't really target analytical workloads. On SSE/GPUs - the ClickHouse guys don't use GPUs today (GPUs are on the roadmap for next year) as their workloads target volumes greater than GPU memory. If your hot dataset sits totally in GPU memory then it makes sense for some things otherwise they found the cost/performance ratio doesn't add up after you paginate in/out. I don't doubt GPU based DB perf numbers but cost is the main factor.

Now just to clarify - you're saying Scylla writes are 100x faster on the same hardware as ClickHouse (so 800M row/s on a NUC). Using the same code that Altinity used I manage around 25M rows/s on my home PC (8 cores/16HT) and elsewhere in this thread the guys from VictoriaMetrics pulled in 53M rows/s on a single node with 28 cores/56 threads (probably doable with ClickHouse on similar hardware I'd suspect).

I'm going to test this with Scylla on my home PC to validate your 800M row/s claim and I'll post about it - I should be able to hit around 2.5 billion rows/s with Scylla if what you've said is true. I've had CH write 300M row/s on my 8 core box using memory buffered tables but that was only at burst.

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

#88

Post by hodgesrm »

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…

Most ClickHouse installations run replication for availability and read scaling. If you do get corrupted data for some reason, you can read it back from another replica. That's much more efficient than trying to fsync transactions, especially on HDD. The performance penalty for fsyncs can be substantial and most users seem to be pleased with the trade-off to get more speed.

This would obviously be a poor trade-off for handling financial transactions or storing complex objects that depend on referential integrity to function correctly. But people don't use ClickHouse to solve those problems. It's mostly append-only datasets for analytic applications.

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

#89

Post by manigandham »

astral303 wrote: post #80
manigandham wrote: post #37

Earlier quoted context omitted.

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?

Plenty of OLTP scenarios that need distributed scale and/or high availability of C* - we use it for user profiles/session storage, counters and some high-volume logging that needs access to individual events.

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

#90

Post by xs83 »

jayleeg wrote: post #73
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…

I would like to see comparisons between CH files, I would specifically challenge the compressability of them vs ORC which pretty much maxes out current compression techniques.

As soon as I see CH format being widespread enough to interact with the multitude of other tools that are available then I would consider getting on board - for now a "loadable" data warehouse does little for the kind of workflows we deal with as the loading would take longer than the processing.

With regards to item two - we use a standard consumer GPU (1060 GTX) to handle the conversion from CSV to ORC / Parquet and it is much much faster and cheaper than a 20+ node spark cluster - hence the preference to work on files.

As everything else runs off these files it is kind of integral to our workload

Post reply on HN