7.6 MyISAM Table Maintenance and Crash Recovery

7.6.1 Using myisamchk for Crash Recovery

This section describes how to check for and deal with data corruption in MySQL databases. If your tables become corrupted frequently, you should try to find the reason why. See *note crashing::.

For an explanation of how 'MyISAM' tables can become corrupted, see *note myisam-table-problems::.

If you run note 'mysqld': mysqld. with external locking disabled (which is the default), you cannot reliably use note 'myisamchk': myisamchk. to check a table when note 'mysqld': mysqld. is using the same table. If you can be certain that no one can access the tables through note 'mysqld': mysqld. while you run note 'myisamchk': myisamchk, you have only to execute note 'mysqladmin flush-tables': mysqladmin. before you start checking the tables. If you cannot guarantee this, you must stop note 'mysqld': mysqld. while you check the tables. If you run note 'myisamchk': myisamchk. to check tables that *note 'mysqld': mysqld. is updating at the same time, you may get a warning that a table is corrupt even when it is not.

If the server is run with external locking enabled, you can use note 'myisamchk': myisamchk. to check tables at any time. In this case, if the server tries to update a table that note 'myisamchk': myisamchk. is using, the server waits for *note 'myisamchk': myisamchk. to finish before it continues.

If you use note 'myisamchk': myisamchk. to repair or optimize tables, you must always ensure that the note 'mysqld': mysqld. server is not using the table (this also applies if external locking is disabled). If you do not stop note 'mysqld': mysqld, you should at least do a note 'mysqladmin flush-tables': mysqladmin. before you run note 'myisamchk': myisamchk. Your tables may become corrupted if the server and note 'myisamchk': myisamchk. access the tables simultaneously.

When performing crash recovery, it is important to understand that each 'MyISAM' table TBL_NAME in a database corresponds to the three files in the database directory shown in the following table.

File Purpose

'TBL_NAME.frm' Definition (format) file

'TBL_NAME.MYD' Data file

'TBL_NAME.MYI' 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.

note 'myisamchk': myisamchk. works by creating a copy of the '.MYD' data file row by row. It ends the repair stage by removing the old '.MYD' file and renaming the new file to the original file name. If you use '--quick', note 'myisamchk': myisamchk. does not create a temporary '.MYD' file, but instead assumes that the '.MYD' file is correct and generates only a new index file without touching the '.MYD' file. This is safe, because note 'myisamchk': myisamchk. automatically detects whether the '.MYD' file is corrupt and aborts the repair if it is. You can also specify the '--quick' option twice to note 'myisamchk': myisamchk. In this case, note 'myisamchk': myisamchk. does not abort on some errors (such as duplicate-key errors) but instead tries to resolve them by modifying the '.MYD' 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 of the table before running note 'myisamchk': myisamchk.

 File: manual.info.tmp, Node: myisam-check, Next: myisam-repair, Prev: myisam-crash-recovery, Up: myisam-table-maintenance

7.6.2 How to Check MyISAM Tables for Errors

To check a 'MyISAM' table, use the following commands:

• *note 'myisamchk TBL_NAME': myisamchk.

  This finds 99.99% of all errors. What it cannot find is corruption that involves only the data file (which is very unusual). If you want to check a table, you should normally run *note 'myisamchk': myisamchk. without options or with the '-s' (silent) option.

• *note 'myisamchk -m TBL_NAME': myisamchk.

  This finds 99.999% of all errors. It first checks all index entries for errors and then reads through all rows. It calculates a checksum for all key values in the rows and verifies that the checksum matches the checksum for the keys in the index tree.

• *note 'myisamchk -e TBL_NAME': myisamchk.

  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 for a large table that has many indexes. Normally, note 'myisamchk': myisamchk. stops after the first error it finds. If you want to obtain more information, you can add the '-v' (verbose) option. This causes note 'myisamchk': myisamchk. to keep going, up through a maximum of 20 errors.

• *note 'myisamchk -e -i TBL_NAME': myisamchk.

  This is like the previous command, but the '-i' option tells *note 'myisamchk': myisamchk. to print additional statistical information.

In most cases, a simple *note 'myisamchk': myisamchk. command with no arguments other than the table name is sufficient to check a table.

 File: manual.info.tmp, Node: myisam-repair, Next: myisam-optimization, Prev: myisam-check, Up: myisam-table-maintenance

7.6.3 How to Repair MyISAM Tables

The discussion in this section describes how to use *note 'myisamchk': myisamchk. on 'MyISAM' tables (extensions '.MYI' and '.MYD').

You can also use the note 'CHECK TABLE': check-table. and note 'REPAIR TABLE': repair-table. statements to check and repair 'MyISAM' tables. See note check-table::, and note repair-table::.

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

• 'TBL_NAME.frm' is locked against change

• Can't find file 'TBL_NAME.MYI' (Errcode: NNN)

• Unexpected end of file

• Record file is crashed

• Got error NNN from table handler

