I have noticed some odd behavior in the NiFi DBCP connection pool between our NiFi 1.7 cluster and an Oracle 19c database. When the NiFi cluster is under load, it stops reusing database connections from the pool. Instead, it uses a connection once, closes it, and opens another one. There is no error in the NiFi log files or in the database - no timeouts, no broken connections or errors returned.
The "Max Wait Time" in the controller services is set to "500 millis" (default) and the "Max Total Connections" is set to "200". We never get more than about 30 connections going, and there are no timeout errors in our log. All database transactions are still completing, but they're taking a lot longer than they should because of the extra login overhead. The database audit trail shows clean logoffs, so this appears to be something that NiFi is doing intentionally.
I have searched this forum and several others and not found any other examples of this behavior or any clues as to what would cause it other than possibly a bug. Has anyone else seen this behavior or have any ideas how to correct it or troubleshoot it further? Is there a NiFi or DBCP parameter we could adjust that would help? I haven't been able to identify one that looks obvious.
Unfortunately upgrading our NiFi is not currently an option...
I would post logs, etc., but there is literally nothing to see there...
Did you set the
Validation Queryproperty? It is used to validate connections to see whether they should be evicted or reused. For Oracle I think you can just set it toSELECT 1 FROM DUAL, and that might help prevent a single connection from being trashed and recreated.There are other DBCP properties that can be set as user-defined properties on DBCPConnectionPool, but that wasn't available until NiFi 1.9 (via NIFI-5790), so you'd need to upgrade if those are the properties you're looking for.