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: [EMAIL PROTECTED]; mysql@lists.mysql.com
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> 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@lists.mysql.com/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 <[EMAIL PROTECTED]
> >>>>> > 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: [EMAIL PROTECTED]
> >>>>> > To: mysql@lists.mysql.com
> >>>>> > 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/[EMAIL PROTECTED]
> >>>>> >
> >>>>>
> >>>>> 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/[EMAIL PROTECTED]
> >>>
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
_________________________________________________________________
See how Windows Mobile brings your life together—at home, work, or on the go.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/
