1

I'm connecting to my postgres instance in springboot and I've implemented Hikari CP for connection pooling . I see after a few days of deployment , my code seems to break I seem to get the following error message in my logs 

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 60000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:602)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:195)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85)
atcom.dell.it.daas.apibuilder.submitrequestservice.Services.PostgresConnectionServiceImpl.connectToPostgresDatabase(PostgresConnectionServiceImpl.java:103)
at com.dell.it.daas.apibuilder.submitrequestservice.Services.PostgresConnectionServiceImpl.getRequestDetailsByRequestId(PostgresConnectionServiceImpl.java:114)

Here's my Hikari CP connection parameters

public static Connection connectToPostgresDatabase(String connectionString, String userName, String 
password)
        throws SQLException {

    if (config == null) {
        config = new HikariConfig();
        config.setJdbcUrl(connectionString);
        config.setUsername(userName);
        config.setPassword(password);
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "256");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.setConnectionTimeout(60000);
        config.setMinimumIdle(10);
        config.setMaximumPoolSize(30);
        config.setLeakDetectionThreshold(60000);
        config.setConnectionTestQuery("SELECT 1");
        config.setInitializationFailTimeout(20000);
        config.addDataSourceProperty("prepareThreshold", 0);
        config.addDataSourceProperty("useServerPrepStmts", true);


        ds = new HikariDataSource(config);
    }


    return ds.getConnection();

}

SO whenever I need to execute any Query I get the connection to database as 

 try(Connection con = 
PostgresConnectionServiceImpl.connectToPostgresDatabase(apibuilderConnectionString, 
apibuilderUserName, apibuilderPassword))
    {
        if(getSchema()!=null)
            con.setSchema(getSchema());


            try(PreparedStatement st = con.prepareStatement(queryToRetrieveRequestDetails);
                ResultSet requestDetailsSet = st.executeQuery();)

I'm using try with resources to do all my database related operations

PostgresConnectionServiceImpl.connectToPostgresDatabase(apibuilderConnectionString, 
apibuilderUserName, apibuilderPassword)

This is the the line which is failing .

Been struggling with this for some time now appreciate any help . Thanks

Dwijraj Bhattacharyya
  • 351
  • 4
  • 14
  • Did you get leak warnings in log? Did your server(s) had load in that time? can you show connectToPostgresDatabase? also see https://stackoverflow.com/questions/50213381/how-do-i-configure-hikaricp-for-postgresql – Ori Marko May 19 '20 at 06:12
  • I've already pasted connectToPostgresDatabase function which does all the hikari CP configurations – Dwijraj Bhattacharyya May 19 '20 at 06:26
  • Why this solution? You are bypassing the Spring connection management here? That being said make sure you configure your pool correctly so that it does idle checking and validation of the connection while nothing is being used so that you always have fresh connections. You shouldn't be using a test query as JDBC4+ has better means of testing/validating a connection. – M. Deinum May 19 '20 at 06:30
  • @M.Deinum I'm using Hikari for Connection pooling instead of spring's default connection pooling . Can you guide me to some materials that I can take reference ? – Dwijraj Bhattacharyya May 19 '20 at 08:53
  • Hikari IS the default connection pool used by Spring Boot. – M. Deinum May 19 '20 at 09:06
  • @M.Deinum can you tell me what is wrong with my configurations and how I can modify them to solve this issue ? – Dwijraj Bhattacharyya May 19 '20 at 14:35

0 Answers0