MyISAM Tables getting Corrupt

Question

sometimes i get an error like "table is marked as corrupt and shld be repaired". that DB (tables) is using MyISAM. recently that keeps happening. what could be the causes? most recently i am executing a batch insert

INSERT INTO table (..., ..., ...) VALUES (...), (...), (...) ...

and it just hung. or took very long to complete it seems hung to me. the next day, when i checked the table was marked as corrupt again. when i try to use mysqlcheck -r it said all tables OK when it reached that "corrupt" table it hung there again...

so, what can i do to prevent this. and what could be the causes. the DB is hosted 3rd party, how can i debug this?

is InnoDB a more reliable engine to use? i heard MyISAM is faster but others say InnoDB can be fast also but it takes abit more to optimize it. can i conclude that InnoDB is something more reliable but abit slower overall even with optimization?

Answer 1

If your tables get corrupt, you can use the repair table command to fix them:

 REPAIR TABLE table;

If you run myisamchk while the server is still running (and inserts/selects are hitting the table), it could be what is corrupting your tables. Most of the corruption issues I run into are when trying to do things outside the server (copying the files, etc) while it is still running.

InnoDB is slower for read only databases, because it has features (ACID compliant, row level locking) that MyISAM leaves out. However, if you are doing a mixture of reads and writes, depending on the mixture, then InnoDB can offer serious performance improvements, because it doesn't have to lock the entire table to do a write. You also don't run into corruption issues.

Answer 2

Go with InnoDB.

Answer 3

ok, so the problem was the company's db exceeded the storage space allowed by the hosting company. so apparently noone told the company they exceeded the usage ... lousy host i guess.

btw, theres no way mysql could have known abt this?