0

I have a server which records various sensor data each second and inserts it into a MySQL table. To view the sensor data on a dashboard I have a chart which also gets updated with live data each second. The database is growing at a rate of more than 250,000rows / 24h (3 sensors+).

When loading the dashboard I want to display history data. At least 24h, more would be better (72+ ideal); my testings are all done with 24h.

The amount of data is huge (86400 rows), so I though of selecting only a subset of data for the history, which can be refined on zoom. I chose an interval of 5 minutes, or 300 seconds:

SELECT * FROM `data` WHERE `sensorId` = 0
 AND `unixDate` >= (1458829800 - 86400)
 AND `unixDate` < 1458829800
 AND NOT `unixDate` % 300 -- left out for all data
ORDER BY `id` DESC

This query is of course amazingly slow, on the damn small machine I'm running this on (a raspberry pi) it takes more than 10 seconds!

Querying all the 86400 rows takes roughly below one second.

I might be able to install a separate database server on a heavy machine as well, but I'd like to get this project done before my server environment is up and running, also I don't want to send so much data around my network each second...

I wonder two things: is there a quicker way of querying data by time intervals and might there be a completely better solution (time series db, which seemed a bit overkill to me) for this kind of work?

1

You should create a summary table that will store this summary data.

5 minute summaries will require additional 288 rows per day (24 * 60 / 5); 30 minute summaries - 48 rows per day (24 * 60 / 30); hourly summaries - 24 rows per day. In total, you're looking at storing additional 360 rows per day (288 + 48 + 60), which is a tiny overhead (360 / 86400 ~= 0.4167%) for a massive performance boost.

Try something like this:

CREATE TABLE data_summary (
    granularity_mins INT NOT NULL,
    unixDate BIGINT NOT NULL,
    sensorId INT NOT NULL,
    sensor_min_value DECIMAL NOT NULL,
    sensor_max_value DECIMAL NOT NULL,
    sensor_ave_value DECIMAL NOT NULL,
    sensor_readings_count INT NOT NULL,
    PRIMARY KEY(granularity_mins, sensorId, unixDate)
)

You can then get your 5 minute summary with a SELECT like this:

SELECT *
FROM data_summary
WHERE granularity_mins = 5
    AND unixDate > 1458829800 - 86400
    AND unixDate < 1458829800
    AND sensorId = 0
1

In addition to what @Serge says...

For the first query... If your typical query is for a single sensor, then use InnoDB and have PRIMARY KEY(sensorId, unixDate). This way, selecting every second will get consecutive (clustered) records, rather than skipping over the records for the other sensors.

Get rid of the surrogate id; the PRIMARY KEY I suggest will do just fine, plus save space and time. And, anyway, you want ORDER BY unixDate, not BY id.

Pick reasonable dataypes, so as to shrink the records and avoid I/O. For example, unixDate should be a 4-byte INT, not an 8-byte BIGINT.

Yes, summary tables is the way to go for any Data Warehouse application.

What granularities do you need? Let's say you don't need anything smaller than 1 minute. A 1-minute summary table will be nearly 1/60th the size of the 'Fact' table, and scanning it for 5-minute interval (or 1-hour or whatever) will still be nearly 60 times as fast as what you have now. Give that a try first, before including multiple granularities in the table.

Mathematics... If your data is always evenly spaced (every second), then the "average of averages" is mathematically correct. If not evenly spaced, then summarize the SUM and COUNT and compute the average = SUM(sums)/SUM(counts).

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.