I have an overview screen using a template with an embedded easy chart in manual mode. There are roughly 17 instances of this easy chart template on a single overview window. 3 tags per chart, pulling historically the last 120 minutes to each chart.
I had been running this for well over a month in 7.6.6 with no performance issues. Screen was very responsive. Never exceeded connection limits (8 concurrent). SQL server performance was good, averaging 15% CPU.
Within minutes of upgrading I got support calls of performance issues on the screen and frequent disconnects of the chart templates. After seeing database connection errors in the console, I checked the sql connections and they were constantly maxing out and chart queries were running very long. 1.78 seconds. I began increasing connections to match the chart count and then buffered that for overrun. I settled on 30 concurrent connections. The screen errors went away but then my SQL server performance became terrible. 90 - 98% average on CPU of the SQL server. Now we have to restart the application 1 to 2 times daily when the sql connection locks up. As soon as I close out the running application the performance on the SQL Server drops to 30% and runs smoothly.
I have made attempts to beef up the sql server. Added an additional core the vm processor, increased RAM, and adjusted auto grow and shrink in sql server. This has not helped. Again, this was not the case when I was running 7.6.6.
Has anyone else experienced this? Anyone have some feedback or ideas to make the performance better for this easy chart window? Customer is pretty frustrated and asking me to roll back to 7.6.6. I am open to architecture changes on window, but its concerning this happened with the upgrade. Any help is greatly appreciated.
Well after thinking through the process, I reflected on default easy chart settings and noted the charts poll rate was 1 second. Since my historizing rate for the tags in question are every 10 seconds, did not make much sense to update the chart every second. Normally this is not in play in historical mode, but since I am running the charts in manual mode that poll rate was causing queries to trigger every second.
Multiply that times 17 and it appears I was maxing out the connection pooling and sending sql server cpu sky high.
changing the poll rates of the chart in my template seems to have corrected my issue. Though it does not explain why 7.6 ran more efficiently than 7.7 because the poll rate was the same. My only guess is that there is much more behind the scenes stuff going on with the gateway in 7.7 that consumes resources of the connection pool.
You’re right, it doesn’t explain it. And no, there isn’t just generally a lot more “stuff” going on in 7.7. I wonder if maybe the client’s cache is being bypassed somehow, we’ll have to look into it.
To anyone else who comes across this:
We looked at the system on the phone, and the primary cause appears to have been the failure to retire previous tag ids after 7.7 inserted new ones.
To explain further: 7.7 modifies historical storage by adding information about the source provider to the database. In order to do this, it needs to create new entries in the tag table (sqlth_te). Usually when new ids are created for a path, the previous ids are “retired”. For some reason (which we’ll investigate), 7.7.0 is failing to do this, so there are multiple non-retired ids for tags. This is not necessarily a problem, and the system is designed to handle this, but on this particular system it seems to be dramatically affecting the database performance.
We were able to retire the previous tags (setting “retired” equal to the “created” value of the new tags) and everything appears to be better now.
We will look at adding logic in 7.7.1 to fix this situation if encountered.
Just an FYI, I also had this issue but it didn’t seem to effect database performance. However a lot of my trend lines would not show up in the Easy Chart component until I put in the retired date, or I would only get history for a few days prior.
There was a separate issue, also fixed for 7.7.1, in which the “scan class validation” was getting mixed up for the two sets of tags, causing data to be marked incorrectly as stale, which causes it to not be displayed on the charts. The solutions for that would be to mark the old tags as retired, and make sure the entries in the sqlth_sce table for both scan class ids cover the time before and after the upgrade.
Besides the performance issue encountered in this thread, there isn’t normally a problem with multiple ids for a path, but in the case of 7.7, since the new id is also under a new provider id, it has caused a few other side effects.
We’re freezing 7.7.1 today, and it will be out next week (as long as testing doesn’t find something big).