SQL query timeouts

I’ve been assigned to maintain an Ignition project that suffers from queries that time out. These queries are the high frequency usage of this project. Typically the problem queries return 50 data points, but you can specify as many as you wish. I suspect that a broken configuration is the problem or that this project is doing something it shouldn’t be. I am new to ignition and would appreciate any advice on where to start trouble shooting this. These are the symptoms:

This project is running on a version 7.8.3 gateway. The project is a port of an existing system from VB.NET to Ignition. Both projects are currently in use and both of them connect to the same back end database (SQL Server) running on a server dedicated to this DB with 4GB RAM. There are always exactly 20 clients connected to the gateway.

There are only a couple of times a day (few minutes each) separated by hours in which the system is lighting fast. When the system is fast the clients are highly responsive and the queries execute instantly. When the system is slow the client freezes the gui can be unresponsive and the query times out.

For a while you can reduce the number of data points to 25 and the query will execute in a minute or so. When the system is in its most volatile state the number of database connections will max out to 20, these will be dropped and restarted. This does not restore the connections to a working state. They will timeout again and again with the same error message:

Message: [componentName=Chart,targetProperty=Data] Error running query:
SQLQuery(query=execute dbo.XXXXXXXX '1.0' , 'XX', 'XX', 'XXX', 1,0.0,0,50,1, database=)@0ms
On: ColourChart.Root Container.Chart.Data
Time: Tue Apr 11 11:06:52AM
Severity: WARN
Logger: Vision.Binding.SQLPropertyAdapter
Stack Trace: java.lang.Exception: Error running query:
SQLQuery(query=execute dbo.XXXXXXXX '1.0' , 'XX', 'XX', 'XXX', 1,0.0,0,50,1, database=)@0ms
On: ColourChart.Root Container.Chart.Data:

at com.inductiveautomation.factorypmi.application.gateway.QueryManager\$QueryExecutor.run(QueryManager.java:317):
at java.lang.Thread.run(Unknown Source):
Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Read timed out:
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:327):
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:486):
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:263):
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:258):
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runLimitQuery(GatewayInterface.java:803):
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.runQuery(GatewayInterface.java:772):
at com.inductiveautomation.factorypmi.application.gateway.SQLQuery.execute(SQLQuery.java:48):
at com.inductiveautomation.factorypmi.application.gateway.QueryManager\$Query.execute(QueryManager.java:690):
at com.inductiveautomation.factorypmi.application.gateway.QueryManager\$Query.access\$500(QueryManager.java:663):
at com.inductiveautomation.factorypmi.application.gateway.QueryManager\$QueryExecutor.run(QueryManager.java:314):
... 1 more:
Caused by: java.net.SocketTimeoutException: Read timed out:
at java.net.SocketInputStream.socketRead0(Native Method):
at java.net.SocketInputStream.socketRead(Unknown Source):
at java.net.SocketInputStream.read(Unknown Source):
at java.net.SocketInputStream.read(Unknown Source):
at java.io.BufferedInputStream.fill(Unknown Source):
at java.io.BufferedInputStream.read1(Unknown Source):
at java.io.BufferedInputStream.read(Unknown Source):
at sun.net.www.http.HttpClient.parseHTTPHeader(Unknown Source):
at sun.net.www.http.HttpClient.parseHTTP(Unknown Source):
at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(Unknown Source):
at sun.net.www.protocol.http.HttpURLConnection.getInputStream(Unknown Source):
at java.net.HttpURLConnection.getResponseCode(Unknown Source):
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:406):
... 9 more:

Stack Trace: com.inductiveautomation.factorypmi.application.gateway.QueryManager$QueryExecutor.run(QueryManager.java:317)
java.lang.Thread.run(Unknown Source)

When the ignition client becomes unusable the users typically switch to the .NET application to continue their work. I have observed the same query running side by side. In ignition it timed out with 50 samples after 1:45, then when executed at 20 samples it completed but it took 0:50, while at the same time the .NET app was able to pull in 10000 samples in less than 0:02 seconds.

Something must be broken in the projects configuration, and I am not sure where to start looking and would appreciate any help.

1 Like

Hmmm. The stack trace shows “Caused by” a socket timeout in the HTTP protocol, which is the client to gateway connection, not the gateway to database connection. This sounds like a Java garbage collection problem. Try the new forum’s search function looking for “G1GC” and “ignition.conf”. That’ll point to some suggestions. :wink:

1 Like

Thanks for the response. I’ll give this a try

Hi - did you resolve this? We are getting the same issue…

Yes it was indeed the garbage collector
We also had some leaks, but changing the garbage collector resolved this issue

1 Like