Hacker News Reader

Comment by lykr0n | Hacker News Reader

Have you looked at Clickhouse for timeseries data? It's the one database I've found that can scale and can query in near-realtime.

I've loaded a 100 Billion Rows in into a 5 shard database and can do full queries across the whole dataset in under 10 seconds. It also natively consumes multiple kafka topics.

View on HN

Specialized tools are specialized: just remember the limitations! - Bad with heterogeneous hardware (Cloudflare experience) - Non-throttled recovery (source replicas flooded with replication load) - No real delete/update support, and no transactions - No secondary keys - Own protocol (no MySQL protocol support) - Limited SQL support, and the joins implementation is different. If you are migrating from MySQL or Spark, you will probably have to re-write all queries with joins. - No window functions

ClickHouse isn't general purpose DBMS. It is the best tool for collecting and near-online analyzing huge amount of events with many properties. We were successfully using ClickHouse cluster with 10 shards for collecting up to 3M events per second with 50 properties each (properties translate to columns). Each shard was running on n1-highmem-16 instance in Google Cloud. The cluster was able to scan tens of billions of rows per second for our queries. The scan performance was 100x better than on the previous highly tuned system built on PostgreSQL.

ClickHouse may be used as a timeseries backend, but currently it has a few drawbacks comparing to specialized solutions: - It has no efficient inverted index for fast metrics lookup by a set of label matchers. - It doesn't support delta coding yet - https://github.com/yandex/ClickHouse/issues/838 .

Learn how we created a startup - VictoriaMetrics - that builds on performance ideas from ClickHouse and solves the issues mentioned above - https://medium.com/devopslinks/victoriametrics-creating-the-... . Currently it has the highest performance/cost ratio comparing to competitors.

> I've loaded a 100 Billion Rows

Have you done any load tests that would more closely mirror a production environment such as performing queries while clickhouse is handling a heavy insert load?

I'm working on developing benchmarking tools for internal testing, but both Yandex and CloudFlare use Clickhouse for realtime querying. I'm still in development phase for my product, but I'll make sure to post information & results when we launch here.

https://blog.cloudflare.com/http-analytics-for-6m-requests-p...

But I've spent a long time looking at the various solutions out there, and while ClickHouse is not perfect, I think it's the best multi-purpose database out there for large volumes of data. TimescaleDB is another one, but until they get sharding it's dead on arrival.

I'd say kdb (from kx systems) is the best database for this problemspace, but it is prohibitively expensive for the http analytics use case. It is also a pain to query, but unbelievable what it can do.

Very cool, I'll check this out!

It's a quirky piece of software and has limitations that need to be considered when standing up a production cluster- such as you cannot reshard it currently. If you have a 3 node cluster, it's messy and requires downtime to add another node.

Still a bit messy, but the clickhouse-copier utility helps a bit: https://github.com/yandex/ClickHouse/issues/2579

I have open issues about it on GitHub. It does not work correctly at this time. If you dig through the issues, there are statements by the devs saying the tool has been neglected.

We load millions of rows per second and use a handful (or more?) of materialized views to build appropriate summaries. Various clients make queries to the "raw data" and the views and it all works fine, basically.

> it all works fine, basically

The "basically" is what intrigues me :D

Do you have a schema available publicly? I would like to build a similar system using custom software + S3 + Parquet + Athena for this task and see if it works.

The schema is

    CREATE TABLE points(Timestamp DateTime,Client String,Path String,Value Float32,Tags Nested(Key String,Value String)) ENGINE = MergeTree() ORDER BY (Client, Timestamp, Path) PARTITION BY toStartOfDay(Timestamp)

And this is a like query I was using

    SELECT (intDiv(toUInt32(Timestamp), 15) * 15) * 1000 as t, Path, Value as c FROM points_dist WHERE Path LIKE 'tst_val1' and Tags.Value[indexOf(Tags.Key, 'server')] = 'node' and Timestamp >= toDateTime(1543421708) GROUP BY t, Path, Value ORDER BY t, Path

This table was made on 5 servers via a distributed table partitioned on the timestamp- so distribution was even.

Thanks! How big is the 100B rows in your system?

Takes up 200gb or so across 5 servers (this is according to ClickHouse's query stats). Actual disk might be a bit higher.

Thanks!

Reply on news.ycombinator.com