Disk bottleneck

[MAD_2]

I've seen a few discussions regarding this issue and need to do so much reading about it, but here is the essence of our problem.

By using Glance I've noticed constant disk bottleneck warnings. Attached is a file which contains a snapshot of "sar -d 2 5" during one of those periods when the bottleneck occurs.

There are four hardware paths:
0/8/0/1.0.0.0 (vg01) --> /dev/dsk/c5t0d0
(/data)
(/data2)
(/oracle)
(/oradata)
(/oradata1)
(/oradata2)

0/0/1/1/2.0 (vg00) --> /dev/dsk/c1t2d0
lvol1 (HFS)
lvol2 (swap/dump)
lvol3 (/stand)
lvol4 (/tmp)
lvol5 (/home)
lvol6 (/opt)
lvol7 (/usr)
lvol8 (/var)
swap01

0/0/2/0.2.0 (vg00) --> /dev/dsk/c2t2d0
lvol1 (HFS)
lvol2 (swap/dump)
lvol3 (/stand)
lvol4 (/tmp)
lvol5 (/home)
lvol6 (/opt)
lvol7 (/usr)
lvol8 (/var)
swap01

0/0/2/1.2.0 (DVD-ROM)

The SC10 disk array is configured to single bus architecture (array in Full Bus Mode) and 3 disk LUN/4disk LUN with 1 hot spare.

All of the logical volumes in vg01 have the entire oracle db files.

Please provide some clues/hints of what can be done to help resolve this bottleneck.

[Steven E. Protter]

Short term bottlenecks are not a problem. Constant ones are.

Collect data over a longer period of time.

Attaching script.

Based on the data consider layout changes, but not before. Post the data back if you use the script.

SEP

[MAD_2]

Thanks for your prompt response Steven... I'll get back with the results from the script a little later.

[Volker Borowski]

Hello,

no real suprise. Oracle on a single disk !

c5t0d0 appears to be your database disk.
So if someone selects data from your oracle database, this disk will be busy.
Since you see very few reads related to the number of blocks transfered, I suspect the application doing a sequential scan for data. So there might be things to optimize inside the database.

Things to optimize outside:

1) buy more disks, distribute /oradata* across all of them but one, and archive destination to the one not used for /oradata* AND
reconfigure online redologs to have members on two diffrent disks --> minimal safty requirement for production

2) If you need to stay with three overall disks, I suspect you do not use archivelog mode. Check Oracle table V$FILESTAT and distribute /oradata* across all disks an do not locate online logs on the disk with the highest access values.

3) If you have no space left on the two OS disks, accept that the third disk will stay slow.

Hope this helps
Volker

[MAD_2]

Actually Volker, it's more like 10 disks (36 GB each) on . It's a I20 RAID5.

The other two disks that come with the CPU (36 GB each too) contain the OS (mirror).

[A. Clay Stephenson]

Glance can be very misleading when trying to evaluate the performance of disk arrays --- all it knows it that an awful lot of I/O is going through what is thinks is one device but what actually might be many physical devices. It is very common for database applications to be I/O bound; what else would they be doing?

I have seen many admins go to great lengths to break a VG into many LUNS so that things will APPEAR to Glance (or other host-based measurement tools) to be much better but the actual I/O transfer rates may not have improved at all (or in a few cases gotten worse).

It does make sense to have as many SCSI paths as possible so that the I/O is spread across multiple channels and overall bandwidth is increased. You should also be aware that you take an instant 3x-7x (or so) hit by using RAID 5 over RAID 1/0.

Finally, in almost all cases, database performance is improved by better tuning of the SQL code itself than all the hardware/database tuning combined -- and often by a factor of 10.

[MAD_2]

OK Steven, unfortunately it seems I have to post the resulting files individually, a total of 8... So here they go:
- HP_info

[MAD_2]

HP_perf_info

[MAD_2]

HP_perf_info.buffer

[MAD_2]

HP_perf_info.cpu