I've read up on this 'wait_timeout' and 'interactive_timeout' and brought it right down to 300 seconds. While I now have less big sleep timeouts (may be coincidence) there are still a handful that currently up to around 700, and increasing! Is there any way possible of establishing what these users have done on the webpage (even a URL would be handy, as I could then at least see what course of action they selected)? Somehow I guess it isn't possible?!
Following this, the mysqld process still regularly hogged the CPU, and was up at 100% most of the time. I then set the following in the ini file set-variable=key_buffer=512K set-variable=max_allowed_packet=1M set-variable=table_cache=256 set-variable=sort_buffer=100k set-variable=read_buffer_size=100k set-variable=record_buffer=1M set-variable=myisam_sort_buffer_size=64M set-variable=thread_cache=8 ... which was suggested in the documentation for lots of connections, but again no noticeable difference. I stopped and restarted the service a few times, and the CPU usage always went straight back up to 100% by the time say a dozen users were connecting again. I am now going to try the query_cache_size setting you mentioned, and will see if that helps the situation. I have installed MySQL 4014 on the machine which is currently running the website, and intend to copy the database from the other machine over to it once this problem goes away :-) I'm with you in that I don't think it's going to have much bearing on things though. With many thanks again for your time and help Gary "Sek-Mun Wong" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > (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
