As our gateway has been growing our historian is getting incredibly slow. If I try to query more than 4 hours of data for a single analog tag the easy chart component will time out.
I’m only historizing 465 tags, the majority are set for a 1 second scan class. All the others are set for 10 seconds. We started using Ignition in 2019. The sqlth_te table has 27,926,615 rows. Does that seem excessive?
Randomly in the logger I see queries show up, and the SELECT statements are massive, which I think is the cause of my issue. But don’t understand why it is happening. How long should a record in the sqlth_te last for before it is retired?
The sqlth_te table should only be getting updated if you make a change to a tag (Not sure if all tag changes insert a new record, or only those that are changing the scan class, data type, or query mode), so that row count seems extremely excessive
How many sqlt_data_* tables do you have?
Do you have ignition setup to do automatic partitioning?
You should probably contact Support about this. The te table should typically not be growing that large. Support can assist in identifying the likely cause.
Are you changing tag history settings on the tags automatically anywhere?
I have a support ticket open. I’ll update it with a reference to this post.
I just turned on data pruning to drop off everything older than a year. I was hoping to see the sqlth_te table get cleaned up, but that doesn’t appear to be what happens. The partitioning settings are the default settings, which is 1 month. So currently I have just 13 sqlt_data_* tables.
The sqlth_te table doesn’t get pruned. Simply put, the table is storing static information needed to determine what history data is from what tag. When tags storing history are altered, it can result in new entries in the sqlth_te tables.
While YMMV with support, we had issues with those database tables previously (unrelated to the issue outlined in this post), and support told us that they could provide us info on those tables, but it was up to us to fix it.
We ended up having to rename the sqlth_te table, let Ignition recreate it, then go in and update ALL of the sqlt_data_* tables to point to the newly created tag id (and set the sqlth_te “created” timestamp to the earliest instance of tag data for every tag)