I've been tasked with designing a backup solution to store sensor data from about 1500 sensors at 10 second intervals. In the event that the primary transmission method of the sensors goes down, my solution should not have a delay of more than 10 seconds since the actual update from the sensor. Thus making my solution the next source of data for them to consume.
The data that is generated is also stored in a file and I am supposed to read them as reference, rather than taking it directly from the sensors themselves.
I've been playing around with the numbers and I can't seem to reach that 10 second threshold. Considering that each file amounts to about 10 MB at the end of each month, I've got close 15 GB of data to process and import.
Currently I have been testing with 3 tables of 500 sensors each to store about 1 month worth of data and I keep hitting the 2 hours mark for processing using MySQL LOAD DATA INFILE method with temporary tables.
From here I can only assume that my methods and approach to this is wrong and was wondering if anyone could provide any kind of advice? Is it even possible to approach that kind of timing in the first place?
I have read the following questions for reference to get a better idea on how to approach this. Database redesign opportunity: What table design to use for this sensor data collection?
Currently using MySQL 5.7.28 for testing. No plans to move towards 8.0. Current structure is as follows:
CREATE TABLE `points0500` ( `DateTime` datetime NOT NULL, `Point0000` double DEFAULT NULL, `Point0001` double DEFAULT NULL, `Point0002` double DEFAULT NULL, ... `Point0497` double DEFAULT NULL, `Point0498` double DEFAULT NULL, `Point0499` double DEFAULT NULL, PRIMARY KEY (`DateTime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Database Configuration: my.ini
innodb_log_buffer_size=256M innodb_buffer_pool_size=2G innodb_log_file_size=256M
Sorry about the lack of details, not sure what else is important.
Current Test Server is running on a Xeon E3-1220, with 16 GB of RAM and two 1 TB HDD on RAID 1.
If the data was used for consolidated report, I am okay with the time taken. The issue with the timing is that they are using the data for something time-sensitive which currently I am not privy to. To the best of my knowledge, they will query the database for the latest readings every 10 seconds to get the sensor readings.