To get more information about the error, run note 'perror': perror. NNN, where NNN is the error number. The following example shows how to use note 'perror': perror. to find the meanings for the most common error numbers that indicate a problem with a table:

 $> perror 126 127 132 134 135 136 141 144 145
 MySQL error code 126 = Index file is crashed
 MySQL error code 127 = Record-file is crashed
 MySQL error code 132 = Old database file
 MySQL error code 134 = Record was already deleted (or record file crashed)
 MySQL error code 135 = No more room in record file
 MySQL error code 136 = No more room in index file
 MySQL error code 141 = Duplicate unique key or constraint on write or update
 MySQL error code 144 = Table is crashed and last repair failed
 MySQL error code 145 = Table was marked as crashed and should be repaired

Note that error 135 (no more room in record file) and error 136 (no more room in index file) are not errors that can be fixed by a simple repair. In this case, you must use *note 'ALTER TABLE': alter-table. to increase the 'MAX_ROWS' and 'AVG_ROW_LENGTH' table option values:

 ALTER TABLE TBL_NAME MAX_ROWS=XXX AVG_ROW_LENGTH=YYY;

If you do not know the current table option values, use *note 'SHOW CREATE TABLE': show-create-table.

For the other errors, you must repair your tables. *note 'myisamchk': myisamchk. can usually detect and fix most problems that occur.

The repair process involves up to four stages, described here. Before you begin, you should change location to the database directory and check the permissions of the table files. On Unix, make sure that they are readable by the user that *note 'mysqld': mysqld. runs as (and to you, because 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.

This section is for the cases where a table check fails (such as those described in note myisam-check::), or you want to use the extended features that note 'myisamchk': myisamchk. provides.

The note 'myisamchk': myisamchk. options used for table maintenance with are described in note myisamchk::. note 'myisamchk': myisamchk. also has variables that you can set to control memory allocation that may improve performance. See note myisamchk-memory::.

If you are going to repair a table from the command line, you must first stop the note 'mysqld': mysqld. server. Note that when you do note 'mysqladmin shutdown': mysqladmin. on a remote server, the note 'mysqld': mysqld. server is still available for a while after note 'mysqladmin': mysqladmin. returns, until all statement-processing has stopped and all index changes have been flushed to disk.

Stage 1: Checking your tables

Run note 'myisamchk .MYI': myisamchk. or note 'myisamchk -e .MYI': myisamchk. if you have more time. Use the '-s' (silent) option to suppress unnecessary information.

If the note 'mysqld': mysqld. server is stopped, you should use the '--update-state' option to tell note 'myisamchk': myisamchk. to mark the table as 'checked.'

You have to repair only those tables for which *note 'myisamchk': myisamchk. announces an error. For such tables, proceed to Stage 2.

If you get unexpected errors when checking (such as 'out of memory' errors), or if *note 'myisamchk': myisamchk. crashes, go to Stage 3.

Stage 2: Easy safe repair

First, try *note 'myisamchk -r -q TBL_NAME': myisamchk. ('-r -q' means 'quick recovery mode'). This attempts 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 *note 'myisamchk -r TBL_NAME': myisamchk. ('-r' means 'recovery mode'). This removes incorrect rows and deleted rows from the data file and reconstructs the index file.

3. If the preceding step fails, use *note 'myisamchk --safe-recover TBL_NAME': myisamchk. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode does not (but is slower).

Note:

If you want a repair operation to go much faster, you should set the values of the 'sort_buffer_size' and 'key_buffer_size' variables each to about 25% of your available memory when running *note 'myisamchk': myisamchk.

If you get unexpected errors when repairing (such as 'out of memory' errors), or if *note 'myisamchk': myisamchk. crashes, go to Stage 3.

Stage 3: Difficult repair

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

1. Move the data file to a safe place.

2. Use the table description file to create new (empty) data and index files:

     $> mysql DB_NAME
   
     mysql> SET autocommit=1;
     mysql> TRUNCATE TABLE TBL_NAME;
     mysql> quit

3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)

Important:

If you are using replication, you should stop it prior to performing the above procedure, since it involves file system operations, and these are not logged by MySQL.

Go back to Stage 2. *note 'myisamchk -r -q': myisamchk. should work. (This should not be an endless loop.)

You can also use the 'REPAIR TABLE TBL_NAME USE_FRM' SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use note 'REPAIR TABLE': repair-table. See note repair-table::.

Stage 4: Very difficult repair

You should reach this stage only if the '.frm' description file has also crashed. That should never happen, because the description file is not 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 *note 'myisamchk -r': myisamchk.

2. If you do not 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, and then move the '.frm' description and '.MYI' index files from the other database to your crashed database. This gives you new description and index files, but leaves the '.MYD' data file alone. Go back to Stage 2 and attempt to reconstruct the index file.

 File: manual.info.tmp, Node: myisam-optimization, Next: myisam-maintenance-schedule, Prev: myisam-repair, Up: myisam-table-maintenance

