I have a query that is constantly active on the database status page. The database is a MYSQL database that is only used for historical data storage. Typically the age of the query is between 10 seconds and 6 minutes. Eventually, the database connection will fault, which will then crash the gateway.
INSERT INTO sqlt_data_2_2013_09_60(tagid,intvalue,floatvalue,stringvalue,datevalue,dataintegrity,t_stamp,vtype) VALUES(?,?,?,?,?,?,?,?) [Batch x23150]
The batch number changes, but the query stays active.
Does this query have to do with the historical tag data? What is causing the query to be constantly running? How can I get it to work more efficiently?
That query is not (directly) causing the db to fault. It’s actually not even slow, the problem is how the pre-process engine holds the connection open for efficiency. The best way to see what happening would be to export and post the logs from the Console page in the gateway, but my guess is that you might be running into the open connection limit, which defaults to 8, and causes a fault. You can increase this value on the database connection settings to something like 25, and see if that helps.
Also: 7.6.3 changes how this connection is managed, to avoid holding it open like this.
Something is causing the gateway to stop running the ignition service. The only thing that I can see is the database faulting. Is there anything else that could cause the gateway to stop the ignition service?
This has been happening continuously at one of our plants. I can email the logs.