Thoughts on Time-series Databases • Hacker News Board

Re: Thoughts on Time-series Databases

#11

Post by rdtsc » Tue, Jun 30, 2015, 7:33 PM UTC

> Aggregation and expiry start to look a lot like dimensionality: they can be implemented asynchronously in a separate policy layer.It doesn't seem important that the actual storage engine is mindful of these; in fact, it's probably better ignored for efficiency's sake.

I wrote my own time series database and actually having well working expiry was harder than expected. I sharded each one of the files into 2GB or 24h (whatever came first) chunks. Then had to be careful how they got deleted. There were various rules there such as ("make sure to not fill partition more than 80%", "stop when partition is full" or "keep data no more than 90 days").

My number of different time series was actually pretty low but each value was a record with complex data inside it i.e. not just simple ints or floats. For example, one series was capturing network packets so it looked like [ (timestamp,packet), (timestamp, packet), ... ] . But then an indexing service was running separately in a separate OS process to generate additional complex indexing from the primary data.

Re: Thoughts on Time-series Databases

#12

Post by Everlag » Tue, Jun 30, 2015, 7:52 PM UTC

I actually just migrated 20 million rows of Magic: the Gathering price data from influxDB to postgres this week. For a few days of effort, I decreased my query latency by an order of a magnitude; a full set query, roughly 270 cards, went from 30 to 3 seconds with a cold cache.

The migration was prompted by influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling. It had no capability to index along anything but time so every query, for my use case, required a full table scan. 0.9 was supposed to fix every issue I had with it but it was due to be 'production ready' months ago.

Unless you're dealing with ingesting an absolutely insane amount of data indexed along time, I'd have to say that postgres or comparable sql database should be more comfortable, more stable, and much more mature.

EDIT: I don't want to come off as shitting all over influxDB, to its credit it barely moved beyond idle resource usage when I was stuffing it full of data.

Re: Thoughts on Time-series Databases

#13

Post by zaphar » Tue, Jun 30, 2015, 7:53 PM UTC

nwmcsween wrote: post #5

Why does every TSD seem so overly engineered and for all the wrong reasons? Why not just use a time decaying ring buffer (multiple buffers could be used), one statistic one file (or more depending on the decay) and offset by a set interval if you have irregular intervals 'smooth' it to fit O(1) for most things. My other issue is (from a glance) looking at some TSD they ignore most research done on how to effectively…

    Why not just use a time decaying ring buffer

Because you don't want to through the data away. RRDTool and Whisper implement ring buffers but you lose data and resolution with them. If that's acceptable then absolutely use those tools. If you don't want to lose data though then you need something else.

Re: Thoughts on Time-series Databases

#14

Post by sciurus » Tue, Jun 30, 2015, 8:00 PM UTC

If you are looking for time-series databases based on Cassandra that you can use with Graphite, check out

Cyanite: https://github.com/pyr/cyanite and https://github.com/brutasse/graphite-cyanite

KairosDB: https://github.com/kairosdb/kairosdb and https://github.com/kairosdb/kairos-carbon and https://github.com/Lastik/KairosdbGraphiteFinder

Re: Thoughts on Time-series Databases

#15

Post by nwmcsween » Tue, Jun 30, 2015, 8:04 PM UTC

zaphar wrote: post #13

nwmcsween wrote: post #5

Why does every TSD seem so overly engineered and for all the wrong reasons? Why not just use a time decaying ring buffer (multiple buffers could be used), one statistic one file (or more depending on the decay) and offset by a set interval if you have irregular intervals 'smooth' it to fit O(1) for most things. My other issue is (from a glance) looking at some TSD they ignore most research done on how to effectively…

Why not just use a time decaying ring buffer Because you don't want to through the data away. RRDTool and Whisper implement ring buffers but you lose data and resolution with them. If that's acceptable then absolutely use those tools. If you don't want to lose data though then you need something else.

You don't have to decay, I just assumed eventually you would want to decay the data but in a separate ring buffer, thus no resolution loss.

Re: Thoughts on Time-series Databases

#16

Post by bane » Tue, Jun 30, 2015, 8:04 PM UTC

Everlag wrote: post #12

I actually just migrated 20 million rows of Magic: the Gathering price data from influxDB to postgres this week. For a few days of effort, I decreased my query latency by an order of a magnitude; a full set query, roughly 270 cards, went from 30 to 3 seconds with a cold cache. The migration was prompted by influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling. It had no capability to index along any…

> 20 million rows

> 30 seconds

Ugh, that's terrible. grepping the file or reading and parsing a csv file is probably faster.

Re: Thoughts on Time-series Databases

#17

Post by necubi » Tue, Jun 30, 2015, 8:05 PM UTC

I've been pretty happy with OpenTSDB (a TSD built on top of HBase). It happily ingests tens of thousands of points per second, supports full-resolution historical data, and has reasonably fast queries.

The main downside is HBase is operationally complex, but if you've already made the investment there (as we had), it's a great option.

Re: Thoughts on Time-series Databases

#18

Post by manigandham » Tue, Jun 30, 2015, 8:12 PM UTC

Everlag wrote: post #12

I actually just migrated 20 million rows of Magic: the Gathering price data from influxDB to postgres this week. For a few days of effort, I decreased my query latency by an order of a magnitude; a full set query, roughly 270 cards, went from 30 to 3 seconds with a cold cache. The migration was prompted by influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling. It had no capability to index along any…

Most SQL databases can scale and handle massive amounts of time series data, especially if they have columnstore features which make scans incredibly fast... while still giving all the advantages of adhoc SQL queries.

Most purpose-built time-series stuff isn't really necessary for much of what I see people trying to use it for.

Re: Thoughts on Time-series Databases

#19

Post by Everlag » Tue, Jun 30, 2015, 8:14 PM UTC

bane wrote: post #16

Everlag wrote: post #12

I actually just migrated 20 million rows of Magic: the Gathering price data from influxDB to postgres this week. For a few days of effort, I decreased my query latency by an order of a magnitude; a full set query, roughly 270 cards, went from 30 to 3 seconds with a cold cache. The migration was prompted by influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling. It had no capability to index along any…

> 20 million rows > 30 seconds Ugh, that's terrible. grepping the file or reading and parsing a csv file is probably faster.

It was pretty awful watching the vps choke to death when I tried implementing any feature using full set prices. Pegged at 99% cpu usage with the go garbage collector frantically trying to not let the process crash... that was not an environment I wanted to take to production.

Re: Thoughts on Time-series Databases

#20

Post by rch » Tue, Jun 30, 2015, 8:17 PM UTC

ignoramous wrote: post #9

Related: Baron Schwartz article on Time-series Database Requirements is quite a read: http://www.xaprb.com/blog/2014/06/08/time-series-database-re... HN discussion: https://news.ycombinator.com/item?id=9166495

I'm not sure it's universally correct to dump authorizations and visibility from the up-front requirements. It might be regarded as another aspect of dimensionality, but that could to missed opportunities for optimization.