logs.zip (2.0 MB)
Traceback (most recent call last):
File "<event:propertyChange>", line 7, in <module>
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Read timed out
caused by GatewayException: Read timed out
caused by SocketTimeoutException: Read timed out
Ignition v8.1.1 (b2020120808)
Java: Azul Systems, Inc. 11.0.7
Seems like your query is taking longer than your designated timeout. If your timeout is 60 seconds and the query takes 70 seconds you won’t see the data and get the error instead.
You can modify the timeout period on the project properties -
Alternatively, probably what I would do first, you can try optimizing your query. Get the EXPLAIN statement for your query and see if there’s any indexes or optimizations you can do.
Hi, Thanks for the input! I’m sorry there isn’t more information immediately, this is the only way I could data out to the support team.
I’ve built an application which uses an easy chart and tag historian see Start and End data values for more information on it.
On Thursday last week I was testing the application and response times were very acceptable, perhaps a few seconds to update the easy chart when dates were changed. Friday I went to setup this on the managers desktops and the application comes up with these really long SELECT queries leading to timeouts in the order of 20+ minutes.
I’m checking back to see if anything changed with the scripting or database, I might need to get the IT guys involved as they made changes to firewall rules to allow managers access to the production network.
Ok. Overall though, caused by GatewayException: Read timed out errors with regards to database queries just means that the query you want to run takes longer than the preset timeout setting you set. So the only two ways to attack this are to make the query complete faster via optimization or whatever idea’s your IT team has, or to increase the timeout. However, given you said the queries are taking 20 minutes now which I am going to guess is unacceptable, you probably want to optimize/fix whatever technical bottleneck is causing the slowness.
Support tried a few queries directly on the database, and it seems to be the problem, doesn’t respond very quickly. I don’t know very much about databases (using SQL Express here) so I don’t know what to look at for fixes. So far I changed this in ignition.conf:
# Initial Java Heap Size (in MB)
#20210217 changed from 1024 to 1100
wrapper.java.initmemory=1100
# Maximum Java Heap Size (in MB)
#20210217 changed from 2048 to 4400
wrapper.java.maxmemory=4400
with no noticeable changes to easy chart updates.
Ok the heap size is something different from the timeout. If you were running a gateway script that had something like
results = system.db.runQuery("SELECT * FROM myTable")
and you had millions of millions of row, then you could have a Heap Size problem and could face an issue.
But query timeout is send out a query to the database and it takes the database longer than your timeout to get any result.
So again that is under project properties per the last post.
I see you are referencing an easy chart update. Is this for a historical tag or something? You might want to look into setting up pruning as otherwise the table just gets larger and larger and the query will take longer and longer (unless you set up the component to initialize with some recent date parameters, which may be something else to try).
Yes, this is an easy chart using tag historian. Sometimes the data on the chart will span a few days, tags are updated every 2 seconds, current data tables are as I posted above, several million records. Part of the project requirements is the retention of this data for up to 30 years. I don't think it's required to be immediately accessible, but the data cannot be deleted or discarded. Maybe I need to look into some sort of archiving behind the scenes, is this what you mean by pruning? When I was testing the application last week Thursday, it worked fine, then on Friday I started getting these timeouts. Shouldn't I have been seeing slower and slower responses as the tables grew in size, rather than this sudden timeouts?
No pruning would mean deleting data that is beyond X days/months/years old.
Yea I would expect slower responses as it grew in size. What’s your timeout set to?
Here’s what I would do - set your timeout super high - so that the query can finish. This way you can get the query in the Slow Query log, copy and paste it into your database workbench, and then run an EXPLAIN statement on the query so you can see if there’s a way to optimize it.
1 Like
Yes the Read Timeout. Right now if your query takes more than 1 minute you will get a Read Timeout error.
I changed the timing from 60000 to 60 000 000 and from 120 000 to 120 000 000. Saved the project and relaunched it. Now my queries are running just fine. A little slow on some of them, but those are getting days worth of records.
I have no idea what changed (except for what I mentioned) to make it work suddenly.
Ok thats good. I see some are taking many seconds, but they look like the queries Ignition knows to use. You can try what I said at this point - copy that top query that took 7 seconds, paste it into workbench, EXPLAIN the statement and see if you can optimize with indexes or similar.
However, since you said this will be 30 years of data, and already millions of rows, creating an index would take up a non-trivial amount of space. You could look into other techniques like data partitioning which is suitable for optimizing queries with date where clauses. I think that may be the way to go for you tbh - if you care to try to optimize the queries.
I find it odd that a simple insert would take nearly 30s.
How do you have your data partitioning set up on the gateway?
1 Like
I don’t know? I guess whatever the defaults are.
I believe the default is 1 Month.
How many items are you collecting and how often (e.g. how fast is your table growing)? You may want to consider setting you partitioning to a shorter rate.
This is configured in the Historical Provider.
Using historical tags, 8 of them, and updating at 2 second intervals. I will look into Historical Provider.
This is where the Historical Provider is configured? I don’t see where partitions is setup, but I do see an option for partitions when I’m on SQL Server Manager, if I right click the tables.
Ignore that, found it in Config > Tags > History on the gateway. I believe it’s all on defaults. Should I try setting it to weekly? What happens if data is requested from an old partition?
I mean, you’re collecting 1.4M rows per day, that is a significant amount of data.
The query will need to join the two tables together to return the result. The built in historical functions take care of joining the tables together for you.