Database Connection Faulting, Too Many Connections?

Ignition
,
1

Hi,

I am running into an issue with my production database connection faulting. The full error is shown below. What I believe to be happening is that there are too many queries running consecutively. I have increased the max active and max idle values to 50 in the connection pooling section which seemed to alleviate the problem for a few hours but it has since come back.

The view that is likely causing this is something that is currently refreshed when there is a specific set of actions, and refreshed for all current sessions. This refreshing of queries is used to build a table with some other subviews that also contain query bindings.

According to the database connection stats, the connections is typically 0/50 but often spikes to 45+/50, with some of the longest queries being ~2.5 seconds. The spiking in active connections seems normal based on the refreshing of query bindings.

Does this like a correct assumption to how connection pooling functions in Ignition? For further information, the database it is connecting to is Postgres, with max_connections = 100, and according to PGAdmin4 a connection limit of -1.

java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)

at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:300)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl.getConnectionImpl(DatasourceManagerImpl.java:169)

at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnection(DatasourceImpl.java:293)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.runPrepQuery(NamedQueryExecutor.java:481)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor$NamedQueryInstance.execute(NamedQueryExecutor.java:399)

at com.inductiveautomation.ignition.gateway.db.namedquery.NamedQueryExecutor.execute(NamedQueryExecutor.java:173)

at com.inductiveautomation.ignition.gateway.db.namedquery.GatewayNamedQueryManager.execute(GatewayNamedQueryManager.java:92)

at com.inductiveautomation.ignition.common.db.namedquery.SecuredNamedQueryManager.execute(SecuredNamedQueryManager.java:78)

at com.inductiveautomation.ignition.gateway.script.GatewayDBUtilities.runNamedQuery(GatewayDBUtilities.java:419)

at jdk.internal.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)

at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

at java.base/java.lang.reflect.Method.invoke(Unknown Source)

at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)

at org.python.core.PyObject.__call__(PyObject.java:400)

at org.python.pycode._pyx229681.transform$1(:16)

at org.python.pycode._pyx229681.call_function()

at org.python.core.PyTableCode.call(PyTableCode.java:173)

at org.python.core.PyBaseCode.call(PyBaseCode.java:306)

at org.python.core.PyFunction.function___call__(PyFunction.java:474)

at org.python.core.PyFunction.__call__(PyFunction.java:469)

at org.python.core.PyFunction.__call__(PyFunction.java:464)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:847)

at com.inductiveautomation.ignition.common.script.ScriptManager.runFunction(ScriptManager.java:829)

at com.inductiveautomation.ignition.gateway.project.ProjectScriptLifecycle$TrackingProjectScriptManager.runFunction(ProjectScriptLifecycle.java:868)

at com.inductiveautomation.perspective.gateway.binding.transforms.script.ScriptTransform.runFunction(ScriptTransform.java:118)

at com.inductiveautomation.perspective.gateway.binding.transforms.script.ScriptTransform.synchronousTransformInternal(ScriptTransform.java:162)

at com.inductiveautomation.perspective.gateway.binding.transforms.AbstractSynchronousTransform.transform(AbstractSynchronousTransform.java:30)

at com.inductiveautomation.perspective.gateway.model.AbstractBindingHarness$TransformSequencer.transform(AbstractBindingHarness.java:295)

at com.inductiveautomation.perspective.gateway.model.AbstractBindingHarness$TransformSequencer.run(AbstractBindingHarness.java:308)

at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)

at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

at com.inductiveautomation.perspective.gateway.threading.BlockingWork$BlockingWorkRunnable.run(BlockingWork.java:58)

at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.util.NoSuchElementException: Timeout waiting for idle object

at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:321)

at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:428)

at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:232)

at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)

... 37 common frames omitted
2

Is there an opportunity to have a trigger a query tag to run once and have all current sessions reference that instead of each one running the same query?

1 Like
3

If you can, use Named Queries and enable caching.

4

In addition to what @pturmel said, you should also try to eliminate as much traffic as possible. How many of these queries are returning filtered data of the original queried dataset? If possible instead of query bindings which return a subset of the original dataset, opt instead for an expression binding or property binding with transform to generate the filtered subset. This eliminates the extra trips to the DB and lessens the load.

Also, if you have/or can use Phil's Integration Toolkit Module there are tools included that can make this type of task (filtering data) easier.

1 Like