List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:January 21 2008 4:34pm
Subject:Re: MySql at 100% CPU
View as plain text  
Hello Ben,

Ben Clewett wrote:
> No worries :)
> 
>  > Good luck with your problem!
> 
> Apparently not, since no member of the MySql team wants to tell me and 
> this mailing list what scenario's will cause a low-load MySql system 
> lock at 100% CPU for hours on end.
> 
> Another of my servers has just entered this mode about half an hour ago. 
>  I include the xload graph just to show I am not imagining this!
> 
> Another guess: these servers are quite new and growing at several gig a 
> week.  Does InnoDB have some mode whereby it checks all tables after 
> some threshold of time, size or row count?  Which is likely to take 
> hours and put the CPU load at 100%?
> 
> If four of my ten MySql servers are doing this regularly, I can't be the 
> only person with this problem.  Can somebody from MySql please let me 
> know that on earth you are doing to my CPU's??
> 

What you might be seeing is the "purge" process of the InnoDB engine.
http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#option_mysqld_innodb_max_purge_lag

and from
http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html
 >>In a scenario where the user inserts and deletes rows in smallish 
batches at about the same rate in the table, it is possible that the 
purge thread starts to lag behind, and the table grows bigger and 
bigger, making everything disk-bound and very slow. Even if the table 
carries just 10MB of useful data, it may grow to occupy 10GB with all 
the “dead” rows.<< 	

What happens during idle times is the purge thread takes over for a 
short period of time and completes its work. However if you have a huge 
purge backlog, that "short period of time" becomes noticably large.

I recommend reducing your innodb_max_purge_lag variable to something 
that will prevent as large a backlog as you are seeing today.

-- 
Shawn Green, Support Engineer
MySQL Inc., USA, www.mysql.com
Office: Blountville, TN
     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /
   / /|_/ / // /\ \/ /_/ / /__
  /_/  /_/\_, /___/\___\_\___/
         <___/
  Join the Quality Contribution Program Today!
  http://dev.mysql.com/qualitycontribution.html


Thread
MySql at 100% CPUBen Clewett17 Jan
  • Re: MySql at 100% CPUKevin Hunter17 Jan
    • Re: MySql at 100% CPUBen Clewett17 Jan
      • Re: MySql at 100% CPUKevin Hunter17 Jan
        • Re: MySql at 100% CPUBen Clewett18 Jan
          • Re: MySql at 100% CPUShawn Green21 Jan
            • Re: MySql at 100% CPUStut21 Jan