HikariCP : Timeout after 30000 waiting for a connection

Question

I am working on a Spring-MVC application where we are using HikariCP. We are using PostgreSQL as the database with 150 connections in PostgreSQL and 15 pool size for Hikari. At times, we suddenly get error that timeout occured. The error goes away in 10-20 seconds, but it slows down the entire server. Many times it's not even busy. I tried some other threads and added leak detection, but no help. Any ideas what I am doing wrong or how to fix this?

Error log :

java.sql.SQLTimeoutException: Timeout after 30000ms of waiting for a connection.
        at com.zaxxer.hikari.pool.BaseHikariPool.getConnection(BaseHikariPool.java:233)
        at com.zaxxer.hikari.pool.BaseHikariPool.getConnection(BaseHikariPool.java:183)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:110)

root-context.xml :

<beans:bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource"  destroy-method="close">
    <beans:property name="dataSourceClassName" value="org.postgresql.ds.PGSimpleDataSource"/>
    <beans:property name="minimumIdle" value="2"/>
   <beans:property name="maximumPoolSize" value="17" />
    <beans:property name="maxLifetime" value="300000" />
    <beans:property name="idleTimeout" value="25000" />
    <beans:property name="leakDetectionThreshold" value="3000"/>
    <beans:property name="dataSourceProperties">
        <beans:props>
            <beans:prop key="url">jdbc:postgresql://localhost:5432/DB_NAME</beans:prop>
            <beans:prop key="user">USERnamE</beans:prop>
            <beans:prop key="password">PASSWORD</beans:prop>
        </beans:props>
    </beans:property>
</beans:bean>

<!-- Hibernate 4 SessionFactory Bean definition -->
<beans:bean id="hibernate4AnnotatedSessionFactory"
            class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <beans:property name="dataSource" ref="dataSource"/>
    <beans:property name="packagesToScan" value="com.ourapp.spring.model"/>
    <beans:property name="hibernateProperties">
        <beans:props>
            <beans:prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQL9Dialect</beans:prop>
            <beans:prop key="hibernate.show_sql">false</beans:prop>
            <beans:prop key="hibernate.jdbc.batch_size">50</beans:prop>
            <beans:prop key="hibernate.hbm2ddl.auto">update</beans:prop>
            <beans:prop key="cache.use_second_level_cache">true</beans:prop>
            <beans:prop key="cache.use_query_cache">true</beans:prop>
            <beans:prop key="hibernate.order_updates">true</beans:prop>
            <beans:prop key="show_sql">false</beans:prop>
        </beans:props>
    </beans:property>
</beans:bean>

Any help would be nice. Thank you. :-)

Update

typical save and read :

@Repository
@Transactional
public class AppDetailsDAOImpl implements AppDetailsDAO {

    private final SessionFactory sessionFactory;
    @Autowired
    public AppDetailsDAOImpl(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }


    @Override
    public Long saveAppDetails(AppDetails appDetails, int personid) {
        Session session = this.sessionFactory.getCurrentSession();
        Person person = (Person) session.get(Person.class, personid);
        if (person != null) {
            person.getAppDetailsSet().add(appDetails);
            appDetails.setUserApps(person);
            Long saveid = (Long) session.save(appDetails);
            session.flush();
            return saveid;
        }
        return (long) 0;
    }

    @Override
    public AppDetails getAppDetailsByAppId(Long appId) {
        Session session = this.sessionFactory.getCurrentSession();
        return (AppDetails) session.get(AppDetails.class, appId);
    }
}

Answer 1

You run out of connections due to your application business logic. Like you said:

the problem is I have mostly short running transactions and very rarely long running transactions

Instead of increasing the pool size for short running transactions declare a separate new DataSource bean for long running transactions. This new DataSource should be backed by new HikariCP pool. It can even have an min size of 0 since the cost of establishing a new database connection should be insignificant if you are running a long running transaction e.g. a monthly report.

Separating OLTP and OLAP processing is the right approach e.g. see CQRS. You can for example have a separate OLAP database refreshed daily to handle reporting while the main OLTP database is unaffected by time consuming reporting workloads.

Answer 2

change the Postgres driver and change your setting for hikaricp

spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000   
spring.jpa.hibernate.connection.provider_class=org.hibernate.hikaricp.internal
.HikariCPConnectionProvider