How to increase database pool for Director?

[#1]

Where would someone increase the database connection pool for Director? Would this be in application.properties or in /etc/default/cloudera-director-server?

I've noticed after tuning the amount of threads for tasks and the amount of instances in parallel in the application.properties that if I try to provision 4 clusters of 100 nodes each at around the same time or with a small delay that Director has issues with not being able to get database connections causing failures.

To pre-empt this I have migrated to a serverless aurora with a minimum of 8 units which from the cloudwatch charts shows no spikes in connections yet Director still fails pipeline errors with the following:

java.util.concurrent.ExecutionException: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

I guess what I'm trying to determine is if a larger initial connection pool would help this.

[#2 · Accepted solution]

@Da It turns out that the inability to configure these properties was a Director bug which has been fixed for our upcoming 6.3 release. Once you are on 6.3, you will be able to configure the setting (for the Hikari connection pool, as it turns out) using the spring.datasource.maximumPoolSize property. I am sorry that there is no workaround for earlier releases.

[#3]

I can't confirm that your problem is caused by the connection pool size, and have never adjusted the connection pool settings manually, but Director uses spring-boot so you should be able to set the appropriate properties in application.properties as described in this stackoverflow question:
https://stackoverflow.com/questions/25573034/spring-boot-how-do-i-set-jdbc-pool-properties-like-maxi...

The property prefix should be spring.datasource, and the supported properties should be these:
http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes

For the pool size specifically, you should be able to set it like this:

spring.datasource.maxActive=200

[#4]

Hi Jadair,

Thanks for responding and pointing me at the spring configurations for the datasource.

However, I tried the following configuration which should keep 100 connections open at all times:

And observed in the cloudwatch monitoring AWS UI that director's connections when shut off dropped the total database connections from 149 to 139 (note that this database is shared by the CM that director spins up which accounts for the other 130ish connections).

When Director was started with the above spring configurations you can see the line return to 149 which indicates that Director only opened 10 connections to the database.

This implies that the defaults for spring.datasource.minIdle and spring.datasource.initialSize are still being used which are 10. Defaults are listed from the link you mentioned: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes

 So it seems Director is not respecting the spring.datasource.* configurations from application.properties. As I don't have access to the source code, is Director loading these things from configurations or hardcoding them somewhere? Apologies in advance if any of my assertions are false.

Quick addition is I also tried adding the -Dkey=value pairs to the JAVA_OPTS in /etc/default/cloudera-director-server but this didn't work either.

[#5]

We're relying on springboot, and are not doing anything special. I notice that in the application.properties embedded in the jar, some of the documented tomcat properties, like testOnBorrow and validationQuery, are being set with a prefix of spring.datasource.tomcat. Based on that stackoverflow answer, I wouldn't expect that to be required for maxActive, but give it a try and let me know if it works.

[#6 · Accepted solution]

@Da It turns out that the inability to configure these properties was a Director bug which has been fixed for our upcoming 6.3 release. Once you are on 6.3, you will be able to configure the setting (for the Hikari connection pool, as it turns out) using the spring.datasource.maximumPoolSize property. I am sorry that there is no workaround for earlier releases.

[#7]

@jadair Ah brilliant. Thanks for following up and confirming that this is a bug!

I'll update to 6.3 as soon as it is released then.

When it is released would I have to use properties such as:

spring.datasource.hikari.minimumIdle
spring.datasource.hikari.maximumPoolSize
spring.datasource.hikari.idleTimeout
spring.datasource.hikari.maxLifetime
spring.datasource.hikari.connectionTimeout

 
Or ones like what you mentioned with:

spring.datasource.maximumPoolSize

Note the spring.datasource.hikari.* versus the string.datasource.* key names.

[#8 · Accepted solution]

According to the comments on the Jira, the properties will not include hikari. That is consistent with the recommendation in the previously linked stackoverflow thread.

[#9]

After upgrading to 6.3 and setting the following in the application.properties:

spring.datasource.minimumIdle=100
spring.datasource.maximumPoolSize=200

I can see in the DEBUG logs of HikariConfig that those settings are being respected and the cloudwatch charts show the same.

Thanks again!