8

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.
  • Teradata is a 'big data' DBMS, but I have no idea where they are with data acquisition (your incoming stream) – user416 Mar 28 '15 at 17:46
  • Is this highly variable data, or is it like plant process data where the next 'value' is highly likely to be the same as the previous 'value'? Would it help to put in differential record recording like the PI (Plant Information) data system does? – user13488 Apr 4 '15 at 13:44
  • I'm with the team that develops Axibase Time-Series Database. It does use HBase for raw storage, and your remark about HBase admin overhead is somewhat valid. We've tried to address it by developing IO and throughput auto-tuning tests to at least minimize the guesswork from configuration. If you're interested, give ATSD a try: axibase.com/products/axibase-time-series-database – Sergei Rodionov Jul 30 '15 at 14:14
  • On HBase tuning. Here's for example, how you can tune batch size/thread count to optimize it for a given workload: i.imgur.com/g7WqNFG.png. It's an auto-test. Dark large text means fast and reliable (low stdev) throughput. Small grey - slow and inconsistent. You can then apply these hints to set appropriate configuration parameters. HBase can be fun. That's 400K inserts per second, by the way. – Sergei Rodionov Jul 31 '15 at 10:24
  • Take a look at Druid. It's used for just this use case by some of the largest sites out there (Airbnb, Ebay, Cisco, Alibaba, etc.). – Nathan Clayton Jul 22 '16 at 18:18
2

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.

1

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 :

snapshot of the popularity of TSDB as of july 2016

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.