In my case they appear to be left open indefinitely, until Apache is
restarted. This is not what's supposed to happen, of course. I
haven't tried tuning low_idle (I think that's what it's called, from
the mysql_connect() doc at php.net). I probably should try that as
well. I'm sure persistent connections make a big difference for an
Oracle-class database, where the startup time for a connection is
large.
On Thursday 03 May 2001 07:14 pm, Paul DuBois opined:
> At 9:35 AM -0700 5/3/01, Scott Baker wrote:
> >That's very odd... Technically pconnect should be a lot faster,
> > not sure exactly why it's not. The other thing you might want to
> > look at is the MySQL idle timeout (or something like that).
> > That's how long your pconnects stay connected if they don't
> > transfer any traffic. I think it defaults to 30 minutes, which
> > is probably ok. Depending on the traffic of your site.
>
> For most databases pconnect() is faster. But it turns out MySQL is
> so fast at making connections that pconnect() doesn't give much
> advantage, if any. So regular connect() can be advantageous in
> terms of not leaving as many connections open.
>
> >At 09:27 AM 5/4/2001 -0700, Gary Bickford wrote:
> >>Thanks for the reply!, Scott! I considered changing the number
> >> of allowed connections but I figured this would just defer the
> >> problem, as the root problem was the increasing number of
> >> connections.
> >>
> >>Last night I went through the entire website looking for code
> >> that used mysql_pconnect() in PHP, which provides 'persistent'
> >> connections. I had thought before that I had caught all of
> >> these but found another 40 or so files that had either been
> >> missed or magically appeared. I converted all those to standard
> >> mysql_connect() calls and ran a stress test all night - using
> >> wget to fetch 1000 pages as fast as possible from a neighboring
> >> machine, over and over.
> >>
> >>On the server I have a loop running like this:
> >>
> >> N=1; while [ $N -ge 1 ]; do ps aux|grep mysql |wc; sleep 5;
> >> done
> >>
> >>which shows the number of mysql processes every five seconds or
> >> so. I had run this test earlier and over two hours' time the
> >> number of connections increased to about 22. After changing all
> >> the mysql_pconnect() calls to mysql_connect(), and leaving the
> >> wget job running all night (the 'at' job I set up to kill it
> >> while I was sleeping failed), the number of connections was
> >> still at 5. This involved fetching almost 400,000 pages.
> >>
> >>So it appears that the increasing number of connections has to do
> >>with some persistent connections never being closed, although PHP
> >>thought they were. Determining if this is a PHP, MySQL, Apache
> >> or Redhat problem is beyond my desired effort and code
> >> experience level. At least all parts are open source!!
> >>
> >>Interestingly, the rate at which wget could fetch pages almost
> >>doubled while the server's CPU load dropped from 86% to 77%,
> >> which bears out the experience of some others on various PHP
> >> lists. Some users get dramatic improvements using
> >> mysql_pconnect(), others don't.
> >>
> >>I will now make another pass at a couple of pages that seemed to
> >> be the focus of the errors that users received, to see if I can
> >> improve the error handling (and maybe mail myself a message!).
> >> For now, I'm calling the bug worked-around.
> >>
> >>GB
> >>
> >>On Wednesday 02 May 2001 04:43 pm, Scott Baker opined:
> >>> You can look into increasing the number of allowed connections
> >>> that MySQL will accept... are you using connect or pconnect?
> >>>
> >>> At 04:23 PM 5/3/2001 -0700, Gary Bickford wrote:
> >>> >Repost - nobody has any ideas? I've run some stress tests,
> >>> > sending several thousand page requests, and the number of
> >>> > mysql processes goes up and down but over time continues to
> >>> > rise. It's up to about 22 now. When it gets to about 30,
> >>> > every request from the web server will be will fail.
> >>> >
> >>> >Could some of these processes be threads that are orphaned
> >>> > for some reason (perhaps Apache continuing to expire its own
> >>> > processes?) and hang around, idle? I speculate that there
> >>> > is some maximum number of processes and finally MySQL can't
> >>> > won't start another thread/process. GB
> >>> >
> >>> >On Tuesday 01 May 2001 05:26 pm, Gary E Bickford opined:
> >>> > > I am running MySQL (3.23.27 at this point) with
> >>> > > Apache1.3.12 and PHP3 on a PC running Redhat 6.0. My
> >>> > > problem has survived upgrades of all components from
> >>> > > earlier versions.
> >>> > >
> >>> > > Every PHP page has at least one query to a MySQL database
> >>> > > for
> >>> > >
> >> > > > session tracking (home-rolled code, not PHPLib or
> >> > > > anything off
> >>> > >
> >>> > > the shelf) There are also on some pages other queries, to
> >>> > > a different database, same server (same mysql). Most of
> >>> > > the latter are Phorum queries.
> >>> > >
> >>> > > Periodically the number of mysql instances that show up in
> >>> > > 'ps auxww'1 gets to about 30 and mysql no longer listens
> >>> > > to PHP/Apache. However I can still use the mysql shell
> >>> > > with no problem, so MySQL is still running but can't hear
> >>> > > anything from PHP.
> >>> > >
> >>> > > The only way to fix it is to run 'apachectl restart' or
> >>> > > equivalent. 'graceful' doesn't do it. I"m not sure if
> >>> > > restarting MySQL instead works. After Apache is
> >>> > > restarted, the mysql processes are down to 3 and
> >>> > > everybody's happy again.
> >>> > >
> >>> > > I have been trying for months to figure out what is
> >>> > > happening and how to fix it. I had heard at one point
> >>> > > that there was a problem with Redhat 5.2 kernel that
> >>> > > caused a similar problem, but I upgraded that but no joy.
> >>> > >
> >>> > > Temporarily I have built a kluge cron job that restarts
> >>> > > apache every so often, which worked as long as the server
> >>> > > was low volume. But now usage is dramatically increasing
> >>> > > due to some new web sites that have been installed and
> >>> > > announced to the entire corporation. So the problem is
> >>> > > triggered before the restart cleans up the mess. I can't
> >>> > > have it restarting every 20 minutes.
> >>> > >
> >>> > > I've also changed every instance of pconnect() to
> >>> > > connect() where I can, but no difference.
> >>> > >
> >>> > > Can anybody tell me anything useful? I'm supposed to come
> >>> > > back with a firm fixit schedule tomorrow morning, so
> >>> > > please email me directly as well as on the list - I'm
> >>> > > afraid I'll miss it in the volume on the list. If I don't
> >>> > > get this fixed very shortly my consulting contract with
> >>> > > this company will no doubt begin to fade out!!
> >>> > >
> >>> > > Thanks in advance,
> >>> > >
> >> > > > Gary Bickford
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
