(I'm not ignoring you, it's just a timezone thing.) wait_timeout is a mysql setting, it tells mysql to timeout and close an idle connection after x seconds.
there is definitely something wrong if you get too many connections at 100 to 400 max_connections, that is extremely high - a reasonably busy and persistent connection to an app should use no more than 30-50 - that's heaps. try restarting your mysql service when the counter hits 100% and see how long it takes for connections to fill up, and if that measures accordingly with hits on pages you're right, the mysql_close doesn't (or shouldn't) do anything... Other things to try, a) upgrade to 4.014 (bugger me if that does anything), b) set mysql query_cache_size so that common queries are cached (you can allocate 8-12Mb to the cache for starters). I doubt these will fix your problem, as you've pointed out, the asp bits are already working. The php bits look good, but you might want to remove the error suppression and see if it's complaining about anything. "Gary Broughton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi again > > The reason I put the max_connections so high is that at 100 and 400 it > reached the limits and then crashed the site (too many connections) - so > that's simply to try and prevent that. > > I'm not sure what 'wait_timeout' you mean? Is this a mysql or php ini > setting? > > I killed a handful of tasks via MySQLCC, but didn't see any immediate drop > to be honest. It still stays at 100% most of the time, dropping to a > realistic changeable level every 10-20 seconds or so for around the same. > > There's over 100Mb free physical memory at the moment after a few hours of > uptime (out of a total of 512Mb, which I hope to double anyway when the boss > gets me some more!). > > I do only connect once to the database, via an include file at the top of > the page - I did have a close include at the bottom, but I believe this > would be ignored with a persistent connection anyway? > $boardlink = @mysql_pconnect("xxx", "xxx", "xxx") or die("Could not connect > " . mysql_error()); > $boarddb = @mysql_select_db("xxx",$boardlink) or die("Could not select > messageboard database - " . mysql_error()); > $newslink = @mssql_pconnect("xxx","xxx","xxx") or die("Could not connect - > "); > $newsdb = @mssql_select_db("xxx",$newslink) or die("Could not select > article database "); > > Again, many thanks for your time. > Regards > Gary > > "Sek-Mun Wong" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > 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
