Pool doesn't hand out any idle connections if any other connections are in queue

[HexyWitch]

If any connection is in the queue (dropping, rollback, new, or disconnecting) the pool will not return any connections when polled, even if there are idle connections available. If any of the connections in the queue take a long time to resolve the pool will stop serving new connections for that entire duration.

My server seems to occasionally get stuck on dropping the results of a connection, leaving a single connection stuck in the dropping queue. I haven't found the cause of that and it might be an issue on my end. But because of this issue the entire server locks up until that one stuck connection gets resolved.

Here's the server not serving any new connections for 5 minutes while 1 connection is stuck in dropping, with plenty of idle connections available:

My suggested change would be to allow Pool::take_conn to return a connection from the idle pool even if there are connections in the queue, but to restrict Pool::poll from creating a new connection if there are connections in the queue.

[HexyWitch]

I'd be happy to submit a pull request for the suggested change if you agree that it makes sense.

[blackbeam]

Hi and thanks for your research.

This was intentional and intention was to make all connections available as soon as possible. Futures in queues should not take much time:

• new queue - future here resolves as soon as handshake with MySql server performed
• disconnecting queue - future here resolves as soon as COM_QUIT is written to the wire
• rollback queue - future here resolves as soon as ROLLBACK command is handled by the server
• dropping queue - future here resolves as soon as unconsumed result is fully iterated

However there was an assumption during implementation, that unconsumed result is odd because the rows, if requested, is actually needed to the application.

Could you please look into your app? Is this actually the case that you requested a lot of data and then it was simply dropped, or there is some other case and i should consider to change the pool behavior?

[HexyWitch]

I'll try to find why dropping the results for this query is taking so long. The app never queries for more than a few dozen rows, so I can't imagine it's simply iterating through a lot of data for several minutes. My app drops connections that exceed a timeout, so taking a long time getting the results is probably what puts it in the dropping queue in the first place.

Thanks for your response! I'll get back to you if I find a legitimate reason that any of those queues could take a long time. Though I would argue that any task that involves sending requests to a mysql server can take an arbitrarily long time. :)

[blackbeam]

Hi.
I've changed the behavior in 25c9219. Could you please test it in your environment?

[HexyWitch]

Thanks, I'll try it out and let you know asap!

I haven't found the cause for the dropping connection taking so long to drop (it only happens in production every few hours or so, I can't reproduce it reliably). It's not showing up in the slow query log at all. But just now I also encountered a rollback operation taking somewhere between 15 and 30 seconds (causing the same issue but more briefly). Those typically wrap up almost immediately, but are there cases where they can take longer?

[HexyWitch]

I've been running it on 338fe66 for ~48h and it has been stable for that time.

[blackbeam]

Thanks! I'll release this asap.

[blackbeam]

Fixed in v0.19.0.