2

I have a MySQL database that will have 2000 new rows inserted / second. These rows indicate the value of a sensor at that particular moment.

The plan is to get this data into graphical representations using chart.js. Of course it would make no sense to show every data point and it would be a ridiculously slow query.

My question is, what is an appropriate route to take in a situation like this? Here is my idea:

Assuming the data resolution needed is running averages of the values for the day's 1 minute intervals, 15 minute intervals, 30 minute intervals, 1 hour intervals, and full day.

  1. Create a cron-job script that parses the values available between two time periods. Store the above intervals in 5 respective tables in a database separate from the raw values.
  2. Purge/archive the raw values from the production database as the cron-job script does its averaging job to determine the interval's values.

With this approach, things could be kept tidy and it would be easy to show the data in graphical representations. Here is where I see issues:

  1. If we are filtering the chart to look at day's values for the last month, we must check 31 tables for values to display.
  2. The issue increases exponentially as the resolution is made smaller & the period is made longer.

I might be too deep in the rabbit hole. I'll take any good reads you might have.

4
  • I wonder if this better suited to the dba forum
    – Strawberry
    Jan 11, 2020 at 22:56
  • What is the dba forum? I was kind of hesitant to post.
    – Sami A
    Jan 11, 2020 at 22:57
  • Not sure if the move to the DBA forum was right. The DB setup is fairly simple - partitioned tables with rolling windows, no indexes, no constraint. The hard part is the processing, voting to move back to SO! Jan 12, 2020 at 20:18
  • Don't worry about which forum; you have two people helping you here.
    – Rick James
    Jan 13, 2020 at 0:08

2 Answers 2

2

Few suggestions for your setup

DB Setup

  1. Use a temporary (staging) table to gather new records. Preferably you should range partition this table with the interval of your finest aggregation (e.g. minute) using the insert timestamp. Insert timestamp, contrary to the timestamp of the measurement (called sensor timestamp), is important to distinct - see the discussion below.

  2. Define the detail table with the same partitioning schema using as the partitioning key the sensor timestamp.

  3. Define the aggregated tables if required, partitioned on the the sensor timestamp. Check carefully what level you need. This is not defined by the requested reports, but with the performance you can query the data.

E.g. 30 minutes levels seem to be an overkill, as you can effectively query it from the 15 minutes level.

Processing

The background job running once per minute processes one partition of the stating table (i.e. one minute delta) and

  • inserts all rows in the detail table

  • updates all aggregated tables with the delta calculated from the stage table

You should be careful here. Counts, sums and averages are no problems, but for distinct counts you must use some implementation of HyperLogLog which will produce only estimated data.

You should also check if you must handle delayed data entries, i.e. case where in your delta are also data with older sensor timestamps. You must refresh all aggregation intervals relevant for those timestamp.

Example: In your process data inserted at 10:01 the oldest sensor entry is 09:59, so you must refresh three minutes aggregation (09:59, 10:00 and 10:01) and two 15 minutes aggregations (9:45 and 10:00) etc.

Daily Closing

To address the possible inaccuracy of the delta processing (see HyperLogLog above but including all other kinds), you can periodically throw away the last part of the aggregation and recalculate them exactly from the detail table.

This will eliminate the problem of "error accumulation" - this concept is also know as Lambda Architecture.

Keeping the History

All tables have a defined a rolling window policy - i.e. how long the data will be kept.

The aggregated tables are controlled by the reporting requirements.

The detail table is used only as a backup if something goes wrong, you have the (limited) possibility to recover the aggregated data. So set it so long as economically meaningful.

Old data is removed using DROP PARTITION only (not with delete).

You may apply a cycle partitioning for the stating table, i.e. you use only time as partition key, not the date component. This will simplify the partition maintenance - you will use TRUNCATE PARTITION instead to remove the data.

Reporting

Your aggregation will be done say once per minute, this means the lowest aggregate is always consistent. Contrary the last 15 minutes aggregate will be often incomplete, containing only few minutes of data.

You should design some normalizing concept to handle this problem. It is not nice to see that the number of successful events dropped significantly in the last quarter of an hour.

