Feature Request: Ability to configure free resource selection algorithm

[ryanquinn3]

Background
I've spent the last couple of days debugging a production issue where we were seeing our application leaking idle connections to mysql. Our application uses both typeorm and knex to fetch data from mysql but we discovered that the leaks were only coming from knex. From there I spent a bunch of time reading over tarn and mysql source code to try to understand what was going on. As it turns out the native mysql driver does not clean up the underlying socket when connection.end() is invoked (which knex does when tarn successfully destroys the resource). This gave me an answer to why I was seeing a lot of more open tcp connections that I would expect in my application container when running netstat | grep mysql. However, this didn't explain why typeorm did not suffer from this leakage problem. typeorm relies on the native mysql library connection pool for its pooling and that library treats it's free resource array as a queue while tarn treats it as a stack.

Request
The choice of data structure has pretty significant impact on how the pool performs relative to its underlying resources. A queue is likely going to result in significantly fewer create & destroy calls as it will more fairly load balance across all the free resources but will lead to the pool being pegged at the max setting. The stack will lead to fewer resources being used much more frequently and optimize for the fewest created resources needed. In my case, I would prefer to use the min/max settings to control the number of connections to mysql and optimize for needing to connect less often as a) the driver doesn't clean up well and b) connecting to the db puts additional non-zero load on it.

Would you consider allowing the Pool class to accept a configuration option that allows a user to adjust the internal selection algorithm? For example:

const pool = new Pool ({
  min: 0,
  max: 10,
  freeResourceSelectionAlgorithm: 'lifo', // 'lifo' | 'fifo' 
  ...
})

I've made a stackblitz to help mysql visualize what is going on it the pool. It may help you as well. In our particular case, every destroyed resource caused a leaked socket to mysql.

If this approach is agreeable, I would happily put up a PR! Thanks!

[laurisvan]

Could this resource selection algorithm help in my use case - reuse connections (=sessions) that have specific variables set?

I would prefer an algorithm that could iterate the whole set of pools and return there the first one that satisfies conditions. In my particular case I could store some state (e.g. who is the 'user' in this session/connection), so that I would not need to execute SET "userId" = ... when initiating the connection, saving an extra round-trip. I am not 100% sure on if I could save and transfer the context information between knex and tarn yet, though.

More context: knex/knex#4703 (comment)