MYSQL MYISAM_RECOVER OPTIONS

MYSQL MYISAM_RECOVER OPTIONS

This variable sets the mode for automatic recovery when a MyISAM table crashed. The option value is any combination of the following values:

•   DEFAULT --Recovery without backup, forcing, or quick checking. It is the same as not giving any option.
•  BACKUP --If the data file was changed during recovery, save a backup of the tbl_name.MYD file as tbl_name-datetime.BAK.
•  FORCE --Run recovery even if we would lose more than one row from the .MYD file.
•  QUICK --Don't check the rows in the table if there aren't any delete blocks.

 Multiple values can be set at the same tiem. To use multiple values, separate them by commas.
   
    myisam_recover=backup,force

To disable this option, set it to “”.

    myisam-recover=””
  
If one or more myisam-recover option is used, each time mysqld opens a MyISAM table, it checks if or not the table is marked as crashed or wasn’t closed properly. If it is the case, mysqld runs a check on the table and attempts to repair it if the table was corrupted. Following are the steps which mysqld will perform according to the MySQL Manual:

•  The server checks the table for errors.
•  If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).
•  If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.
•  If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

 If the recovery wouldn't be able to recover all rows from previously completed statements and you didn't specify FORCE in the value of the --myisam-recover option, automatic repair aborts with an error message in the error log:

       Error: Couldn't repair table: test.g00pages

If FORCE was specified, a warning like this is written instead:

      Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if the automatic recovery value includes BACKUP, the recovery process creates files with names of the form tbl_name-datetime.BAK. You should have a cron script that automatically moves these files from the database directories to backup media.

Myisam-recover is not a dynamic variable; hence, it requires a server restart to reset the variable.

References:

• MySQL Manual: Server Command Options
• Understanding MySQL Internals: Chapter 5: Configuration Variables
• High Performance MySQL: Chapter 6: Optimizing Server Settings
• Other Reference On the Web

 Related Posts:

• MySQL Delay Key Write Option

MYSQL MYISAM_MAX_SORT_FILE_SIZE Variable

MYSQL MYISAM_MAX_SORT_FILE_SIZE Variable

MYISAM_MAX_SORT_FILE_SIZE sets the maximum size of the temporary file that MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. The value is given in bytes and the default value is 2GB.

Set this values a bit larger thatn the largest index file if there is disk space is available to ensure that REPAIR TABLE is be done by sort rather than repair by key cache. Sorting is much faster than repair by key cache.

References:

• MySQL Manual: MYISAM_MAX_SORT_FILE_SIZE Variable
• Other References On The Web

 Related Posts:

• MySQL MYISAM_SORT_BUFFER_SIZE Variable
• MySQL KEY_BUFFER_SIZE Variable

MYSQL BULK_INSERT_BUFFER_SIZE Variable

MYSQL BULK_INSERT_BUFFER_SIZE SYSTEM VARIABLE

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. The BULK_INSERT_BUFFER_SIZE variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB. The maximum value is 4GB. The bulk_insert_buffer_size can be set both on system and session levels.

If data is being added to a non-empty table, tuning the bulk_insert_buffer_size variable can make data insertion faster. Normaily, it shows the improvement when the data to be inserted is more than 10k rows. But it is hard to say what is the right value, so, trail and try with incremental buffer size values.

Also, performance improvement will be more obvisoue if the following variables are also set:

• MYISAM_SORT_BUFFER_SIZE
• KEY_BUFFER_SIZE

 In addition, when doing bulk inset, consider disable the indexes before loading the data using the following alter table command:

    alter table t disable keys;

References:

• MySQL Manual: Bulk_Insert_buffer_Size variable.
• Other References On The Web

 Related Posts:

• MySQL MYISAM_SORT_BUFFER_SIZE Variable
• MySQL KEY_BUFFER_SIZE variable
• MySQL Alter Table t Disable Keys Statement
• MySQL Optimize Table Command