List:General Discussion« Previous MessageNext Message »
From:Doug Bridgens Date:September 23 2008 2:37pm
Subject:Re: Ancient, unsolved high-CPU problem
View as plain text  
nothing on a server is weird, just not understood yet  :-)

so, 'show processlist' comes up with nothing.   Does 'ps auxw' show  
any php processes still active?   Does 'netstat -atp' show any  
established connections to mysql?

How long does it take to re-create the problem?   You've restarted  
mysql, how long to high cpu again?   Did you start to rebuild indexes  
any time recently?

You seem to have ruled out the web scripts, so can you stop the 30%  
cron job scripts running?

in your other email CPU usage is 45%.   The top figure is the  
percentage of CPU usage that mysql has used, compared with other  
processes.   92% of 45% (I believe).

if you run 'vmstat 5' it will print out resource usage every 5 seconds  
(ignore the first line) which is more readable than top output as you  
see changes over time.  Change 5 for an interval that suits your  
purpose (ie, if it takes 10 hours to happen, then vmstat 300).

cheers,


On 23 Sep 2008, at 15:04, Rene Fournier wrote:

> Appreciate the suggestions, some of which I've done. The processlist  
> typically just shows the known PHP command-line scripts that run.  
> Maybe 8-10 on average, 20 max.
>
> Here's a strange thing: If I stop all the requests to MySQL (shut  
> down Apache, and exit all the commandline PHP scripts), MySQL's CPU  
> usage remains high.  So... no processes in MySQL, nothing hitting  
> the database, yet MySQL CPU stays stuck at 60-70%.
>
> If I shutdown MySQL and restart, it's normal again.
>
> Weird, no?
>
> 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
>>
>

Thread
Ancient, unsolved high-CPU problemRene Fournier22 Sep
Re: Ancient, unsolved high-CPU problemRene Fournier22 Sep
  • Re: Ancient, unsolved high-CPU problemJeffrey Santos23 Sep
    • Re: Ancient, unsolved high-CPU problemRene Fournier23 Sep
      • Re: Ancient, unsolved high-CPU problemDoug Bridgens23 Sep
        • Re: Ancient, unsolved high-CPU problemRene Fournier23 Sep
          • Re: Ancient, unsolved high-CPU problemDoug Bridgens23 Sep
        • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier23 Sep
          • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psKen Menzel23 Sep
            • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier24 Sep
              • RE: Ancient, unsolved high-CPU problem -- vmstat, top and psMartin Gainty24 Sep
                • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier30 Sep
              • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psKen Menzel24 Sep
                • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psRene Fournier30 Sep
                  • Re: Ancient, unsolved high-CPU problem -- vmstat, top and psKen Menzel30 Sep