Table D-1 Diagnosing performance problems
--------------------------------------------------------------------------------
Insufficient CPU power at high load Possible solutions
--------------------------------------------------------------------------------
[mp]sar -q shows runq-sz > 2 Measures that can be taken include:
[mp]sar -u shows %idle < 20% on
multiuser system + check that the system is not swapping
[mp]sar -u shows %idle < 5% on or paging out excessively
dedicated database server
Additionally for SMP: + reschedule jobs to run at other times
mpsar -q shows %runocc > 90%
cpusar -u shows %idle < 20% on any + tune applications to use less CPU power
CPU of multiuser system
cpusar -u shows %idle < 5% on any + replace applications with ones needing
CPU of dedicated database server 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 Increase free memory until swapping does
[mp]sar -q shows %swpocc > 20% not occur by:
[mp]sar -w shows swpot/s > 1
swap -l shows free < 50% of blocks + 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% Increase disk performance by:
[mp]sar -d shows avque >> 1 and
%busy > 80% + 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 Improve buffer cache performance by:
%wcache < 65%
+ 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 Allow table sizes to grow dynamically; for console example, set MAX_PROC to 0 for the process [mp]sar -v shows ov > 0 table (overflows)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:
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.
Table D-2 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 Run queue is not continually occupied only)See Chapter 3, ``Tuning CPU resources''.
------------------------------------------------------------------------------- Memory performance Explanation ------------------------------------------------------------------------------- [mp]sar -p shows rclm/sSee Chapter 4, ``Tuning memory resources''.≈ 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 No swapped-out runnable processes %swpocc≈ 0% [mp]sar -r shows freemem >> GPGSHI Ample free memory and swap space and freeswp≈ constant
-------------------------------------------------------------------------------
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 Good hit rate for reading and writing to
%wcache > 65% 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
Chapter 5, ``Tuning I/O resources''.
Table D-3 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 Run queue is not continually occupied only)See your database documentation and Chapter 3, ``Tuning CPU resources''.
------------------------------------------------------------------------------- Memory performance Explanation ------------------------------------------------------------------------------- [mp]sar -p shows rclm/sSee your database documentation and Chapter 4, ``Tuning memory resources''.≈ 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 No swapped-out runnable processes %swpocc≈ 0% [mp]sar -r shows freemem≈ GPGSHI Little excess free memory; allow the and freeswp≈ constant database to use any excess memory by increasing its internal work area.
-------------------------------------------------------------------------------
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
Chapter 5, ``Tuning I/O resources''.