Reputation: 253
I have a spring webflux application integrated with AWS Aurora Postgres.
spring-boot-starter-webflux:2.6.6
spring-boot-starter-data-r2dbc:2.6.6
This brings in the below dependencies:
io.r2dbc:r2dbc-spi:jar:0.8.6.RELEASE:compile
io.r2dbc:r2dbc-pool:jar:0.8.8.RELEASE:compile
Connection pooling has been implemented using the configuration below
@Bean
@Qualifier("customConnectionFactory")
public ConnectionFactory connectionFactory() {
ConnectionFactory connectionFactory =
new PostgresqlConnectionFactory(
PostgresqlConnectionConfiguration.builder()
.host(hostName)
.port(5432)
.database(databaseName)
.username(userName)
.password(password)
.connectTimeout(Duration.ofMinutes(120))
.build());
ConnectionPoolConfiguration configuration =
ConnectionPoolConfiguration.builder(connectionFactory)
.maxIdleTime(Duration.ofMinutes(30))
.initialSize(200)
.maxSize(300)
.maxLifeTime(Duration.ofMillis(Long.MAX_VALUE))
.build();
return new ConnectionPool(configuration);
}
@Bean
@Qualifier("customPostgresClient")
public DatabaseClient databasePostgresClient(
@Qualifier("customConnectionFactory") ConnectionFactory connectionFactory) {
return DatabaseClient.create(connectionFactory);
}
The issue is that validation query (SELECT 1) is triggerred at regular intervals when the application is started and these connection remains in idle status forever. The application queries do not remain in idle status.I have tried changing the maxLifeTime value to 2 hours but even after 2 hrs the connections can still be seen as idle.Below is the query I use to check the connection status
select
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state,
pg_stat_activity.query_start
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' and query='SELECT 1' order by pg_stat_activity.query_start desc;
Results comes up as below:
Upvotes: 0
Views: 2292
Reputation: 887
Have you tried using backgroundEvictionInterval
as described here https://github.com/r2dbc/r2dbc-pool/blob/main/README.md
It seems that it is disabled by default which could explain idle connections are not evicted automatically (I guess it would then be evicted if you reach the max pool size)
Upvotes: 0