| Previous | Table of Contents | Next |
vmstat
The vmstat utility provides information about CPU and memory utilization. By using vmstat, we can determine if there are CPU bottlenecks or memory allocation problems. CPU bottlenecks, like disk bottlenecks, are important because they limit transaction throughput. Memory allocation problems are important because if memory is overallocated, the server dedicates CPU and disk resources to swapping memory. In other words, memory problems will contribute to CPU and disk utilization problems.
Tip:
Solve memory allocation problems before addressing disk and CPU bottlenecks.
CPU bottlenecks can be alleviated by tuning the application and reducing its CPU requirements, or by adding more CPU chips to the server (if possible). Tuning application SQL can help reduce an application’s CPU requirements. Even though databases are thought to have more to do with I/O than CPU utilization, SQL instructions applications issue spend CPU cycles as well as I/O cycles. Memory allocation problems are alleviated by reducing memory allocated to the database or adding memory to the server. Memory allocation problems can cause problems with disk or CPU utilization, so I normally fix them before other types of problems.
| Format: | vmstat [interval] [count] |
| Examples: | vmstat 5 5 |
| vmstat 5 | |
| vmstat | |
| Notes: | Always ignore first observation. |
Fortunately, vmstat output for AIX, HP-UX, and Solaris is similar. I’ve presented output from vmstat on a Solaris system in Output 31.3.
Output 31.3. Solaris vmstat output.
procs memory page disk faults cpu r b w swap free re mf pi po fr de sr s1 s1 s1 s1 in sy cs us sy id 0 0 0 720 344 5 6 33 62 71 0 2 1 2 2 1 236 291 225 13 2 85 0 0 0 991976 8048 11 0 27 88 124 0 7 1 3 2 1 308 2416 434 49 5 46 0 0 0 991976 8056 17 0 188 176 339 0 35 0 7 5 0 315 2492 456 47 4 50 0 0 0 991976 8104 10 0 48 99 99 0 0 0 4 3 0 254 1874 323 47 4 49 0 0 0 991976 8032 9 0 958 32 438 0 101 0 17 17 0 347 2787 395 57 6 37 0 0 0 991976 8016 7 0 1771 99 801 0 218 4 32 32 4 474 2741 448 55 9 36
The columns I pay attention to the most are the following:
| Section | Column | Description |
|---|---|---|
| procs | r | Number of processes being run during the interval |
| b | Number of processes waiting for I/O | |
| w | Number of processes that have been swapped out | |
| page | pi | Number of memory pages paged in during the interval |
| po | Number of memory pages paged out during the interval | |
| cpu | us | Percent of time CPU is used for user processes |
| sy | Percent of time CPU is used for system processes | |
| id | Percent of time CPU is idle | |
If paging (both pi and po) is greater than zero for most intervals, you have overallocated memory. In Output 31.3, notice that because pi and po are always greater than zero, the server in the example has memory allocation problems. If there is a severe shortage of memory, you will see processes being swapped out (w column greater than zero).
If the id column is zero for most intervals, chances are that you have a CPU bottleneck. Often, if the CPU bottleneck is severe, the Run queue (column r) will be high as well.
A significant number of processes waiting for I/O can indicate a disk utilization problem. The iostat utility should be used to confirm that one or more hot disks exist and to determine which disks are at issue.
Data File/Volume Size
Many UNIX systems will serialize writes to individual files or raw volumes to ensure data consistency. In other words, if 100 users issue update, insert, or delete statements that affect the same data file at roughly the same time, the underlying operating system will serialize those writes and perform only one write at a time. This means that if both the data files and the write transaction volume are large, we may have a locking bottleneck on these data files at an operating system level.
If we make all data files no larger than 400MB to 500MB in size, we reduce the possibility of write bottlenecks at an operating system level. This recommendation holds regardless if data files are created as raw volumes or within file systems.
Tip:
Performance for OLTP databases can be increased by keeping all data files under 500MB in size.
Use of RAID and Mirroring
Data redundancy of any type will necessitate unwanted workload on the server. How-ever, with most OLTP applications, some form of data redundancy is required to ensure high availability and point-in-time requirements. As you’re no doubt aware, there are many forms of disk redundancy (RAID-1 (mirroring), RAID-5 (parity), RAID-6 (double parity), and others). Most types of disk redundancy are available via a logical volume manager, such as Veritas, or at a hardware level in some type of disk subsystem. Chapter 24 has a more detailed description of RAID options.
Hardware RAID has superior performance to software RAID. In OLTP databases with high transaction volume, consider using hardware RAID if possible. Benchmarks I’ve conducted in the past suggest that the performance of read operations is identical between mirroring and RAID-5. These benchmarks also revealed a 10% performance degradation for write operations on RAID-5 volumes when compared to mirrored volumes. Because of these benchmarks, I tend to recommend mirroring as opposed to RAID-5.
| Previous | Table of Contents | Next |