Vertx Mysql: Ensuring minimumIdle number of connections in the Pool

[Chandra R.]

Hi, I'm using the vertx MySql client for a high traffic application, and I wanted to know if there's any way to ensure a minimum number of Idle connections in the Pool.

(e.g. HikariCP has this option).

This is especially useful when I use the idleTimeout option with the pool. Currently, when idleTimeout expires, connections are closed by Vertx but no new connections are created proactively till a call is made to getConnection() when a request arrives to my application.

In my scenario, acquiring a new connection is expensive (100+ms), so it helps if the connection pool is warmed with "minIdle" connections. 

Another scenario where this is useful is in steady state, let's say I use 8 connections. Often, there could be a burst of traffic that requires creating new connections. If the peak traffic is predictable, having a "warm" connection pool will allow the application to absorb at least some of the burst by not spending time in creating new connections.

One way I think this can work is to "optionally" schedule a new connection creation internally whenever the closeHandler in the Pool is invoked, up to minIdle connections. Internally, I believe this will call pool.acquire() with a flag to create a new connection.

Does this sound reasonable?

[Thomas SEGISMONT]

Hi,

Somebody in the community is working on it: https://github.com/eclipse-vertx/vertx-sql-client/pull/1298#issuecomment-1491000661

Regards

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/15cdef64-4502-4ba0-b0df-5e0234740bd2n%40googlegroups.com.

[Chandra R.]

Thank you. I checked the linked PR - makes complete sense to have a non-idle but total Lifetime Ms, and follow it up with a way to replenish the evicted connections.

Couple of questions:

1) Will this be backported to 4.x?

2) The current "Eviction" mechanism can evict connections that are IN_USE. What is the behavior in this case?

Does the socketConnection.close() get queued up behind pending requests on the connection, so any existing requests before CLOSE won't see any error?

Or does this mean that all pending requests (before/after CLOSE) will see an error the moment an IN_USE connection is evicted underneath?

[Thomas SEGISMONT]

Le ven. 14 avr. 2023 à 20:34, Chandra R. <mahe...@gmail.com> a écrit :

Thank you. I checked the linked PR - makes complete sense to have a non-idle but total Lifetime Ms, and follow it up with a way to replenish the evicted connections.

Couple of questions:

1) Will this be backported to 4.x?

Possibly

 

2) The current "Eviction" mechanism can evict connections that are IN_USE. What is the behavior in this case?

Does the socketConnection.close() get queued up behind pending requests on the connection, so any existing requests before CLOSE won't see any error?

Or does this mean that all pending requests (before/after CLOSE) will see an error the moment an IN_USE connection is evicted underneath?

A connection in use cannot be evicted.

See https://github.com/eclipse-vertx/vert.x/blob/master/src/main/java/io/vertx/core/net/impl/pool/SimpleConnectionPool.java#L524

If slot usage is not equal to zero, the connection is not evicted.

 

On Friday, April 14, 2023 at 5:37:18 AM UTC-7 Thomas SEGISMONT wrote:

Hi,

Somebody in the community is working on it: https://github.com/eclipse-vertx/vertx-sql-client/pull/1298#issuecomment-1491000661

Regards

Le ven. 14 avr. 2023 à 09:37, Chandra R. <mahe...@gmail.com> a écrit :

Hi, I'm using the vertx MySql client for a high traffic application, and I wanted to know if there's any way to ensure a minimum number of Idle connections in the Pool.

(e.g. HikariCP has this option).

This is especially useful when I use the idleTimeout option with the pool. Currently, when idleTimeout expires, connections are closed by Vertx but no new connections are created proactively till a call is made to getConnection() when a request arrives to my application.

In my scenario, acquiring a new connection is expensive (100+ms), so it helps if the connection pool is warmed with "minIdle" connections. 

Another scenario where this is useful is in steady state, let's say I use 8 connections. Often, there could be a burst of traffic that requires creating new connections. If the peak traffic is predictable, having a "warm" connection pool will allow the application to absorb at least some of the burst by not spending time in creating new connections.

One way I think this can work is to "optionally" schedule a new connection creation internally whenever the closeHandler in the Pool is invoked, up to minIdle connections. Internally, I believe this will call pool.acquire() with a flag to create a new connection.

Does this sound reasonable?

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/15cdef64-4502-4ba0-b0df-5e0234740bd2n%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/99eaba58-6c4f-44ad-b7bd-def59373845dn%40googlegroups.com.

[Chandra R.]

Sorry, I missed the usage check in the Evict logic.

In that case, 

a) Is it possible that a connection never gets evicted because it is heavily in use?

Do you think it is a good idea to mark IN_USE connections as evicted, and skip returning it back to the application as part of getConnection()?

All such evicted connections will be counted as non-existent from the point-of-view of the minIdle Pool Size, so minIdle would still make sure we have minIdle connections NOT_IN_USE and NOT_EVICTED.

b) I think it would be a good idea to add a jitter to the maxLifetime in PooledConnection similar to: https://github.com/brettwooldridge/HikariCP/blob/dev/src/main/java/com/zaxxer/hikari/pool/HikariPool.java#L464

I'm willing to make this change (and also the change mentioned in the previous point) once this PR is merged.

[Thomas SEGISMONT]

Two good comments, thank you.

I'll ping you here when the PR is merged.

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/660d64ed-7392-41d2-b7a2-6eea1c2c6293n%40googlegroups.com.

[Thomas SEGISMONT]

The PR has been merged

[Chandra R.]

Hi, can I get your thoughts on https://github.com/eclipse-vertx/vertx-sql-client/pull/1319, especially regarding the additional work required to merge this PR?
Thank you.

[Thomas SEGISMONT]

