Table in mysql database gets damaged everytime the server crashes

Question

There is something driving me crazy... I have a table in one database, that is over 1.4 GB and has about 500.000 records.

The problem is that everytime, or almost all times, that my server crash, due to overload, or anyother problem, this table gets damaged.

That is what I think, because, the website that uses it, load forever, and the server get overloaded. To fix, I need to run repair table TABLE.

Is there anything I can do to avoid this?

Answer 1

Convert the table to InnoDB. There are several reasons to do so:

REASON 1 : MyISAM tables perform a full table lock with each INSERT, UPDATE, and DELETE. InnoDB performs row-level locks that can be still read thanks to MVCC (Multiversion Concurrency Control)

REASON 2 : Crash recovery with InnoDB can be as simple as restarting MySQL since the redo logs record uncommitted transactions and that are processed either through normal commits of transactions or during mysql startup.

REASON 3 : MyISAM tables maintain a counter in its headers for open file handles against the table. The simplest 'crashed' MyISAM table is a table who data is just fine but whose open file handle count is greater than zero when the table is closed. Performing 'myisamchk -r' or REPAIR TABLE against a MyISAM table always runs the risk of losing one or more rows of data.

REASON 4 : InnoDB has ACID (Atomicity, Consistency, Isolation, Durability) compliance. Transactions can be consist of one or more SQL statements and treat them as one operation (Thus, Atomicity). Data is either fully committed or fully rolled back (Thus Consistency and Durability). Every DB Connection can have its own view of the data while it is being updated without being locked (Thus Isolation by means of MVCC).

Answer 2

A table that's not in a consistent state (say, for instance, if the server crashes) should need a repair to check for and repair any issues due to inconsistent disk state (partially completely changes, etc) - this is very much by design.

I'd recommend putting that effort toward having fewer server crashes.

Answer 3

It might be worth it to invest some time into stabilizing the server altogether, because I don't think you can avoid repairing the tables.