MyISAM Suggestions

For administrators supporting MyISAM-based tables, a few simple, yet effective, disk performance-improvement strategies are worth exploring.

Symbolic Links

Spreading the disk-processing burden among multiple drives helps diminish the performance expense of relying on a single disk drive for all the work. Because MyISAM databases consist of individual file system based objects, one way to achieve this distribution is to use symbolic links.

Symbolic links can apply to the entire database, in which case the database appears to be on one drive when it is, in fact, on another disk. For administrators on Linux or Unix, symbolic linking granularity can also be set lower, affecting individual tables and indexes.

Prior to version 4.0, symbolic links carried an increased risk of undesirable events if you inadvertently ran certain database maintenance operations, such as OPTIMIZE TABLE or ALTER TABLE. These risks are somewhat mitigated in newer versions, but symbolic links do require some thought when it comes to database management, particularly for backup and restore. You can even elect to place data and index files on the same drive, or separate them onto different hardware. In the absence of RAID, this latter strategy achieves maximum diffusion of information and is likely to be a very simple, yet responsive solution.

Table Compression

Although it's not a viable solution for all database applications, consider using the myisampack utility to compress tables containing read-only or infrequently updated information.

The performance benefits from this tactic typically accrue from the reduced amount of disk operations necessary to locate and retrieve a particular set of information, even taking the extra CPU costs of decompressing the data into account.