26

I need to store and to be able to query some very large amounts time series data.

Properties of the data are as follows:

  • number of series : around 12.000 (twelve thousand)
  • number of data points, globally : around 500.000.000 per month (five hundred millions)
  • mixed value types: the majority of data points are floating point values, the rest are strings
  • sampling period : variable between series as well as within a series
  • timestamps : millisecond precision
  • data retention period : several years, without decay or downsampling
  • data archives need to be built in near realtime, but a reasonable delay (~1 hour) is acceptable
  • past data can be rebuilt if needed, but at a high cost
  • sometimes, but quite rarely, some past data needs to be updated

Properties of envisioned queries:

  • most of the queries against the data will be timestamp-based queries; ranging from one day to several months/years. 90%+ will be queries on the most recent data

Other requirements:

  • the solution must be free as in free beer and preferably opensource

My initial thought thought was to use PyTables / Pandas with HDF5 files as storing backend instead of an SQL database.

Questions :

  1. Assuming PyTables / Pandas is the "best" route, would it be better to split the data in several HDF files, each one spanning a given period of time, or put everything in a single file that would then become huge ?

  2. Should I go and prefer the fixed or the table format ? To me, fixed format looks OK if I keep one HDF file per month, as this way a whole series probably fits in RAM and I can slice in-memory without needing a table format index. Am I correct ?

And if that's not the best approach, how should I structure this data store or what technologies should I be considering? I'm not the first to tackle storing large sets of time series data, what is the general approach to resolving this challenge?


Other approaches I have considered :

  • array databases: they are a superb fit for time series with constant sampling period, as you then only need to store start and end times and sampling period of the array, and then only values in the array itself and indexing is easy. But with variable sampling periods within series themselves, I need to keep a closer timestamp->value relation, that in my view is not such a good fit for array DBMS.
  • standard SQL database with timestamp,paramID,value as columns but by their nature they request a lot of disk I/O for any query

migrated from stackoverflow.com Jan 9 '15 at 16:10

This question came from our site for professional and enthusiast programmers.

  • You should consider array databases -- en.wikipedia.org/wiki/Array_DBMS#List_of_Array_DBMS. I'm not saying that one of them would be the right, or even the best or even a good enough, answer, just that they should enter your thoughts. Besides the entries in that list there is the kdb system (kx.com) though it is far from free. – High Performance Mark Jan 9 '15 at 16:16
  • Thank you for your input. I have considered array databases but the issue I find with these is that they are a superb fit for time series with constant sampling period, as you then only need to store start and end times and sampling period of the array, and then only values in the array itself and indexing is easy. But with variable sampling periods within series themselves, I need to keep a closer timestamp->value relation, that in my view is not such a good fit for array DBMS. With that said, I would be happy to be proven wrong. – flyingmig Jan 9 '15 at 18:40
  • editing question to add what I have considered so far – flyingmig Jan 9 '15 at 18:50
  • Question: do you need to store all the data? Can the data decay over time and/or is there some acceptable level of precision for the float-based series? – J Trana Jan 10 '15 at 3:42
  • 1
    @moinuddin-quadri I ended up using pandas DataFrame objects backed by monthly HDF5 files using table format. The system has been running for more than a year and has shown very stable and fast, not even using SSD disks. I will try to make a write-up of all that as an answer when I have time. Else feel free to PM me. – flyingmig May 19 '16 at 20:30
5

You might want to take a look at carbon and whisper, part of the graphite project. Carbon can handle very large amounts of time series data. Though, now that I read the docs (it's been a few years since I've used it), it's only for numerical data. You said you also have string data so you might not find this useful. Though, you might be able to glean some wisdom about how they are able to process large amounts of data quickly.

To give you an idea of how well it scales, when graphite was first put into production at Orbitz, it was handling 160,000 metrics per minute.

  • Thank you for the suggestion, but from my understanding whisper does not fit because its precision is the second when I need millisecond precision and as you rightfully pointed out, I have string data as well which cannot be stored there. – flyingmig Jan 10 '15 at 13:29
  • 1
    @flyingmig Don't write whisper off so fast. Its timestamps are Unix-epoch values. And the "string data" you described in the question sounds more like enums, and those are usually stored as small integer values. – Ross Patterson Feb 6 '15 at 15:29
  • Sears is using Carbon / Graphite / Ceres to store 4M+ unique datapoints per minute. It's not perfect, and it requires graphite clustering and SSD's, but it works. All the other solutions out there aren't scalable to this level, that we've found, but if you have ideas, feel free to chime in. – Kevin J. Rice Nov 23 '15 at 16:56
3

InfluxDB is an open source database written in Go. It has been written especially to handle time series data, and they published benchmarks showing far better performance vs. Cassandra:

InfluxDB outperformed Cassandra in all three tests with 4.5x greater write throughput, while using 10.8x less disk space, and delivering up to 168x faster response times for tested queries.

2

you might want to checkout column-oriented databases. I am not sure what you mean by array databases but with my suggested approach you can have a dynamic number of values per time frame. You can also have multiple values for the same timestamp. The interesting part is that if you have values measured at the same timestamp you can save them as additional columns (e.g. a sensor that measures temperature and humidity, in stock trading price and size of an trade, ...). Because of the column-oriented nature you can have tables with 100 columns but if your query only accesses five columns the database reads only the data of the five columns.

I wrote a series about creating your own time series database, you might want to have a look at it:

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