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.

Solution 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);

Solution 2

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).

Solution 3

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

Try-with-resources fixed it:

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

Solution 4

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.

Solution 5

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

View more solutions

Comments

[user7294900]

Please add hikari version and other settings of hikari to your question

[user8012596]

My hikari version is HikariCP-2.6.1.jar. I dont have any other settings other that stated above in the question. Thank you.

[MedMahmoud]

how did you initialize "dataSource" parameter that you are passing to DataSourceUtils.getConnection(dataSource)?

[Andres Rincon]

Look, like this: @Autowired private DataSource dataSource;

[user8012596]

How I dont get you ? which java are you using ?

[Vlad L]

Try with resources is since Java 7.

[mowwwalker]

This SO question has more info on why: stackoverflow.com/questions/9642643/…

[payne]

Assuming all our endpoints run in less than 2 seconds, what else might be the cause of a timeout when trying to acquire a connection? Our application runs fine as 1 instance, but once it scales horizontally, we get those errors in all of the newly created applications.

[alegria]

I did the same thing with Entity Managers, same thing different color..

[nelsonda]

@payne I'm a little late to this comment, but if you're running into this as you scale horizontally, I'd hazard a guess that you're exhausting the connection limits on the database server.