Yeah, after analyse of design and think same problem after years.. I know it is design problem. as commentor said. Give thousands of connection to Hikari not means Database will handle that thousands of connection request. So design must be updated to optimum database connection and threads must wait the next connection available. if your thread not finishing in time and still working in minutes, hours.. So what you doing to give thousands connection to other threads. it is WRONG! totally wrong! Give thousands connection to threads not means ok everything has been solved. If you think we have huge performance :) it is fail success.
I have threaded system. 7000 thread processing some complex tasks.
We have java solutions: spring, hibernate for persistence, log4j2, hikari connection pool for db access (it's loaded and managed with beans)
I set Hikari connection pool size %30 of thread size before. But we got this Connection is not available, request timed out after 30025ms... error. After this error i cannot fix sevices stopping. I try to increase db pool size to 8000.
So thread size is : 7000, db pool size is: 8000..
But im getting this erros unexpected. What happening? Somebody can explain why db connections doesn't enoght this service?
I read most of users run their services with minimized pool size example 20.000 thread but only 20 db pool size. How could be done? Could you explain me, what is my service design problem.
Yes i have @Transactional annotation and Requires new on many fuctions in that complex taks. Is it problem on there? Point is this?
Could you help me find main problem what is it?
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC
Connection at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:431)
Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
Caused by: java.sql.SQLTransientConnectionException: springHikariCP - Connection is not available, request timed out after 30025ms.
Caused by: java.sql.SQLException: Network error IOException: Address already in use: connect
Caused by: java.net.BindException: Address already in use: connect
Just because you allow a program to use thousands of simultaneous connections to the database, doesn't mean the database can handle them. That's why increasing connections becomes counter productive after a while (in the tens or possibly hundreds of connections).
You can configure the timeout for waiting for a free connection (30 seconds in your example), but it doesn't change the fact that if you're trying to do much on a poorly sized server, you're going to be in trouble.
Get a bigger db (or scale out), use fewer threads or try to find performance hotspots in the database you can optimize.