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.

Please add hikari version and other settings of hikari to your question — Ori Marko, Dec 17 '17 at 15:47
My hikari version is HikariCP-2.6.1.jar. I dont have any other settings other that stated above in the question. Thank you. — user8012596, Dec 17 '17 at 19:04

Girdhar Singh Rathore · Accepted Answer · 2019-08-29T19:32:34.437

31

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

edited Aug 29 '19 at 19:32

answered Aug 25 '19 at 16:33

Girdhar Singh Rathore

5,030
7
49
67

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. – payne Dec 10 '21 at 18:40
1

@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. – nelsonda May 03 '22 at 22:58
34

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. – IcedDante Jul 06 '22 at 19:38
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. – Josh M. Feb 01 '23 at 14:40

score 10 · Answer 2 · edited Aug 08 '20 at 13:58

10

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

edited Aug 08 '20 at 13:58

Arghya Sadhu

41,002
9
78
107

answered Aug 08 '20 at 12:28

Andres Rincon

379
2
6

2

how did you initialize "dataSource" parameter that you are passing to DataSourceUtils.getConnection(dataSource)? – MedMahmoud Feb 24 '21 at 16:39
1

Look, like this: @Autowired private DataSource dataSource; – Andres Rincon Mar 01 '21 at 20:06
1

This SO question has more info on why: https://stackoverflow.com/questions/9642643/datasourceutils-getconnection-vs-datasource-getconnection – mowwwalker Aug 25 '21 at 05:30
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 – Ev.Rei. Feb 10 '23 at 20:58

Vlad L · Answer 3 · 2021-06-26T00:49:49.323

6

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

Try-with-resources fixed it:

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

edited Jun 26 '21 at 00:49

answered Jun 24 '21 at 04:38

Vlad L

1,544
3
6
20

1

How I dont get you ? which java are you using ? – user8012596 Jun 25 '21 at 17:36
1

Try with resources is since Java 7. – Vlad L Jun 25 '21 at 18:42
I did the same thing with Entity Managers, same thing different color.. – alegria Jan 30 '22 at 20:40

score 5 · Answer 4 · answered Sep 02 '22 at 10:42

5

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

answered Sep 02 '22 at 10:42

Yeshi

281
3
5

2

What if the entity manager is autowired? Do we need to also close it? – TheRealChx101 Dec 19 '22 at 22:08
No, the container should manage that within the transaction boundaries – Nanotron Jun 18 '23 at 14:38

score 2 · Answer 5 · answered Sep 22 '21 at 13:54

2

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

answered Sep 22 '21 at 13:54

HGreg

31
1

score 2 · Answer 6 · answered Feb 22 '22 at 21:32

2

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.

answered Feb 22 '22 at 21:32

ebeb

429
3
12

Is there any way to check what were the number of open connection on DB for a specific time period? – Akshansh Jain Dec 25 '22 at 15:20
Yes each database like MySQL, PostgreSQL have commands to list number of open connections you can run periodically. Do a google search. – ebeb Dec 26 '22 at 16:34

justCurious · Answer 7 · 2023-06-07T07:55:20.553

I've fixed my issue using:

increase the minIdle and maxPool

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.

score 1 · Answer 8 · answered Aug 03 '21 at 13:30

1

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
}

answered Aug 03 '21 at 13:30

mafyak

51
3

score 1 · Answer 9 · answered Nov 08 '22 at 22:33

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.

Geoffrey · Answer 10 · 2022-11-14T15:43:58.950

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.

rafaelnadal · Answer 11 · 2022-10-13T16:56:26.843

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.

Madhukar Tatiraju · Answer 12 · 2022-12-12T11:43:04.490

0

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

edited Dec 12 '22 at 11:43

answered Nov 17 '22 at 11:13

Madhukar Tatiraju

1
1

score 0 · Answer 13 · edited Sep 02 '23 at 03:57

0

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

edited Sep 02 '23 at 03:57

Abra

19,142
7
29
41

answered Aug 31 '23 at 17:19

Kavita Bansal

1

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

13 Answers13

Linked