Hi everyone,
We’re in preparation stages for our very first Ignition deployment - I’d like to ask for your advice in regards to setting up MySQL as the historian database. Our project will consist of several thousand tags logging at sub 1 second resolution, so I’m expecting the database to grow quickly - the aim is to configure the system as efficiently as possible from the start.
Our Ignition SCADA server has available a fast NVME drive (~300 Gb) and a SATA 7200 RPM HDD (4 Tb). I’m aware of Ignition’s ability to configure partitioning at pre-defined intervals, but, is it possible to keep the last 6 months of data tables on the fast NVME drive, then automatically migrate the older (less accessed) data to the slower spinning drive? MySQL has an option to define the location of the data directory for each table but it seems that the database partitioning functions are all under the hood.
Thanks in advance - I warmly welcome your input and suggestions!
Partitioning in Ignition is done rather simply by creating new tables (f.e. creating one table per month or per week).
It doesn’t use under-the-hood partition methods of the different database systems, as those systems differ a lot between the different providers.
So if you know how to define the location of a table on disk in MySQL, you should be ready to go. Just put that in a periodical script and move the tables to the slower HDD based on the name of the table (which includes the date).
https://docs.inductiveautomation.com/display/DOC/Data+Partitioning+and+Pruning
1 Like
Thanks - this is the MySQL script I came up with in case anyone else is looking - seems to be working OK on the test bench. Will think about running it automatically at a later date - in the short term this would be manual maintenance
# The commands in this script are used for SCADA Ignition historian database table maintenance.
#
# The server is equipped with a fast NVME drive (C:\) and a spinning 7200 RPM disk (D:\).
# By default, a new database table is created each month on the C:\ drive., eg. "ignition.sqlt_data_1_2017_08"
#
# Once the data ages, moving the older (less frequently accessed) data to the D:\ drive ensures
# that the NVME drive does not run out of available space.
# STEP 00
# -------
# Perform a search and replace all on this file.
# search = [zzz_TableName_zzz]
# replace = table name, eg. "sqlt_data_1_2017_08"
# STEP 01
# -------
# Run this command to view the format of the table to be moved to the D:\ drive
SHOW CREATE TABLE ignition.[zzz_TableName_zzz];
# STEP 02
# -------
# Using the result of the command from STEP 01, rename table and index and add the {DATA DIRECTORY='D:/MySQL/Data'}
# The resulting command should be similar to this:
CREATE TABLE ignition.`[zzz_TableName_zzz]_d_drive` (
`tagid` int(11) NOT NULL,
`intvalue` bigint(20) DEFAULT NULL,
`floatvalue` double DEFAULT NULL,
`stringvalue` varchar(255) DEFAULT NULL,
`datevalue` datetime DEFAULT NULL,
`dataintegrity` int(11) DEFAULT NULL,
`t_stamp` bigint(20) NOT NULL,
PRIMARY KEY (`tagid`,`t_stamp`),
KEY `[zzz_TableName_zzz]t_stampndx` (`t_stamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DATA DIRECTORY='D:/MySQL/Data'
# STEP 03
# -------
# Copy the data from the current table into the new table created on D:\ drive
INSERT INTO ignition.[zzz_TableName_zzz]_d_drive SELECT * FROM ignition.[zzz_TableName_zzz];
# STEP 04
# -------
# Verify the data has copied successfully
SELECT * FROM ignition.[zzz_TableName_zzz]_d_drive;
# STEP 05
# -------
# Rename the original table to a backup file
RENAME TABLE ignition.[zzz_TableName_zzz] TO ignition.[zzz_TableName_zzz]_bck;
# Rename the new table as the original table name.
RENAME TABLE ignition.[zzz_TableName_zzz]_d_drive TO ignition.[zzz_TableName_zzz];
# STEP 06
# -------
# Once operation verified, drop the original table to free up disk space on C:\ drive
# DROP TABLE ignition.[zzz_TableName_zzz]_bck;