I have a system to store and display the MQTT Messages that came from IoT to Server. The data comes in every 5 seconds and there are multiple data types in a single message. Therefore, I have thought that I can create new tables when a new message comes based on the IoT device serial number. Here is my issue that every day I receive around a million data per device and I have hundreds of devices. So what is the best way for me to store this data and create graphs when queried?
To have a more clear picture for you here is my table structures:
CREATE TABLE IF NOT EXISTS `mqttpacket_',device_serial_number,'`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`data_type_id` int(11) DEFAULT NULL,
`data_value` int(11) DEFAULT NULL,
`inserted_time` TIMESTAMP(6) NOT NULL DEFAULT NOW(),
PRIMARY KEY(`id`),
FOREIGN KEY(data_type_id) REFERENCES datatypes(id))
COLLATE='LATIN1_SWEDISH_CI' ENGINE = InnoDB;
Here is my stored procedure:
CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_NewTest`(
IN `incoming_data` TEXT,
IN `value_array` TEXT,
IN `device_serial_number` VARCHAR(50),
IN `data_timestamp` VARCHAR(50),
OUT `result_id` INT,
OUT `result_message` VARCHAR(500)
)
BEGIN
DECLARE value_iteration VARCHAR(50);
DECLARE lcl_data_type_id INT;
DECLARE arr_data_type_name VARCHAR(50);
DECLARE affected_data_row_count INT;
DECLARE lcl_insert_mqtt_query VARCHAR(500);
DECLARE create_table_query VARCHAR(500);
DECLARE exit handler FOR 40001
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
SET result_id = -2;
SET result_message = CONCAT('RETURNED ERROR NO : ', @p1 , '\r\nMESSAGE TEXT : ',@p2);
ROLLBACK;
END;
DECLARE exit handler FOR sqlexception
BEGIN
GET DIAGNOSTICS CONDITION 1
@p1 = MYSQL_ERRNO, @p2 = MESSAGE_TEXT;
SET result_id = -999;
SET result_message = CONCAT('RETURNED ERROR NO : ', @p1 , '\r\nMESSAGE TEXT : ',@p2);
ROLLBACK;
END;
START TRANSACTION;
SET affected_data_row_count = 0;
SET result_id = 0;
SET result_message = 'Success';
IF(SELECT count(id) FROM devices WHERE devices.serial_number = device_serial_number) > 0 THEN
SET create_table_query = CONCAT('CREATE TABLE IF NOT EXISTS `mqttpacket_',device_serial_number,'`(`id` int(11) NOT NULL AUTO_INCREMENT, `data_type_id` int(11) DEFAULT NULL, `data_value` int(11) DEFAULT NULL,`inserted_time` TIMESTAMP(6) NOT NULL DEFAULT NOW(), PRIMARY KEY(`id`), FOREIGN KEY(data_type_id) REFERENCES datatypes(id)) COLLATE=\'LATIN1_SWEDISH_CI\' ENGINE = InnoDB;');
PREPARE stmt FROM create_table_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
WHILE (LOCATE(',', value_array) > 0)
DO
SET arr_data_type_name = SUBSTRING_INDEX(value_array,',',1);
SET value_array = SUBSTRING(value_array, LOCATE(',',value_array) + 1);
SELECT JSON_EXTRACT(incoming_data, arr_data_type_name) INTO value_iteration;
SET arr_data_type_name := SUBSTRING_INDEX(arr_data_type_name, ".", -1);
IF (SELECT COUNT(id) FROM datatypes WHERE datatypes.data_name = arr_data_type_name) > 0 THEN
SELECT id INTO lcl_data_type_id FROM datatypes WHERE datatypes.data_name = arr_data_type_name LIMIT 1;
ELSE
INSERT INTO datatypes (datatypes.data_name,datatypes.description ,datatypes.inserted_time) VALUES(arr_data_type_name,arr_data_type_name,NOW());
SELECT id INTO lcl_data_type_id FROM datatypes WHERE datatypes.data_name = arr_data_type_name LIMIT 1;
END IF;
SET lcl_insert_mqtt_query = CONCAT('INSERT INTO mqttpacket_',device_serial_number,'(data_type_id,data_value,inserted_time) VALUES(',lcl_data_type_id,',',value_iteration,',\'',data_timestamp,'\');');
PREPARE stmt FROM lcl_insert_mqtt_query;
EXECUTE stmt;
SET affected_data_row_count = affected_data_row_count +1;
END WHILE;
SET result_id = 0;
SET result_message = CONCAT('Query performed successfully. Effected rows : ',CAST(affected_data_row_count AS CHAR));
ELSE
SET result_id = -1;
SET result_message = 'Customer Serial Number not found.';
END IF;
COMMIT;
END
I basically get the serial number of the device and if their tables don't exist in the database, create the table for them and parse the JSON to insert into tables inside the stored procedure.
CREATE TABLE `devices` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`serial_number` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
`device_type_id` INT(11) NULL DEFAULT NULL,
`customer_id` INT(11) NULL DEFAULT NULL,
`inserted_time` TIMESTAMP(6) NOT NULL DEFAULT current_timestamp(6),
PRIMARY KEY (`id`) USING BTREE,
INDEX `device_type_id` (`device_type_id`) USING BTREE,
INDEX `customer_id` (`customer_id`) USING BTREE,
CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`device_type_id`) REFERENCES `templaritest`.`devicetypes` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT `devices_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `templaritest`.`customers` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
CREATE TABLE `devicetypes` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`device_type_name` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
PRIMARY KEY (`id`) USING BTREE
)
CREATE TABLE `datatypes` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`data_name` VARCHAR(50) NOT NULL COLLATE 'latin1_general_ci',
`inserted_time` TIMESTAMP(6) NOT NULL DEFAULT NOW(),
`description` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`device_type_id` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `device_id` (`device_id`) USING BTREE,
CONSTRAINT `datatypes_ibfk_1` FOREIGN KEY (`device_type_id`) REFERENCES `test_database`.`devicetypes` (`id`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
CREATE TABLE `customers` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`customer_name` VARCHAR(50) NOT NULL COLLATE 'latin1_swedish_ci',
`inserted_time` TIMESTAMP(6) NOT NULL DEFAULT current_timestamp(6),
PRIMARY KEY (`id`) USING BTREE
)
I would be grateful if you can suggest to me how can I optimize the database based on the Size and the query's efficiencies.
