MySQL Reference Manual for version 3.23.5-alpha. - 13.1 Using isamchk for table maintenance and crash recovery



	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. `--help` 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`