[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)?

[Filipus Klutiero]

Le 2011-10-28 01:01, Clint Byrum a écrit :
> 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.

Hehe. I think that suggests the answer :-) The application's next 
version *will* introduce InnoDB support, but only experimental. There 
are still features missing in InnoDB in our minimal required MySQL 
version (5), although we're also working on moving from FULLTEXT search.
> 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.

Thanks. I'm aware that using InnoDB would solve this problem, but our 
past versions don't support InnoDB. I am planning to review the InnoDB 
support which was introduced when I have the opportunity, and hopefully 
I can start testing it.

BTW, I forgot to mention that I am not a database expert at all, so not 
in a good position to judge whether the time to check a MyISAM table is 
reasonable or not.