Time-series charts for large amounts of data

I have a couple of thousand time-series covering several years at second-granularity. I'd like to store the data in a suitable DB (i.e. one that scales well and can retain all data at original granularity, e.g. Druid, openTSDB or similar). The goal is to be able to view the data in a browser (e.g. by entering a time frame and ideally having zoom/pan functionality).

To limit the number of datapoints that my webserver needs to handle I'd like to have functionality which seems to be working out of the box for Graphite/Grafana (which, if I understand correctly, is not a good choice for long-term retention of data):
a time-series chart in Grafana will limit data by querying aggregations from graphite (e.g. return mean value over 30m buckets when zooming out while showing all data when zooming in).

Now the questions:

Feedback on the choice of DB is also welcome (open-source preferred).

2 Answers
2

You can absolutely store multiple years of data in Graphite, the issue you'll have is that the way that Graphite selects the aggregation level to read from is by locating the highest-resolution archive that covers the requested interval, so you can't automatically take advantage of aggregation to both have efficient long-term graphs and the ability to drill down to the raw data for a time period in the past.

One way to get around this problem is to use carbon-aggregator to generate multiple output series with different intervals from your input series so you can have my.metric.raw, my.metric.10min, my.metric.1hr, etc. You'd combine that with a carbon schema that defines matching interval and retention for each of the series so my.metric.raw is stored at 1-second resolution, .1min at 1-minute etc.

my.metric.raw

my.metric.10min

my.metric.1hr

my.metric.raw

.1min

If you do that then in Grafana you can use a template variable to choose which interval you want to graph from, so you'd define a variable $aggregation with options raw, 10min, etc and write your queries like my.metric.$aggregation.

$aggregation

raw

10min

my.metric.$aggregation

That will give you the performance that you need with the ability to drill into the raw data.

That said, we generally find that while everyone thinks they want lots of historical data at high granularity, it's almost never actually used and is typically an unneeded expense. That may not be the case for you, but think carefully about the actual use-cases when designing the system.

The Axibase Time Series Database (ATSD) provides a built-in charts library that performs period aggregations on the fly, depending on the date interval displayed in the browser. This is controlled with period = auto setting which you can override to set the period explicitly. As the user zooms in or out on the time scale, the aggregation period is adjusted automatically.

period = auto

Here's a live example and a screenshot below. By the way, the '1 year' chart displays 2 million samples, aggregated at request time on the server. Aggregates are not cached which makes the implementation resilient to out-of-order writes.

References:

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.