Disaster Prevention and Recovery with the MySQL Database - 4.6.2.4 Repair Options for myisamchk (Page 3 of 7 ) myisamchk supports the following options for table repair operations: --backup, -B Make a backup of the .MYD file as file_name-time.BAK --character-sets-dir=path The directory where character sets are installed. See Section 4.7.1, "The Character Set Used for Data and Sorting." --correct-checksum Correct the checksum information for the table. --data-file-length=#, -D # Maximum length of the data file (when re-creating data file when it's "full"). --extend-check, -e Do a repair that tries to recover every possible row from the data file. Normally this will also find a lot of garbage rows. Don't use this option unless you are totally desperate. --force, -f Overwrite old temporary files (files with names like tbl_name.TMD) instead of aborting. --keys-used=#, -k # For myisamchk, the option value indicates which indexes to update. Each binary bit of the option value corresponds to a table index, where the first index is bit 0. For isamchk, the option value indicates that only the first # of the table indexes should be updated. In either case, an option value of 0 disables updates to all indexes, which can be used to get faster inserts. Deactivated indexes can be reactivated by using myisamchk -r or (isamchk -r). --no-symlinks, -l Do not follow symbolic links. Normally myisamchk repairs the table that a symlink points to. This option doesn't exist as of MySQL 4.0, because versions from 4.0 on will not remove symlinks during repair operations. --parallel-recover, -p Uses the same technique as -r and -n, but creates all the keys in parallel, using different threads. This option was added in MySQL 4.0.2. This is alpha code. Use at your own risk! --quick, -q Achieve a faster repair by not modifying the data file. You can specify this option twice to force myisamchk to modify the original data file in case of duplicate keys. --recover, -r Do a repair that can fix almost any problem except unique keys that aren't unique (which is an extremely unlikely error with ISAM/MyISAM tables). If you want to recover a table, this is the option to try first. You should try -o only if myisamchk reports that the table can't be recovered by -r. (In the unlikely case that -r fails, the data file is still intact.) If you have lots of memory, you should increase the value of sort_buffer_size. --safe-recover, -o Do a repair using an old recovery method that reads through all rows in order and updates all index trees based on the rows found. This is an order of magnitude slower than -r, but can handle a couple of very unlikely cases that -r cannot. This recovery method also uses much less disk space than -r. Normally, you should repair first with -r, and then with -o only if -r fails. If you have lots of memory, you should increase the value of key_buffer_size. --set-character-set=name Change the character set used by the table indexes. --sort-recover, -n Force myisamchk to use sorting to resolve the keys even if the temporary files should not be very big. --tmpdir=path, -t path Path of the directory to be used for storing temporary files. If this is not set, myisamchk uses the value of the TMPDIR environment variable. Starting from MySQL 4.1, tmpdir can be set to a list of directory paths that will be used successively in round-robin fashion for creating temporary files. The separator character between directory names should be colon (':') on Unix and semicolon (';') on Windows, NetWare, and OS/2. --unpack, -u Unpack a table that was packed with myisampack.
4.6.2.5 Other Options for myisamchk myisamchk supports the following options for actions other than table checks and repairs: --analyze, -a Analyze the distribution of keys. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which keys it should use. To obtain information about the distribution, use a myisamchk --description --verbose tbl_name command or the SHOW KEYS FROM tbl_name statement. --description, -d Print some descriptive information about the table. --set-auto-increment[=value], -A[value] Force AUTO_INCREMENT numbering for new records to start at the given value (or higher, if there are already records with AUTO_INCREMENT values this large). If value is not specified, the AUTO_INCREMENT number for new records begins with the largest value currently in the table, plus one. --sort-index, -S Sort the index tree blocks in high-low order. This optimizes seeks and makes table scanning by key faster. --sort-records=#, -R # Sort records according to a particular index. This makes your data much more localized and may speed up range-based SELECT and ORDER BY operations that use this index. (The first time you use this option to sort a table, it may be very slow.) To determine a table's index numbers, use SHOW KEYS, which displays a table's indexes in the same order that myisamchk sees them. Indexes are numbered beginning with 1.
4.6.2.6 myisamchk Memory Usage Memory allocation is important when you run myisamchk. myisamchk uses no more memory than you specify with the -O options. If you are going to use myisamchk on very large tables, you should first decide how much memory you want it to use. The default is to use only about 3MB to perform repairs. By using larger values, you can get myisamchk to operate faster. For example, if you have more than 32MB RAM, you could use options such as these (in addition to any other options you might specify): shell> myisamchk -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 myisamchk uses temporary files in TMPDIR. If TMPDIR points to a memory filesystem, you may easily get out of memory errors. If this happens, set TMPDIR to point at some directory located on a filesystem with more space and run myisamchk again. When repairing, myisamchk will also need a lot of disk space: Double the size of the data file (the original one and a copy). This space is not needed if you do a repair with --quick; in this case, only the index file is re-created. This space is needed on the same filesystem as the original data file! (The copy is created in the same directory as the original.) Space for the new index file that replaces the old one. The old index file is truncated at the start of the repair operation, so you usually ignore this space. This space is needed on the same filesystem as the original index file! When using --recover or --sort-recover (but not when using --safe-recover), you will need space for a sort buffer. The amount of space required is: (largest_key + row_pointer_length) *
number_of_rows * 2 You can check the length of the keys and the row_pointer_length with myisamchk -dv tbl_name. This space is allocated in the temporary directory (specified by TMPDIR or --tmpdir=path).
If you have a problem with disk space during repair, you can try to use --safe-recover instead of --recover. Next: 4.6.2.7 Using myisamchk for Crash Recovery >>
More MySQL Articles
More By Sams Publishing |  This article is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345). Check it out today at your favorite bookstore. Buy this book now.
|
|
|
