0

I need to create a new database, recording data for around 1500 sensors.

I need to store hourly data, and keep a rolling database for the past 100 days.

My first thought was to create a database with one row for each sensor (so around 1500 rows), and one column per hour - so 2400 columns.

Reading this other SO question (Too many columns in MySQL), this is clearly a bad idea!!

So how can I best organise and set up my table, to store 2400 data points for each of 1500 sensors, while creating a manageable table that is efficient?

  • What are these measurements? How many measurements per sensor? What, exactly, are you measuring? – Vérace Nov 22 '17 at 12:12
  • We are measuring hourly data. One measurement per sensor per hour. We are measuring a variety of data. The type of data is not important. It is just a number. The question should be fairly clear. We need to record 24 hours per 100 days (so 2400 pieces of data) for 1500 different sensors. – Richard Nov 22 '17 at 12:19
2

Rolling 100 days -- I recommend using PARTITIONing and break it into weekly partitions. It will be about 17 partitions. Details: http://mysql.rjweb.org/doc.php/partitionmaint . DROP PARTITION is much faster than DELETE.

It is possible to map between "hour" and "datetime" (or "timestamp") with some arithmetic involving FLOOR( ... / 3600). Then that will fit nicely in MEDIUMINT UNSIGNED and replace 3 of Vérace's columns.

Don't have an AUTO_INCREMENT if you have a 'natural' PK. Suggest

PRIMARY KEY(sensor_id, hour)

and

PARTITION BY (hour)

You may, but probably won't, need

INDEX(hour, ...)
  • I revised my post in the light of your comments - you were totally correct on the PK - no need for a surrogate! – Vérace Nov 22 '17 at 21:03
0

What I would do in this case is to have a simple structure with the potential for rapidly querying the data.

If I've understood you correctly, you have 1500 (no. of sensors) x 24 (hours per day) x 100 (retention time) = 360M records. This is not very small, but not exactly large either by today's standards.

What I would recommend is something like (db-fiddle here - DDL and DML also at the end of the post):

CREATE TABLE sensor_reading
(
  sensor_id         SMALLINT UNSIGNED NOT NULL,
  reading_datetime  DATETIME NOT NULL,
  reading_type      VARCHAR (50) NOT NULL, -- pressure, temp, altitude
  reading_value     INTEGER NOT NULL, -- could be FLOAT? not sure of exact values - make it as big as you think you need.
  reading_unit      VARCHAR (25) NOT NULL, -- if using S.I. possibly redundant.
  reading_date      DATE GENERATED ALWAYS AS (DATE(DATE_ADD(reading_datetime, INTERVAL 30 MINUTE))) VIRTUAL,
  reading_hour      TINYINT UNSIGNED GENERATED ALWAYS AS (EXTRACT(HOUR FROM (DATE_ADD(reading_datetime, INTERVAL 30 MINUTE)))) VIRTUAL,
    UNIQUE KEY sensor_hour_ix (sensor_id, reading_date, reading_hour),
    INDEX reading_value_ix (reading_hour)
);

This will only work on MySQL 5.7. On earlier versions, you can use TRIGGERs to fill in the reading_date and reading_hour fields - this time it will have to be actual stored data, rather than VIRTUAL (i.e. GENERATED) fields.

A good reason to have reading_hour is to prevent problems due to small granularity variations in your sensors' timings (see DDL - INSERTs). If you're going to load them all at once in the same transaction (per hour) and/or specify an exact time each and every time, then this shouldn't be an issue! You can look at DATETIME vs. TIMESTAMP in MySQL here.

The reason I have made the choice to "break out" the hour and date into separate fields is so that queries are v. easy and groupings can be done without much calculation on the client end!

This functionality is provided free and gratis by the server and you should take advantage of it - the calculation will always be correct - and you'll never type in 36000 instead of 3600 or similar error when extracting the hour and/or date! Even using triggers, with storage being relatively cheap nowadays, this is not a big cost to bear, and none at all for VIRTUAL columns.

You may find other indexes helpful depending on your queries - but you can probably get a fair bit done with the outline above!

