mysql_pconnects and how/why they kill servers stone dead (was Re: [thelist] php ad server?

[Matthew D. Fuller]

On Mon, Jan 21, 2002 at 08:07:27PM +0000 I heard the voice of
John Handelaar, and lo! it spake thus:
>
> Which brings up an interesting question (to me)...
> 
> Why are persistent db connections in PHP often so lethal to apache?  (Or
> in other words, how can I use them and not have mysql get hosed with
> un-reused connections in 'sleep' mode - which I assume is what happened
> to Nicole?

Well, I've witnessed similar things in a box of a friend of mine with
PostgreSQL, but...

I'd say it's because the persistent connection pool isn't a pool at all.
It doesn't pool across the processes, it just opens a connection (possibly
more, but only if you're making more than 1 connection simultaneously
per script, in which case I should probably smack you ;) per httpd worker
process, which then stays open until that process exits.  So even if 10%
of your hits hit the database, sooner or later ALL of your httpd
processes will have a DB connection sitting there.  And being as even a
moderately hit server will probably keep at least 20-30 processes around
on a fairly permanent basis...

It basically means (from my perspective) that, unless a high percentage
(like 66% or better, offhand) of your individual page hits hit the DB,
and you have a high traffic rate, persistent connections are a net loss,
not a net gain.  The upside is that I BELIEVE you can just turn off
persistent connections in the php.ini file, and foo_pconnect() will
become the equivalent of foo_connect(), so you can just switch off the
persistence from a central location.  And really, unless you're getting
over a hit a second that touches the DB, I can't see how connection time
(even with PostgreSQL fork()'ing, to say nothing of MySQL creating a new
thread) can make any rational difference.


-- 
Matthew Fuller     (MF4839)     |    fullermd at over-yonder.net
Unix Systems Administrator      |    fullermd at futuresouth.com
Specializing in FreeBSD         |    http://www.over-yonder.net/

"The only reason I'm burning my candle at both ends, is because I
      haven't figured out how to light the middle yet"