I'm writing a metallurgical web application, that must store data (electrical parameters) recorded at different frequencies. For example:

  • six parameters recorded with frequency of one measure per second,
  • three parameters recorded with frequency of ten measures per second.

(all parameters are decimals with six digits following the decimal point -- DECIMAL(10,6)).

How should I design database structure to store such values efficiently? And to be able to query this kind of datasets easily for presenting them on any kind of graph?

Possibilities (ideas?), I came so far with:

  1. Store both parameters sets in two different tables, with one record per corresponding time unit, store all parameters in columns declared as above (decimals), and query one record from first table and ten records from second table per each point on drawn graph. That would make 11 records per second.

  2. Store all parameters in one table, with one record per one second, store each parameter from first group in decimal columns and each parameter from second group as an array of decimals, and query one record from this table only per each point of drawn graph. That would make 1 record per second.

  3. Something different (if both above are wrong).

I have no idea which solution would be better in terms of better performance and lesser data redundancy.

First solutions seems to consume much more database space, because you have eleven records per each second of measures (and we can deal even with thousands of devices getting their measures each second), but in the same time seems to be faster on parsing, because you have direct access to each value in each record and in each column. While, in second case, you should get much lesser database size (cause we have one record per each second and each device) but parsing this data seems slower, because you have to parse array of ten values per each second and each parameter measured with 1/100 ms freq.

If my (above) assumtions are correct, then this question narrows to answering, what is more important in today database design -- database size or parsing time? For low-cost solutions (like shared hosting or low-hardware VPSes) I'd assume, that database size matters far more and therefore I'd go into second option.

I'm mainly focused on using MySQL, but I can accept nearly any other kind of database, if there would be any advantage of using different RDBMS.

Additional information:

  1. There is no need (at least in my case) to store in DB microtime in case of second group of paramteres (i.e. those, that are recorded with 1/100 ms frequency). They only need to be correctly linked to that "second" (timestamp) under which other parameters (recorded with 1/1 s frequency) are stored.

  2. There is no need to store each parameter in a separate record in database. As my ideas shows, all parameteres can be stored in one record per each measure.

  • 1
    Is the rate high enough to require in-memory database? – rwong May 22 '15 at 7:25
  • Nope, I don't think so. Data will be recorded with 1/s or 10/s freq. by a mobile reader, but it will be pushed to server and recorded into database with frequency around 1 write per 5-10 seconds. – trejder May 22 '15 at 8:18
  • 1
    Are you planning to do reporting/graphs directly from SQL or are you fine with doing programmatic post-processing of data to make them fit for reporting? – Euphoric May 22 '15 at 12:15
  • @Euphoric I'm completely fine with post-processing of data retrieved from database before it is actually rendered as a graph. This is even necessary in this case, as data will be provided by API means and rendered into graph by an end-point client, after whatever post-processing it needs. – trejder May 22 '15 at 13:25
  • 1
    (1) Do you actually need these values in a database? Have you considered putting them in flat files? (Write performance will be great; files can be simply removed when you need to discard old data.) Then you can insert preprocessed, aggregated data into a table to simply reporting. (2) If you need a more robust solution, you can consider time series databases. – Marek Grzenkowicz May 28 '15 at 21:28

Probably the simplest solution is to collect the 1/10th second data in cache and write them with the 1 second fact when it arrives. Storing both values at the same time removes the complexity of dealing with summary tables and the related cleanup tasks. A good way to handle the 1/10th value is to add all the samples and divide by the # collected when the 1 second fact arrives. You can also keep other statistics of interest on the 1/10th fact such as min, max, stddev, and etc depending on the value of the 1/10th second values for information like spikes. A sample table design might be: fact 1 is the 1 sec fact 2 is the 1/10th sec timestamp fact_1 fact_2_avg fact_2_min fact_2_max fact_2_count fact_2_stddev

After this is in place, you might consider the granularity of the data you need over time and consider rolling both values up before writing them to the database so you might only write every minute or 5 minute or some time that is configured in the table.

Also, as the system runs for months or years you may consider if you need to know what the value was on 3/15/2012 12:03.55. If you don't need it, consider summary levels like you might see in MRTG where you have a live view for 24 hours, a 5 minute summary at a weekly view, then a 1 hour view at 30 days, and etc. This allows you to keep data and maintain fast performance and monitoring of trends without having the query through 31 million of rows of data per year.

Another trick for dealing with data like this is to use a table that is a 24 hour rolling table. Your key just uses hour, minute, second and no date. This way you have a free rolling 24 hour table. Just watch out for gaps (clean data between your last sample and this one so you don't have old data between your inserted values if you miss a second or two while collecting data). Think of it as an update instead of insert and you'll understand how this table works.

  • Well... quite an interesting point of view on the matter, that I never thought about. I don't know, if I'm going to push this the way you suggest, but the idea of storing only stats info (min, max, avg etc.) instead of real values for 1/10th values really need further examination. I'm accepting your answer as very interesting one. However, I might unaccept it, if something better ever arrives. – trejder May 29 '15 at 8:50

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.