Cannot get a connection, pool error Timeout waitin

问题:

I have increased the concurrent tasks to be '10' for PutSQL processor.

At that time it shows below error but there is no data loss.

failed to process due to org.apache.nifi.processor.exception.ProcessException: org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object; rolling back session: 

if i have remove concurrent tasks then it worked without those exception

while google this exception i have found answer in below link

I am getting Cannot get a connection, pool error Timeout waiting for idle object, When I try to create more than 250 threads in my web application

But i don't know how to avoid this issue in NiFi putSQL.

Can anyone help me to resolve this?

回答1:

This exception occurs when the pool manager cannot produce a viable connection to a waiting requester and the maxWait has passed therefore triggering a timeout. There are several causes, but they usually fall into 2 main categories:

• The DB is down or unreachable.
  
• The connection pool (which is set to 100 max active) is out of connections.

DBCPConnectionPool controller service in nifi has 8 max connection by default and 500 milli seconds max wait time. When PutSQL processor occupied 8 connection from DBCP pool and when it request for 9th connection or exceed the max connection limit then it will throw "Cannot get a connection" exception.

You can try 2 things to avoid this Exception :

1. You can increase the "Max Wait Time" in DBCPConnectionPool controller
   service configuration.
2. You can increase the "Max Total Connections" limit in DBCPConnectionPool controller service configuration.

Kindly find the below screenshot where you need to do changes.

It might resolve your issue.

回答2:

This exception can occurs if some connections are never closed so they do not become available in the pool again. So more and more connections remain open until reaching the max.

Make sure all threads are closing the connections used.