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

[Alzhy]

SEP,

Most of our Oracle instances are OLTP that behave like DSS -- for the very reason that some of our tables are raw and blob -- we store zillions of large images -- each on the average in the hundreds of megabytes.

For all our systems that use LVM, we have standardised on 64MB as PE_SIZE so we don't not give birth to a new VG everytime a new tablespace is added which require more LUNs. We should be prepped for VG's or Filesystems as large as 250 TB! (in case we shift the business to splicing genes or nanotech). And as everyone has mentioned, LVM PE size has no bearing at all on Filesystem performance except if you are doing host based striping of your LUNs.

Oracle blocksize -- we kept at 8k although with Oracle9i - we are experimenting with variable blocksizes for the various table spaces. On Solaris9/Oracle9 environments we are playing with both multiple vxfs block, oracle block and OS page sizes.

I could give you no performance facts nor white papers - our site based our configs on real app performance - through an iterative process of finding out the right mix.

[Alzhy]

SEP.

You can give me no points on this one.

Why don't you explore at the possibility of using VxVM instead of LVM on your environments... You won't face the familair issues (and limitations) with LVM. Despite talk of an enhanced LVM with Tru64 flavours on it -- I still believe VxVM is your safe bet when it comes to large environments. And Oracle is very much at home on Veritas protected environments -- OjFS (aka VxFS) is a Veritas product and HP (so far) has done a nice job integrating (an understatement really) VxVM with HPUX11i specially.

[Steven E. Protter]

I implemented over the weekend.

The consensus conclusion is that PE_SIZE has no effect.

We have no reason to use vxvm. We had to rip it out because it was causing problems with Ignite Golden images.

Nelson, In my threads everyone gets something for participating. I'm trying to make a point by doing this.

Thanks for your input.

SEP