Soon after startup I see a some queries like the below at the DB server, they take long time to execute and lead to massive temporary files, this either overs flow the RAM drive or stall the C: drive depending on where I place the temporary files, what is propose of this query, can it be modified?
OS = Windows Server 2019
Ignition Version = 7.9.13
DB = MariaDB 10.4
SELECT a.`tagid`,a.`intvalue`,a.`floatvalue`,a.`stringvalue`,a.`datevalue`,a.`t_stamp`,a.`dataintegrity`,b.`datatype`
FROM sqlt_data_1_2020_03 a, sqlth_te b
WHERE b.`id`=a.`tagid` and a.`t_stamp`>=1583035845923
ORDER BY a.`t_stamp` ASC
LIMIT 250000
This happens quit early on in the gateway boot, before any clients get loaded, I suspect it is an internal Ignition house keeping task, with this in mind I have disable the the History pruning, but no change, I then disabled the Pre-processed Partitions and have not seen any of these quires over three boot cycles now, tomorrow I will re-enable this on one of the larger history DB’s and see if the issue reappears.
It would be nice to know just what this query is used for, so I can have some confidence that what I have done here will give reliable results, or have I just been lucky this time?
After more research I found this is caused by the Pre-processed Partitions option in the historical tag provider setup, with this feature disabled on all history providers, these queries have ceased.
In the past we used to run this project on Linux, but the customer requested we move it to Windows, I just checked one of the existing production Linux DB’s and found several queries each one running for 10s of minutes and producing Gigabyte temporary files, but the machine just continues to operate without becoming paralyzed, so never realty noticed, although it would account for why Pre-processed Partitions database tables never seemed to be created for some providers, so long story short, if you DB is unexpectedly eating all your disk I/O try disabling Pre-processed Partitions.