Hacker News Reader

I've found PostgreSQL to be extremely fast if you store time series in arrays (http://www.postgresql.org/docs/9.4/static/arrays.html) in a round-robin fashion. You can also limit the array size, so that you have a fixed number of points per table row (thereby splitting your series across multiple rows), and if you adjust it such that it fits on one PG page it is quite performant.

I don't think you can limit them: However, the current implementation ignores any supplied array size limits, i.e., the behavior is the same as for arrays of unspecified length.

By "limit" I mean your code would do it, not Postgres. E.g. your series is 86400 datapoints long (seconds in a day), you would store it as 100 rows of 864-element-long arrays.

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.

I've hit the same problem and I would like to move back to a SQL data store. However none of the nice dashboards / visualizations support postgres or any SQL database (for now)...

My question (to everyone): what do you use as replacement for kibana or grafana?

If you don't need to keep data forever, but only several weeks or months, and you only need numeric time series data and not raw event logs, Prometheus (http://prometheus.io/) is your friend. Since it's optimized towards purely numeric time series (with arbitrary labeled dimensions), it currently uses an order of magnitude less disk space than InfluxDB for this use case, and I've also heard a few reports of people's CPU+IO usage dropping drastically when they switched from InfluxDB to Prometheus for their metrics.

As dashboards for Prometheus, you can currently use PromDash (http://prometheus.io/docs/visualization/promdash/), Console HTML templates (http://prometheus.io/docs/visualization/consoles/), or Grafana (http://prometheus.io/docs/visualization/grafana/).

Durable long-term storage is still outstanding. Although replication into OpenTSDB and InfluxDB is experimentally there.

I've just implemented a custom backend for graphite-api which seems to be working ok although I don't have crazy requirements. https://github.com/brutasse/graphite-api is a cleaned up fork of graphite (which is much easier to install). I'm using grafana as the front-end and my data is in a postgresql database and graphite-api is linking them together.

Hello, I find myself having the same need. Would you agree to share your implementation or point me to it? Thank you!

If I decided to move to using a SQL data store (I use graphite now), I would re-implement the graphite API as a listener process, or write a graphite backend. The biggest strength of Graphite is how simple it is to upload and query metrics, and I wouldn't want to lose that even if the backend were to change.

Shameless plug here - The author of the article does build time-series databases for a living, and more specifically the Datadog monitoring platform - which will gladly collect your millions of metrics, graph them and alert on them, along with all the events you care to keep :-)

We've been through a number of data stores ourselves, starting with Postgres back in 2010 - then on to Redis + Cassandra before we built our own. But that's a story for another post...

http://datadog.com

Did you consider a hybrid solution? You could store the most recent data in time series database for visualization purposes and dump the rest into a traditional SQL data store. Other than that, IIRC Grafana had plans for PostgresSQL but it's not there yet.

> 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.

Could it be the environment didn't have enough memory to do things? I've seen frantic swapping and garbage collection before in VMs without enough RAM.

> vps

There's your problem. If you're at the "make it fast" part of "make it work, make it right, make it fast", you should almost certainly be on dedicated hardware.

That was actually the "make it work" portion: I was attempting to grab some 10 full set prices at a time for a gallery feature and managed to crash the influxdb.

For those of you reading this that are interested in getting on InfluxDB 0.9. If you intend to use authentication and have a somewhat high request rate, I advise you to wait until the fixes related to #3102 (https://github.com/influxdb/influxdb/issues/3102) are included. Without that fix your CPU is killed because InfluxDB is bcrypting on every request.

That explains why my updates on 100ms intervals seemed like hogging resources.

On the performance problem, my guess is that you wrote in a bunch of columns and had those in where clauses. I hope the documentation made it clear that you'd be range scanning over data and that you probably wouldn't get desirable performance.

In 0.8 and before, the preferred way to model your data was to create many separate series names. This method is currently giving many users great performance.

As with all databases, how you model your schema has a significant impact on performance.

That being said, it might be that Influx wasn't right for your use case and Postgres is just a better thing to go with.

Also, we're not supporting anything prior to the 0.9 series of releases. 0.8 is deprecated and we'll be pushing everyone to move over to 0.9 as we put out more point releases and fix bugs and ensure that it works for their use case over the coming months.

I evaluated InfluxDB for an advanced packet capture and processing application and it couldn't handle things very well. Namely expiry of old data, blocking too much on inserts. So I wrote my own in Python + C extensions. It turned out well. Has been going non-stop for year and a half now.

What data structures does it use?

For the main data and index just a plain sorted list of tuples. Main data file is [ (timestamp,data), ...] and index is [ (timestamp,offset_in_datafile), ... ]. There is a requirement that ntp server must be running and the machine synchronized to it.

Then all the files have a prefix that look like <startsec>_<startmicrosec>_<metric>. A binary search is perfomed first on files in order to pick the right files only. Then the index is read into memory, then binary search performed on the index to get the offset range and then the main data file is read.

When switching to new file time chunk, a cleanup action is peformed where previous old files are removed.

Just for the record, InfluxDB 0.9 seems to actually be production ready now. Though it doesn't look there's an easy way to migrate to it yet from 0.8.

Based on the changes in 0.9.1, there's no way that 0.9 was production ready and I'd be very cautious about using it.

https://influxdb.com/blog/2015/07/02/InfluxDB-0_9_1-and-Tele...

"Seems to be", sure. Sinks like a tanker when you try to actually use it, though. None of the software libraries have been updated for 0.9 yet.

> Sinks like a tanker when you try to actually use it

Can you name some examples of this besides the CPU authentication bug? Honest question, I'm considering adding InfluxDB to our stack so I'm genuinely curious.

We've had issues with Influx being a massive resource hog. Not properly persisting data to disk. They openly admit that Influx isn't built to gracefully recover from crashes, and that you will lose data.

This by itself wouldn't be a problem for us. But when you're inserting data points, the entire damn thing seems to become unresponsive. Admin UI freezing, http endpoint no longer responding to queries, ...

I know this product is relatively young and your mileage may vary. They have a long way to go.

Authentication in 0.9 has a high CPU penalty right now: https://news.ycombinator.com/item?id=9810538.

It'd be great to see a more detailed guide to using and tuning PGSQL for use as a TS DB.

As an addendum, I'd like to be able to do time-based append-only operations in Postgres, a la Datomic.

It pains me to UPDATE and DELETE, thereby destroying useful data.

Would Timetravel module work for your use-case: http://www.postgresql.org/docs/9.1/static/contrib-spi.html#A...

Same here. InfluxDb has a pretty nice DSL. I wish they switched their backend to something usable and mature instead of re-inventing the bicycle. Operationally, it is expensive to support specialized databases like Influx, unless it is your core business, I guess...

> influxDB 0.8 eating 50% of the VPS' cpu and 77% of the ram while idling

> to [InfluxDB's] credit it barely moved beyond idle resource usage when I was stuffing it full of data.

solution: always be cramming it full of data ?

I don't see how it could idle when you're bulk writing, but when it's not serving any traffic it's taking up 50% of cpu (unless it's doing some kind of indexing or cleanup or something in the background, but you would expect that to eventually quiesce).

I think he meant that idle CPU (50%) and full throttle usage (51%) were hardly different? Who knows. :P

Curious if anyone has experienced problems like this in InfluxDB 0.9 ?? My 0.9 implementation is performing well, but has only small amounts of metrics for testing - not yet rolled out to production.