How Time Series Databases Work, and Where They Don’t

Re:How Time Series Databases Work, and Where They Don’t by oconnore on Monday October 18, 2021 @01:45AM (#28902596)

> Often an operational analytics DB (Clickhouse, CrateDB) might also be a solution

This might be a bit off topic, but speaking of gaps in common observability tooling: is an OLAP database a common go-to for longer-timescale analytics (as in [1])? We're using BigQuery, but on ~600GB of log/event data I start hitting memory limits even with fairly small analytical windows.

In this context I have seen other references to: Sawzall (google), Lingo (google), MapReduce/Pig/Cascading/Scalding. Are people using Spark for this sort of thing now? Perhaps a combined workflow would be ideal: filter/group/extract interesting data in Hadoop/Spark, and then load into OLAP for ad-hoc querying?

[1]: https://danluu.com/metrics-analytics/

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by proddata on Monday October 18, 2021 @02:23AM (#28902770)

> is an OLAP database a common go-to for longer-timescale analytics (as in [1])?

I would not consider Clickhouse or CrateDB "classic" OLAP DBs. I can speak for CrateDB (I work there), that it definitely would be able to handle 600GB and query across it in an ad-hoc manner.

We have users ingesting Terabytes of events per day and run aggregations across 100 Terabyte.

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by nicoburns on Monday October 18, 2021 @04:45AM (#28903405)

What kind of hardware requirements would be needed to store and query this much data?

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by proddata on Monday October 18, 2021 @05:26AM (#28903652)

- Depends - Just inserting, indexing, storing and simple querying can be done with little memory (i.e. 1:500 memory-disk-ratio 0.5GB RAM per 1TB disk). Typical production clusters with high query load are in the 1:150 range i.e. 64GB RAM for 10TB disk).

Otherwise typical general purpose hardware (Standard SSDs, 1:4 vCPU:memory ratios, ...)

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by nicoburns on Monday October 18, 2021 @10:09AM (#28905576)

Interesting, so that'd be about 1 vCPU and 4GB RAM per 625GB of data. That seems very price efficient. Would something like AWS's EBS be sufficient for this? Would you need one of the higher tiers? Or would you be looking at running this on a box with locally attached storage?

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by proddata on Monday October 18, 2021 @02:56PM (#28909871)

Most of CrateDB clusters run on cloud providers hardware (azure, aws, alibaba). Using EBS (GP2 or now GP3) is also quite common. Due to the indexing / storage engine, gp disks are typically sufficient and faster disks have little to no advantage

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by claytonjy on Monday October 18, 2021 @10:31AM (#28905786)

Wouldn't 0.5GB RAM per 1TB disk be more like 1:2000 memory-disk-ratio? Which is even better!

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by proddata on Monday October 18, 2021 @02:59PM (#28909937)

Sorry, mixed up the number 2GB memory (0.5GB heap). So 1:500 is correct

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by jpgvm on Monday October 18, 2021 @03:36AM (#28903110)

For longer-scale timeseries I still recommend Druid as the go-to. Mainly because if you make use of it's ahead-of-time aggregations (which you can do for real-time or scale-out batch ingestion) then your ad-hoc queries can execute extremely quickly even over very large datasets.

Druid only really has 1 downside, which is it's still a bit of a pain to setup. It's gotten a ton ton better in recent times and I have been contributing changes to make it work better out of the box with common big data tooling like Avro.

For performance it's the top dog except for really naive queries that are dominated by scan performance. For those you are best off with Clickhouse, it's vectorized query engine is extremely fast for simpler/scan heavy workloads.

[ Parent | on HN ]

Re:How Time Series Databases Work, and Where They Don’t by shaklee3 on Monday October 18, 2021 @08:43AM (#28904803)

We used clickhouse on about 80TB in a raid10 setup. It was extremely fast

[ Parent | on HN ]