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

[Rene Fournier]

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
>