Yet another Oracle database question. What PE_SIZE are you using for oracle oltp data?

[Steven E. Protter]

Due to the large(relative term) size of my Raid 10 oracle data area, I was unable to fully extend my oracle data filesystem when 20 more GB were allocated to it to hold some new instances.

I decided to segregate oracle data, redo and other non-binary stuff to its own volume group.

I want this volume group to be able to grow without being torn down and built again. Its a pain, my maintenance window is in the middle of the night and frankly it screws up my week.

So I've decided to increase the pe_size from 4 MB to a larger figure. This will let me have more PE's in the volume group and avoid the break and build process.

What I'm looking for is real life data. I'm running a 4-8 GB oltp instance and I'm wondering what a good pe_size for this volume group should be.

I remember an oracle performance class where the instructors recommended a PE_SIZE of 32 mb or 64 mb. The data gets read in large blocks and peformance can be significantly enhanced.

So what PE_SIZE are you using for your oracle oltp data?? Why? Is your system OLTP(Online Transaction Processing)?

The maintenance is Saturday night, so answers are good until 9 p.m. Central Saturday night.

7 points for any try I think is reasonable. If you answer all of the questions or post up a good document that deals with this, 10 point bunny is quite possible.

Sorry to Pete and the Informix crowd, but if someone can dig me a document, thats great. I have metalink and otn so those documents are good.

Thanks.

Looking forward to handing out the points. I may wait until Saturday night because people sometimes avoid threads with bunnies, and I'm taking a survery here.

SEP

[James Murtagh]

Hi Steven,

To the best of my knowledge it will make little, if any difference. You metioned the data is on filesystems - data will be read at this level and the blocks allocated will be from the filesystem driver. Although I read a document on the way LVM manages its data a few months ago the details escape me now, but I do believe it breaks it down into somethink like 1k chunks?? In any case, there is no lvm cache and I don't believe disk arrays (with caching) read the entire extent so the only question that remains would be of locking of the extent for the IO, if any at all. I think there is a IO queue on the driver for that.

As far as Oracle recommending LVM extent sizes....I would be dubious but if I'm proved wrong will hold my hands up.

Cheers,

James.

[Con O'Kelly · Accepted solution]

Hi SEP

We asked HP this exact question a couple years back and the response was that the PE Size makes no difference at all to the FS performance. I believe the response came from a contact at WTEC. I did spend a bit of time looking into this but didn't find anything to suggest any links to performance.

In the past we've tried to standardise on creating VG's with the same PE size (-s), No PV's(-p) and phys extents per disk (-e). (From memory we used -p 40, -e 4640, -s 16 giving us a max disk size of around 72GB for the VG.)

The only real benefit was ensuring we never had any issues with increasing space allocated to the VG's.

The only issues with a 4MB PE Size might concern the the LVM header. I think the LVM header has to fit on 1 PE. Possibly using very large values for -e & -p can increase the size of the LVM header, hence you need to use a larger PE Size??
The LVM header may affect the combinations you can use for -e, -p & -s options but I have never looked into this in detail.

I've also focussed on LV creation and FS creation for addressing any performance issues. If you haven't seen it the JFS Sys Admin Guide has a pretty good sectiuon on FS performance.
http://docs.hp.com/hpux/pdf/B3929-90011.pdf

Cheers
Con

[Steven E. Protter]

Sounds like my question may not be well formed after all.

Same point allocation for what options such as block size do you use for the vxfs filesystem in for your oracle filesystem. Data. OLTP.

Great doc, Con. Rabbit inbound, you'll have it by Friday afternoon.

SEP

[A. Clay Stephenson]

PE size is bookeeping -- nothing more and nothing less; it has no impact on performance with one dubious exception. If you are using extent-based striping then a smaller PE size would distribute the i/o better but 1MB (the smallest possible) is still much too large a stripe so the gains are minimal.

For the other question (and I have measured this) on vxfs filesystems with reasonably current patches blocksize makes no difference --- in spite of what the Oracle guys have beaten into your head. The reason: vxfs filesystems (unlike hfs/ufs/s5 filesystems) are extent based. The block size can be left at default. I find that if you are striping then 64k is a good stripe size of vxfs because it appears that vxfs writes occur in 64k chunks as much as possible.

[Steven E. Protter]

This has been a very enlightening discussion.

Anyone have any stories to tell about setting vxfs parameters. I'd also pay in points for /etc/fstab settings that enhance performance.

Thanks A. Clay.

As they say, bring on the replies, I'm learning.

SEP

[Massimo Bianchi]

PE SIZE
As Clay says, it's no difference.
The point is how long will you extend this vg, and how many disks are you going to use.
Plan this, it's more important.
bigger PE allow for bigger disks, i know it's obvious, but sometimes we understimate the simpler things.


FS
If you have jfs 3.5 version, i found very usefull to change some vxfs parameters with vxtunefs.

You can set the preferred read ahead , and if you can keep this aligned with the multiread_block_factor of oracle (multiplied by db_block_size), each seq read from oracle will be exactly a seq read in the fs, without any waste. for single block read, you will get no benefit, since extra blocks will be read unnecessarily... must be tuned!

I suggest to check these and tune accordingly:

read_pref_io
read_unit_io



For the fstab.....
delayglog moutn ption can be considered, but if you have onlinejfs you can use the minchace=direct,convosync=direct

[Jean-Luc Oudart]

Hi SEP,

I don't think PE size will do anything for your performance. I suppose you alreaday have the document on SAME from Oracle
http://otn.oracle.com/deploy/performance/pdf/opt_storage_conf.pdf
But this is no good on PE size !

or again repeat myself with my thread a while ago (on vxfs mount options):
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=193104

If PE size matter you should build right 1st time, an other option would be to have different volume group for say data and indexes, the issue goes back to capacity planning.
Why one vg only ?

Regards,
Jean-Luc

[Steven E. Protter]

Examples based on Massimo's suggestion would be useful.

Thanks so far, I think that going with 8 MB PE size instead of the 32 I was planning will give adequate disk space. That and the -e 32000 when I create the volume group should get me past 100 GB.

SEP

[Gary Cantwell]

The subject line you wanted