Apache NiFi Database Connection Pool with Amazon Aurora MySQL

[#1]

I am trying to use two different processors to query an Amazon Aurora cluster. I have created an Amazon Aurora Database cluster running MySQL with three instances: the main instance that backs the cluster and two read replicas for balancing. However, the cluster does not seem to be balancing the reads at all and I believe this is because of the Connection Pool NiFi uses. The issue is that Aurora scales based on new incoming database connections but by using NiFi's connection pool the connections made to the database are never new because they are just freed after a query or update is executed and then reused. I know there is a parameter in Apache commons DBCP connection pool that is a “maxConnLifetime” setting that defaults to infinite. I think I want to find a way to set it to 10 seconds or something so I can keep making new connections to the database instead of reusing them. Does anyone have ideas on how to go about do this or any other ideas on how to get around this issue to make use of the scaling Aurora provides? Any help is appreciated.

[#2 · Accepted solution]

If you can't set that property on the JDBC URL, then as of Apache NiFi 1.2.0 (HDF NiFi 3.0.x), due to NIFI-3426, you can add user-defined properties that will be passed to the connection. A list of these properties is available here, and includes the one you mention.