0549-0550

Previous Table of Contents Next

Page 549

3. Implement changes.
4. Monitor the database.

As with applications tuning, the more proactively the process is done, the more effective it is. The process is seldom effective when it is done on-the-fly or without the proper amount of research.

Operating System Tuning

Tuning at the operating system level is beyond the scope of this chapter. This task falls to the system administrator ”only in rare cases to the DBA. However, it is often the role of the DBA to offer suggestions. Some issues to understand and consider are

• At the operating system level, paging and swapping are used to efficiently allow numerous processes to share system memory and resources. Paging involves moving the pages (virtual memory areas of program code and/or data) of one or more programs out of main memory to a special disk area. Swapping is where the memory manager "swaps" entire programs out of main memory to disk. The DBA should expect to see both paging and swapping occur on his/her system.
• Database and systems administrators should become concerned if their system begins to experience excessive paging and swapping. This problem is known as thrashing. Thrashing is where the system becomes sluggish because the operating systems memory management daemons are spending too much time moving process pages in and out of main memory instead of doing any productive work. This can seriously degrade system performance. The administrator can easily determine if his or her system is thrashing by monitoring system activity with the sar(1) command. (See a UNIX systems manual.) You can eliminate thrashing by adding more memory to your system.
• Paging and swapping allow the system to perform more work than it would otherwise be capable of doing. When you efficiently manage numerous programs' access to memory resources (as well as other system resources), users are given the illusion that their jobs are the only ones running on the machine. The DBA and the system administrator should work together to optimize memory to reduce or eliminate paging and swapping.
• In recent years , the rapidly declining price-per-megabyte of disk space has not been matched in magnitude by improved performance or reliability. This has resulted in a situations in which disk space is relatively inexpensive, but not more reliable. The lower cost of disk space has predictably led to more disks being installed in machines, but at the cost of a greater chance of hardware failures. Disk access speed and transfer rates have also not grown dramatically. Processor speeds have greatly increased, but this only pushes the bottleneck to disk access by decreasing the Mean Time Between Failures (MTBF). The larger amount of disk space and faster processor speeds have left a situation in which performance and reliability are more important than ever.

Page 550

Redundant Array of Inexpensive/Independent Disks (RAID) seeks to answer the questions of disk performance and data availability. By spreading data over an array of inexpensive and redundant disks, read/write performance and data availability are improved. The read/write performance is improved by having multiple read/write heads operating at the same time across multiple disks. This increases throughput. Data availability is improved by having data stored redundantly. If a single disk failure occurs, the data does not become unavailable because it is either stored redundantly or can be re-created. Note that disk reliability is not affected per se by RAID. The disks will still fail as regularly as usual, but the data will still be available for use. These are the main ideas behind RAID. Currently, RAID has evolved through a minimum of 10 different levels (RAID-0 to RAID-S) utilizing combinations of disk mirroring, striping, and parity disks:

Disk mirroring, sometimes called shadowing, is the RAID practice of keeping identical copies of data on two separate disks of an array. This one-to-one redundancy provides very good data availability. If one disk fails, a complete copy of all the information is still available for immediate use. When one mirrored disk fails, the other is utilized normally as it would be in a non-RAID environment, but in a reduced mode. This reduced mode occurs when the working disk's data is copied to a backup disk in either a hot-sharing mode or in a manual-hot, swappable mode. This resync (resynchronization) operation will occur at a reduced speed, but the data will still be available. Data can be lost if the remaining disk fails during the resync period, but that is unlikely . If a second failure does immediately occur, the source of the problem is likely not the disk itself, but some other area in the system (such as a disk controller). The greatest benefit of disk mirroring is data availability. Negatives are the increased costs incurred by utilizing redundant disks and the time needed to read/write to two disks as opposed to only one disk.

Striping is the RAID practice of spreading out data over multiple disks. Rather than focusing on just one disk, all the disks in the array can be utilized. This greatly improves performance because all the disks can be accessed at maximum speed. Unlike redundantly reading/writing completely to two disks in the disk mirroring scheme, data is only read/written in portions to separate disks. The greatest drawback is the data interdependence between the disks in the array. If any one disk fails, the data becomes unavailable because it is spread out across multiple disks and is not redundant. Striping without any form of redundancy or the ability to re-create data is not commonly used.

Parity, in terms of RAID, is the practice of striping the data over multiple disks, but meanwhile also keeping either a separate disk or portions of all disks with parity information needed to re-create data when a failure occurs. This provides reliable data availability in times of a disk failure, but not at the high costs incurred in full disk mirroring. If one of the disks fails, data is re-created on a

Previous Table of Contents Next