Hi,

Sorry for the delay, I haven't been able to come back to this yet.

When we last looked at it, our concern was that maybe this feature should belong to the core pool implementation.

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/eec3793f-02e0-4b8c-a210-143b3350fdf1n%40googlegroups.com.

[Chandra R.]

I tried implementing the idea mentioned in: https://github.com/eclipse-vertx/vert.x/issues/4680

Basically,

1) Add a maxTttlMs option to the pool

2) Add a softEvict flag to the slot (boolean), and createTimeMs field to the slot.

3) createTimeMs is initialized when a connection is created in a slot

4) In recycle, check if the maxTtlMs has expired for the slot. If yes, and if the slot usage is 0, remove the connection and set softEvict = true. If not, only set softEvict = true.

5) In acquire, add a check to the Selector to not select any slot that has softEvict = true

Then, during pool initialization and during remove, we need to create a method that refreshes the pool with minIdle connections (not part of the gist).

If this looks good to you, I can create a PR: 

https://gist.github.com/chandramouli-r/f8adf3b765919ceb54dea9126a2d97f2

[Julien Viet]

Hi,

we are actually thinking of a class that would use the simple
connection pool for that and be reused in other projects instead of
modifying the simple connection pool itself.

the simple connection pool is passive and does not handle things like
timer, etc...

JUlien

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/a78d9ea9-ac95-40ee-a7bb-d254d93fa123n%40googlegroups.com.

[Chandra R.]

Thanks for the update. Does the rest of the code/proposal seem ok? (to handle maxTtl on the recycle path?)

[Julien Viet]

Hi,

for the recycle part, you mean that if a connection is recycled and is
too old then the connection is closed and a new connection is created
?

Julien

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/b4c0c661-763b-4c33-8ff9-992095336c71n%40googlegroups.com.

[Chandra R.]

Yes. I was thinking of 2 steps:

1) recycle/eviction can mark the slot as inactive, so that a subsequent Acquire does not hand over the connection.

2) Whenever a slot is marked as inactive, a corresponding pool.connect() can be implicitly called

The third thing is basically ensuring minimum number of connections, which can be done separately as you mentioned earlier (since it doesn't fit in SimpleConnectionPool)

[Julien Viet]

Hi,

I think that we should have the minimum active in the pool actually,
if there is an initial call to activate the pool (e.g an init method)

Then the pool can maintain this invariant when it triggered, e.g the
init method will create the min connections and each subsequent pool
action will try to maintain this invariant.

Julien

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/65bd7043-3931-4f67-bccb-34039ece33d1n%40googlegroups.com.

[Chandra R.]

Makes sense to me. I created a draft PR for handling https://github.com/eclipse-vertx/vert.x/issues/4680

The context is that the maxLifeTime  check outside of SimpleConnectionPool can simply use lease.release() instead of lease.recycle() on the connection return path.

A follow-up to this PR would be to maintain the minIdle invariant whenever remove() is triggered either via evict or via release().

Here's the Draft: https://github.com/eclipse-vertx/vert.x/pull/4736

Please let me know if I am going in the right direction. I can then add the maxLifetime check (on the connection returning to pool path) to vertx-sql-client

[Julien Viet]

Hi,

I think instead we should have a checker inside the pool that decides
what to do with a connection when its usage becomes 0.

Currently the connection is maintained, instead we could have a
checker that returns a value that indicates whether the connection
remains valid or not and when it is not valid it is closed by the
pool.

Combined with the min idle connection invariant we want to maintain,
this will automatically create a new connection in the pool when a
stale connection is closed and the min connection size is not reached.

Julien

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/c23bcbe1-def0-4821-b274-471e07b3752fn%40googlegroups.com.

[Chandra R.]

Sounds good to me. Do you agree that the maxLifetime is handled outside of the pool (as it is done currently?).

[Julien Viet]

I am actually leaning toward this plan:

1/ implement the min size in the pool as invariant

2/ add a method to trigger pool invariant that calls the connection
checker and ensures that min pool size is honoured

3/ replace the current pool evict method by a connection validator
method, this connection validator is called

- when a connection use becomes 0
- by the pool invariant check

Unless I am wrong this looks coherent to me and minimise the amount of
work to do in the various pool use of the vertx stack to implement the
features we need.

then maxLifetime could be handled by the connection validator

Julien

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/002e23df-c587-4512-b910-b3d8783708a4n%40googlegroups.com.

[Chandra R.]

I see (1) and (2) make sense to me. 

In your proposal, is the "connection validator" something that can be passed to SqlConnectionPool/SimpleConnectionPool?

If we do that, then the validator can be used to check things like maxLifetime outside of SimpleConnectionPool, and all that SimpleConnectionPool does is to maintain the minIdle invariant.

Something like, 
MySQLPool pool = MySQLPool.pool(...);

pool.connectHandler(foo).connectionValidator(bar);

SqlConnectionPool can have an optional validator for validating lifetime/idleTime if that check is enabled in PoolOptions.

[Julien Viet]

no the validator would be internal and not exposed.

it is a synchronous validator that is used to implement max lifetime and such.

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/ecac6e38-d7d0-4547-9099-570c326f55d7n%40googlegroups.com.

[Chandra R.]

Thanks for the clarification. Please let me know if I can help out in any way.

Just curious - when are we targeting this feature for?

[Chandra R.]

Hi, any updates on which release we would be targeting this feature for?

[Julien Viet]

Hi,

I think this means some internal rewrite of the connection pool logic,
at least Vert.x 5 could have this work, I don't know if we would
backport this to 4.x

Julien

> To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/506ad9d2-a125-4457-8f17-da11b6dfd3a6n%40googlegroups.com.