13.1 Utiliser isamchk pour la maintenance et la réparation

Pour vérifier/réparer les tables ISAM (.ISM et .ISD), vous devez utiliser l'utilitaire : isamchk. Pour vérifier/réparer les tables MyISAM (.MYI et .MYD) vous devez utiliser utiliser l'utilitaire myisamchk. 10.18 Types de tables MySQL.

Par la suite, nous traiterons le cas de isamchk mais les instructions pourront aussi s'appliquer ˆ myisamchk.

Vous pouvez utiliser l'utilitaire isamchk pour receuillir des informations sur votre base de données et ses tables, pour vérifier et réparer les tables ou encore les optimiser. La section suivante décrit les commandes d'invocations de isamchk (y compris les options), les méthodes de maintenance régulière, et comment utiliser isamchk pour effectuer diverses tàches.

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 Syntaxe isamchk

isamchk is invoked like this:

shell> isamchk [options] nom_table

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.

nom_table 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 how 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 méthode; 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 Utilisation de la mémoire par isamchk

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