MySQL Reference Manual for version 3.22.14b-gamma.


13 Using isamchk for table maintenance and crash recovery

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.

13.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 will make some joins in MySQL faster.
-#, --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 index blocks. This speeds up "read-next" in applications.
-R index_num, --sort-records=index_num
Sort 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:

keybuffer             default value: 520192
readbuffer            default value: 262136
writebuffer           default value: 262136
sortbuffer            default value: 2097144
sort_key_blocks       default value: 16
decode_bits           default value: 9

13.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 sortbuffer=16M -O keybuffer=16M \
           -O readbuffer=1M -O writebuffer=1M ...

Using -O sortbuffer=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.

13.3 Setting up a table maintenance regime

It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur. For maintenance purposes, you can use isamchk -s to check tables. The -s option causes isamchk to run in silent mode, printing messages only when errors occur.

It's a good idea to check tables when the server starts up. For example, whenever the machine has done a reboot in the middle of an update, you usually need to check all the tables that could have been affected. (This is an "expected crashed table".) You could add a test to safe_mysqld that runs isamchk to check all tables that have been modified during the last 24 hours if there is an old `.pid' (process ID) file left after a reboot. (The `.pid' file is created by mysqld when it starts up and removed when it terminates normally. The presence of a `.pid' file at system startup time indicates that mysqld terminated abnormally.)

An even better test would be to check any table whose last-modified time is more recent than that of the `.pid' file.

You should also check your tables regularly during normal system operation. At TcX, we run a cron job to check all our important tables once a week, using a line like this in a `crontab' file:

35 0 * * 0 /path/to/isamchk -s /path/to/datadir/*/*.ISM

This prints out information about crashed tables so we can examine and repair them when needed.

As we haven't had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for a couple of years now (this is really true), once a week is more than enough for us.

We recommend that to start with, you execute isamchk -s each night on all tables that have been updated during the last 24 hours, until you come to trust MySQL as much as we do.

13.4 Getting information about a table

To get a description of a table or statistics about it, use the commands shown below. We explain some of the information in more detail later.

isamchk -d tbl_name
Runs isamchk in "describe mode" to produce a description of your table. If you start the MySQL server using the --skip-locking option, isamchk may report an error for a table that is updated while it runs. However, since isamchk doesn't change the table in describe mode, there isn't any risk of destroying data.
isamchk -d -v tbl_name
To produce more information about what isamchk is doing, add -v to tell it to run in verbose mode.
isamchk -eis tbl_name
Shows only the most important information from a table. It is slow since it must read the whole table.
isamchk -eiv tbl_name
This is like -eis, but tells you what is being done.

Example of isamchk -d output:

ISAM file:     company.ISM
Data records:           1403698  Deleted blocks:         0
Recordlength:               226
Record format: Fixed length

table description:
Key Start Len Index   Type
1   2     8   unique  double
2   15    10  multip. text packed stripped
3   219   8   multip. double
4   63    10  multip. text packed stripped
5   167   2   multip. unsigned short
6   177   4   multip. unsigned long
7   155   4   multip. text
8   138   4   multip. unsigned long
9   177   4   multip. unsigned long
    193   1           text

Example of isamchk -d -v output:

ISAM file:     company.ISM
Isam-version:  2
Creation time: 1996-08-28 11:44:22
Recover time:  1997-01-12 18:35:29
Data records:           1403698  Deleted blocks:              0
Datafile: Parts:        1403698  Deleted data:                0
Datafilepointer (bytes):      3  Keyfile pointer (bytes):     3
Max datafile length: 3791650815  Max keyfile length: 4294967294
Recordlength:               226
Record format: Fixed length

table description:
Key Start Len Index   Type                      Root Blocksize Rec/key
1   2     8   unique  double                15845376      1024       1
2   15    10  multip. text packed stripped  25062400      1024       2
3   219   8   multip. double                40907776      1024      73
4   63    10  multip. text packed stripped  48097280      1024       5
5   167   2   multip. unsigned short        55200768      1024    4840
6   177   4   multip. unsigned long         65145856      1024    1346
7   155   4   multip. text                  75090944      1024    4995
8   138   4   multip. unsigned long         85036032      1024      87
9   177   4   multip. unsigned long         96481280      1024     178
    193   1           text

Example of isamchk -eis output:

Checking ISAM file: company.ISM
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:  98%  Packed:   17%

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1626.51, System time 232.36
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 627, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 639, Involuntary context switches 28966

Example of isamchk -eiv output:

Checking ISAM file: company.ISM
Data records: 1403698   Deleted blocks:       0
- check file-size
- check delete-chain
index  1:
index  2:
index  3:
index  4:
index  5:
index  6:
index  7:
index  8:
index  9:
No recordlinks
- check index reference
- check data record references index: 1
Key:  1:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 2
Key:  2:  Keyblocks used:  98%  Packed:   50%  Max levels:  4
- check data record references index: 3
Key:  3:  Keyblocks used:  97%  Packed:    0%  Max levels:  4
- check data record references index: 4
Key:  4:  Keyblocks used:  99%  Packed:   60%  Max levels:  3
- check data record references index: 5
Key:  5:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 6
Key:  6:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 7
Key:  7:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 8
Key:  8:  Keyblocks used:  99%  Packed:    0%  Max levels:  3
- check data record references index: 9
Key:  9:  Keyblocks used:  98%  Packed:    0%  Max levels:  4
Total:    Keyblocks used:   9%  Packed:   17%

- check records and index references
[LOTS OF ROW NUMBERS DELETED]

Records:          1403698    M.recordlength:     226   Packed:             0%
Recordspace used:     100%   Empty space:          0%  Blocks/Record:   1.00
Recordblocks:     1403698    Deleteblocks:         0
Recorddata:     317235748    Deleted data:         0
Lost space:             0    Linkdata:             0

User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798

Here are the sizes of the data and index files for the table used in the preceding examples:

-rw-rw-r--   1 monty    tcx     317235748 Jan 12 17:30 company.ISD
-rw-rw-r--   1 davida   tcx      96482304 Jan 12 18:35 company.ISM

Explanations for the types of information isamchk produces are given below. The "keyfile" is the index file. "Record" and "row" are synonymous.

ISAM file
Name of the ISAM (index) file.
Isam-version
Version of ISAM format. Currently always 2.
Creation time
When the data file was created.
Recover time
When the index/data file was last reconstructed.
Data records
How many records are in the table.
Deleted blocks
How many deleted blocks still have reserved space. You can optimize your table to minimize this space. See section 13.5.3 Table optimization.
Datafile: Parts
For dynamic record format, this indicates how many data blocks there are. For an optimized table without fragmented records, this is the same as Data records.
Deleted data
How many bytes of non-reclaimed deleted data there are. You can optimize your table to minimize this space. See section 13.5.3 Table optimization.
Datafile pointer
The size of the data file pointer, in bytes. It is usually 2, 3, 4 or 5 bytes. Most tables manage with 2 bytes, but this cannot be controlled from MySQL yet. For fixed tables, this is a record address. For dynamic tables, this is a byte address.
Keyfile pointer
The size of the index file pointer, in bytes. It is usually 1, 2 or 3 bytes. Most tables manage with 2 bytes, but this is calculated automatically by MySQL. It is always a block address.
Max datafile length
How long the table's data file (.ISD file) can become, in bytes.
Max keyfile length
How long the table's key file (.ISM file) can become, in bytes.
Recordlength
How much space each record takes, in bytes.
Record format
The format used to store table rows. The examples shown above use Fixed length. Other possible values are Compressed and Packed.
table description
A list of all keys in the table. For each key, some low-level information is presented:
Key
This key's number.
Start
Where in the record this index part starts.
Len
How long this index part is. For packed numbers, this should always be the full length of the column. For strings, it may be shorter than the full length of the indexed column, because you can index a prefix of a string column.
Index
unique or multip. (multiple). Indicates whether or not one value can exist multiple times in this index.
Type
What data-type this index part has. This is an NISAM data-type with the options packed, stripped or empty.
Root
Address of the root index block.
Blocksize
The size of each index block. By default this is 1024, but the value may be changed at compile time.
Rec/key
This is a statistical value used by the optimizer. It tells how many records there are per value for this key. A unique key always has a value of 1. This may be updated after a table is loaded (or greatly changed) with isamchk -a. If this is not updated at all, a default value of 30 is given.
In the first example above, the 9th key is a multi-part key with two parts.
Keyblocks used
What percentage of the keyblocks are used. Since the table used in the examples had just been reorganized with isamchk, the values are very high (very near the theoretical maximum).
Packed
MySQL tries to pack keys with a common suffix. This can only be used for CHAR/VARCHAR/DECIMAL keys. For long strings like names, this can significantly reduce the space used. In the third example above, the 4th key is 10 characters long and a 60% reduction in space is achieved.
Max levels
How deep the B-tree for this key is. Large tables with long keys get high values.
Records
How many rows are in the table.
M.recordlength
The average record length. For tables with fixed-length records, this is the exact record length.
Packed
MySQL strips spaces from the end of strings. The Packed value indicates the percentage savings achieved by doing this.
Recordspace used
What percentage of the data file is used.
Empty space
What percentage of the data file is unused.
Blocks/Record
Average number of blocks per record (i.e., how many links a fragmented record is composed of). This is always 1 for fixed-format tables. This value should stay as close to 1.0 as possible. If it gets too big, you can reorganize the table with isamchk. See section 13.5.3 Table optimization.
Recordblocks
How many blocks (links) are used. For fixed format, this is the same as the number of records.
Deleteblocks
How many blocks (links) are deleted.
Recorddata
How many bytes in the data file are used.
Deleted data
How many bytes in the data file are deleted (unused).
Lost space
If a record is updated to a shorter length, some space is lost. This is the sum of all such losses, in bytes.
Linkdata
When the dynamic table format is used, record fragments are linked with pointers (4 to 7 bytes each). Linkdata is the sum of the amount of storage used by all such pointers.

If a table has been compressed with pack_isam, isamchk -d prints additional information about each table column. See section 12.3 The MySQL compressed read-only table generator, for an example of this information and a description of what it means.

13.5 Using isamchk for crash recovery

The file format that MySQL uses to store data has been extensively tested, but there are always external circumstances that may cause database tables to become corrupted:

This chapter describes how to check for and deal with data corruption in MySQL databases.

When performing crash recovery, it is important to understand that each table tbl_name in a database corresponds to three files in the database directory:

File Purpose
`tbl_name.frm' Table definition (form) file
`tbl_name.ISD' Data file
`tbl_name.ISM' Index file

Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.

isamchk works by creating a copy of the `.ISD' (data) file row by row. It ends the repair stage by removing the old `.ISD' file and renaming the new file to the original file name. If you use --quick, isamchk does not create a temporary `.ISD' file, but instead assumes that the `.ISD' file is correct and only generates a new index file without touching the `.ISD' file. This is safe, because isamchk automatically detects if the `.ISD' file is corrupt and aborts the repair in this case. You can also give two --quick options to isamchk. In this case, isamchk does not abort on some errors (like duplicate key) but instead tries to resolve them by modifying the `.ISD' file. Normally the use of two --quick options is useful only if you have too little free disk space to perform a normal repair. In this case you should at least make a backup before running isamchk.

13.5.1 How to check tables for errors

To check a table, use the following commands:

isamchk tbl_name
This finds 99.99% of all errors. What it can't find is corruption that involves ONLY the data file (which is very unusual). If you want to check a table, you should normally run isamchk without options or with either the -s or --silent option.
isamchk -e tbl_name
This does a complete and thorough check of all data (-e means "extended check"). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a LONG time on a big table with many keys. isamchk will normally stop after the first error it finds. If you want to obtain more information, you can add the --verbose (-v) option. This causes isamchk to keep going, up through a maximum of 20 errors. In normal usage, a simple isamchk (with no arguments other than the table name) is sufficient.
isamchk -e -i tbl_name
Like the previous command, but the -i option tells isamchk to print some informational statistics, too.

13.5.2 How to repair tables

The symptoms of a corrupted table are usually that queries abort unexpectedly and that you observe errors such as these:

In these cases, you must repair your tables. isamchk can usually detect and fix most things that go wrong.

The repair process involves up to four stages, described below. Before you begin, you should cd to the database directory and check the permissions of the table files. Make sure they are readable by the Unix user that mysqld runs as (and to you, since you need to access the files you are checking). If it turns out you need to modify files, they must also be writable by you.

Stage 1: Checking your tables

Run isamchk *.ISM or (isamchk -e *.ISM if you have more time). Use the -s (silent) option to suppress unnecessary information.

You have to repair only those tables for which isamchk announces an error. For such tables, proceed to Stage 2.

If you get weird errors when checking (such as out of memory errors), or if isamchk crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try isamchk -r -q tbl_name (-r -q means "quick recovery mode"). This will attempt to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

  1. Make a backup of the data file before continuing.
  2. Use isamchk -r tbl_name (-r means "recovery mode"). This will remove incorrect records and deleted records from the data file and reconstruct the index file.
  3. If the preceding step fails, use isamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode doesn't (but is slower).

If you get weird errors when repairing (such as out of memory errors), or if isamchk crashes, go to Stage 3.

Stage 3: Difficult repair

You should only reach this stage if the first 16K block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it's necessary to create a new index file. Do so as follows:

  1. Move the data file to some safe place.
  2. Use the table description file to create new (empty) data and index files:
    shell> mysql db_name
    mysql> DELETE FROM tbl_name;
    mysql> quit
    
  3. Copy the old data file back onto the newly created data file. (Don't just move the old file back onto the new file; you want to retain a copy in case something goes wrong.)

Go back to Stage 2. isamchk -r -q should work now. (This shouldn't be an endless loop).

Stage 4: Very difficult repair

You should reach this stage only if the description file has also crashed. That should never happen, because the description file isn't changed after the table is created.

  1. Restore the description file from a backup and go back to Stage 3. You can also restore the index file and go back to Stage 2. In the latter case, you should start with isamchk -r.
  2. If you don't have a backup but know exactly how the table was created, create a copy of the table in another database. Remove the new data file, then move the description and index files from the other database to your crashed database. This gives you new description and index files, but leaves the data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

13.5.3 Table optimization

To coalesce fragmented records and eliminate wasted space resulting from deleting or updating records, run isamchk in recovery mode:

shell> isamchk -r tbl_name

You can optimize a table in the same way using the SQL OPTIMIZE TABLE statement. OPTIMIZE TABLE is easier, but isamchk is faster.

isamchk also has a number of other options you can use to improve the performance of a table:

-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.
-a, --analyze
Analyzes the distribution of keys in a table. This improves join performance when you retrieve records from the table later.


This document was generated on 3 January 1999 using the texi2html translator version 1.52 (extended by davida@detron.se).