Connection pool configurations

Hi,

I’m trying to configure a connection pool for the PostgreSQL DB used by Camunda. However, the configurations won’t be in place. I’m wondering whether I’m doing something wrong. Let me say that, once the Spring Boot app is run, there is no problem, everything works fine. However, after a certain period of unused time e.g. a whole weekend, I start to get timeouts from the REST service e.g. for this path: /rest/engine/default/history/task?processVariables=responderID_eq_482&sortBy=startTime&sortOrder=desc&maxResults=10

I tried to different way of configuring the connection pool with no success. I put the following configurations into the bootstrap.yml file keeping the other configurations as well. Here they are:

The following part is common for both way:
camunda:

bpm:
webapp:
index-redirect-enabled: false
database:
schema-update: true
type: postgres
admin-user:
id: kermit
password: superSecret

First way:
spring:
datasource:
url: jdbc:postgresql://${CAMUNDA_DB_HOST:localhost}:${CAMUNDA_DB_PORT:5432}/${CAMUNDA_DB_NAME}?currentSchema=public
username: ${CAMUNDA_DB_USERNAME}
password: ${CAMUNDA_DB_PASSWORD}
driverClassName: org.postgresql.Driver
initialSize: 10
testWhileIde: true
timeBetweenEvictionRunsMillis: 600000
validationQuery: SELECT 1

Second way:
spring:
datasource:
url: jdbc:postgresql://${CAMUNDA_DB_HOST:localhost}:${CAMUNDA_DB_PORT:5432}/${CAMUNDA_DB_NAME}?currentSchema=public
username: ${CAMUNDA_DB_USERNAME}
password: ${CAMUNDA_DB_PASSWORD}
driverClassName: org.postgresql.Driver
tomcat:
initial-size: 10
test-while-idle: true
time-between-eviction-runs-millis: 600000
validation-query: SELECT 1

With these configurations, I expect that there should be always 10 DB connections and they should be validated in 10 minutes of intervals. I neither see 10 connections for the database nor the existing ones are validated periodically.

So, is there something wrong I’m doing? What should be the correct way?

Thanks in advance! (Sorry for the improper formatting in the post)

Hi @Suat_Gonul,

Have a look at Why Tomcat need to restart every day? in case of tomcat connection pool configuration.
I think you are missing testOnBorrow conf.

Best regards,
Yana

Hi @yana.vasileva

I didn’t use the testOnBorrow parameter but did use the periodic validation approach. Anyway, the problem is solved when I added the tomcat-related configurations to the DataSource Bean in the source code. Here are the current configurations and Bean created:

Configurations:
spring:
datasource:
url: jdbc:postgresql://${CAMUNDA_DB_HOST:localhost}:${CAMUNDA_DB_PORT:5432}/${CAMUNDA_DB_NAME:camunda}
username: ${CAMUNDA_DB_USERNAME}
password: ${CAMUNDA_DB_PASSWORD}
driverClassName: org.postgresql.Driver
tomcat:
initial-size: 10
test-while-idle: true
time-between-eviction-runs-millis: 600000
min-evictable-idle-time-millis: 3600000

And the bean injected:
@Bean
@Primary
public DataSource getDataSource() {
DataSource ds = DataSourceBuilder.create()
.url(environment.getProperty("spring.datasource.url"))
.username(environment.getProperty("spring.datasource.username"))
.password(environment.getProperty("spring.datasource.password"))
.driverClassName(environment.getProperty("spring.datasource.driverClassName"))
.build();

// Assume we make use of Apache Tomcat connection pooling (default in Spring Boot)
org.apache.tomcat.jdbc.pool.DataSource tds = (org.apache.tomcat.jdbc.pool.DataSource) ds;
tds.setInitialSize(Integer.valueOf(environment.getProperty("spring.datasource.tomcat.initial-size")));
tds.setTestWhileIdle(Boolean.valueOf(environment.getProperty("spring.datasource.tomcat.test-while-idle").toUpperCase()));
tds.setTimeBetweenEvictionRunsMillis(Integer.valueOf(environment.getProperty("spring.datasource.tomcat.time-between-eviction-runs-millis")));
tds.setMinEvictableIdleTimeMillis(Integer.valueOf(environment.getProperty("spring.datasource.tomcat.min-evictable-idle-time-millis")));
return tds;
}

Thanks!

1 Like