[debian-mysql] Bug#646859: Bug#646859: [mysql-server-5.1] check_for_crashed_tables() uses too much CPU time for each MyISAM table when MySQL starts (debian-start)?

[Clint Byrum]

Excerpts from Filipus Klutiero's message of Thu Oct 27 16:49:30 -0400 2011:
> Package: mysql-server-5.1
> Version: 5.1.58-1
> Severity: wishlist
> 
> Hi,
> I'm opening a ticket about a general problem, but this is food for 
> thought too. Please apologize and triage as you wish.
> 
> I am a web developer working on a web application which has a large 
> schema. There are currently over 200 tables in the schema, all of them 
> MyISAM.
> Every few weeks, I'll install a new version of the application and 
> create a new MySQL database for it.
> 
> My development desktop was installed in April 2010, and has been getting 
> slower and slower to boot, to the point where I'm thinking about 
> reinstalling since months. There is a lot of disk read during boot, and 
> I never knew precisely why. Today I realized by accident that mysqld was 
> taking about 10% CPU for a very long time during boot, and went to 
> investigate why.
> 
> Restarting mysql seems to happen fast (less than 5 seconds), but it was 
> in fact causing significant CPU usage for a long time. I found that this 
> CPU usage happened every time mysql was started, and then that this came 
> from /etc/mysql/debian-start. I then discovered it came from 
> check_for_crashed_tables(). The function contains:
> 

My first question would be why are you using MyISAM for anything
important.  Or rather, why are you using MyISAM, at all. I know that
we are still stuck with MySQL 5.1 and, therefore, have MyISAM as the
default, but its become quite obvious that it is such a limited data
storage engine, that it should not be used by any serious application.

I would recommend that you alter all of your tables to InnoDB, and start
explicitly setting the engine to InnoDB during your create tables. You
will get a far more resilient system with nearly the same speed on most
common sane operations. There is no need to check these tables, because
like a journaled filesystem, one can trust that the database is in a
consistent state or not just by looking at the headers and journals.