Re: [phplib] HELP: phplib leaving processes in MySQL From: Bob Bowker (bowker <email protected>)
Date: 04/07/01

Good Morning --

I am certainly not knowledgeable enough to really explain this, but here's
my understanding of the issues.

When Apache receives an HTTP request from a browser, that request is
assigned to one of its child processes. If the request involves a MySQL
query, a connection is made between the child process and MySQL ... if it's
a "pconnect", the connection is kept open after the query is finished and
the work on the HTTP request is completed, the point at which the
connection would have been killed if it had been a straight
"connect". This child process will stay open as long as there's work being
done ... when Apache gets to its max_connections, the "oldest" child
connection gets killed (along with its persistent MySQL connection, if it
has one) and a new child is spawned - which will open its own MySQL
connection if necessary ...

So on a busy server, with requests coming in great numbers, the child
processes stay pretty busy, and the advantages of a pconnect are obvious:
the child doesn't have the connection overhead with MySQL for every HTTP
request assigned to it. The problem I faced was on a busy server hosting
virtual sites at Alabanza ... our client's site was a dynamic e-commerce
site -- only a couple of thousand products, but it was MySQL
intensive. Apache had spawned a lot of child processes to handle the 100 -
125 sites on that machine (max_connections was set to 200, if I remember),
but which of them got "assigned" to handle requests directed to my code was
pretty much at random.

When I learned about pconnect, I immediately changed all my code ... and
immediately brought the server to its knees.

My best guess as to what happened is this: an HTTP request comes in for my
code and is assigned to a "random" child process, which opens its
connection to MySQL and keeps it open ... the next several hundred requests
are for other sites on that server which (given that it's a virtual hosting
machine) probably don't open pconnects with MySQL ... the next request for
my code goes to a different child process than the first one, which opens
its connection to MySQL and keeps it open ... well, you can see the
snowball growing. In my case, within about 5 minutes, we busted the
mysql.max_persistent setting, and everybody - every virtual site on that
machine using MySQL to any degree whatsoever - got the "too many
connections" error. MySQL basically threw its hands in the air and took a
"timeout".

I changed all my pconnects back to connect, Apache was happy, MySQL was
singing again, and Alabanza Tech Support (sic) stopped yelling at me ...

A few weeks after this happened, I attended an O'Reilly conference in
Monterrey, CA where both Rasmus and Monty were conducting sessions. During
a break in the PHP gathering, I introduced myself to Rasmus, and while we
were chatting, Monty walked up. Not being the shy type, I immediately gave
the two of them the short version of the problem I had run into, and asked
for some guidance. Neither had even heard of this issue ... after much
head-scratching, their recommendation was to "balance Apache's
max_connections and MySQL's max_persistent settings" ... well, on a virtual
host server, that just ain't in the cards, and I was still in the dark ...

For the majority of our clients, pconnect just isn't in our lexicon ... our
extended phpLib DB_SQL class for virtual clients uses plain ol' connects,
and the various Apaches and MySQLs are quite happy.

We have a second phpLib DB_SQL class for high-bandwidth clients who are on
dedicated, co-located or in-house machines, where we are fairly confident
that every child process handling HTTP requests is doing so for our scripts
... in these cases, pconnects indeed make sense, and we use them.

I truly hope this doesn't start a whole thread, although I would be very
interested in all better explanations ... I'm afraid that I may indeed have
overstepped the boundaries of my knowledge and in the process "invented"
Apache, PHP and MySQL functionality! But as I said, this is all based on
experience and not necessarily a deep understanding of "how she works" ...

Best --

Bob.

At 09:06 PM 4/6/01 -0700, Philip Strnad wrote:
>Bob,
>
>Could you go into further detail about why you think connect is better
>than pconnect? I know you said you didn't have concrete evidence but
>I'm interested anyway. Thanks
>
>Philip
>
>
>Bob Bowker wrote:
> >
> > Steve --
> >
> > Are you using "mysql_pconnect" ...? If so, change it to "mysql_connect"
> > and the problem should go away. Unless you're running 100,000 people a
> > minute through your web site, the downside to pconnects far outweighs the
> > upside, no matter what database you're using.
> >
> > Not based on anything concrete, just the solution that worked for me -
> > immediately and completely. At a California conference 18 months ago,
> > Monty and Rasmus scratched their heads over this, and ultimately blamed
> > Apache (!).
> >
> > Good luck!
> >
> > Bob.
> >
> > At 05:29 PM 4/6/01 -0400, Stephen Woodbridge wrote:
> > >Hi all,
> > >
> > >I just noticed that phplib seems to be leaving sleeping processes in
> > >MySQL. What am I doing wrong or is this a know bug? What is the
> > >fix/work-a-round?
> > >
> > >Any help would be appreaciated.
> > > -Steve
> > >
> > >Here are the details:
> > >
> > >Here is a very basic test secript that reproduces the problem:
> > >
> > ><?php
> > > php_track_vars;
> > > page_open(array("sess" => "Example_Session",
> > > "auth" => "Example_Default_Auth",
> > > "perm" => "Example_Perm"));
> > >
> > > print "<html>\nHello\n</html>\n";
> > >
> > > page_close();
> > >
> > >?>
> > >
> > >I am using the following:
> > >
> > >phplib RELEASE 7.2b
> > >PHP Version 3.0.15
> > >System: Linux porky.devel.redhat.com 2.2.5-22smp #1 SMP Wed Jun 2
> > >09:11:51 EDT 1999 i686
> > >Allow persistent links: Yes
> > >Persistent links: 1/Unlimited
> > >Total links: 2/Unlimited
> > >Client API version: 3.22.32
> > >pache Version: Apache/1.3.12
> > >Apache Release: 10312100
> > >Apache API Version: 19990320
> > >Loaded modules: mod_php3, mod_perl, mod_setenvif, mod_so,
> > > mod_usertrack, mod_headers, mod_expires,
> > >mod_proxy,
> > > mod_digest, mod_auth_db, mod_auth_anon,
> > >mod_auth, mod_access,
> > > mod_rewrite, mod_alias, mod_userdir,
> > >mod_actions, mod_imap,
> > > mod_asis, mod_cgi, mod_dir, mod_autoindex,
> > >mod_include,
> > > mod_info, mod_status, mod_negotiation,
> > >mod_mime,
> > > mod_log_referer, mod_log_agent, mod_log_config,
> > >mod_env,
> > > mod_vhost_alias, http_core
> > >Apache/1.3.12 (Unix) (Red Hat/Linux) PHP/3.0.15 mod_perl/1.21
> > >
> > >---------------------------------------------------------------------
> > >To unsubscribe, e-mail: phplib-unsubscribe <email protected>
> > >For additional commands, e-mail: phplib-help <email protected>
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: phplib-unsubscribe <email protected>
> > For additional commands, e-mail: phplib-help <email protected>

---------------------------------------------------------------------
To unsubscribe, e-mail: phplib-unsubscribe <email protected>
For additional commands, e-mail: phplib-help <email protected>