Database for large volume of metrics (key, value, timestamp)

Question

I'm looking for a Database for large amounts of metrics, and i'm confused that there is nothing out there to:

• store large amounts (terrabytes) of metrics data in the form:
  • key (string)
  • value (integer)
  • datetime (timestamp)
• from a stream of 1Gb/s of such metrics
  • from highly unreliable sources (tcp bad)
• execute aggregation on a subset (~10Gb) of data:
  • avg(value), max(value), min(value), count(*)
  • where value == "foo"
  • where X < datetime < Y
  • group by datetime/timespan
• within less than 5 seconds, when the subset is "current"
  • need to be able to make a subset "current", i.e. load to ram
  • queries on old data not marked "hot", have no time constraint

so far i evaluated:

postgres

• +never lost data ever
• -very difficult to scale
• -no performance bonus from the specific requirements
• -i don't understand how to partition data into "current" and "old"

crate.io

• -too young, many bugs while scaling
• +great partition semantics allows partition by timestamp
• -http based. alot of overhead for a 1Gb stream

influxdb

• +semantics are exactly what i need
• -doesn't scale at all
• -crashes often and loses data

opentsdb

• +looks like it was designed for my use case
• -the complexity of hbase isn't worth it

cassandra

• +scales well, and unlike hbase, i understand how it works
• +twitter uses it for metrics (rainbird)
• -aggregation is supposed to be done on top of hadoop
• -hadoop is hard

graphite

• +custom database for metric purposes
• -custom database ...
• -seems very slow and i don't see how it's supposed to scale

prometheus.io

• +looks like it was designed for my use case
• -but isn't. instead it's some sort of aggregation for "scaped" statistics. It doesn't scale well to unreliable event streams.

Answer 1

Completely agree about graphite, influx, opentsdb. Been there, ran into the same issues.

The best answer is probably AWS Redshift or Cassandra.

Redshift: Postgres but with columnar data store. Thousands of times faster for typical time series...

Cassandra: you got the pros and cons. May be better to use with Spark though...

Before you get data into your data store, you have to stage it. For example Redshift can easily handle the data velocity you have, but is limited to a fairly small number of inserts per second, so forget about insering each data point as it comes in. You can simply log each second/minute/hour (perhaps split by key) and put the logs in S3, then bulk insert them. You may also want to keep the logs long-term for reliability.

You say TCP bad. That means you probably don't want MQTT, but you might use CoAP. Look into what AWS Kinetis uses, I'm not familiar with the protocol. I think you may be able to set ut up to stream Kinetis data into Redshift directly.

P.S. My solution was to cache locally and write to S3 at regular intervals. A lot of analytics could be run directly from S3 since the names of objects describe what+when (key and time range). The aggregation simply reads S3 into pandas, aggregates using the super-convenient pandas functions, and memoizes the results. Moving this whole thing into Redshift is in progress but looks pretty speedy so far.

Answer 2

First of all, the key word you're looking for is "time series database". Here's a snapshot of the popularity of TSDB as of july 2016 according to DB engines ranking :

Secondly you can't have your cake and eat it. If influxDB is simple but does not scale it's because it does not run on a cluster, if openTSDB scale it's because they can run on a cluster. But of course setting up a cluster environnement is hard ! So you're going to have to choose between scaling (openTSDB) and simplicity (influxDB), or you may have to pay for someone to handle the complexity (kdb+ seems to be the one for that).

Alternatively, you may look into Warp10 that use docker for installation, druid that only require Zookeeper and not the full hadoop stack, or Graphite that has its own install script (Synthesize). They migth be easier to install while still being able to scale.

Answer 3

Currently the most promising solution I am aware of is TimescaleDB.