I’m having some major issues importing a large amount of history and thought I might post my approach to see if anyone has some suggestions or alternative solutions.
I’m importing a year of history for about 300k+ tags which equates to 60+ million records.
My Ignition historian already has a few months of history, and is configured with default settings of 1 month partitions.
My initial approach was to read the data from the daily CSV files and run all the records through system.tag.storeTagHistory.
Since storeTagHistory allows to insert multiple history records at a time, I assumed going in that I’d have to test the optimal number of records to import per call, but that was just the beginning of my problems.
I eventually found that importing storeTagHistory with many records was more efficient that importing 1 record per call, so I set it to run the import for every 1k records.
My first issue is that the Store & Forward Buffer would fill up really fast and records would get dropped, so I increased the Disk Cache size by quite a bit to 100,000 and tried again.
This time, not only did the Disk Cache fill up again, but the Store & Forward process of writing to the database from the Disk Cache got very slow.
IA Support explained that there are issues with the HyperSQL database getting this large, so we lowered the max size to 25,000 records and added a sleep delay mid script to avoid filling it up.
Next problem was that when importing history, the realtime values would stop historizing. Which I figured out is because they too have to go through the same Store & Forward engine and will be placed in the back of the large Disk Cache queue.
So, the approach to this was to create a new Historian in my gateway dedicated for importing history.
This seemed to work well, but the throughput was still pretty limited by the single threaded nature of the Store & Forward Engine.
So, I’ve since added 3 more Historian’s in my gateway and my script now loops through to spread the load across all 4, storing 1k tags per connection, and sleeping every 4k tags for a second.
The next problem I ran into is that sometimes my records would get quarantined due to duplicate records.
Because of my previous partially successful attempts to import history, I had some of the records already imported, but couldn’t determine which were already imported and which had failed.
I was hesitant to just wipe the partitions due to the amount of time I had already put, and the amount of execution time on the gateway to get the records that I had so far.
At this point, working with support, I learned of the most frustrating behavior in this whole process.
Regardless of how storeTagHistory is called, Ignition batches multiple records to be inserted to the database per query for performance.
So, if any record in a batch is a duplicate, the entire batch will get quarantined.
My solution to this issue was to import all of my history to another database table, add a flag to indicate if it had previously been imported, and run a query to join my “history import staging” table with the history partitions and update the flag if it was already in the historian.
My script then imports records from the history import staging table that don’t have the imported flag set.
This seems to be mostly successful, but since that history import staging table is so large, all of the queries are very slow.
All-in-All this seems like a very complex solution for a problem that is fundamental to SCADA and I hope there is something I’m missing.
Thanks for your time.
-Trent