Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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).

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.