Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
From: Shawn Green (shawnmysql.com)
Date: Mon Jan 21 2008 - 10:34:00 CST
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.
>>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!
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql