2

Just a few words describing the data: In my application, there are acceleration measurements (for example at 25kHz) for the exemplary duration of one second. These measurements get repeated in not necessery äquidistant time steps for that measurement point. (Maybe each five or ten minutes). This is a kind of interrupted permanent monitoring, somehow two-periodic:

  • short time period of measurement is 25.000 Hz (the resolution of the measurements)
  • long time periodic (not in a strong sense, might differ) every 5 minutes

There are 20 or more of those points.

As dealing with time series, the first idea might be the usage of a time series db. On the other hand, for me it seems as if the main purpose of time series db is storage of scalar values. Of course, my measurements are scalar values. But I'm not sure if it would be a good idea to store every scalar value as a (time/value/measpos_id)-triple - leading to an enormous number of entries. I think single of those entries would never be evaluated.
Another idea could be the storage of the measurement vector (all values from that second) together with the starting time and the measpos_id. But howto do that? Taking all values as a blob? Not every system is capable of dealing with vectors - and maybe they differ in length. Are there concepts in timeseries-db for such problems, which I'm not aware of?
Further for evaluation (extraction) I think maybe the exctraction of the complete vector would be the most used case.
Please feel free to ask, if my description is incomplete or some more details could help in finding a good solution.
What are your recommendations? NoSQL or relational SQL? Further ideas? Every hint is welcome. Thanks in advance.

additions:

  • A rough idea for the volume is steady growing in size of about 1 TB a year
  • Giving a sample is not that easy - I'll try to describe:
    Think of 1 column with 25000 float values for each measurement (each minute rougly and for each measurement position), timestamped each of these columns (at begin).
  • Usage for big data evaluation (means testing many kind of algorithms); windowing data, fft (spectral analysis), comparison, aggregation like energetic sum, value of max amplitude, pos (freq) of max amplitude, many more
  • purpose (focus) of evaluation: wear detection for condition monitoring of for example rolling devices (gears, generator sets, turbines, shafts, bearings)
  • evaluation would (from todays view) focus on each seperate column and maybe compare to others - but not combine (stack) columns together.
  • data size example: 25.000 float values in each column for 20 measured engines each 5 minutes (12 per hours) results in 6e6 floats each hour or 5.25e10 floats each year.
  • 2
    Can you provide a sample of your data? Do you need to record an actual datetime it occured? How many time series? Anticipated volume and rate if growth? – Sir Swears-a-lot Jul 3 '17 at 8:36
  • 2
    What do you want to do with data once it is stored? Aggregate? Plot? Compare? – Sir Swears-a-lot Jul 3 '17 at 8:38
  • @Peter - I'll answer your good questions within an edit to the question. – Bastian Ebeling Jul 3 '17 at 8:45
  • 2
    Are the recorded values analysed independently? Or are you intending to combine them to provide a long dataset over time? – Sir Swears-a-lot Jul 3 '17 at 8:56
  • @Peter: Jes - monitoring the whole Vibration would take too much data (in this case 300-times the amount). Interrupted permanent Monitoring of engines (or similar) by measuring burst of accelaration as samples from the complete. Somehow two-periodic: short time period of measurement is 25.000 Hz (the resolution of the measurements) and long time periodic (not in a strong sense, might differ) every 5 minutes. Hoping it is clearer now? – Bastian Ebeling Jul 3 '17 at 9:44
1

I can suggest Akumuli. It's a time-series database that supports compression and high-throughput data ingestion. With 25KHz measurement frequency and 20 engines, you will need to write 500K data points per second in the worst case. Akumuli can handle an order of magnitude larger throughput (largest throughput ever recorded is around 16M data points per second).

Also, because of compression, the database needs only around 3-9 bytes per data point. Each data point is a timestamp with nanosecond precision + 64-bit floating point value. There is an automatic data retention that deletes old data only if there is not enough disk space to store the new data.

You can store data from each engine in the same time-series or you can create new time-series per burst.

The real time-series database can be a big win because you won't need to use all these fancy tricks. There is a downsides of cause. E.g. there is no clustering and backfill.

Disclaimer: I'm the author so I'm a bit biased.

1

I believe this would be possible with a relational database but throughput will be an issue. SQL Server In Memory Optimised Tables could be very useful for this.

The best form of storing the data will be trade off between simplicity and storage efficiency.

Given the sheer volume of data, If the observations were taken at the same second. I think it would make sense to save each engine in its own column. This would result in 25,000 rows rather than 500,000 rows for 1 seconds worth of data.

Edit: However as the observation time will vary then it would make more sense to store each series in its own row. Although this will result in a massive volume of data, Storing each observed value in its own field will make reporting and analysis much easier. However long term I don't think this would be feasible.

The next challenge is then being able to make use of this data. Realistically a human can't interpret that volume of raw data. And so Aggregating data would make sense, So for a given sample record the max, min, avg, std dev etc.

  • Hi @Peter, interesting answer. Thank you. First the sad detail: observations on different engines are not taken at the same time. But I'll neglect that first. Just for me and better understanding: You think of a table with 20 columns (each engine one) and additional the timestamp. And each row (2.628e9 after one year) representing one scalar value? I think querying might be complicated - as getting all 25000 values from one observation - where to start? – Bastian Ebeling Jul 3 '17 at 10:43
  • If the samples were taken at the same time the advantage would be that the data would be quite densely packed. i.e 20 columns full of data for a given point in time, rather than lots of null/empty fields. – Sir Swears-a-lot Jul 3 '17 at 21:08
  • 1
    Although I'm an Oracle & SQL Server DBA I also work with time-series product called Hilltop: hilltop.co.nz which uses files. It gets about 4x better compression that SQL Server. And you could easily split out engines into separate files. It also supports integration with R Statistics. Although it's origins are in hydro/meteorology it is also used by power & utility companies and engineering projects. We use it for storing and analyzing SCADA data for operating a treatment plant. I think it would be very good for the scenario you describe. – Sir Swears-a-lot Jul 6 '17 at 4:37

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.