UNIX Configuration Issues

In OLTP applications with large numbers of users, UNIX configuration decisions and tuning at an operating system level are more important than they are in smaller systems. In this section, we discuss some of those issues and highlight important considerations for OLTP systems.

Raw Volumes Versus File System

A frequent debate among database designers and administrators is whether data files should reside on raw volumes or file systems. The debate is complicated by the fact that there are so many different UNIX platforms with so many different volume managers, and technological advances are continuously being made on all of them. As with any decision, there are advantages and disadvantages to both raw volumes and file systems.

The most frequently cited advantage to using file systems is ease of administration. It is far easier to back up, recover, and manipulate file system file scripts than to manipulate raw devices. Furthermore, in most shops, no involvement from UNIX system administrators is required to create data files when file systems are being used. Most shops do not permit DBAs to create and administrate raw volumes, so space management requires coordination between people and groups that often leads to bureaucracy and red tape.

Over the past few years, improvements have been made in file systems that provide buffering and caching at an operating system level that is not available on raw devices. On most systems, the performance difference between having data files on raw volumes and on file systems is imperceptible. However, even with buffering improvements, raw devices are still more efficient than file systems. In OLTP applications where server performance easily can be a bottleneck, I recommend that all data files be defined as raw devices. Although I usually use file systems in most kinds of systems, I insist on raw devices for large OLTP and VLDB databases.

UNIX Utilities for Performance Tuning

UNIX performance-tuning utilities differ widely per platform. However, quite a lot of information can be obtained from the iostat and vmstat utilities that are available on all UNIX platforms I’m acquainted with. In this section we will discuss these utilities, things to watch for with large OLTP applications, and how to alleviate resource bottlenecks.

Performance tuning at the UNIX and database levels basically consists of identifying bottlenecks and removing them to increase system throughput. With iostat and vmstat, we can detect resource bottlenecks at a UNIX level. Some administrators prefer using sar instead of vmstat. As a consultant who works in many different shops, I prefer vmstat because many shops don’t provide DBAs the necessary privileges to run sar.

iostat

The iostat utility provides information about disk utilization. It can be used to detect I/O bottlenecks or hot disks. Hot disks are those that have no surplus capacity, which often limits system throughput (the number of transactions processed per hour) because transactions cannot obtain data on the hot disk as fast as requested.

Once hot disks are identified, there are primarily two ways to correct the problem. One way is to move some of the data files on the hot disks to separate disks. In other words, we spread the work around to the point where no disk is considered hot. The other way is to tune SQL within the application to eliminate or reduce some of the application I/O (see Chapters 24 and 25, “Oracle8 Application Tuning,” on how to tune SQL).

Although iostat is universally available on different UNIX platforms, the output and options differ per platform. We will talk about chief differences between the top three major platforms: AIX, Solaris, and HP-UX. (Before I’m lynched in some circles, these “top” platforms are determined strictly by market share. This does not imply any judgments about the technical capabilities of any UNIX operating system or platform.)

isotat can be executed from the command line as follows:

The first observation from iostat should be ignored. AIX and Solaris have similar output for iostat. Statistics are given for each physical device. (Note: hardware RAID devices often display as one physical device.) The most important column is percent busy (%b). Disks over 85% busy are hot disks. The optimal state is to have a roughly even distribution for I/O workload among all disks.

Output 31.1 shows the Solaris iostat output produced by using iostat –xtc 5 5.

Output 31.1. Solaris iostat output.

extended disk statistics      tty        cpu

disk      r/s  w/s  Kr/s  Kw/s wait actv  svc_t %w %b tin tout us sy wt id

sd15      0.1  0.7  0.8   5.4  0.0  0.0   64.9   0  1   0   13 13  2  6 79

sd16      0.7  1.6 12.7  17.8  0.0  0.0   13.6   0  2

sd17      0.7  1.6 12.7  18.4  0.0  0.0   13.9   0  2

sd18      0.1  0.7  0.7   5.5  0.0  0.0   61.1   0  1

sd24      2.5  1.0 40.4   7.4  0.0  0.0    8.7   0  2

sd25      2.1  0.9 35.5   7.2  0.0  0.0    9.1   0  2

sd26      2.5  1.0 40.5   7.4  0.0  0.0    8.5   0  2

sd27      2.5  1.0 40.5   7.4  0.0  0.0    8.2   0  2

sd28      2.1  0.9 35.6   7.3  0.0  0.0    8.8   0  2

sd29      2.5  0.9 40.6   7.4  0.0  0.0    8.2   0  2

Unfortunately, iostat output for HP-UX is considerably less useful. Although you get raw data about the amount of data read or written along with the number of operations, no information about disk capacity is presented. Hence, there is no way to deduce the percentage busy and no way to determine which disks are hot disks. HP provides a motif utility called glance that provides some of this information.

Output 31.2 shows the HP-UX iostat output produced with iostat 5 5.

Output 31.2. HP-UX iostat output.

device    bps     sps    msps

  c0t6d0    0     0.0     1.0

  c0t4d0    0     0.0     1.0

  c0t5d0    0     0.0     1.0

bps == Kilobytes transferred per second

sps == Number of seeks per second

msps == Milliseconds per average seek