From: Martin Gainty Date: September 24 2008 1:59pm Subject: RE: Ancient, unsolved high-CPU problem -- vmstat, top and ps List-Archive: http://lists.mysql.com/mysql/214641 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_6b06fe9d-43b5-41eb-9dcf-5b91694a5eb9_" --_6b06fe9d-43b5-41eb-9dcf-5b91694a5eb9_ Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hello Rene is hostname a FQDN or IP? Martin=20 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > CC: junk@stripped=3B mysql@stripped > From: m5@stripped > To: kenm@stripped > Subject: Re: Ancient=2C unsolved high-CPU problem -- vmstat=2C top and ps > Date: Wed=2C 24 Sep 2008 14:21:44 +0200 >=20 > Thanks. I've read those links=2C and they sound like my problem. >=20 > On each connection=2C MySQL calls gethostbyname() to resolve the =20 > hostname in the connection string into 127.0.0.1 -- e.g.=2C =20 > mysql_connect("localhost"=2C "user"=2C "password") -> 127.0.0.1. Because= =20 > FreeBSD 4.0's (and Mac OS X's) DNS lookups aren't thread-safe=2C bad =20 > things can happen while MySQL waits on gethostbyname(). At least=2C =20 > that's where the CPU is spending much of its time. >=20 > Now=2C it sounds like using using 127.0.0.1 in place of localhost in the = =20 > connection string is not enough=2C since MySQL will still call =20 > gethostbyaddr() as a reverse-lookup. (Right?) So this is why=2C as you = =20 > say=2C it's necessary to add "skip-name-resolve" to my.cnf. (Right?) =20 > It's also then necessary to make the Grant tables not depend on =20 > hostnames (localhost)=2C but specify 127.0.0.1. >=20 > But here's the strange thing: On a test machine=2C I've added "skip-name-= =20 > resolve" to my.cnf. But I can still use a hostname in the connection =20 > string=2C and it works. >=20 >=20 > On 23-Sep-08=2C at 5:44 PM=2C Ken Menzel wrote: >=20 > > Hi Rene=2C > > This smells like an old freebsd issue with a non thread safe get-=20 > > host-by-name issue and possibly other thread issues. Since Mac OS/X/=20 > > Darwin is a freebsd 4 branch it is a good bet they are the same. Is =20 > > it possible for you to try adding "skip-name-resolve" to my.cnf. =20 > > Alternatively you could compile with -D SKIP_DNS_CHECK. Please read =20 > > about these options before trying them to understand any implication =20 > > it my have on your GRANTs if you grant to a domain or server. > > > > Here are some links to more information=2C > > http://jeremy.zawodny.com/blog/archives/000203.html > > http://bugs.mysql.com/bug.php?id=3D414 > > http://www.mail-archive.com/mysql@stripped/msg87497.html > > > > Hope this helps=2C > > Ken > > > > Rene Fournier wrote: > >> In case a bit more data might help=2C here's what the server looks =20 > >> like right now=2C 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=2C 3 running=2C 112 sleeping... 504 =20 > >> threads 08:12:30 > >> Load Avg: 2.43=2C 2.44=2C 2.30 CPU usage: 45.3% user=2C 48.2% sy= s=2C =20 > >> 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/=20 > >> MACH CSW > >> 25943 mysqld 92.6% 57:11:01 6473 0/0 154/154 =20 > >> 1121358/340 3231 > >> 20067 php 9.1% 6:53:45 1764 0/238 14/7 =20 > >> 6128/14 584 > >> 25957 Terminal 7.0% 12:20:23 150 0/0 1013/814 =20 > >> 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 =20 > >> 3431:26.73 > >> On 23-Sep-08=2C at 3:47 PM=2C Doug Bridgens wrote: > >>> it's all a bit too general=2C we could be asking continual questions = =20 > >>> until someone asks the right one. > >>> > >>> However=2C I would put some debugging into the 30% scripts to check = =20 > >>> they complete before the next one starts=2C as if one script takes =20 > >>> slightly longer (especially if the queries are the same) to =20 > >>> complete then the rest build up quickly. Something else could be =20 > >>> locking the table that your cron queries are trying to access=2C =20 > >>> causing the stacking that never recovers. > >>> > >>> Once the problem occurs I'd be using 'show processlist' in mysql=2C = =20 > >>> and vmstat and ps to check the system resources. Is it =20 > >>> definitely mysql=2C or php/apache=2C a slow disk=2C etc.. > >>> > >>> In terms of your stats below=2C I have (on a fairly average spec =20 > >>> server) 500 queries per second and 2000 open tables. So=2C unless = =20 > >>> it's a PC or very badly tuned=2C it should be fine. > >>> > >>> cheers=2C > >>> Doug > >>> > >>> > >>> On 23 Sep 2008=2C at 14:16=2C Rene Fournier wrote: > >>> > >>>> 10% of queries are web-based (Apache/PHP). > >>>> 30% of queries are from command-line PHP scripts that get =20 > >>>> executed (average 1/second -- they end with mysql_close() btw). > >>>> 60% of queries are from command-line PHP scripts that run =20 > >>>> continuously (in a loop=2C with sleep())=2C acting on incoming socke= t =20 > >>>> data. > >>>> > >>>> ...Rene > >>>> > >>>> On 23-Sep-08=2C at 2:20 PM=2C Jeffrey Santos wrote: > >>>> > >>>>> Rene=2C > >>>>> > >>>>> How are you querying the database during normal use? What kind =20 > >>>>> of applications are you using? > >>>>> > >>>>> ~Jeffrey Santos > >>>>> > >>>>> On Mon=2C Sep 22=2C 2008 at 2:46 PM=2C Rene Fournier >>>>> > wrote: > >>>>> Uptime: 1054977 Threads: 10 Questions: 15576766 Slow queries: =20 > >>>>> 229 Opens: 489 Flush tables: 1 Open tables: 483 Queries per =20 > >>>>> second avg: 14.765 > >>>>> > >>>>> ---- > >>>>> > >>>>> I know what the slow queries are--some that take 20-30 seconds =20 > >>>>> to compute=2C and they are normal. The number of open tables seems = =20 > >>>>> high=2C no? The database that gets 95% of the load has ~35 tables = =20 > >>>>> in total. > >>>>> > >>>>> As for cron jobs=2C I have a number of command-line PHP scripts =20 > >>>>> that perform regular queries. They've been running for about 10 =20 > >>>>> days now. The current high CPU state started a couple days ago. > >>>>> > >>>>> > >>>>> > >>>>> > >>>>> On 22-Sep-08=2C at 8:30 PM=2C 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 =20 > >>>>> official business of Sender. This transmission is of a =20 > >>>>> confidential nature and Sender does not endorse distribution to =20 > >>>>> any party other than intended recipient. Sender does not =20 > >>>>> necessarily endorse content contained within this transmission. > >>>>> > >>>>> > >>>>> > From: m5@stripped > >>>>> > To: mysql@stripped > >>>>> > Subject: Ancient=2C unsolved high-CPU problem > >>>>> > Date: Mon=2C 22 Sep 2008 19:41:25 +0200 > >>>>> > >>>>> > > >>>>> > For the longest time=2C I've had a strange problem with MySQL. > >>>>> > Basically=2C after a certain amount of time--sometimes a few days= =2C > >>>>> > sometimes a couple weeks--its CPU usage will go from a steady =20 > >>>>> 20-30% > >>>>> > to 80-90%. Actual load and number of queries is the same=2C =20 > >>>>> nothing else > >>>>> > changes. > >>>>> > > >>>>> > If I shutdown MySQL and restart it (not the server)=2C CPU% goes = =20 > >>>>> back to > >>>>> > normal. What could this be? > >>>>> > > >>>>> > (Xserve G5 2GHz=2C 8GB=2C 3x250GB RAID5=2C Mac OS X 10.4.11=2C My= SQL =20 > >>>>> 5.0.51a) > >>>>> > > >>>>> > ...Rene > >>>>> > > >>>>> > -- > >>>>> > MySQL General Mailing List > >>>>> > For list archives: http://lists.mysql.com/mysql > >>>>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@hotm= ail.com > >>>>> > > >>>>> > >>>>> Get more out of the Web. Learn 10 hidden secrets of Windows =20 > >>>>> Live. Learn Now > >>>>> > >>>>> > >>>> > >>> > >>> > >>> --=20 > >>> MySQL General Mailing List > >>> For list archives: http://lists.mysql.com/mysql > >>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dm5@renefourni= er.com > >>> > > > > > > --=20 > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dm5@renefournier= .com > > >=20 >=20 > --=20 > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmgainty@stripped= om >=20 _________________________________________________________________ See how Windows Mobile brings your life together=97at home=2C work=2C or on= the go. http://clk.atdmt.com/MRT/go/msnnkwxp1020093182mrt/direct/01/= --_6b06fe9d-43b5-41eb-9dcf-5b91694a5eb9_--