sequelize-pool icon indicating copy to clipboard operation
sequelize-pool copied to clipboard

Custom keepalive on idle connections protected due to minimum pool size

Open brandonw opened this issue 9 months ago • 4 comments

Greetings!

While diagnosing some db issues, we realized there may be a feature that could help mitigate or remove aborted connections.

The current state of our setup is:

  • a pool with a min size
  • pool resource idle timeout of 10s
  • db connection idle timeout of 60s

On the surface, one would think there should never be any aborted clients because the pool idle timeout is lower than the db connection idle timeout. However, this is not the case because the pool (rightly) does not reap idle connections once you have reaped down to the pool minimum size.

This means that in periods of low activity, the db will end up aborting the pool idle connections because they are not being used but the pool is protecting them from reaping.

With a goal of reducing the number of db aborted clients to zero, I wonder if it is possible to allow some configuration in [1]. The goal being something like:

  1. removeIdle triggers
  2. all idle connections down to min pool size are reaped as normal
  3. all idle connections below the min pool size allow a config option to be called with that resource to basically tell the resource "do what you need to do in order to keep yourself healthy"

In my specific case using mysql2, this would probably be something like pooledObject.resource.ping().

The end result would be that even in periods of low traffic, the pooled connections below the min threshold would keep themselves healthy by continually resetting the idle timeout on the server so that they are not closed due to lack of usage.

Would a feature like that be useful or accepted? If so, I may be able to work on a PR.

[1] https://github.com/sequelize/sequelize-pool/blob/da269d7d8ca2763e87fc324beddfc6523d71167c/src/Pool.ts#L263-L277

brandonw avatar May 10 '24 21:05 brandonw