Monday, 8 February 2010

MyISAM Table Maintenance

There are numerous ways to check/repair MyISAM tables

1. mysqlchk

2. mysqlcheck

3. myisam_recover

One should not use mysqlchk with online database, unlike mysqlcheck it directly accesses tables and may damage them. There are two alternative solutions

  1. MYISAM_RECOVER option: quick option to automate recovery but it should be used very carefully. As all live tables are accessed quite frequently, enabling this option may trigger many check/repair at once.
  2. CHECK TABLE: it is safer compared to myisam_recover option. Unlike myisam_chk it does not directly access tables.

It is not recommend that one should run extended table checks regularly. However it is highly recommended using medium/quick check options to ensure that all tables are okay. Furthermore one should be optimizing tables regularly, as this reduces risks associated with table errors. Unlike check table operations, optimize table operation takes much longer but the good news is that we can easily identify the table(s) that need optimization.

No comments:

Post a Comment