CODEWITHSUNDEEP
spring-webfluxproject-reactorreactor-nettyspring-data-r2dbcr2dbc-postgresql
Amol Kshirsagar
Amol Kshirsagar

Reputation: 253

Validation query for R2DBC remains Idle when connection pool created using io.r2dbc.pool.ConnectionPoolConfiguration

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:

enter image description here

Upvotes: 0

Views: 2292

Answers (1)

Daniel
Daniel

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

Related Questions