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

[Ken Menzel]

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
>>
> 
>