SQL custom queries and Ignition performance in general

Hi All
I’m starting to re-evaluate my thoughts on custom queries/ named queries and thinking if I should switch over to using stored procedures instead. The main concern is having a high CPU (40 - 50%) and Memory usage of my gateway computer (my MySQL server and the ignition gateway are located in different machines). Before doing any experiments I wanted to confirm a few things:

  • When a custom/ named query gets executed or called through an event on the Ignition client, who does the majority of the computing, the computer that hosts the SQL server or the one that runs the gateway?

  • Would it be a lesser load on the gateway computer if I switch over to stored procedures and pass arguments from the client when an event is triggered?

Honestly, it may not even be the queries from looking at the performance threads,

I don’t know why the scripts consume a lot of CPU even though more than half of the timer/tag change scripts are disabled?

The lion’s share of the work will fall on the database instance. However, the gateway still has to do some ‘work’ with the results of the query, especially if you’re using Vision; the query is issued from the gateway to your database via JDBC, then the results are unpacked on the Gateway and sent to the client in a different wire format - that implies a linear growth in CPU and memory consumption on the gateway with the size of the result objects coming from the database (more rows = more pressure on the gateway).

This likely won’t affect the gateway, for the reason noted above. Short of just delivering less results to clients, there’s not much you can do to optimize the client → gateway → database chain.

2 Likes

Thank you, sir! That explains a lot, I think my performance issue would be with some scripts. Do you know what this ‘xopc tag driver’ thread could be, is this pertaining to the OPCUA server?