Time-Series Database Requirements

http://www.xaprb.com/blog/2014/06/08/time-series-database-requirements/

by eaxitect • 11 years ago

94 44 11 years ago

nimish• 11 years ago

Druid is quite a good one

zodvik• 11 years ago

How has your experience being using Druid? It seems like a good fit for our use cases, but there doesn't seem to be much of a community around it.

hartror• 11 years ago

This smells like a job for Apache Kafka [1], I've yet to use it personally but its feature set appears to hit the mark though it lacks SQL. The application described sounds like it uses something similar to event sourcing [2] which people have used Kafka for successfully. If you're not familiar with Kafka there is a very good interview with Jun Rao [3] on se radio.

[1] http://kafka.apache.org/

[2] http://martinfowler.com/eaaDev/EventSourcing.html

[3] http://www.se-radio.net/2015/02/episode-219-apache-kafka-wit...

misframer6• 11 years ago

Kafka is great, but it doesn't solve the problem the author describes since you can't specify an index. Data arrives in (timestamp, metric) order, but it needs to be indexed by (metric, timestamp).

jat8502• 11 years ago

Would Apache Samza fold in here better, perhaps?

otterley4• 11 years ago

Kafka is a message bus (for data in transit), not a store for data at rest. Sure, you could abuse it as an append-only log-structured primary data store, but then again, when you have a hammer, everything looks like your thumb, I guess.

Also, as another poster said, it has no indices, just an offset pointer.

welder• 11 years ago

This sounds like a job for http://www.aerospike.com/

nivertech• 11 years ago

How would you model timeseries on top of Aerospike?

Using 1KB chunks of datapoints as simple K/V?

Or using Large Data Types [1] like Large Stack [2]?

    Large Stack
    
    A Large Stack collection is naturally aligned with time series data
    because the stack preserves the insert order.
    Stacks provide Last In First Out (LIFO) order, 
    so it's a convenient mechanism for tracking "recent" data.

    Usage examples include:

        Show me the last N events
        Search the last N actions with a filter
        Show me all documents in reverse insert order

[1] https://www.aerospike.com/docs/guide/ldt.html

[2] https://www.aerospike.com/docs/client/java/usage/ldt/ldt.htm...

chupy• 11 years ago

Ok...but does it really? What made you say that?

digitalzombie• 11 years ago

Cassandra seems like a good fit.

Writes are faster than read, it's an AP, and you shouldn't really update frequently it unless you want tombstone hell. There's also TTL too.

Is there any cons of using Cassandra as a Time Series Database? I'd like to hear it.

The biggest thing for Cassandra is you should know your queries before hand before you data model.

otterley4• 11 years ago

Cassandra is the de facto backing store for pretty much every metrics service out there - Datadog etc.

kodablah2• 11 years ago

I too believe it is the best fit, but the "aggregate functions" gets most people. The use of counter columns is very limiting and many engineers don't want to struggle with storing state during streaming writes to precalculate the aggregates on write. Also, engineers tend not to want to do large reads to rebuild large aggregate values on small data changes.

It is what we use, and we use spark streaming for the rollups. We had evaluated Influx, OpenTSDB, and Druid also. So long as you know the exact read patterns for your client I think Cassandra is definitely the best fit for most things.

salex89• 11 years ago

Did you try out the Cassandra-backed KairosDB? I'm very interested in the results if you did.

dmichulke• 11 years ago

I also use cassandra heavily.

From what I read (so not confirmed) one problem is that it uses space inefficiently. Since I predefine the columns anyway, they might as well use an efficient storage instead of mongodb style kv-pairs.

