I'm working on a Spring MVC project (Spring version 4.1.1), and I configured HikariCP as connection pool manager. When the application is not used for a while, it seems like Hikari starts losing connections with DB, and when I try to connect to the web app, Tomcat shows this:
com.zaxxer.hikari.pool.PoolBase - HikariPool-8 - Failed to validate connection Pooled connection wrapping physical connection org.postgresql.jdbc.PgConnection@2bb0433a (Connection has been closed.). Possibily consider using a shorter maxLifetime value. Here is my Hikari datasource configuration for Spring:
<!-- Initialize DataSource com.zaxxer.hikari.HikariDataSource --> <bean id="hikariConfig" class="com.zaxxer.hikari.HikariDataSource"> <property name="dataSourceClassName" value="org.postgresql.ds.PGPoolingDataSource" /> <property name="dataSourceProperties"> <props> <prop key="url">${jdbc.url}</prop> <prop key="user">${jdbc.username}</prop> <prop key="password">${jdbc.password}</prop> </props> </property> <property name="maxLifetime" value="120000" /> <property name="connectionTestQuery" value="SELECT 1" /> <property name="maximumPoolSize" value="10" /> </bean> <!-- HikariCP configuration --> <bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"> <constructor-arg ref="hikariConfig" /> </bean> I already tried to use shorter maxLifetime values, as suggested by the stacktrace, but it did not make any difference.
Could anybody please help me resolving this issue? What am I doing wrong?
Other useful info:
- HikariCP version: 3.3.1
- Java version: 1.8
- Tomcat version: 8.5
- Spring MVC version: 4.1.1
- JDBC PostgreSQL driver version: 42.2.16
- DBMS version: PostgreSQL 13.0
try { conn = dataSource.getConnection(); ... } finally { conn.close(); }. I also noticed that theidle_in_transaction_session_timeoutis disabled in my DBMS. Maybe I need to set it with a value, such as 5 min? That is greater than themaxLifetimeof the application (2min)?idle_in_transaction_session_timeoutonly affects connections whose state isidle in transaction, but in my case I have a lot ofidleconnections (I am usingpg_stat_activityto spot them). I also found a few posts that suggest to use a cron job to programmatically kill idle connections :/ (stackoverflow.com/questions/13236160/…, stackoverflow.com/questions/12391174/…). Do you think it is worth trying this?