HikariPool-1 - Connection is not available, request timed out after 30000ms for very tiny load server

Question

I have a small Java application for testing purposes. I have moved to hikari recently. What I notice is that I keep getting this error.

java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
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)

Below is my settings for the hikari initially.

 HikariConfig config = new HikariConfig();
            config.setJdbcUrl("jdbc:mysql://localhost:3306/****"); 
            config.setUsername("***"); 
            config.setPassword("*****");      
            config.setMaximumPoolSize(20);  

Hardly its being used my two devices and I ensure towards the end I do close it. So I don't know why it keep getting the error? What could be the issue or is there some settings which I need to change?

My hikari version is HikariCP-2.6.1.jar.

Answer 1

Your database is not obtaining connection within (30000 milliseconds that is default connectionTimeout property) because of network latency or some of the queries which are taking too long to execute(more than 30000 milliseconds).

Please try to increase value of property connectionTimeout.

YML configuration example:

spring:
  datasource:
    hikari:
      minimumIdle: 2
      maximumPoolSize: 10
      idleTimeout: 120000
      connectionTimeout: 300000
      leakDetectionThreshold: 300000

Java Config example:

HikariConfig config = new HikariConfig();
        config.setMaximumPoolSize(20);
        config.setConnectionTimeout(300000);
        config.setConnectionTimeout(120000);
        config.setLeakDetectionThreshold(300000);

Answer 2

Make sure you close the connections once used.

An easy way to accomplish this in Kotlin is:

dataSource.connection.use { connection ->
    // Your code here
}

Answer 3

I've fixed my issue using:

increase the minIdle and maxPool/connectionTimeout

spring.datasource.hikari.minimumIdle=20
spring.datasource.hikari.maximumPoolSize=30
spring.datasource.hikari.connectionTimeout=50000

To debug the issue/check if the values are ok, enable the logging for Hikari:

logging.level.com.zaxxer.hikari.HikariConfig=DEBUG 
logging.level.com.zaxxer.hikari=TRACE

The logs will look like:

DEBUG 2023-01-06T16:12:31.932018849Z  HikariPool-1 - Before cleanup stats (total=17, active=0, idle=17, waiting=0)
DEBUG 2023-01-06T16:12:31.932665522Z  HikariPool-1 - After cleanup stats (total=17, active=0, idle=17, waiting=0)
DEBUG 2023-01-06T16:12:31.932733949Z  HikariPool-1 - Fill pool skipped, pool is at sufficient level.
DEBUG 2023-01-06T16:12:32.495269726Z  HikariPool-1 - After adding stats (total=17, active=0, idle=17, waiting=0)
DEBUG 2023-01-06T16:12:38.309953158Z  HikariPool-1 - Fill pool skipped, pool is at sufficient level.
DEBUG 2023-01-06T16:12:39.200246897Z  HikariPool-1 - Fill pool skipped, pool is at sufficient level.
DEBUG 2023-01-06T16:12:44.812065268Z  HikariPool-1 - Before cleanup stats (total=18, active=0, idle=18, waiting=0)
DEBUG 2023-01-06T16:12:44.812822113Z  HikariPool-1 - After cleanup stats (total=18, active=0, idle=18, waiting=0)

Good Luck ! :)

Warning: Please be careful, a big maximumPoolSize can be a code smell and can hide a performance issue e.g: long transactions. Also DB Vendors recommend to use a smaller maximumPoolSize, e.g: maximumPoolSize=10. The DB performance will be affected by big values for maximumPoolSize.

Answer 4

Generally opened and unclosed connections cause this problem.There is a limit of application servers to connect database and if you over this limit it will be crash your environment.

Connection must be stand on singleton pattern but if you really need to open a datasource or connect external datasource like reports you must close your connection in your finally block where you open connection block

connection.getConnection().rollback();
connection.getConnection().close();

You must also close if you are using PersistenceJpa without singleton

persistenceJPAConfig.dataSource().getConnection().rollback();
persistenceJPAConfig.dataSource().getConnection().close();

If you are using some stress test tools via creating threads to test your methods you probably get this error on your queries which take long time.It will be lead the way optimizing your queries or service instance size.

Answer 5

In my case a:

o.h.engine.jdbc.spi.SqlExceptionHelper: HikariPool-1 - Connection is not available, request timed out after 30019ms.
i.s.commons.web.error.ExceptionLogger: Internal Server Error
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Was caused by a too low spring.hikari.maximumPoolSize in the application properties, increasing from 5 to 20 solved the issue. The log message is kind of miss-leading.

Answer 6

In my case I used solution similar to @Andres Rincon:

try (Connection conn = connectionManager.getDataConnection()) {
    Statement stmt = conn.createStatement();
    ...
    conn.close();
} catch (Exception e) {
    e.printStackTrace();
}

Answer 7

request timeout is not something that you can fix by increasing the timeout. Perhaps you'd need to evaluate all the queries from your service and implement indexing if it's needed

Answer 8

In my case the code wasn't closing the connections.

Try-with-resources fixed it:

try (
    Connection connection = dataSource.getConnection();
    Statement statement = …
) {
…
}

Answer 9

Took forever to figure it out... In my case I used solution similar to @Andres Rincon:

try (Connection connection = DataSourceUtils.getConnection(jdbcTemplate.getDataSource())) {
    // some code here
}

Answer 10

This can also happen if the client app is requesting lot of open connections and the database server setting has a max limit on number of pool connections. So the client app is unable to get any more connections from the database server. Check the database server connections pool to see if the max is exceeded during the time period of the errors.

Answer 11

What fixed the issue in my case was to add proper indexing in the proper db tables. Take a look at the queries / transactions you're making to the db.

In my case the statement that was causing the latency was an UPDATE statement, e.g.

UPDATE table_name WHERE column1 = value1, column2 = value2;

What fixed the issue for me in this case was to add an index in that table for those two columns like:

CREATE INDEX index_name ON table_name (column1, column2);

Another good reason could be that you're not closing out your connections. You can close the connections with a try-with-resource statement like:

try( Connection connection = datasource.getConnection() ){
    //your code
}

In my opinion, increasing the timeout as Girdhar Singh Rathore suggested is not ideal. It could temporarily fix the issue, but at some point you'll need to take care of proper indexing and closing connections management.

Hope this helps.

Answer 12

In my case I have increased the size of hikaripoolConnection to 10 and this got resolved.

Answer 13

In my case I was using JPA and hence using EntityManagerFactory for persistence and query for my springBoot project and got the same error.

The reason was in any CRUD operation I was not closing EntityManager once the operation is done hence exhausting the resources.

Hope this helps!!

EntityManager em = emf.createEntityManager();
       Customer c  = em.find(Customer.class , id);
        em.close();

Answer 14

I am using spring boot and I was facing the same problem, and my solution was to get the connection like this "DataSourceUtils.getConnection(dataSource)". So I change from dataSource.getConnection() to DataSourceUtils.getConnection(dataSource).