Custom SQL query extension encounters PoolExhaustedException

[Hillman]

Hi all,

We have created the custom SQL query extension and test it on our environment. It can retrieve data for a short while and then not able to retrieve data anymore. We have checked the extension log and found the following exception:

[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out][8c59faf9-e372-3ac0-a78d-bff4ed46a22f]2024-08-19 | 06:32:44.928 | pool-5-thread-2      | ERROR | c.d.s.p.p.AbstractPoller  | While polling from endpoint <db_server>:6432:<db_instance>, querying group '<group>' failed
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]org.apache.tomcat.jdbc.pool.PoolExhaustedException: [pool-5-thread-2] Timeout: Pool empty. Unable to fetch a connection in 30 seconds, none available[size:8; busy:8; idle:0; lastwait:30000].
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:741)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:199)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:133)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at com.dynatrace.sql.jdbc.JdbcExecutor.getConnection(JdbcExecutor.java:136)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at com.dynatrace.sql.jdbc.JdbcExecutor.execute(JdbcExecutor.java:57)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at com.dynatrace.sql.polling.pollers.AbstractPoller.poll(AbstractPoller.java:79)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at com.dynatrace.sql.polling.pollers.SubgroupPoller.pollWithParentCache(SubgroupPoller.java:62)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at com.dynatrace.sql.polling.pollers.SubgroupPoller.poll(SubgroupPoller.java:41)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at com.dynatrace.sql.polling.pollers.SubgroupPoller.lambda$schedulePolling$0(SubgroupPoller.java:49)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
[8c59faf9-e372-3ac0-a78d-bff4ed46a22f][1617012385557513144][1419832][out]       at java.base/java.lang.Thread.run(Unknown Source)

The pool size mentions is very small "[size:8; busy:8; idle:0; lastwait:30000]", which is much smaller than our PostgreSQL connection pool (our pool size is 5000 per user).

I have raised ticket and asked the Dynatrace support. They have suggested to set the SQL query timeout. We have tried but it still keep failing. After some checking, they find it seems to be related to Tomcat JDBC Driver. https://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html#Common_Attributes . They suggest us to tune the maxWait parameter.

I have checked the maxWait parameter is not in the original extension schema. I am not sure where to configure this parameter. Also, it seems like the driver has its own pool size.

However, custom extension is out of their support scope and the support are unable provide more help unless we purchase a paid service. I can only seek help here.

Is it possible to tune the maxWait or the JDBC driver pool size in the custom extension?

[chaitanya]

Hi,

I will also join with you, could anyone please help us to customize timeout?

Thank you

Chaitanya