Re: Re: re : PHP and persistent connections - Mailing list pgsql-novice

Note: CC'd to Hackers, as this has wandered into deeper feature issues.

Tom Lane wrote:
> GH <grasshacker@over-yonder.net> writes:
> > Do the "persistent-connected" Postgres backends ever timeout or die?
> No.  A backend will sit patiently for the client to send it another
> query or close the connection.

This does have an unfortunate denial-of-service implication, where
an attack can effectively suck up all available backends, and there's
no throttle, no timeout, no way of automatically dropping these....

However, the more likely possibility is similar to the problem that
we see in PHP's persistant connections.... a normally benign connection
is inactive, and yet it isn't dropped. If you have two of these created
every day, and you only have 16 backends, after 8 days you have a lockout.

On a busy web site or another busy application, you can, of course,
exhaust 64 backends in a matter of minutes.

> > Is it possible to set something like a timeout for persistent connctions?
> > (Er, would that be something that someone would want
> >       to do? A Bad Thing?)
> This has been suggested before, but I don't think any of the core
> developers consider it a good idea.  Having the backend arbitrarily
> disconnect on an active client would be a Bad Thing for sure.

Right.... but I don't think anybody has suggested disconnecting an *active*
client, just inactive ones.

> Hence,
> any workable timeout would have to be quite large (order of an
> hour, maybe? not milliseconds anyway).

The mySQL disconnect starts at around 24 hours. It prevents a slow
accumulation of unused backends, but does nothing for a rapid
accumulation. It can be cranked down to a few minutes AFAIK.

> And that means that it's not
> an effective solution for the problem.  Under load, a webserver that
> wastes backend connections will run out of available backends long
> before a safe timeout would start to clean up after it.

Depends on how it's set up... you see, this isn't uncharted territory,
other web/db solutions have already fought with this issue. Much
like the number of backends set up for pgsql must be static, a timeout
may wind up being the same way. The critical thing to realize is
that you are timing out _inactive_ connections, not connections
in general. So provided that a connection provided information
about when it was last used, or usage set a counter somewhere, it
could easily be checked.

> To my mind, a client app that wants to use persistent connections
> has got to implement some form of connection pooling, so that it
> recycles idle connections back to a "pool" for allocation to task
> threads that want to make a new query.  And the threads have to release
> connections back to the pool as soon as they're done with a transaction.
> Actively releasing an idle connection is essential, rather than
> depending on a timeout.
>
> I haven't studied PHP at all, but from this conversation I gather that
> it's only halfway there...

Well...... This is exactly how apache and PHP serve pages. The
problem is that apache children aren't threads, they are separate copies
of the application itself. So a single apache thread will re-use the
same connection, over and over again, and give that conection over to
other connections on that apache thread.. so in your above model, it's
not really one client application in the first place.

It's a dynamic number of client applications, between one and hundreds
or so.

So to turn the feature request the other way 'round:
"I have all sorts of client apps, connecting in different ways, to
my server. Some of the clients are leaving their connections open,
but unused. How can I prevent running out of backends, and boot
the inactive users off?"

-Ronabop

--
Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
which is currently in MacOS land.  Your bopping may vary.