Re: Diagnosing performance problems

From: Ric Davis (spam_at_fil.ion.ucl.ac.uk)
Date: 12/04/03 

Date: Thu, 4 Dec 2003 15:26:50 +0000

Vikas Agnihotri <fornewsgroups@vikas.mailshell.com> wrote:

> On Thu, 4 Dec 2003 10:17:29 +0000, Ric Davis <spam@fil.ion.ucl.ac.uk>
> wrote:
>
> > Vikas Agnihotri <fornewsgroups@vikas.mailshell.com> wrote:
> >
> >> 1. Out of the usual 3 suspects (cpu, memory, disk), which is the
> >> bottleneck here? Looking at the top output, it seems like the iowait is
> >> killing me here. But the disk svc times dont seem too bad. Yes, there
> >> is a lot of IO going on, but doesnt seem like the disks are holding
> >> things up.
> >
> > There are disks nearly 100% busy, and the CPUs are spending 40% of their
> > time waiting for them. As long as the busy disks aren't busy with swap,
> > I vote disk, or Oracle config.
>
> Yes, I see that they are busy, but the asvc_t is not too high. These are
> standard Sun 10000RPM SCSI disks, so a svc_t of around 30-40ms is
> acceptable, right?

Those disks are near enough 100% (90, 97 and 82%) busy. That means that
whatever is using them can't go any faster, it's held up waiting for the
disks. As Toomas Soome says, some kind of RAID looks attractive.
virtual_adrian is an easy and free next thing to try. It's very good at
deciding what is the next bottleneck
>
> Also, you say, "as long as the busy disks arent busy with swap...". How
> can I verify if they are busy with swap or not?

Where is swap? Which disk is it on? Look at /etc/vfstab. If swap is on
the busy disks, then RAM is almost certainly the bottleneck. If Oracle
tables are on the busy disks, then disk performance is the bottleneck.
If both Oracle tables and swap slices are on the busy disks, then a
simple bit of reconfiguration may well help.
>
> Another thing I dont quite understand is the memory usage. The way Oracle
> works is when it starts up, it pre-allocates the entire memory configured
> in its init files (called the SGA). This is set to 250M. All the processes
> connecting to the database attach to this shared memory segment. NATTCH in
> 'ipcs -ma' shows me about 200 processes attaching.
>
> But when I use the process monitoring utiltiies like top, prstat, ps -o
> vsz, etc, they show each process using 250M for a grand total of some
> ridiculous number like 100GB or something.
>
> Dont these utilities understand the concept of shared memory segments and
> adjust the numbers accordingly?
>
Clearly not. Which of RSHRD RSIZE VSIZE are you looking at in top? 

-- 
'ric