Tuesday, January 29, 2013

MySQL Corrupt Tables and How to Avoid it

Once in a while MySQL's tables became corrupted. This post is not interested in repair process (you should see other posts, but the most general advice is to do a REPAIR TABLE table; )
In my humble opinion, a real life production database must not have any corruption, it must have sufficient failsafe mechanisms to avoid such corruption.

Causes of Corruption

MyISAM tables could became corrupted by  (refer http://dev.mysql.com/doc/refman/5.1/en/corrupted-myisam-tables.html) :
  • mysqld is being killed in the middle of write
  • unexpected computer shutdown occured
  • hardware failures
  • running an external program (example: myisamchk) while mysqld is running
  • software bug in Mysql/myISAM code

Tips to mitigate data corruption

Do not use MyISAM for long lasting data. Use InnoDB. InnoDB is less corruption prone than MyISAM. Use file per table option.
Check your disk space. Check database availability periodically. On one occasion, my mysql data partition is full, and noticed that queries have been stopped responding. I deleted some unused log files and MySQL returned to normal service, without any corruption. Somehow the engine pauses when it run out of disk space and resumes after space is available.
Turn on binary logging. It helps in disaster forensics (such as when your table has zero rows and you need to find out which app/person responsible).
Install a secondary mysql as slave server. If you only have virtual machines, it would be better if the slave server is ensured to be in another region or another physical server.
Ensure MySQL's memory usage is compatible with available memory. This means no other application are allowed to dynamically eat memory. Out of memory conditions will turn the Linux OS into a process killer, so the probability of which should be set as zero as possible.
Backup periodically. Daily automated backups will be perfect. Must think about where the backups will be stored outside the server.

No comments: