Hi Sek-Mun (?)
My max_connections is set to 750 as it's for an online forum with an
average of 200/300 (I think). My my.ini is very simple and looks as
follows:
[mysqld]
basedir=C:/mysql
datadir=C:/mysql/data
max_connections=750
max_connect_errors=10000
Whereas in ASP I always issued an "oRsRecordset.Close" command, I have
replaced these with "mysql_free_result($resMessage2)", such as follows:
$sql = "SELECT username FROM users
WHERE user_id = '" . $_SESSION["UserID"] . "'";
$resUsers = mysql_query($sql) or die("Query failed [unable to locate
user details]" . mysql_error());
if (mysql_num_rows($resUsers) > 0) {
$oRsUsers = mysql_fetch_assoc($resUsers);
$strUserName = trim($oRsUsers["username"]);
} else {
$strUserName = "unknown";
}
mysql_free_result($resUsers);
However, I have noticed that a handful of the mysql processes are
sleeping with quite large millisecond "timeouts"(?)
i.e. | 2727 | mbuser | MESSAGEBOARD:1701 | messageboard | Sleep | 766
|.
The state is showing as NULL on each of them though, but does this
suggest I haven't freed them all up (although I've been through and
checked quite a few times)?
I've set the PHP session garbage collection at 5%. This is completely
alien to me, so I don't know if it should go any higher? All session
data is stored in one directory under c:\php\sessiondata\.
The web log / event viewer isn't showing anything that I believe to be
of significance.
Many many thanks for your advice, it is greatly appreciated.
Regards
Gary
"Sek-Mun Wong" <[EMAIL PROTECTED]> wrote in message
news:<[EMAIL PROTECTED]>...
> a 100% cpu process is pretty rude, I don't think that the network
> bandwidth (even if it is only 10Mpbs) would cause your problem, so I
> doubt that's it.
>
> Ok, check the following:
>
> 1) max connections setting in mysql (might be out of connections)
> 2) check physical memory remaining (rogue process? see (4))
> 3) are you using table locks? (deadlock issues?, if you have to lock,
> try not using pconnect for those transactions)
> 4) do mysql_free_result when you're done with large queries (just in
> case, I think php should do garbage collection properly)
> 5) can you see the number of / what processes/queries are running the
> the mysql daemon?
> 6) have you gone through your web logs to see if there's unusual
> activity?
>
> You say the ASP version behaves, but obviously the code is doing
> something different, funny because I assume that in asp you're using
> odbc to connect to mysql? it *is* weird to get this problem in php and
> not asp.
>
> fwiw we run about a dozen virutal hosts, about 1500 page views per day
> each site, IIS, dual p3-933, 1Gb RAM, php4.3.2, mysql 4.0.12 and the
> server hardly touches 10% at any time. It's all persistent
> connections, and the mysql daemon is on the same box. Our sql is
> intense, but streamlined.
>
> We also "pool" (it's not true persitent pooling, but per page
> invocation) connections in our framework so a pconnect is done once
> per framework call (ie, per page). But we religiously free_result all
> the time when we're done... haven't tried leaving this out so I can't
> tell if that's a real factor.
>
> That's probably the key, it's hard to tell without seeing how you run
> your sql or how the pages are designed.
>
>
> "Gary Broughton" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> > Hi guys
> >
> > Many thanks for your replies. I did originally use the CGI version,
> > but earlier today built a second machine, and clean installed IIS5
> > and PHP 4.3.2 onto it, using the ISAPI module - and now connect to
> > the remote MySQL database.
> >
> > Unfortunately, I'm getting pretty much the same 100% usage from the
> > mysqld-nt on the database server (although it occasionally drops for
> > a few seconds every minute or two). I've also tried mysqld-max-nt
> > and mysqld-opt, but there is no difference.
> >
> > I use the 'mysql_pconnect()' option on my php pages, and indeed the
> > 'mysql.allow.persistent' is set to 'on'. I just don't get it,
> > especially as the ASP version runs efficiently! Confused and
> > frustrated!
> >
> > It's probably going to simply need another parameter or two setting,
> > but I'll be damned if I can find the answer!
> >
> > Any more suggestions would be more than welcome.
> >
> >
> > "Sek-Mun Wong" <[EMAIL PROTECTED]> wrote in message
> > news:<[EMAIL PROTECTED]>...
> > > are you running php4isapi.dll (ISAPI) or php.exe (CGI) version?
> > >
> > > I know people recommend CGI, but post 4.2.3, the ISAPI version is
> > > very
> >
> > > stable and you'd be nuts to run a busy site in CGI mode.
> > >
> > > If you're running CGI, here is an explanation:
> > > The most expensive operation is the *connection*, why mysql gets
> > > flogged is because every time you close down a php script, it dies
> > > and
> >
> > > the connection dies, so a new one needs to be re-established. It
> > > can take only 10-20 concurrent connections to kill the server.
> > >
> > > To "fix" this (it's more an infrastructure issue, really):
> > >
> > > 1) run ISAPI version of php
> > > 2) under [MySql] in php.ini, make sure
> > >
> > > mysql.allow_persistent = On
> > >
> > > You'll be amazed at the difference.
> > >
> > >
> > > "Gary Broughton" <[EMAIL PROTECTED]> wrote in message
> > > news:[EMAIL PROTECTED]
> > > > I wonder if anyone could offer any advice. We have a series of
> > > > message boards using a MySQL database running under ASP on
> > > > Windows 2000 IIS 5
> > > fine.
> > > > Today I put the a PHP version live after it had been tested over
> > > > the
> > > weekend
> > > > with about 20 users. As soon as I enabled PHP on the live
> > > > website
> > > (separate
> > > > to the test PHP already running), the CPU usage for MySQL-nt
> > > > went up
> >
> > > > to
> > > 100%
> > > > constantly, making the site run at a crawl. After uninstalling
> > > > PHP and reverting to the ASP version for the live site, it all
> > > > ran smoothly again. I have the Application set to Low (IIS
> > > > Process) on both sites, the cgi.force_redirect is 0 as required,
> > > > and the machine
> >
> > > > is a dual 1.8G
> > > Pentium
> > > > with 512MB of memory (I know this could do with doubling, but
> > > > why is
> >
> > > > it
> > > okay
> > > > running ASP code?). I've scoured the net looking for any
> > > > suggestions, but without coming across anything concrete. Has
> > > > anyone any ideas I could try out at all? Many thanks Gary
> > > > Broughton
> > > >
> > > >
> > >
> >
>
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
