Re: Ancient, unsolved high-CPU problem -- vmstat, top and ps

[Ken Menzel]

Hi Rene,
   (Note I have updated my reply address to my current company name).   
Yes, you can still use a hostname in the connection string, that's not 
what mysql uses it for, that hostname gets you from the client to the 
server.  If you use GRANT to permit access from certain hosts IE 'grant 
all on mydb.* to 'mydbuuser'@'%.mydomain.com'.  Then the server will not 
be able to resolve those named permissions from the connecting  IP back 
to a domain name matched to a grant to allow login. Also I don't think 
you can use subnets in a grant, you can use a single IP. However  
'myuser'@'%' should continue to work just fine as should localhost using 
the file socket.  Everything else should behave normally.  Localhost 
connections are usually through the the mysql socket file 
(/tmp/mysql.sock) not 127.0.0.1 and is mapped to localhost.

Don't forget to restart the server after the change in the my.cnf file.

Hopefully Apple will rebranch Darwin from a more current freebsd, there 
have been many improvements in threading, SMP scheduling and 
performance, and I highly recommend version 7 and 8 looks like it will 
be even better.

Ken

Rene Fournier wrote:
> 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
>>
>