search

r2dbc / r2dbc-pool

Connection Pooling for Reactive Relational Database Connectivity
https://r2dbc.io
Apache License 2.0
331 stars 55 forks source link

Provide a way to do scheduled connection validation #187

Open grantas33 opened 1 year ago

grantas33 commented 1 year ago

Feature Request

Is your feature request related to a problem? Please describe

Idle connections can become invalid due to timeouts on the database or the network side or other causes. In such case, when performing a database call we potentially would have to retry the acquirement of the whole pool size of connections and re-warmup the pool if no valid connections exist. This adds a substantial amount of time to the database call, which could have been avoided if the connection validation was periodically run in the background.

Describe the solution you'd like

The implementation behind backgroundEvictionInterval configuration parameter could be extended. Instead of checking for each connection if it has exceeded its maxIdleTime, it would also check for each connection if it is valid. Also, after the checks, the connection pool should be re-warmuped, so that it contains at least minIdle connections.

This feature exists in popular JDBC pool libraries like HikariCP (keepaliveTime) and C3P0 (idleConnectionTestPeriod) and the lack of scheduled validation could be a problem when migrating to r2dbc-pool

ifindya commented 1 year ago

Yes, it seems to be a problem for me as well. I implemented it in my program, and I hope there has a plan about supporting things like "keep-alive".

mp911de commented 1 year ago

Background validation would require taking the connection from the pool, testing it, and putting it back to ensure that the pool doesn't use it concurrently and another process that allocates the connection. In any case, that kind of functionality would be required to be provided by Reactor Pool. Feel free to file a ticket at https://github.com/reactor/reactor-pool/issues

grantas33 commented 1 year ago

Issue was created at https://github.com/reactor/reactor-pool/issues/169

grantas33 commented 1 year ago

Background validation would require taking the connection from the pool, testing it, and putting it back to ensure that the pool doesn't use it concurrently and another process that allocates the connection. In any case, that kind of functionality would be required to be provided by Reactor Pool. Feel free to file a ticket at https://github.com/reactor/reactor-pool/issues

@mp911de https://github.com/reactor/reactor-pool/issues/169#issuecomment-1499045186 is this functionality provided by reactor-pool sufficient?

ifindya commented 1 year ago

Connection is removed if invalid when getting from the pool. How did this happen? Any help would be appreciated.

mp911de commented 1 year ago

@grantas33 the comment refers to warmup, not scheduled validations. For scheduled validation, we would need to have a scheduler and a task to allocate/de-allocate a connection from the pool. When we do this from the outside, then we have to follow the pool order and therefore, we cannot guarantee that we've verified every connection.

That being said, we require support for scheduled validation in Reactor Pool first.