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.

Hamill

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

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

Are you using Hikari along with some framework? Like JDBI, Spring Data, jOOQ, Hibernate? – Rozele

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

Swedenborgianism

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. – Jessejessee

@Jessejessee 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. – Inae

Just an important caveat for people reading the most upvoted answer: increasing the timeout may solve the symptom but almost certainly does not address the root problem. – Overshoot

Seems like the problem here would more likely be fixed by increasing the number of connections allowed on the DB server / in the pool, rather than increasing the timeout. If you have to wait 30sec to get a connection from the pool, that's really not acceptable. – Conglutinate

is it connection to database, or waiting for one in the pool to be available? – Immethodical

@owengerig, connectionTimeout This property controls the maximum number of milliseconds that a client (that's you) will wait for a connection from the pool. If this time is exceeded without a connection becoming available, a SQLException will be thrown. Lowest acceptable connection timeout is 250 ms. Default: 30000 (30 seconds) reference – Throwback

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

Colver

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

Look, like this: @Autowired private DataSource dataSource; – Colver

This SO question has more info on why: #9643143 – Almond

I had exactly same problem and I used solution above and it worked just well! I am curios what DatSourceUtils does differently when getting connection – Abelmosk

DataSourceUtils verifies the connection to use wether there are active ones and the pool and it seems its more effective than the simple getConnection() one, also I want to add that this happened to me using MS SQL Server, I had the same code using MySQL and never saw that error. – Extinctive

Answer 3

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

Try-with-resources fixed it:

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

Audraaudras

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

Try with resources is since Java 7. – Audraaudras

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

Answer 4

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

Cerebritis

What if the entity manager is autowired? Do we need to also close it? – Dredger

No, the container should manage that within the transaction boundaries – Evangelineevangelism

Answer 5

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.

Acting

Answer 6

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.

Rotor

In my case I was also running out of connections, but I had to rewrite parts of my app to manage connections better. – Wilson

Answer 7

Make sure you close the connections once used.

An easy way to accomplish this in Kotlin is:

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

Boreal

Answer 8

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

Beria

Answer 9

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.

Hilda

Is there any way to check what were the number of open connection on DB for a specific time period? – Swahili

Yes each database like MySQL, PostgreSQL have commands to list number of open connections you can run periodically. Do a google search. – Hilda

Answer 10

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.

Blondellblondelle

Answer 11

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
}

Renascence

Answer 12

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();
}

Harts

Answer 13

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.

Drakensberg

Answer 14

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

Haver

Answer 15

In my case ,I use flowing method ,get connection with Try-with-resources fixed it:

try (Connection connection = DataSourceUtils.getConnection(dataSource){
} catch(SQLException e){
}

Driedup