Appendix D

Quick system tuning reference

Diagnosing performance problems

Insufficient CPU power at high load	Possible solutions
[mp]sar -q shows runq-sz > 2 [mp]sar -u shows %idle < 20% on multiuser system [mp]sar -u shows %idle < 5% on dedicated database server Additionally for SMP: mpsar -q shows %runocc > 90% cpusar -u shows %idle < 20% on any CPU of multiuser system cpusar -u shows %idle < 5% on any CPU of dedicated database server	Measures that can be taken include: check that the system is not swapping or paging out excessively reschedule jobs to run at other times tune applications to use less CPU power replace applications with ones needing less CPU power replace non-intelligent serial cards with intelligent ones upgrade the system to use faster CPU(s) upgrade to a multiprocessor system add more CPUs to a multiprocessor system purchase an additional system to share the load

Excessive paging out or swapping	Possible solutions
[mp]sar -p shows rclm/s >> 0 [mp]sar -q shows %swpocc > 20% [mp]sar -w shows swpot/s > 1 swap -l shows free < 50% of blocks	Increase free memory until swapping does not occur by: reducing number of buffers (watch out for reduced cache hit rates) running fewer large applications locally moving users to another machine adding RAM

Poor disk performance	Possible solutions
[mp]sar -u shows %wio > 15% [mp]sar -d shows avque >> 1 and %busy > 80%	Increase disk performance by: using HTFS filesystem(s) using striping across several disks to balance load keeping filesystems < 90% full reorganizing directories keeping directories small distributing different types of activity to different disks adding more disks using faster disks, controllers, and host adapters improving buffer cache performance improving namei cache performance reducing filesystem fragmentation

Poor buffer cache performance	Possible solutions
[mp]sar -b shows %rcache < 90% and %wcache < 65%	Improve buffer cache performance by: increasing number of buffers increasing number of buffer hash queues per buffer

Poor namei cache performance	Possible solutions
[mp]sar -n shows hit % < 65%	Increase namei cache hit rate by: increase value of CASHEENTS make each pathname component less than or equal to 14 characters

Fragmented filesystem	Possible solutions
df -v shows blocks %used > 90%	Reduce the number of disk blocks used by: using DTFS filesystem(s) removing unwanted files regularly archiving and removing, or compressing infrequently used files mounting commonly used resources across the network using NFS adding disk(s)

	Reduce fragmentation by: archiving and removing the files, and rebuilding the filesystem

Kernel tables too small	Possible solutions
error messages displayed on console [mp]sar -v shows ov > 0 (overflows)	Allow table sizes to grow dynamically; for example, set MAX_PROC to 0 for the process table

The desirable attributes of systems with many logged-in users and database server systems differ in some respects. Use the following tables to check that you have not overlooked anything:

• ``Attributes of a well-tuned multiuser system''

• ``Attributes of a well-tuned dedicated database server system''

To record system activity to a file for later analysis, use the -o option of sar(ADM) on a single processor system, and of mpsar(ADM) on a multiprocessor system. Take the measurements over a period of at least an hour with a sampling interval sufficiently small to capture the level of detail which you are interested in. Record the system's activity at varying levels of loading so that you can identify when bottlenecks are appearing.

Attributes of a well-tuned multiuser system

CPU performance	Explanation
[cpu]sar -u shows %idle > 20%	Some idle time on each CPU at high load
[mp]sar -q shows runq-sz < 2	Few processes waiting to run
mpsar -q shows %runocc < 90% (SMP only)	Run queue is not continually occupied

See ``Tuning CPU resources''.

Memory performance	Explanation
[mp]sar -p shows rclm/s 0	Little or no swapping or paging out activity
[mp]sar -w shows swpot/s 0	Little or no activity on the swap device(s)
[mp]sar -q shows swpq-sz 0 and %swpocc 0%	No swapped-out runnable processes
[mp]sar -r shows freemem >> GPGSHI and freeswp constant	Ample free memory and swap space

See ``Tuning memory resources''.

Disk I/O performance	Explanation
[cpu]sar -u shows %wio < 15%	Little time spent waiting for I/O to complete
[mp]sar -b shows %rcache > 90% and %wcache > 65%	Good hit rate for reading and writing to the buffer cache
[mp]sar -d shows avque 1	Low average number of disk requests queued
[mp]sar -n shows hit % > 65%	Good hit rate for namei cache

See ``Tuning I/O resources''.

Attributes of a well-tuned dedicated database server system

CPU performance	Explanation
[cpu]sar -u shows %idle > 5%	Some idle time on each CPU at high load
[mp]sar -q shows runq-sz < 2	Few processes waiting to run
mpsar -q shows %runocc < 90% (SMP only)	Run queue is not continually occupied

See your database documentation and ``Tuning CPU resources''.

Memory performance	Explanation
[mp]sar -p shows rclm/s 0	Little or no swapping or paging out activity
[mp]sar -w shows swpot/s 0	Little or no activity on the swap device(s)
[mp]sar -q shows swpq-sz 0 and %swpocc 0%	No swapped-out runnable processes
[mp]sar -r shows freemem GPGSHI and freeswp constant	Little excess free memory; allow the database to use any excess memory by increasing its internal work area.

See your database documentation and ``Tuning memory resources''.

Disk I/O performance	Explanation
[cpu]sar -u shows %wio < 15%	Little time spent waiting for I/O to complete
[mp]sar -d shows avque 1	Low average number of disk requests queued

See your database documentation and ``Tuning I/O resources''.