13.1 Using isamchk for table maintenance and crash recovery

To check/repair ISAM tables (.ISM and .ISD) you should use the isamchk utility. To check/repair MyISAM tables (.MYI and .MYD) you should use the myisamchk utility. 10.18 MySQL table types..

In the following text we will talk about isamchk but everything also applies to myisamchk.

You can use the isamchk utility to get information about your database tables, check and repair them or optimize them. The following sections describe how to invoke isamchk (including a description of its options), how to set up a table maintenance schedule, and how to use isamchk to perform its various functions.

If you run mysqld with --skip-locking (which is the default on some systems, like Linux), you can't reliably use isamchk to check a table when mysqld is using the same table. If you can be sure that no one is accessing the tables through mysqld while you run isamchk, you only have to do mysqladmin flush-tables before you start checking the tables. If you can't guarantee the above, then you must take down mysqld while you check the tables. If you run isamchk while mysqld is updating the tables, you may get a warning that a table is corrupt even if it isn't.

If you are not using --skip-locking, you can use isamchk to check tables at any time. While you do this, all clients that try to update the table will wait until isamchk is ready before continuing.

If you use isamchk to repair or optimize tables, you MUST always ensure that the mysqld server is not using the table (this also applies if you are using --skip-locking). If you don't take down mysqld you should at least do a mysqladmin flush-tables before you run isamchk.

You can in most cases also use the command OPTIMIZE TABLES to optimize and repair tables, but this is not as fast or reliable (in case of real fatal errors) as isamchk. On the other hand, OPTIMIZE TABLE is easier to use and you don't have to worry about flushing tables. OPTIMIZE TABLE.

13.1.1 isamchk invocation syntax

isamchk is invoked like this:

shell> isamchk [options] tbl_name

The options specify what you want isamchk to do. They are described below. (You can also get a list of options by invoking isamchk --help.) With no options, isamchk simply checks your table. To get more information or to tell isamchk to take corrective action, specify options as described below and in the following sections.

tbl_name is the database table you want to check. If you run isamchk somewhere other than in the database directory, you must specify the path to the file, since isamchk has no idea where your database is located. Actually, isamchk doesn't care whether or not the files you are working on are located in a database directory; you can copy the files that correspond to a database table into another location and perform recovery operations on them there.

You can name several tables on the isamchk command line if you wish. You can also specify a name as an index file name (with the `.ISM' suffix), which allows you to specify all tables in a directory by using the pattern `*.ISM'. For example, if you are in a database directory, you can check all the tables in the directory like this:

shell> isamchk *.ISM

If you are not in the database directory, you can check all the tables there by specifying the path to the directory:

shell> isamchk /path/to/database_dir/*.ISM

You can even check all tables in all databases by specifying a wildcard with the path to the MySQL data directory:

shell> isamchk /path/to/datadir/*/*.ISM

isamchk supports the following options:

-a, --analyze
Analyze the distribution of keys. This improves join performance by enabling the join optimizer to better choose in which order it should join the tables and which keys it should use.
-#, --debug=debug_options
Output debug log. The debug_options string often is 'd:t:o,filename'.
-d, --description
Prints some information about the table.
-e, --extend-check
Check the table VERY thoroughly. This is necessary only in extreme cases. Normally, isamchk should find all errors even without this option.
-f, --force
Overwrite old temporary files. If you use -f when checking tables (running isamchk without -r), isamchk will automatically restart with -r on any table for which an error occurs during checking.
Display a help message and exit.
-i, --information
Print informational statistics about the table that is checked.
-k #, --keys-used=#
Used with -r. Tell the NISAM table handler to update only the first # indexes. Higher-numbered indexes are deactivated. This can be used to get faster inserts! Deactivated indexes can be reactivated by using isamchk -r.
-l, --no-symlinks
Do not follow symbolic links when repairing. Normally isamchk repairs the table a symlink points at.
-q, --quick
Used with -r to get a faster repair. Normally, the original data file isn't touched; you can specify a second -q to force the original data file to be used.
-r, --recover
Recovery mode. Can fix almost anything except unique keys that aren't unique.
-o, --safe-recover
Recovery mode. Uses an old recovery method; this is slower than -r, but can handle a couple of cases that -r cannot handle.
-O var=option, --set-variable var=option
Set the value of a variable. The possible variables are listed below.
-s, --silent
Silent mode. Write output only when errors occur. You can use -s twice (-ss) to make isamchk very silent.
-S, --sort-index
Sort the index tree blocks in high-low order. This will optimize seeks and will make table scanning by key faster.
-R index_num, --sort-records=index_num
Sorts records according to an index. This makes your data much more localized and may speed up ranged SELECT and ORDER BY operations on this index. (It may be VERY slow to do a sort the first time!) To find out a table's index numbers, use SHOW INDEX, which shows a table's indexes in the same order that isamchk sees them. Indexes are numbered beginning with 1.
-u, --unpack
Unpack a table that was packed with pack_isam.
-v, --verbose
Verbose mode. Print more information. This can be used with -d and -e. Use -v multiple times (-vv, -vvv) for more verbosity!
-V, --version
Print the isamchk version and exit.
-w, --wait
Wait if the table is locked.

Possible variables for the --set-variable (-O) option are:

key_buffer_size       current value: 16776192
read_buffer_size      current value: 262136
write_buffer_size     current value: 262136
sort_buffer_size      current value: 2097144
sort_key_blocks       current value: 16
decode_bits           current value: 9

13.1.2 isamchk memory usage

Memory allocation is important when you run isamchk. isamchk uses no more memory than you specify with the -O options. If you are going to use isamchk on very large files, you should first decide how much memory you want it to use. The default is to use only about 3M to fix things. By using larger values, you can get isamchk to operate faster. For example, if you have more than 32M RAM, you could use options such as these (in addition to any other options you might specify):

shell> isamchk -O sort=16M -O key=16M -O read=1M -O write=1M ...

Using -O sort=16M should probably be enough for most cases.

Be aware that isamchk uses temporary files in TMPDIR. If TMPDIR points to a memory file system, you may easily get out of memory errors. If this happens, set TMPDIR to point at some directory with more space and restart isamchk