I am also not convinced of the partition key necessity (although it doesn't hurt either once you got it).

Finally, since my application runs on the JVM, I'd actually like to see a direct integration / an API that allows me to skip the socket overhead and launch cassandra directly on start-up. The advantage is mainly memory (just need half of it) and latency although I agree this is more difficult to maintain in a distributed scenario.

manigandham• 11 years ago

Recently came across Prometheus (http://prometheus.io)

There's also OpenTSDB (http://opentsdb.net) that's been around for a while.

otterley4• 11 years ago

Prometheus has no scaling model at this time. Sharding is currently described as an exercise for the user. This surprises me if they're actually still using it at SoundCloud.

Filligree• 11 years ago

Why do you need scaling? A single machine should be enough to monitor thousands of others.

Or, in short, if you do need scaling you probably can afford to implement it.

aaa667• 11 years ago

Would be keen to know what people think about: https://github.com/ambiata/ivory

Rapzid3• 11 years ago

I would actually be very interested in hearing more about this MySQL implementation.

misframer6• 11 years ago

I work with Baron, the author. He gave a talk recently at SCaLE[0] about our time series storage using MySQL. The slides[1] are also available in case you don't want to watch the entire talk.

[0] http://youtu.be/EoUfkkrIbPg

[1] http://www.slideshare.net/vividcortex/scaling-vividortexs-bi...

Rapzid3• 11 years ago

Thanks very much. I have experimented with used Riak as a backend with its search 2.0(lucene) for indexing the streams. I'm very interested in how the MySQL tables are laid out; On the watch/reading list for tonight :)

Rapzid3• 11 years ago

I'm wondering if it has been considered to buffer the metrics into a memory table so they can participate in queries, and then let MySQL flush them to durable tables at regular intervals?

doodlebugging• 11 years ago

This might not be what he's looking for but it is one way to manage huge amounts of data:

[HDF5](http://www.hdfgroup.org/)

rspeer• 11 years ago

HDF5 is mostly a storage format. It's not what he's looking for because it doesn't meet the requirements of a database.

For example, only one process at a time can open an HDF5 file for writing.

thrownaway2424• 11 years ago

"Reads need to be fast, even though they are rare. There are generally two approaches to dealing with this. The first is to write efficiently, so the data isn’t read-optimized per-series on disk, and deploy massive amounts of compute power in parallel for reads, scanning through all the data linearly. The second is to pay a penalty on writes, so the data is tightly packed by series and optimized for sequential reads of a series."

As the little girl says in the GIF: why don't we have both? Write to a write-optimized store of limited size that requires full access during reads, and re-write that into a read-optimized format hourly or daily. Because it's limited in size, you won't care that the most recent data isn't very efficient for reading, or isn't particularly compact.

k1w1• 11 years ago

I built exactly the database that you are describing here. Unfortunately it is not open source. However it is no secret that we used sqlite for the storage. Inserts and queries in sqlite are fast, even for databases with billions of rows. Deletes are very, very slow, so we created a new sqlite database for each week, and simply deleted an entire database file when the retention period expired. Sqlite is ACID, supports all of the complex queries that could be imagined and is easy to embed into the overall engine code base. We use aggregate tables to more efficiently display common aggregations of the data (e.g. graph metric over the last day, or average metric over the last date). The aggregates were updated as data was inserted, so that real-time views were always available.

biokoda• 11 years ago

Sounds like you implemented something similar to http://www.actordb.com/

sethev• 11 years ago

I'm kind of curious about the no "tagging" point. Won't there always be some data that doesn't fit the [timestamp double] format?

misframer6• 11 years ago

We don't use tags, but we basically encode tags into the metric name of our time series. An example would be "host.queries.c.1374c6821ead6f47.tput". This tells you the category, type of query, query ID, and the metric associated with it. In this case, it's the time series for the throughput of the query with ID 1374c6821ead6f47.

philsnow• 11 years ago

If you have multiple tags, do you canonicalize the ordering of the metric name? If so, where does that canonicalization happen (on the metric origination side or on the server/collection side)? It would seem to be a pain if you end up with metrics named

    host.queries.type=GET.result=200.tput

and

    host.queries.result=200.type=GET.tput

damian2000• 11 years ago

Anyone heard of Operational Data Historians? Like OSISoft's PI, Honeywell's PHD, GE's Proficy. They're expensive suites of software that have optimal real-time ability, plus historical access. They usually work in process control/operations of factories or manufacturing plants. Each item being measured is called a tag.

Just thought I'd throw this out there since its a specialised area that not many people know about. I've done some work with them in terms of writing adaptors to a time series data visualisation product.

http://en.wikipedia.org/wiki/Operational_historian

http://en.wikipedia.org/wiki/OSIsoft

https://www.honeywellprocess.com/en-US/training/programs/adv...

http://www.geautomation.com/products/proficy-historian

On the topic of Historians vs Relational Databases, theres a blog post here about it ...

https://osipi.wordpress.com/2010/07/05/relational-database-v...

... admittedly this is by the developer OSISoft so it may be biased, but their points seem valid. Especially the swinging door algorithm reference and the fact they are far more efficient in storage.

snowwindwaves• 11 years ago

I just installed and threw 200 tags at the rockwell factory talk branded version of osi. I always wanted to work with it but unfortunately a "historian" was just one of many deliverables so I only got to do the bare minimum that ticked the box. It is noticeably faster at retrieving data from the historian than the rockwell factory talk SE HMI binary datalog files though.

obstinate2• 11 years ago

Multi-dimensionality optional/drawback? Strongly disagree.

This may be my experience as a Googler talking, but I also somewhat disagree with the notion that the data can't fit in memory. I operate a X0,000 task service, and our monitoring data could fit into memory in a large server, if need be.

Of course, we don't keep per-task data permanently, that would be prohibitive at a 5s monitoring interval like the one we use, even if it were put on disk. Instead, we accomplish what I described by aggregating away some dimensions, mostly task number, and then holding the aggregated series in memory, for fast queries. There are some nuances, particularly around having foresight over the cases where you do want to see individual tasks.

But suffice it to say that this person's experience does not match mine in terms of what I need from a TSDB. Perhaps his ops background comes from a different set of needs than mine, but if you're building a TSDB for many customers, I wouldn't take this list as gospel.

devdas2• 11 years ago

If I need a few terabytes of data to be stored reliably enough that I can lose a node (or two), support mostly writes and some reads, for a duration of a few years (at least three), would you still suggest storing this in RAM?

obstinate2• 11 years ago

My team keeps replicated copies of the data in RAM, replacing nodes that get lost. The data is also echoed to disks, of course.

rodionos• 11 years ago

I'm one of the developers behind Axibase Time-Series Database which runs on top of HBase. ATSD is two years into development and has a built-in rule engine, forecasting,and visualization: http://axibase.com/products/axibase-time-series-database/vis.... The rule engine allows you to write expressions such as abs(forecast_deviation(avg())) > 2.0 to trigger url/email/command actions if sliding window average is outside of 2.0 sigmas from Holt-Winters/ARIMA forecast.

The license is commercial and there's a free CE version which can be scaled vertically without any throughput constraints. Tags are supported for series as well as for entities and metrics to avoid storing long-term metadata such as location, type, category etc. along with data itself.

I wouldn't be surprised if functional differences between TSDBs and historians will disappear in just a few years. Right now the historians are good at compressing repetitive data at source and on disk which makes sense given their heritage in archiving data from SCADA systems.

Time-Series Database Requirements

Related Stories