I am experiencing strange behavior with database connections. In one of our environments, the database connections get blocked because the connection limit is reached, but Ignition does not purge the connections (Cannot get a connection, pool error Timeout waiting for idle object).
However, I tested this in my development environment, where I tried to reach the configured connection limit on the gateway for the database connection. In this case, Ignition automatically purged the connections by disabling and re-enabling the database connection, causing the connection count to drop to 0 without any database blocking.
The most common cause of this is slow-running queries, often historical queries. Production environments are more likely to have multiple people looking at long trends and stressing your DB.
You should study the gateway's slow query diagnostics, and perhaps have a qualified DBA study your schema. (Particularly looking for missing indices and inefficient query plans.)
I have tested with long-running queries to simulate load on the database, and when I reach the connection limit in one environment, Ignition automatically purges the connections (by disabling and re-enabling the database connection), which prevents blocking.
However, in the other environment, Ignition does not behave the same way and the connections remain blocked once the limit is reached. I am not sure why this difference occurs.
While I continue to investigate, I am not sure if I should implement a script to increase the number of allowed connections dynamically or to purge (reset) the connections manually via a script in Ignition, is a good idea
Ignition doesn't do this automatically. Someone might have written a script in that environment to do so. (Which sounds terrible--is this an odd JDBC driver?)