don't think it would be a problem with mysql, (even if 750 connections is waaaay too much!)
what's your wait_timeout set to (try something lower)? and how much physical memory remains? The fact that the processes are left running is not a good sign, try killing some of these and see if the cpu comes down. The last resort is to use only 1 connect per script execution to minimise rogue processes. Let me know how you go, I'd be interested in how to solve this issue "Gary Broughton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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
