I have a gateway timer script that gets all the values from about 7339 tags each minute. Starting Sunday the 27th at 9:10am the same 'system.tag.queryTagHistory' script for the same number of tags and for just the last minute, went from 10 seconds to 20 seconds. Then later the same day at 11:57am it went from 20 seconds to about 250 seconds and is still taking that long each time. No change in the number of tags and it should still be on the same historian partition for the month of April.
I've restarted the scripting but not the gateway. Just wondering why it's now taking so much longer.
There is a second isolated system doing the same for about the same number of tags and it's not seeing the issue.
You can get a gateway thread dump during script execution period to see if the time taken is in Ignition's code, or in the JDBC driver/waiting for response from the database.
You might also want to look into your database' query metrics - it might be able to EXPLAIN why some queries are taking much longer (if they actually are at the DB level).
I couldn't figure anything out in a timely manner, so I gave in and rebooted the server. The query times are now back down to 10 seconds. This is concerning because a lot of data was missed and I did not feel like I could just leave it like that.
The other system has been running for more than a year without issue.
On this newer build, I'd been trying to reduce the query time and had added a pre-processed table to see if that could help. However, they require raw values, and each query is for just the last minute.
I did try to get a thread dump that made it look like the system was waiting on MSSQL, but I'm not sure.
The attracted dump is the only one I have.
Once it stated to take longer it never got better.
After 11:57am on the 27th it was stuck continually running the script back to back but only finishing an instance once every 4 minutes or so until the server was rebooted. Currently both independent systems are all in one, everything is running locally on one server.
That is indeed what that particular thread dump implicates. Bottom to top, you've got a timer script: "org.python.pycode._pyx1866.f$0(<TimerScript:TEST02/REST POST Delay Count - Production @60,000ms >:66)",
Running code from the project library: "org.python.pycode._pyx1868.post$3(<module:REST.API_POST>:387)",
Which is calling into system.tag.queryTagHistory: "app//com.inductiveautomation.ignition.common.script.builtin.AbstractTagUtilities.queryTagHistory(AbstractTagUtilities.java:737)",
Which is calling out to the database: "com.inductiveautomation.gateway.tags.history.query.DatasourceHistoryInterface.loadTagInfo(DatasourceHistoryInterface.java:294)",
And, ultimately, waiting for a native socket opened by the JDBC driver to return a value: "com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:613)", "java.base@17.0.12/java.net.Socket$SocketInputStream.read(Unknown Source)",
You may want to try manually upgrading the JDBC driver. This is somewhat of a wild stab - there's no guarantee it will help (and if you're on a very old MSSQL version, it may even break things, so make sure you've got backups!)...but it's possible this is some known issue with MSSQL's JDBC driver that's already been fixed.
You may also want to research timeout properties you can set on your MSSQL DB connection - thus forcing the driver to 'give up' if a query exceeds 30 seconds or whatever. That prevents threads being locked up ~forever due to whatever underlying bug.