Handling Big Data

Question

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.

Answer 1

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!

Answer 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.