Good Luck!

2
  • IODKU is often an easy way to both update current data and delayed data, without having to have two pieces of code.
    – Rick James
    Jan 12, 2020 at 23:59
  • Don't worry about recalculating AVG and STD -- Instead, compute them in the SELECTs from SUMs and COUNTs (and SUM(x*x) for STD).
    – Rick James
    Jan 13, 2020 at 0:01
2

The main problems I see:

  • The size of the table containing the detailed data. It sounds like it will take 2-3 terabytes per year
  • The ingestion speed of 2000 rows per second. Are you using SSD drives?
  • Purging 'old' data -- big DELETEs are a big cost.

so...

  1. Do not store all the data in a database. You don't need it. Instead write it to a plain file if you really need to keep it.
  2. Collect the 120000 readings per minute somewhere (more later), summarize it, and store it in a table. (I'll give you more details if you give me more -- like how many different sensors and how frequent is each sensor read?)
  3. Now you are ready for any chart that is happy with a dot every minute. (Is that adequate granularity?) If necessary, adjust "minute" to something else (in step 2).
  4. At the same time, one or two more tables -- perhaps 1-hour and 1-day resolution. You don't need a lot. If you need a dot every 15 minutes, use the 1-minute resolution table and do suitable arithmetic. (Etc)

Will you be purging old data? If so, you probably need to worry only about the 1-minute table; the other tables are so tiny in comparison that you could keep them 'forever'. To do that, use PARTITION BY RANGE and base the number of partitions on the retention period. See http://mysql.rjweb.org/doc.php/partitionmaint . (DROP PARTITION is much faster, and less invasive, than the equivalent DELETE.)

High-speed ingestion -- If the above tips did not shrink the effort enough, then see http://mysql.rjweb.org/doc.php/staging_table .

More tips:

  • Have a PRIMARY KEY, but don't use AUTO_INCREMENT if there is an adequate "natural index".
  • Carefully size each column. Most real-life sensors have values that can fit in 8- or 16-bits. For example, weather temperature can use TINYINT SIGNED (1 byte) for temps to a resolution of 1 degree. To get a tenth or hundredth of a degree, it might be best to scale by multiplying by 10 and using SMALLINT SIGNED (2 bytes). Eschew FLOAT (4 bytes) and especially DOUBLE (8 bytes) unless really needed.
  • Normalize. That is, use a TINYINT UNSIGNED or SMALLINT UNSIGNED for the sensor_id; have another table mapping that to/from the name/location/bulky-id/etc of the sensors.
  • Minimize secondary indexes.
5
  • Would you consider recommending the MySQL Archive engine for this (or similar) tasks? +1 for a nice answer! Jan 12, 2020 at 16:28
  • @Vérace - Thanks. As for Engine=Archive, I used it once about 20 years ago; I have not found another use for it since then. Compressed, no indexes, etc, etc. But, rethinking in this context... I am pushing the OP to summarize rather than do huge table scans. Usually "old" data can be more and more summarized -- daily average for old sensor/stock/etc data; hourly average for recent data.
    – Rick James
    Jan 12, 2020 at 23:36
  • And the suggestion about writing to flat files is aimed at 'hoarders'; it gives them a way to reparse old data in some new way "just in case". Meanwhile, i would guess that Archive give 5x improvement on compression than InnoDB's ROW_FORMAT=COMPRESS. But my "flat files" could also be compressed and comprable to Archive. I guess I don't see enough benefit of Archive over other possibilities.
    – Rick James
    Jan 12, 2020 at 23:41
  • I guess it depends on the OP's requirements? At least with Archive, he doesn't have to take the flat files out of "cold storage" and put back into a database to query them! That was my motivation for suggesting that route - HMMV! The tables can always be converted to text using mysqldump when the next NoSQL data analytic fad arrives! Jan 13, 2020 at 5:42
  • "Fad" -- Yes. To argue for or against a BigData solution or algorithm, I simply "count the disk hits". That is the main determinant of performance.
    – Rick James
    Jan 15, 2020 at 2:47

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.