Nifi - How to disable connection pooling in DBCPConnectionPool controller service

[#1]

Hi,

All applications within our portfolio utilize PgBouncer as the database connection pool mechanism, and consequently, any application-level pooling is to be disabled. Apache NiFi is one of the applications required to adhere to this standard. We are seeking guidance on the options available to disable database connection pooling in NiFi. Could you please advise on how we can accomplish this? Our current nifi version is 2.1

Thanks for your help and let me know if more details are required.

[#2]

@shiva239 

NiFi's DBCPConnectionPool controller services is designed to create a pool of connections that are created the first time it is invoked.  These connections can then be used by multiple components on the canvas that are configured to use this same connection pool.  This is designed to maximize performance of the dataflows.

You can control the behavior of the connection pool  using the configuration properties available to this  controller service:

• Max Idle Connections
• Minimum Evictable Idle Time
• Minimum Idle Connections
• Soft Minimum Evictable Idle Time
• Time Between Eviction Runs
  

Please help our community grow. If you found any of the suggestions/solutions provided helped you with solving your issue or answering your question, please take a moment to login and click "Accept as Solution" on one or more of them that helped.

Thank you,
Matt

[#3]

Thank you @MattWho , for the detailed information. Could you please advise whether customization would be beneficial in our situation? Specifically, I would like to know if we can create a CustomDBConnectionService to establish a database connection in NiFi without any pooling. There is a centralized connection pool managed by PgBouncer in our environment that handles pooling.

Can you please clarify the below regarding DB connection customization.

1. Is it possible to create a custom database service without utilizing connection pooling?

2. If yes, what would be the impact on the performance of Apache NiFi when local pooling is not enabled?

However, I would like to ascertain whether the settings below would effectively lead to the soft or virtual disabling of connection pooling.

Property Name	Value	Comments
Max Idle Connections	0	Set to zero to make sure no idle connections in the pool
Minimum Idle Connections	0	Set to zero to make sure no idle connections in the pool
Minimum Evictable Idle Time	1 secs	Any idle connection will be eligible for eviction after 1 second
Soft Minimum Evictable Idle Time	1 secs	Any idle connection will be eligible for eviction after 1 second
Time Between Eviction Runs	1 secs	To run evictor thread every second
Max Connection Lifetime	-1	Can you clarify what does -1 indicate?  Does it mean no limit on the lifetime of a connection?
Max Total Connections	-1	Set to negative value to allow unlimited active connections
Max Wait Time	-1	Set to negative value to allow waiting indefinitely for connections
Validation query	Null	No validation of connection

Thanking you for your help!

[#4 · Accepted solution]

@shiva239 

1. If you are building your own custom components for NiFi, I suppose you can have them do whatever you want.  But considering your use case, you would be better off building a custom processor rather then a custom controller service.  For example, building a custom version of the PutDatabaseRecord processor that instead of using a connection pool controller service, makes a a direct connection for each record.

2. I have nothing setup to test those settings, but based on setting there is still opportunity for connection reuse with multiple NiFi FlowFiles.  There is the 1 sec between when one processing ends and the next may start that may grab the connection that is idling for 1 sec.  Keep in mind that there is nothing in the DBCPConnectionPool code that would prevent the sever from killing closing connections at end of transaction.  That is the whole purpose of the "ValidationQuery" existence.  It is not common that server side closes connection.  So when the DBCPConnectionPool tries to give an connection from the pool to a requesting processor, it runs the validation query to make sure the connection is still active.  If validation query fails, that one is dropped from pool a new connection is made.  I don't think "Max Idle Connections" is going to do anything since you set "Min idle connections" to zero which means "zero to allow no idle connections".

- Can you clarify what does -1 indicate?  Does it mean no limit on the lifetime of a connection? <-- yes

The setting you have sound solid, but I would still set a validation query to ensure avoiding any chance of a  race condition scenario where a 1 sec idle connection ends up getting reused that may already be a closed connection.  The processor would just sit there assuming the connection was good waiting for a return.  But with min idle connections set to 0, this may not be an issue.  I have not tested with  this specific setup ever.

Please help our community grow. If you found any of the suggestions/solutions provided helped you with solving your issue or answering your question, please take a moment to login and click "Accept as Solution" on one or more of them that helped.

Thank you,
Matt

[#5]

Thanks @MattWho  for your valuable inputs. This is very helpful for conducting the correct analysis on our end. I will check the possibility of customizing the processors that connect to the database.