Database design for large amount of sensors at very fast intervals

Question

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?

Edit 1

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.

Edit 2

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.

Answer 1

150 new rows per second? At least one of these needs to be true:

• Using SSD, not HDD.
• Batch loading (LOAD DATA, INSERT with multiple rows, etc)

Other notes:

• Don't use DOUBLE. Double takes 8 bytes and has far more precision than any sensor can provide. This is important because the size of the data, as you noted, is important. FLOAT gives you about 7 significant digits in 4 bytes, ample for virtually all sensors. You could even consider DECIMAL or various *INT types, perhaps with scaling, to save even more space.
• How much RAM do you have? (I'll critique your my.ini settings after getting the RAM size.)
• What is the problem with taking 2 hours to load 720 hour's worth of data?
• What will you do with the data? There are several directions I will point you, depending on what kinds of queries you will apply to the data. Some will make a significant improvement in speed and/or space.

Back to your question:

I've been tasked with designing a backup solution to store sensor data ... in the event that the primary transmission method of the sensors goes down.

If that is the question, the answer is trivial: A flat file, in the same format as the raw data comes in. That way, when recovering from the outage, you focus on getting data from a different source, but not a different format.