max pe for an pe size of 2mb ? some maths..

[Alvaro Miranda Aguilera]

hello guys, this is my first post.

Today I have an vg01 group from 1 eva 3000, this vg comes from 1 eva volume of 400 gb using a pe size of 32 mb, so it uses 12798 pe.

I point this because the machine who have this volume runs oracle 9i, with a 8kb block size, so, I assume for every read the eva sends 32 mbytes.

My question is:

I should re-create this vg from 16 eva volums of 25 gb ? Doing some maths, if I do this, I can assign 12799 PE of 2mb x 16.

I undestand is even better create smalls vg for each datafile, but I need to start with some first, then I would propose something better.

Thanks guys,

à lvaro

[Alvaro Miranda Aguilera]

I forgot provide more info..

I'm running hp 11v2, 11.24 on itanium 2.

>>ioscan -funCdisk
Class I H/W Path Driver S/W State H/W Type Description
==========================================================================
disk 0 0/0/2/0.0.0 sdisk CLAIMED DEVICE HP 36.4GMAS3367NC
/dev/dsk/c0t0d0 /dev/rdsk/c0t0d0
/dev/dsk/c0t0d0s1 /dev/rdsk/c0t0d0s1
/dev/dsk/c0t0d0s2 /dev/rdsk/c0t0d0s2
disk 1 0/0/2/0.2.0 sdisk CLAIMED DEVICE HP 36.4GMAS3367NC
/dev/dsk/c0t2d0 /dev/rdsk/c0t2d0
/dev/dsk/c0t2d0s1 /dev/rdsk/c0t2d0s1
/dev/dsk/c0t2d0s2 /dev/rdsk/c0t2d0s2
/dev/dsk/c0t2d0s3 /dev/rdsk/c0t2d0s3
disk 2 0/0/2/1.3.0 sdisk CLAIMED DEVICE HP DVD-ROM 305
/dev/dsk/c1t3d0 /dev/rdsk/c1t3d0
disk 3 255/255/0/0.0 sdisk CLAIMED DEVICE HSV100 HP
/dev/dsk/c12t0d0 /dev/rdsk/c12t0d0

[Steven E. Protter]

I am not sure I'd actually change anything.

Unless Oracle is telling you that this setup is causing peformance problems, I'd really leave well enough alone and concentrate on optimizing the kernel for good Oracle operations.

PE size has according to the real Oracle guru's here little or no impact on Oracle performance.

SEP

[Alvaro Miranda Aguilera]

Thanks for reply.

I'm having some %wio and extra disc utilization, so I'm guessing..

I believe for each oracle request, the os is getting 32mb from the eva, and then searching for the rigth 8kb.

Maybe I'm totally wrong.

This is the sar output

08:00:01 %usr %sys %wio %idle
11:20:00 22 4 68 6
11:30:00 26 4 67 2
11:40:00 46 12 41 1

Average 20 4 52 23


this is the sar -d output

08:00:01 device %busy avque r+w/s blks/s avwait avserv
11:40:00 c0t2d0 1.85 15.50 12 178 9.13 4.66
c0t0d0 0.73 0.77 2 23 0.80 6.66
c12t0d0 99.90 48.08 2342 37465 17.97 3.25

Average c0t2d0 1.55 14.64 8 124 8.20 4.84
Average c0t0d0 0.70 0.70 2 22 0.61 6.63
Average c12t0d0 81.83 34.93 1886 30177 12.28 2.83

Some top info.

Load averages: 0.45, 0.43, 0.47
306 processes: 277 sleeping, 28 running, 1 zombie
Cpu states:
CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
0 0.45 41.7% 0.0% 7.2% 51.1% 0.0% 0.0% 0.0% 0.0%
1 0.46 42.9% 0.0% 8.2% 48.9% 0.0% 0.0% 0.0% 0.0%
--- ---- ----- ----- ----- ----- ----- ----- ----- -----
avg 0.45 42.3% 0.0% 7.6% 50.1% 0.0% 0.0% 0.0% 0.0%

Memory: 2356756K (1319336K) real, 4484296K (2519376K) virtual, 428584K free Pag
e# 1/9

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
1 ? 13259 oracle 154 20 1360M 8172K sleep 26:36 11.83 11.80 oraclemp01
1 ? 13255 oracle 149 20 1360M 11180K sleep 3:44 7.86 7.84 oraclemp01
0 ? 13211 oracle 154 20 1360M 10732K sleep 6:16 5.58 5.57 oraclemp01
1 ? 13060 oracle 149 20 1393M 11820K sleep 4:11 5.18 5.17 oraclemp01

thanks for any guideline..

[Bill Hassell]

Very important: the PE size has nothing to do with I/O in Oracle. Volume groups know nothing about filesystems, they are simply groups of disk storage, soemtimes one, somtimes many disks. A PE is just a unit to map a logical location to a physical location. Therefore, 32Mb PE's have NO effect on applications. Oracle will still use 8Kb blocks and whether several blocks crossover a PE boundary is irrelevant.

As far as creating small VG's, there isn't a lot of advantage unless you start mapping physical drives to volume groups--which defeats the whole purpose of a disk array. The array makes the physical layout transparent to the computer and uses hardware computation to allocate the space. Splitting up a database across multiple volumes should be done with an eye towards managing the space, not trying to match 8Kb to anything. Oracle will use a minimum of 8Kb but may (with sequential blocks) read or write multiple 8Kb blocks. And to make it just a bit more complicated, HP-UX will buffer those blocks in the buffer cache where the final I/O may be much larger than what was requested.

[Alvaro Miranda Aguilera]

Thanks bill for your reply,

I'm getting the idea more clear now.

I will start now try to find out why oracle is getting that kind of %wio.

I was only cheking and idea, normally I was sure that size of 'block' (maybe there was my error) will produce nothing just delay.

I was thinking an PE of 32mb was equal to a 'cluster', or an block of filesystem.

Alvaro.

[Bill Hassell · Accepted solution]

Oracle is asking for a lot of I/O so you would naturally expect high %WIO. To reduce the I/O, ask your DBAs to look at increasing SGA to eliminate temp sort files on disk, to accomodate full row insertions, to increase Oracle's buffereing capability, etc. All of this (and more) will decrease the number of I/O's for the same set of circumstances, but realize that SGA may need to be several Gb (RAM) to accomodate all these changes. Also remember that a database that doesn't have enough indexes or poorly written SQL tasks may cause massive (but unnecessary) I/O. You don't want to fix this in the opsystem...fix the database. And for very large SGA, you'll need large RAM and 64-bit versions of Oracle and related applications.

[Steven E. Protter]

As Bill and I noted(note we agree) PE size doesn't effect Oracle performance.

There are a lot of factors that DO effect Oracle performance in the kernel.

I'm attaching some scripts to collect performance data. These should be run while the database is being stressed. You will find bottlenecks such as wait i/o and you can make adjustments based on that.

This document deals very nicely with the kernel stuff:
http://www1.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-682735245+1085428704626+28353475&searchCrit=allwords

The author is the guy that helped me when I had Oracle tuning problems on a box that was overpowered based on what was being asked of it.

SEP