ClickHouse cost-efficiency in action: analyzing 500B rows on an Intel NUC
altinity.com
altinity.com
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 support it [2]. We just did this again for Apache Airflow [3].
[1]: https://github.com/DataDog/integrations-core/pull/4957
(interestingly, we have a nearly identical data ingestion/ETL stack running on spot instances and saving to parquet/s3)
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 turn and crashed to the wall at 70 mph, hence Java runs fast and Java doesn't use brakes were both described perfectly by the same sentence.
Yandex used the meme to advertise Clickhouse for engineers.
edit: formatting
edit2: brakes instead of breaks, wtf
I thought maybe it meant using break statements in your loops slows things down or something.
It was kind of a crummy use case for Scylla anyway (it’s a transactional write store, not an analytics engine)
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.
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 shit, that wasn't the task though.
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.
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 all. That original 1B rows/sec blog post by them is kind of a strange benchmark to begin with.
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?
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...
https://ark.intel.com/content/www/us/en/ark/products/93341/i...
Intel is using the Canyon word for their range of powerful NUCs (Skull Canyon, Hades Canyon, etc. and the upcoming Ghost Canyon).
https://clickhouse.yandex/docs/en/operations/settings/settin...
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.
[1] https://medium.com/@valyala/wal-usage-looks-broken-in-modern...
[2] https://medium.com/@valyala/how-victoriametrics-makes-instan...
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 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.
> 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.
OLAP also doesn't mean "be the source of truth of the data". You can have a separate source of truth of the "complete set of data" outside of your OLAP engine and load (and reload) data into your OLAP engine any time you're not sure if you have the "complete set of data" in it.
The important difference lies in how often one finds themselves in that situation. In OLAP, the sheer majority of the time is spent querying (i.e., reading) data than loading (i.e., writing) data and waiting for it to be durably saved (i.e., fsync-ed). Because of this imbalance, it makes sense to prioritise for one scenario and handle the other sub-optimally.
> They wouldn't care if some of that data is lost so they don't use fsync.
Or, they can still care about data correctness and simply re-load data they suspect is/may not consistent in the rare case of an improper shutdown. It's not like they use ClickHouse as their primary data store.
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
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.
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.
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.
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#L98https://groups.google.com/d/msg/clickhouse/cjJ6v8uzu0Q/jGV59...
> The reason is because CH does not use fsync (for performance)
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
{event: "viewedArticle", article_id: 63534, user_id: 42, topic: "news", time: "2020-01-06"}
I want to be able to build aggregations which shows number of "viewedArticle" events grouped by hour, grouped by topic, counting unique user_ids within each bucket.
Or let's say I want the top K articles viewed each day, filtered by a topic.
That's something that's trivial with Elasticsearch, which has a hierarchical aggregation DSL. Is ClickHouse good at this?
Whenever I see time-series databases such as InfluxDB mentioned, they look like they're focused on measurements, not discrete rows. You can attach the event data as "labels", but this isn't efficient when the cardinality of each column is very high (e.g. article IDs or user IDs in the above example).
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 where each table can have a separate 'engine' including some that automatically apply aggregations. Start with a normal table though since it'll be plenty fast enough for most use cases.
For example, a result like:
topic;time;count
news;2020-01-01;44
news;2020-01-02;31
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 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.
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.
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
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.
Does anyone know what format it stores it internally? Is there a way to simply have it query in-place?
Just to answer my own question - this looks good - I might have to try it out!
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.
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
Example: https://www.altinity.com/blog/2019/6/11/clickhouse-local-the...
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 most certainly does not. The UX is horrible to boot.
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 them into your tables.
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.
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.
> 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.
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.
I know some folks running one of the larger clickhouse instances out there... but this article made me trust the community less, not more.
The actual point here is that you can execute millions of (different!) individual queries per second on ScyllaDB, which beats any columnar datastore hands down. ClickHouse "cheated" here by translating the (unfortunate) benchmark setup into a single query that's extremely heavily optimized under the hood.
ScyllaDB is amazing when it comes to OLTP performance but not in the Analytical ones.
I think they took pretty mediocre Analytical Workload results and shared them as something outstanding.
GPU compute stacks are increasingly geared towards multi-gpu/multi-node & streaming, esp. given the crazy bandwidth they're now built for (2TB/s for a dgx2 node?). Likewise, per-GPU memory and per-GPU-node memory is going up nicely each year (16-24GB/GPU, and 100GB-512GB/node with TBs connected same-node). Network is more likely to become the bottleneck if you saturate that, not your DB :)
Though I like to do mostly single gpu streaming in practice b/c I like not having to think about multinode and they're pretty cheap now :)
Also, Snowflake is so painful to use and has a bunch of weird edge cases. It’s also so expensive.
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.
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.
The numbers on sticker are from a cluster of 400 servers and 200 servers were participated in query execution.