Monday, September 10, 2007

myisamchk - checking and repairing myisam tables in mysql

First of all, how and why does a mysql table go corrupt?

Tables generally go corrupt when mysql server crashes and the tables are not closed properly. They remain in an inconsistent state when mysql is again restarted. What generally happens when a table gets corrupted is that the index file (.MYI) and the data file(.MYD) do not synchronize properly. So there might be some records in the data file for which index entries are not proper.

I have also seen cases where the table goes corrupt just like that. Which might be due to
a) the disk being bad
b) some bug in mysql leading to inconsistent reads and writes.

And one of the ways to repair corrupted mysql (MYISAM) tables is to use the myisamchk utility.

Generally i would do the following (sweet and simple)

mysql> CHECK TABLE ABC;
mysql> REPAIR TABLE ABC;

But there are cases when these queries fail to recover the table. I had a case where "REPAIR TABLE" used to give me "OK" but "Check table" used to give me "table marked as crashed". To recover from such cases the only method to use is the myisamchk utility.

./bin/myisamchk [options] ./data/<database_name>/<table_name>.MYI

A myisamchk --help would list down all the options.

Note: No other program should be accessing the table when myisamchk is repairing a table. I prefer to turn off the mysql on the machine where table is corrupted unless it is repaired.

The best option for full recovery is --safe-recover. Which can be used to repair tables which cannot be repaired by any other option. If the table cannot be repaired using this option then you might have to give up on the table.

But --safe-recover takes up a lot of time.

The way to speed up myisamchk is to increase the buffers that it uses to read, write and process.

--read_buffer_size and --write_buffer_size is used to read and write data from the table and to the temporary file.

--sort_buffer_size is used when the keys are repaired by sorting keys, which is the normal case when you use --recover

--key_buffer_size is used when you are checking the table with --extend-check or when the keys are repaired by inserting keys row by row into the table, that is using --safe-recover option. Repairing using the key buffer is slower and also takes much less disk space as compared to sorting.

To speed up table repairing use large values for key buffer and sort buffer. Both values can be made very large cause only one of them is used at a time.

Eg: myisamchk --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=8M --write_buffer_size=8M ./path/to/database/table.MYI

No comments: