connection time out

[Mohsin Khan]

Hi,

We have HikariCP connection pool set up for our Tomcat Based Spring Hibenate mysql Rest API,  It is working fine in local and stating, when we put this in production server, we are getting
 Following exception

Caused by: java.sql.SQLTransientConnectionException: zwayamHikariCP - Connection is not available, request timed out after 30000ms.
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:147)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:83)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)

This exception means all the connection are active and in use and there is not free connection available,  it waits for 30 sec and then time out,  Because of this our API is not responding.

Following are the configurations

jdbc.hikari.maximumPoolSize=100
jdbc.hikari.minimumIdle=20
jdbc.hikari.idleTimeout=30000
jdbc.hikari.maxLifetime=2000000
jdbc.hikari.connectionTimeout=30000
jdbc.hikari.prepStmtCacheSize=250
jdbc.hikari.prepStmtCacheSqlLimit=2048

What exactly the ideal configuration for the production server,  when there is not connection in the pool will not create connection and keep in the pool.

We tried this two twice in the production same error , we want to use this in our production, Right now again we disabled it in the production.

Thanks
Mohsin

[Brett Wooldridge]

Do you get this error all the time in the production environment, or randomly?  If you get the error all the time, meaning that the pool essentially fails to start, then there is some configuration difference between the test environment and the production environment.

I recommend trying three things.  First, set the HikariCP pool property "initializationFailFast" to "true".  Second, enable the "leakDetectionThreshold".  Finally, enable debug logging and past the log from 30 seconds before+after the exception.

-Brett

[Mohsin Khan]

Yes when we enable this in production, after say 5 to 6 hours this error comes, 
I am try to enable debug but it is not dispalying in eclipse console

log4j.logger.com.zaxxer.hikari=DEBUG
log4j.additivity.com.zaxxer.hikari=true

I want to verify is the connecton are getting back to pool or not. Please let me know how to enable debug log for tomcat in eclipse.

Thanks
Mohsin

[Mohsin Khan]

Some how enabled the debug, but only getting following information

On Thursday, 31 March 2016 00:16:24 UTC+5:30, Mohsin Khan wrote:

Yes when we enable this in production, after say 5 to 6 hours this error comes, 
I am try to enable debug but it is not dispalying in eclipse console

log4j.logger.com.zaxxer.hikari=DEBUG
log4j.additivity.com.zaxxer.hikari=true

I want to verify is the connecton are getting back to pool or not. Please let me know how to enable debug log for tomcat in eclipse.

2016-03-31 19:55:10,008 [pool-6-thread-1] DEBUG TransactionImpl:62 - committing
2016-03-31 19:55:10,009 [pool-6-thread-1] DEBUG LogicalConnectionManagedImpl:137 - Initiating JDBC connection release from afterTransaction
2016-03-31 19:55:10,009 [pool-6-thread-1] DEBUG PoolBase:205 - zwayamHikariCP - Reset (autoCommit) on connection com.mysql.jdbc.JDBC4Connection@3fa9a6de
2016-03-31 19:55:10,009 [pool-6-thread-1] DEBUG LogicalConnectionManagedImpl:137 - Initiating JDBC connection release from afterTransaction
 

[Mohsin Khan]

On Thursday, 31 March 2016 19:55:40 UTC+5:30, Mohsin Khan wrote:

Some how enabled the debug, but only getting following information