Approaches for storing and analysing large amounts of time-based data

Question

I've been asked to develop a "telemetry" application that records data generated by a hardware device, which I read every 100ms.

There are approx 250 data points (32-bit values), but only a subset of these (say 30-50) will be returned by each 100ms read, so it will take several reads before I've got all 250 data points. Out of the data point values read, only a small number are likely to have changed since those same data points were last read - some change every 100ms, others perhaps every few seconds, while others very infrequently (or even never).

The main requirements for the system are:-

1. The ability to plot one or more data points over time, at 100ms resolution
2. The ability to view the values of all 250 data points at a particular point in time
3. The application needs to store the last 60 hours worth of data

I'm looking for some thoughts on how best to tackle this, particularly how to store the data in a way that supports the required reporting. So far I'm veering towards this idea:-

I maintain a "dictionary" of all 250 data points, updating the values after each read. Every 100ms I would write all 250 values to my data store. It's easy to implement and easy to query the data to satisfy the requirements. The downside is that it's not a particularly efficient use of storage (but hey - disk space is cheap nowadays).

Alternatively, ever 100ms, I could store only values that have changed since they were last read, however this becomes more difficult to implement, especially when it comes to querying. E.g. for requirement #2, I would have to construct a "snapshot" by traversing back through the data from the required point in time, looking for each data point's last stored value.

Now I've written all this out I think I've answered my own question. The "write everything" approach seems to be a no-brainer. However I've had no experience of this kind of mass data storage and analysis so I would be interested to hear what others think.

Answer 1

What you want to implement is called an historian in the industrial world. You should have a look at openTSDB which is an open source historian built on top of HBase. There are also commercial products like Wonderware Historian, GE Proficy Historian and Kepware’s Industrial Data Forwarder (IDF) for Splunk. Yes, compression is mandatory to effectively store all your measurements. I think another challenge is to have the value when you don't have an exact match for the timestamp. This can also happen is the acquisition of your 250 signals is not perfectly synchronized. That's when interpolation comes into play.

Answer 2

Depending on the frequency with which queries may be made against the data, I would suggest: Storing the data in a compressed format; and decompressing the data prior to querying.

Alternatively, if queries are too frequent for the above: store the data in uncompressed format; and compress data older than 60 hours at a frequency of your choosing.

Answer 3

I suggest you record the data point values as they come and timestamp them.

timestamp  point  value
1436285231 A      0
1436285231 B      2
1436285232 A      2
...        ...     ... 

You will be accumulating 270 Megabytes per 60 hours of data. If I got my estimates correct :)
((((250*32bits)*100ms)*36000ms)*60hrs) (not including timestamp and any other data you need to store)

To speed up your UI, Average the data per minimum interval that you will allow in your plots, and cache the result.

Answer 4

I would write everything, and then have a scheduled job to erase unchanged values and only have the points where data changes.

Like so:

Before:    After:
A B C D    A B C D
0 0 0 0    0 0 0 0
0 0 1 0    0 0 1 0
1 1 2 2    1 1 2 2
2 2 2 2    2 2 2 2
2 2 2 2    2 3 2 2
2 2 2 2    
2 3 2 2