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
--
Paul DuBois, [EMAIL PROTECTED]
---------------------------------------------------------------------
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
