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

    I've tried quite a bit to use persistent connections with PHP (for over
a year) and always the scripts that I try to use them with behave crazy...
The last time I tried there were problems all over the place with PHP,
variables getting overwritten, certain functions just totally breaking
(date() to name one) and so on.. I know I'm not being specific but my point
is that I think there are some other outstanding PHP issues that play into
this problem as the behavior that I've seen isn't directly related to
PostgreSQL but only happens when I use persistent connections.. I've been
trying to corner the problem for quite some time, it's an elusive one for
sure.. I spoke with the PHP developers 9 or so months ago about the problems
and they didn't seem to pay any attention to it, the thread on the mailing
list was short with the bug report collecting dust at the bottom of the
to-do list I'm sure (as that was back before PHP 4 was even released and
obviously the problem remains)..

Just my $0.02 worth.


-Mitch

----- Original Message -----
From: "Ron Chmara" <ron@Opus1.COM>
To: "Tom Lane" <tgl@sss.pgh.pa.us>; "PostgreSQL Hackers List"
<pgsql-hackers@postgresql.org>
Cc: "GH" <grasshacker@over-yonder.net>; <pgsql-novice@postgresql.org>
Sent: Saturday, November 25, 2000 4:26 PM
Subject: [HACKERS] Re: [NOVICE] Re: re : PHP and persistent connections


> 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.
>