MYSQL MyISAM I/O Tuning

Let’s begin by considering how MyISAM performs I/O for its indexes. MyISAM normally flushes index changes to disk after every write. If you’re going to make many modifications to a table, however, it may be faster to batch these writes together.

One way to do this is with LOCK TABLES, which defers writes until you unlock the tables. This can be a valuable technique for improving performance, as it lets you control exactly which writes are deferred and when the writes are flushed to disk.

You can defer writes for precisely the statements you want. You can also defer index writes by using the delay_key_write variable. If you do this, modified key buffer blocks are not flushed until the table is closed.* The possible settings are as follows:

OFF
MyISAM flushes modified blocks in the key buffer (key cache) to disk after every write, unless the table is locked with LOCK TABLES.

ON
Delayed key writes are enabled, but only for tables created with the DELAY_KEY_
WRITE option.

ALL
All MyISAM tables use delayed key writes.

Delaying key writes can be helpful in some cases, but it doesn’t usually create a big performance boost. It’s most useful with smaller data sizes, when the key cache’s read hit ratio is good but the write hit ratio is bad. It also has quite a few drawbacks:

• If the server crashes and the blocks haven’t been flushed to disk, the index will be corrupt.

• If many writes are delayed, it’ll take longer for MySQL to close a table, because it will have to wait for the buffers to be flushed to disk. This can cause long table cache locks in MySQL 5.0.

• FLUSH TABLES can take a long time, for the reason just mentioned. This in turn can increase the time it takes to run FLUSH TABLES WITH READ LOCK for an LVM snapshot or other backup operation.

• Unflushed dirty blocks in the key buffer might not leave any room in the buffer for new blocks to be read from disk. Therefore, queries might stall while waiting for MyISAM to free up some space in the key buffer.

In addition to tuning MyISAM’s index I/O, you can configure how MyISAM tries to recover from corruption. The myisam_recover option controls how MyISAM looks for and repairs errors. You have to set this option in the configuration file or at the command line. You can view, but not change, the option’s value with this SQL statement (this is not a typo—the system variable has a different name from the corresponding command-line option):

mysql> SHOW VARIABLES LIKE 'myisam_recover_options';

Enabling this option instructs MySQL to check MyISAM tables for corruption when it opens them, and to repair them if problems are found. You can set the following values:

DEFAULT (or no setting)
MySQL will try to repair any table that is marked as having crashed or not marked as having been closed cleanly. The default setting performs no other actions upon recovery. In contrast to how most variables work, this DEFAULT value is not an instruction to reset the variable to its compiled-in value; it essentially means “no setting.”

BACKUP
Makes MySQL write a backup of the data file into a .BAK file, which you can examine afterward.

FORCE
Makes recovery continue even if more than one row will be lost from the .MYD file.

QUICK
Skips recovery unless there are delete blocks. These are blocks of deleted rows
are still occupying space and can be reused for future INSERT statements. This can be useful because MyISAM recovery can take a very long time on large tables.

You can use multiple settings, separated by commas. For example, BACKUP,FORCE will force recovery and create a backup.

We recommend that you enable this option, especially if you have just a few small
MyISAM tables. Running a server with corrupted MyISAM tables is dangerous, as they can sometimes cause more data corruption and even server crashes. However, if you have large tables, automatic recovery might be impractical: it causes the server to check and repair all MyISAM tables when they’re opened, which is inefficient. During this time, MySQL tends to block connections from performing any work. If you have a lot of MyISAM tables, it might be a good idea to use a less intrusive process that runs CHECK TABLES and REPAIR TABLES after startup. Either way, it is very important to check and repair the tables.

Enabling memory-mapped access to data files is another useful MyISAM tuning option. Memory mapping lets MyISAM access the .MYD files directly via the operating system’s page cache, avoiding costly system calls. In MySQL 5.1 and newer, you can enable memory mapping with the myisam_use_mmap option. Older versions of MySQL use memory mapping for compressed MyISAM tables only.

Source of Information : OReIlly High Performance MySQL Second Edition