Out of the box, NiFi (at least, as of 1.8.0), appears to keep open one database connection for each DBCPConnectionPool controller service enabled. I have multiple DBCPConnectionPool controller services configured to access the same server with different options, so this quickly adds up against a limited number of connections to my database server. I have a scheduled workflow that runs ~nightly. Connections need not be active except when in active use during a short window of time.
Looking around DBCPConnectionPool documentation, it appears that there are not controller service configuration options associated with Apache Commons-DBCP BasicDataSource parameters like minIdle and maxIdle, which I think should be both set to 0 in my particular use case. Alternatively, I think I could set maxConnLifetimeMillis to something even in the minutes range and satisfy my use case (a connection need not be released immediately but within a reasonable period of time).
Is there a way to pass this options without modifying the DBCPConnectionPool code? Will Commons-DBCP accept these options passed as JDBC parameters by setting them as dynamic properties on a NiFi DBCPConnectionPool controller service configuration?
NiFi as of 1.8.0 keeps one connection per database open at all times because it uses the default configuration of Commons-DBCP 2.5.0 that does not clean up connections. The Commons-DBCP default value for timeBetweenEvictionRunsMillis is -1, which causes idle connection eviction never to run.
NiFi 1.8.0 does not expose the Commons-DBCP configuration options necessary to adjust these timings.
NiFi 1.9.0 will expose these settings, added in this commit.