General Suggestions

The following suggestions are grouped together because of their cross-platform, cross database engine validity.

Deploying the Fastest Possible Disk Drives

Although the cost per gigabyte for disk drives continues to plunge, there are still price premiums for faster drives in any storage capacity compared to their more lethargic cousins. As you plan out your hardware purchase or upgrade, carefully consider making the investment in drives with lower seek times than average. Although you pay more initially, the return on investment is quite dramatic; a slower disk drive continually hurts performance.

Configuring Memory Caches Correctly

It seems self-evident, but the best way to reduce the cost of disk access is to simply reduce or eliminate as much disk access as possible. MySQL's specialized memory caches do an excellent job of letting the database server spend more time working within memory-based structures rather than having to continually interact with the disk drive.

Any MySQL administrator who is serious about improving disk performance (or any other type of performance, for that matter) should take the time to learn more about MySQL's memory caches, including their configuration, monitoring, and management. These topics are all covered in detail in Chapters 10, 11, and 12 ("General Server Performance and Parameters Tuning," "MyISAM Performance Enhancement," and "InnoDB Performance Enhancement," respectively).

Implementing a RAID Strategy

A redundant array of independent (or inexpensive, depending on your budget) disks (RAID) lets administrators spread the processing load among multiple disk drives, while also helping to reduce the risk of catastrophic data loss should something happen to a particular disk drive.

It's best to think of RAID as a collection of different disk usage strategies, rather than a single, monolithic technology. Multiple classes, or levels, of RAID usage are available.

It stands to reason that because disk access is usually the most expensive and time- consuming portion of a database-driven application, anything you can do to reduce or optimize these operations translates into dramatic benefits. By spreading the load among multiple drives, RAID technology helps to diminish the inevitable blockages caused by disk operations.

RAID is actually the tip of the iceberg when it comes to modern, sophisticated fault tolerance and disk performance enhancement technologies. Numerous additional options are at your disposal, including the following:

• Storage area networks (SAN)/Network area storage (NAS)

• Journaling file systems

• Logical volume management

These technologies typically coexist with RAID, although they might not be aware of each other. Rather than embarking on an extensive storage review, this chapter stays focused on RAID.

As an administrator, you have a number of decisions to make when implementing a RAID strategy. The following sections look at the various RAID levels, as well as situations in which they are most appropriate. A little later, this chapter covers methods of spreading the load among multiple disk drives without employing a RAID strategy.

RAID 0

Also known as disk striping, this RAID level simply divides data among two or more disk drives that appear as a single unit to the application. The primary benefit of RAID level 0 is the load balancing brought about by spreading the work among multiple drives. However, serious data loss is likely if even one of the level 0 disk drives is damaged.

If you already have an effective replication and backup strategy in place, RAID 0 might suffice for a significant portion of your data. This level ensures faster performance, and your replication/backup policies mean that you will have a way to restore information should you experience a severe disk failure.

In summary, RAID 0 provides an easy-to-implement load balancing strategy that is unfortunately hampered by its lack of true fault tolerance.

RAID 1

Commonly referred to as mirroring or duplexing, RAID level 1 sees all disk writes to a particular drive copied to all other appropriate drives in the RAID set. For example, if there are four disks (identified as A through D), when information is written to disk A, RAID level 1 means that drives B, C, and D also receive mirrored copies of this data.

These extra writes add some performance overhead, but this is more than offset by RAID level 1's enhanced data security. In addition, read operations (which make up the bulk of most database applications) can be split among these drives, also yielding increased speed. Some installations feature a dedicated channel (known as "disk duplexing") among the drives in the RAID set, which offers even better performance and security capabilities.

In summary, RAID 1 is the simplest RAID storage subsystem architecture; it also incurs the highest disk overhead of all RAID configurations.

This RAID level is a good choice for data that would be lost forever in the event of a disk failure. However, the next RAID level represents the best choice.

RAID 0+1

As its name implies, this RAID level takes the performance benefits of RAID 0 and combines them with the data security features of RAID 1. The result is a fast and secure solution, especially tailored for those environments that see large, sequential database loads.

This speed and data safekeeping comes at a price: the financial outlay to buy the extra disks necessary to keep copies of the data. However, the cost per gigabyte of disk drives continues to plummet, so the financial impact of this approach is ever-decreasing.

Several other combinations and permutations of RAID levels are availble, but RAID 0, 1, and 0+1 are the most relevant for this book's purposes.

Implementing RAID

After deciding to deploy a RAID implementation, your next step is to establish the exact strategy that you will follow. Again, administrators must choose among several options:

• Hardware-based RAID Numerous RAID-aware disk controllers are on the market today. These controllers are usually more expensive than standard devices, and are typically closed solutions, meaning that they aren't easily portable among different systems. However, they do provide increased speed and simplicity.

• Operating system software-based RAID Choosing this kind of RAID strategy entails using either built-in or add-on software that is tied very closely to your particular operating system. This is usually less expensive than the cost of acquiring specialized controllers and fast disk drives. In fact, it's often possible to let older, less-expensive disks participate in this type of configuration. However, these price and flexibility benefits can translate into some additional configuration work for the administrator.

• MySQL software-based RAID If your build of MySQL supports it, you can instruct MySQL to divvy a MyISAM table's data among multiple disk files by supplying values for the RAID_TYPE portion of the CREATE TABLE statement. This effectively implements a RAID strategy unbeknownst to the operating system or hardware, and is primarily useful when attempting to surmount disk file size limitations that can affect your environment. Realistically, if you want RAID, you should choose between the hardware and operating system based approaches described previously.

Distributing Temporary Directories

You just learned how executing a well-planned RAID strategy can distribute the disk process load among multiple storage devices. As you saw, however, setting up a RAID configuration requires some planning and careful action to ensure success. Luckily, when it comes to temporary files, administrators have a fast, easy way to distribute these files among multiple drives, regardless of whether RAID is in place.

Commencing with MySQL 4.1, the tmpdir server variable lets you instruct MySQL to use, in a round-robin fashion, a collection of devices for its temporary file needs. Before embarking on this journey, however, make sure that the drives cited when you configure this variable are indeed on separate disks. Otherwise, this alteration will have minimal performance benefits because you really haven't distributed the load.