@RickJames ' suggestion regarding the PRIMARY KEY is correct - using an AUTO_INCREMENT field here was the wrong way to go. I'm less sure about his partitioning scheme - personally, I would store "old" (i.e. > 100 days) in the ARCHIVE engine and I would copy it off using an event fired every day at, say, 03:00 or other time when there's not much activity on the server (+ remove it from the live table). It has been my experience that you never know when you might require old data and old trends can be useful - this way they are stored but won't take up a huge amount of space.

=================== DDL and DML ==================

CREATE TABLE sensor_reading
(
  sensor_id         SMALLINT UNSIGNED NOT NULL,
  reading_datetime      DATETIME NOT NULL,
  reading_type      VARCHAR (50) NOT NULL, -- pressure, temp, altitude
  reading_value     INTEGER NOT NULL, -- could be FLOAT? not sure of exact values - make it as big as you think you need.
  reading_unit      VARCHAR (25) NOT NULL, -- if using S.I. possibly redundant.
  reading_date  DATE GENERATED ALWAYS AS (DATE(DATE_ADD(reading_datetime, INTERVAL 30 MINUTE))) VIRTUAL,
  reading_hour TINYINT UNSIGNED GENERATED ALWAYS AS (EXTRACT(HOUR FROM (DATE_ADD(reading_datetime, INTERVAL 30 MINUTE)))) VIRTUAL,
  UNIQUE KEY sensor_hour_ix (sensor_id, reading_date, reading_hour),
  INDEX reading_value_ix (reading_hour)
);

-- I have chosen to use +30 mins as the cutoff for the time, 
-- you many find another interval to be better suited to your 
-- requirements. It is arbitrary, but I'm assuming that a sensor
-- that measures by the hour might be a minute or two off, but
-- not > ~ 30 mins.

INSERT INTO sensor_reading 
(sensor_id, reading_datetime, reading_type, reading_value, reading_unit) VALUES
(1, '2017-11-03 15:00:01.5', 'Temp', 12, 'Celsius'), -- should be hour 15 on the 11th.
(1, '2017-11-03 16:00:01.5', 'Temp', 14, 'Celsius'), -- should be hour 16 on the 3rd.
(2, '2017-11-04 15:59:01.5', 'Temp', 13, 'Celsius'), -- should be hour 16 on the 4th.
--
-- Now, datetimes around midnight
--
(1, '2017-11-03 23:59:01.5', 'Temp', 12, 'Celsius'), -- should be hour 0 on the 4th.
(5, '2017-11-03 00:00:01.5', 'Temp', 14, 'Celsius'), -- should be hour 0 on the 3rd.
(7, '2017-11-03 23:59:01.5', 'Temp', 12, 'Celsius'), -- should be hour 0 on the 4th
(2, '2017-11-04 00:59:01.5', 'Temp', 13, 'Celsius'); -- should be hour 1 on the 4th.

============ Results =========================

sensor_id   reading_datetime    reading_type    reading_value   reading_unit    reading_date    reading_hour
1   2017-11-03 15:00:02     Temp    12  Celsius     2017-11-03  15
1   2017-11-03 16:00:02     Temp    14  Celsius     2017-11-03  16
2   2017-11-04 15:59:02     Temp    13  Celsius     2017-11-04  16
1   2017-11-03 23:59:02     Temp    12  Celsius     2017-11-04  0
5   2017-11-03 00:00:02     Temp    14  Celsius     2017-11-03  0
7   2017-11-03 23:59:02     Temp    12  Celsius     2017-11-04  0
2   2017-11-04 00:59:02     Temp    13  Celsius     2017-11-04  1
  • 1
    360M rows is enough to be more careful about unnecessarily bulky columns... Combine the type and units into a single ENUM (or TINYINT UNSIGNED or lookup table). – Rick James Nov 22 '17 at 13:18
  • A TINYINT "hour" won't suffice -- need at least SMALLINT UNSIGNED to hold 24*100. – Rick James Nov 22 '17 at 13:22
  • @RickJames - no to ENUMs. As for the hour, I meant hour per day (1 - 24) and not for the entire period. If the OP decides to keep older data, those hour measurements from an arbitrary start time will be meaningless. – Vérace Nov 22 '17 at 14:00
  • Then sensor_hour_ix can't be UNIQUE. OP wants 100 days. – Rick James Nov 22 '17 at 18:55
  • DIV 3600 can last as long as DATE or TIMESTAMP (and fit into 3 bytes). – Rick James Nov 22 '17 at 18:56

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.