Recommendation on connection recycling

[nitsnwits]

Based on the recommendation from AWS Aurora MySQL DBA Handbook

• Check and validate connections periodically even when they're not
borrowed. It helps detect and clean up broken or unhealthy connections
before an application thread attempts to use them.

• Don't let connections remain in the pool indefinitely. Recycle
connections by closing and reopening them periodically (for example,
every 15 minutes), which frees the resources associated with these
connections. It also helps prevent dangerous situations such as runaway
queries or zombie connections that clients have abandoned. This
recommendation applies to all connections, not just idle ones.

Can you please suggest a good way to recycle connections in a pool? Aurora has DNS based endpoints and any DNS changes do not reflect in the connections right away. Moreover, if there are multiple readers, creating a pool doesn't uniformly distribute the connection load amongst the readers. If the connections are periodically reset (closed/reopened) before application accesses it, in the longer run, it can fully utilize multiple readers for read-scalability. I did not find a way where I can get all the connections from the pool and reset them. I can potentially loop through _allConnections.length, keep some state based on connectionId, but there's no way to free a connection and not get it back again. If I recreate the pool, that seems very expensive to perform every 15 minutes and there could be other instances/functions using the pool to get connections at the time it's recreated. I could potentially get a connection from the pool and remove it from the pool and close it. Would the pool recreate the connection lazily again in that case? It still won't guarantee that every time this operation is run, the same connection is not closed and recreated.

Any suggestions are welcome, happy to create a PR if this could be a useful feature in longer run.

[sidorares]

the easiest way to reset all connections would be to close all of them via pool.end() and create another pool pool = mysql.createPool({...})

[sidorares]

not sure what you mean by reseting a connection? Also if you close individual connection pool should track that it's closed and remove from list of active connections

[sidorares]

same connection is not closed and recreated

Also not sure I follow here. Connections are never recreated, wether when managed by pool or not. When connection is closed there is no way to re-attach it again. It notifies all pending commands about error ( if closed forcefully ) and thats it

[nitsnwits]

I'm concerned that if I close out the pool, any function that has borrowed a connection from it at the time, would abruptly disconnect. I'm not sure if there's a way to drain the connections from a pool and say close it when all the connections are free, as the application starts using the newly created pool.

By "resetting connection" I meant closing and reopening (mostly to get the updated DNS info).

By same connection is not closed and recreated, I meant if I do this:

const conn = pool.getConnection();
pool.releseConnection(conn);

every 15 minutes, it's possible that every time getConnection is called, I'm just getting the newest (newly created) connection and the oldest connection is never closed. Like, if there are 10 connections in the pool, I can likely get the 10th one every time and close it, pool will recreate it, and I'll keep repeating that process while the other 9 are still connected to an old instance, not helping in balancing the load uniformly across the readers. Hope it makes sense.

[sidorares]

pool.end() waits until last connection is closed, example

let pool = mysql.createPool({...});
pool.query('select sleep(1)');
pool.query('select sleep(1)');
pool.query('select sleep(1)');
pool.end();
pool = mysql.createPool({...});

all 3 selects will be processed in the background while you can immediately use new pool

[nitsnwits]

Interesting, thanks for that input. I think recreating the pool could be the solution in that case. I'll try that.

A follow-up suggestion (taken from the handbook link shared in the original description):

For the purposes of this whitepaper, a “smart driver” is a database driver or
connector with the ability to read DB cluster topology from the metadata table.
It can route new connections to individual instance endpoints without relying
on high-level cluster endpoints. A smart driver is also typically capable of load
balancing read-only connections across the available Aurora Replicas in a
round-robin fashion.

The MariaDB Connector/J is an example of a third-party Java Database
Connectivity (JDBC) smart driver with native support for Aurora MySQL DB
clusters. Application developers can draw inspiration from the MariaDB driver
to build drivers and connectors for languages other than Java.
See the MariaDB Connector/J page for details.2

Follow-up reference: https://mariadb.com/kb/en/failover-and-high-availability-with-mariadb-connector-j/#specifics-for-amazon-aurora

Full aurora support could be a different issue, but I'm suggesting that this could be a potential feature that every connection in the pool has a lifetime and is closed after that time automatically and a new connection is created lazily.

Sorry about the segue. Let me know if that's interesting.

I'll update this issue once I confirm recreating the pool helps in recycling connections.

[sidorares]

I'll need to check what's the status of pool connection idleTimeout. It might be already implemented, for the purpose of Aurora balancing you might want to set it to a relatively low number ( say, 10 seconds )

[nitsnwits]

@sidorares Did you get a chance to check on the idleTimeout for a pool connection? I did not find it in the code, I checked these files:

https://github.com/sidorares/node-mysql2/blob/master/lib/pool_connection.js https://github.com/sidorares/node-mysql2/blob/master/lib/connection.js https://github.com/sidorares/node-mysql2/blob/master/lib/pool.js

I also tested (and stress-tested) the pool.end() workaround. The application is a node app serving an API that connects to the database and runs a select query and returns the results. There's a caveat to watch out for, which is, create the new pool, let the new requests use the new pool (and create connections lazily on-demand), but close the old pool after waiting a while to let the old requests finish, and any requests that were in-flight before the pointer to pool was switched to new pool. This is still a fragile situation under high-stress as concurrently, it's possible a lot of callbacks were scheduled which still had the pointer to the old pool and will run into errors when they execute a query, which leads me to the conclusion that there's no perfect time to end a pool under high stress within your application.

I think there could be a better approach here as you suggested (and some other alternatives):

• idleTimeout for a connection (a pool can, after some time, when a connection is free, remove it from the pool and close it)
• alternatively, if there's a way to know how long a connection has been alive in the pool, maybe, have a method in the pool, get the oldest one, and periodically, the client can close that connection

  const conn = pool.getOldestConnection();
  conn.removeFromPool();
  conn.close();

Let me know what do you think. Open to any other suggestions for Aurora balancing (any reference/inspirations from MariaDB Smart Driver suggested in AWS docs, as shared in comments above).

[olsavmic]

We have the very same use case with AWS Aurora (dynamic number of read replicas so in order to use both connection pool and discover new hosts, we need to close the connection after some time and recreate new with refreshed DNS). It's basically required for HA.

The only viable solution seems (as @nitsnwits said) adding maxConnectionLifetime option per connection that will be checked when the connection is released. We already have several java applications using the very same "functionality" and it seems like it should not be hard to add when I look at https://github.com/mysqljs/mysql/pull/2218 which solves a very similar issue.

@sidorares Would you be open for such change?

[linkdrone]

Is't might be already implemented? @sidorares

[danielhinrichs86]

It would be great to see this or a similar add to the mysql2 pool to make it more cluster aware and "Aurora friendly". When we horizontally scale our Aurora nodes the pool doesn't pick the new nodes up in a timely fashion as idle connections don't get regularly reestablished in order to query the Aurora endpoint to find the additional nodes.

[aonamrata]

Any updates on this for mysql2? https://github.com/mysqljs/mysql/pull/2218 -- this is old and not being updated/rebased for review. https://github.com/mysqljs/mysql/pull/2507/files -- can this work?

[david-szabo97]

Idle connections can be released by setting idleTimeout to your desired timeout value. It defaults to 60 seconds. See https://github.com/sidorares/node-mysql2/pull/1791

Although it would be great to have the option to set a maximum lifetime for the connections. Along with a minimum-maximum pool configuration.