List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:September 24 2008 1:59pm
Subject:RE: Ancient, unsolved high-CPU problem -- vmstat, top and ps
View as plain text  
Hello Rene

is hostname a FQDN or IP?

Martin 
______________________________________________ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender.
This transmission is of a confidential nature and Sender does not endorse distribution to
any party other than intended recipient. Sender does not necessarily endorse content
contained within this transmission. 


> CC: junk@stripped; mysql@stripped
> From: m5@stripped
> To: kenm@stripped
> Subject: Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps
> Date: Wed, 24 Sep 2008 14:21:44 +0200
> 
> Thanks. I've read those links, and they sound like my problem.
> 
> On each connection, MySQL calls gethostbyname() to resolve the  
> hostname in the connection string into 127.0.0.1 -- e.g.,   
> mysql_connect("localhost", "user", "password") -> 127.0.0.1.  Because  
> FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe, bad  
> things can happen while MySQL waits on gethostbyname().  At least,  
> that's where the CPU is spending much of its time.
> 
> Now, it sounds like using using 127.0.0.1 in place of localhost in the  
> connection string is not enough, since MySQL will still call  
> gethostbyaddr() as a reverse-lookup.  (Right?)  So this is why, as you  
> say, it's necessary to add "skip-name-resolve" to my.cnf.  (Right?)   
> It's also then necessary to make the Grant tables not depend on  
> hostnames (localhost), but specify 127.0.0.1.
> 
> But here's the strange thing: On a test machine, I've added "skip-name- 
> resolve" to my.cnf. But I can still use a hostname in the connection  
> string, and it works.
> 
> 
> On 23-Sep-08, at 5:44 PM, Ken Menzel wrote:
> 
> > Hi Rene,
> >  This smells like an old freebsd issue with a non thread safe get- 
> > host-by-name issue and possibly other thread issues. Since Mac OS/X/ 
> > Darwin is a freebsd 4 branch it is a good bet they are the same. Is  
> > it possible for you to try adding "skip-name-resolve" to my.cnf.  
> > Alternatively you could compile with -D SKIP_DNS_CHECK.  Please read  
> > about these options before trying them to understand any implication  
> > it my have on your GRANTs if you grant to a domain or server.
> >
> > Here are some links to more information,
> > http://jeremy.zawodny.com/blog/archives/000203.html
> > http://bugs.mysql.com/bug.php?id=414
> > http://www.mail-archive.com/mysql@ style="color:#666">stripped/msg87497.html
> >
> > Hope this helps,
> > Ken
> >
> > Rene Fournier wrote:
> >> In case a bit more data might help, here's what the server looks  
> >> like right now, while experiencing the strange high-CPU load:
> >> VM_STAT sayeth:
> >> Mach Virtual Memory Statistics: (page size of 4096 bytes)
> >> Pages free:                   534327.
> >> Pages active:                 331233.
> >> Pages inactive:              1094528.
> >> Pages wired down:             137065.
> >> "Translation faults":      957568490.
> >> Pages copy-on-write:       241306984.
> >> Pages zero filled:        1302796176.
> >> Pages reactivated:            790261.
> >> Pageins:                       95668.
> >> Pageouts:                       1212.
> >> Object cache: 217985425 hits of 220226841 lookups (98% hit rate)
> >> Top says:
> >> Processes:  115 total, 3 running, 112 sleeping... 504  
> >> threads          08:12:30
> >> Load Avg:  2.43, 2.44, 2.30     CPU usage:  45.3% user, 48.2% sys,  
> >> 6.5% idle
> >> Networks:       676 ipkts/72K              738 opkts /181K
> >> Disks:           10 reads/52K              594 writes/3049K
> >> VM:               0 pageins                  0 pageouts
> >>  PID COMMAND      %CPU   TIME   FAULTS PGINS/COWS MSENT/MRCVD  BSD/ 
> >> MACH    CSW
> >> 25943 mysqld      92.6% 57:11:01   6473     0/0      154/154     
> >> 1121358/340    3231
> >> 20067 php          9.1%  6:53:45   1764     0/238     14/7       
> >> 6128/14      584
> >> 25957 Terminal     7.0% 12:20:23    150     0/0     1013/814     
> >> 244/2407    648
> >> [...]
> >> And PS:
> >> USER       PID %CPU %MEM      VSZ    RSS  TT  STAT STARTED      TIME
> >> mysql    25943  114.1 -29.2  1239384 613296  ??  R    10Sep08  
> >> 3431:26.73
> >> On 23-Sep-08, at 3:47 PM, Doug Bridgens wrote:
> >>> it's all a bit too general, we could be asking continual questions  
> >>> until someone asks the right one.
> >>>
> >>> However, I would put some debugging into the 30% scripts to check  
> >>> they complete before the next one starts, as if one script takes  
> >>> slightly longer (especially if the queries are the same) to  
> >>> complete then the rest build up quickly.   Something else could be  
> >>> locking the table that your cron queries are trying to access,  
> >>> causing the stacking that never recovers.
> >>>
> >>> Once the problem occurs I'd be using 'show processlist' in mysql,  
> >>> and vmstat and ps to check the system resources.   Is it  
> >>> definitely mysql, or php/apache, a slow disk, etc..
> >>>
> >>> In terms of your stats below, I  have (on a fairly average spec  
> >>> server) 500 queries per second and 2000 open tables.   So, unless  
> >>> it's a PC or very badly tuned, it should be fine.
> >>>
> >>> cheers,
> >>> Doug
> >>>
> >>>
> >>> On 23 Sep 2008, at 14:16, Rene Fournier wrote:
> >>>
> >>>> 10% of queries are web-based (Apache/PHP).
> >>>> 30% of queries are from command-line PHP scripts that get  
> >>>> executed (average 1/second -- they end with mysql_close() btw).
> >>>> 60% of queries are from command-line PHP scripts that run  
> >>>> continuously (in a loop, with sleep()), acting on incoming socket  
> >>>> data.
> >>>>
> >>>> ...Rene
> >>>>
> >>>> On 23-Sep-08, at 2:20 PM, Jeffrey Santos wrote:
> >>>>
> >>>>> Rene,
> >>>>>
> >>>>> How are you querying the database during normal use?  What kind 
> 
> >>>>> of applications are you using?
> >>>>>
> >>>>> ~Jeffrey Santos
> >>>>>
> >>>>> On Mon, Sep 22, 2008 at 2:46 PM, Rene Fournier
> <m5@stripped 
> >>>>> > wrote:
> >>>>> Uptime: 1054977  Threads: 10  Questions: 15576766  Slow queries:
>  
> >>>>> 229  Opens: 489  Flush tables: 1  Open tables: 483  Queries per 
> 
> >>>>> second avg: 14.765
> >>>>>
> >>>>> ----
> >>>>>
> >>>>> I know what the slow queries are--some that take 20-30 seconds 
> 
> >>>>> to compute, and they are normal. The number of open tables seems
>  
> >>>>> high, no? The database that gets 95% of the load has ~35 tables 
> 
> >>>>> in total.
> >>>>>
> >>>>> As for cron jobs, I have a number of command-line PHP scripts  
> >>>>> that perform regular queries. They've been running for about 10 
> 
> >>>>> days now. The current high CPU state started a couple days ago.
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> On 22-Sep-08, at 8:30 PM, Martin Gainty wrote:
> >>>>>
> >>>>> curious if you have any cron jobs starting to execute?
> >>>>>
> >>>>> what does mysqladmin status show ?
> >>>>>
> >>>>> Martin
> >>>>> ______________________________________________
> >>>>> Disclaimer and confidentiality note
> >>>>> Everything in this e-mail and any attachments relates to the  
> >>>>> official business of Sender. This transmission is of a  
> >>>>> confidential nature and Sender does not endorse distribution to 
> 
> >>>>> any party other than intended recipient. Sender does not  
> >>>>> necessarily endorse content contained within this transmission.
> >>>>>
> >>>>>
> >>>>> > From: m5@stripped
> >>>>> > To: mysql@stripped
> >>>>> > Subject: Ancient, unsolved high-CPU problem
> >>>>> > Date: Mon, 22 Sep 2008 19:41:25 +0200
> >>>>>
> >>>>> >
> >>>>> > For the longest time, I've had a strange problem with
> MySQL.
> >>>>> > Basically, after a certain amount of time--sometimes a few
> days,
> >>>>> > sometimes a couple weeks--its CPU usage will go from a
> steady  
> >>>>> 20-30%
> >>>>> > to 80-90%. Actual load and number of queries is the same, 
> 
> >>>>> nothing else
> >>>>> > changes.
> >>>>> >
> >>>>> > If I shutdown MySQL and restart it (not the server), CPU%
> goes  
> >>>>> back to
> >>>>> > normal. What could this be?
> >>>>> >
> >>>>> > (Xserve G5 2GHz, 8GB, 3x250GB RAID5, Mac OS X 10.4.11,
> MySQL  
> >>>>> 5.0.51a)
> >>>>> >
> >>>>> > ...Rene
> >>>>> >
> >>>>> > --
> >>>>> > MySQL General Mailing List
> >>>>> > For list archives: http://lists.mysql.com/mysql
> >>>>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=1
> >>>>> >
> >>>>>
> >>>>> Get more out of the Web. Learn 10 hidden secrets of Windows  
> >>>>> Live. Learn Now
> >>>>>
> >>>>>
> >>>>
> >>>
> >>>
> >>> -- 
> >>> MySQL General Mailing List
> >>> For list archives: http://lists.mysql.com/mysql
> >>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >>>
> >
> >
> > -- 
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
See how Windows Mobile brings your life together—at home, work, or on the go.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
Thread
Ancient, unsolved high-CPU problemRene Fournier22 Sep
Re: Ancient, unsolved high-CPU problemRene Fournier22 Sep
  • Re: Ancient, unsolved high-CPU problemJeffrey Santos23 Sep
    • Re: Ancient, unsolved high-CPU problemRene Fournier23 Sep
      • Re: Ancient, unsolved high-CPU problemDoug Bridgens23 Sep
        • Re: Ancient, unsolved high-CPU problemRene Fournier23 Sep
          • Re: Ancient, unsolved high-CPU problemDoug Bridgens23 Sep
        • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier23 Sep
          • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psKen Menzel23 Sep
            • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier24 Sep
              • RE: Ancient, unsolved high-CPU problem -- vmstat, top and psMartin Gainty24 Sep
                • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier30 Sep
              • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psKen Menzel24 Sep
                • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier30 Sep
                  • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psKen Menzel30 Sep