Use lazy connect and let server disconnect on inactivity

[zuiderkwast]

Currently, when you start an application using MySQL/OTP+Poolboy, you immediately see a number of connections to the DB server. Most of them are idle.

This is an idea of reducing the number of connections to the server to what is actually needed. With MySQL/OTP 1.6.0, no error is logged when the server disconnects. This, with the new lazy connect strategy, allows us to have a more server-friendly approach:

• Change poolboy strategy to LIFO (which is the default for poolboy, so we just remove the {strategy, fifo} option)
• Turn off keep-alive
• Set connect strategy to lazy

The scenario would look like this:

1. Poolboy starts starts a pool of workers. The workers don't connect to the DB yet.
2. When a worker is used and a query is executed, the worker connects to the DB.
3. On load spikes, more workers are used, thus more workers connect to the DB.
4. When the load goes down, the last connected nodes will be disconnected because of inactivity. (The timeout can be set on the server side.) This causes poolboy to restart the worker, but it will be started in disconnected (lazy connect) state.

I suggest we make these options the default in this project.

[zuiderkwast]

@getong, @juhlig, other users, what's your opinion on this?

[juhlig]

Sounds good to me :) Setting the connect strategy to lazy allows workers to (re-) start more quickly and reliably, and works around poolboy's annoying deficiency to crash an entire pool if one worker fails to (re-) start, too.

One ugliness that remains is that part of it (the idle timeout) depends on the server, ie there is no way for the client to decide that it wants to be extra nice and close the connection when it has not been used for a while (maybe even to just close and revert to the not-connected state of lazy mode).

[zuiderkwast]

and works around poolboy's annoying deficiency to crash an entire pool if one worker fails to (re-) start, too

Yes, I didn't even think about this. It's an extra benefit.

maybe even to just close and revert to the not-connected state of lazy mode

Hm... Then we lose the connection state, but as a non-default option with an explicit warning, perhaps yes. Anyway, with just the server disconnecting after 8 hours idle, the application will come to some balance of how many connections it needs within 24 hours.

[juhlig]

and works around poolboy's annoying deficiency to crash an entire pool if one worker fails to (re-) start, too

Yes, I didn't even think about this. It's an extra benefit.

Oh, plus it has the benefit that workers will always start quickly. With poolboy, worker starts happen synchronously, blocking all other pool operations, so quick worker starts are good :)

maybe even to just close and revert to the not-connected state of lazy mode

Hm... Then we lose the connection state, but as a non-default option with an explicit warning, perhaps yes.

Just a thought, I think not really worth it.

Anyway, with just the server disconnecting after 8 hours idle, the application will come to some balance of how many connections it needs within 24 hours.

I guess so :) In any case, I think that this would be a good improvement, so I'm in favor of it :)