7.6.4 MyISAM Table Optimization

To coalesce fragmented rows and eliminate wasted space that results from deleting or updating rows, run *note 'myisamchk': myisamchk. in recovery mode:

 $> myisamchk -r TBL_NAME

You can optimize a table in the same way by using the note 'OPTIMIZE TABLE': optimize-table. SQL statement. note 'OPTIMIZE TABLE': optimize-table. does a table repair and a key analysis, and also sorts the index tree so that key lookups are faster. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use note 'OPTIMIZE TABLE': optimize-table. See note optimize-table::.

*note 'myisamchk': myisamchk. has a number of other options that you can use to improve the performance of a table:

• '--analyze' or '-a': Perform key distribution analysis. This improves join performance by enabling the join optimizer to better choose the order in which to join the tables and which indexes it should use.

• '--sort-index' or '-S': Sort the index blocks. This optimizes seeks and makes table scans that use indexes faster.

• '--sort-records=INDEX_NUM' or '-R INDEX_NUM': Sort data rows according to a given index. This makes your data much more localized and may speed up range-based *note 'SELECT': select. and 'ORDER BY' operations that use this index.

For a full description of all available options, see *note myisamchk::.

 File: manual.info.tmp, Node: myisam-maintenance-schedule, Prev: myisam-optimization, Up: myisam-table-maintenance

7.6.5 Setting Up a MyISAM Table Maintenance Schedule

It is a good idea to perform table checks on a regular basis rather than waiting for problems to occur. One way to check and repair 'MyISAM' tables is with the note 'CHECK TABLE': check-table. and note 'REPAIR TABLE': repair-table. statements. See *note table-maintenance-statements::.

Another way to check tables is to use note 'myisamchk': myisamchk. For maintenance purposes, you can use note 'myisamchk -s': myisamchk. The '-s' option (short for '--silent') causes *note 'myisamchk': myisamchk. to run in silent mode, printing messages only when errors occur.

It is also a good idea to enable automatic 'MyISAM' table checking. For example, whenever the machine has done a restart in the middle of an update, you usually need to check each table that could have been affected before it is used further. (These are 'expected crashed tables.') To cause the server to check 'MyISAM' tables automatically, start it with the 'myisam_recover_options' system variable set. See *note server-system-variables::.

You should also check your tables regularly during normal system operation. For example, you can run a 'cron' job to check important tables once a week, using a line like this in a 'crontab' file:

 35 0 * * 0 /PATH/TO/MYISAMCHK --fast --silent /PATH/TO/DATADIR/*/*.MYI

This prints out information about crashed tables so that you can examine and repair them as necessary.

To start with, execute *note 'myisamchk -s': myisamchk. each night on all tables that have been updated during the last 24 hours. As you see that problems occur infrequently, you can back off the checking frequency to once a week or so.

Normally, MySQL tables need little maintenance. If you are performing many updates to 'MyISAM' tables with dynamic-sized rows (tables with note 'VARCHAR': char, note 'BLOB': blob, or note 'TEXT': blob. columns) or have tables with many deleted rows you may want to defragment/reclaim space from the tables from time to time. You can do this by using note 'OPTIMIZE TABLE': optimize-table. on the tables in question. Alternatively, if you can stop the *note 'mysqld': mysqld. server for a while, change location into the data directory and use this command while the server is stopped:

 $> myisamchk -r -s --sort-index --myisam_sort_buffer_size=16M */*.MYI

 File: manual.info.tmp, Node: optimization, Next: language-structure, Prev: backup-and-recovery, Up: Top

8 Optimization **************

• Menu:

• optimize-overview:: Optimization Overview
• statement-optimization:: Optimizing SQL Statements
• optimization-indexes:: Optimization and Indexes
• optimizing-database-structure:: Optimizing Database Structure
• optimizing-innodb:: Optimizing for InnoDB Tables
• optimizing-myisam:: Optimizing for MyISAM Tables
• optimizing-memory-tables:: Optimizing for MEMORY Tables
• execution-plan-information:: Understanding the Query Execution Plan
• controlling-optimizer:: Controlling the Query Optimizer
• buffering-caching:: Buffering and Caching
• locking-issues:: Optimizing Locking Operations
• optimizing-server:: Optimizing the MySQL Server
• optimize-benchmarking:: Measuring Performance (Benchmarking)
• thread-information:: Examining Server Thread (Process) Information

• optimizer-tracing:: Tracing the Optimizer

This chapter explains how to optimize MySQL performance and provides examples. Optimization involves configuring, tuning, and measuring performance, at several levels. Depending on your job role (developer, DBA, or a combination of both), you might optimize at the level of individual SQL statements, entire applications, a single database server, or multiple networked database servers. Sometimes you can be proactive and plan in advance for performance, while other times you might troubleshoot a configuration or code issue after a problem occurs. Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.

 File: manual.info.tmp, Node: optimize-overview, Next: statement-optimization, Prev: optimization, Up: optimization