topic Re: Planing Oracle database & application in Operating System - HP-UX

Planing Oracle database & application

Inesa Clinko — Wed, 08 Oct 2003 06:29:30 GMT

Hi!
Could anybody help me to plan VGs and FSs
for an application, based on Oracle9 dbase,
OS HPUX11,mashine rp8400 and storage HP StorageWorks.
The initial parameters are:
-70 GB for software,system
tablespaces,rollback,temp..);
-1.3-1.4 TB for data, based on RAID5;

How I have to design my VGs in order to
phisically separate software, system, data,indexes,rollback,segments,log files..?
What is the usual solution?
Is there any special remarks about kernel parameters,connected with Oracle?

Re: Planing Oracle database & application

Pete Randall — Wed, 08 Oct 2003 06:33:43 GMT

I'm not an Oracle expert but I can offer some general guidelines.

Keep your root volume group separate. Usually the root vg is placed on internal disk and mirrored using MirrorDisk/UX. Keep all your data in separate volume groups, probably on external storage. We use vg01 for non-db data and vg02 for the database itself.

Pete

Re: Planing Oracle database & application

James Specht — Wed, 08 Oct 2003 06:38:07 GMT

Here we have three volume groups. vg00 root, vg01 DB data, vg02 DB applications. vg01 is in a VA74000 all by itself.
When we migrated from oracle 8 to oracle 9 there were about 10 kernel parameters we had to change. There is documentation on which parameters that comes with oracle or can be downloaded from oracle's site.

--Jim

Re: Planing Oracle database & application

Steven E. Protter — Wed, 08 Oct 2003 06:43:35 GMT

Petes advice is excellent.

Let me add that Oracle reccomends Raid 1 or Raid 10 for data and rollback.

On a database that large, its costly but if you don't do it, expect performance issues at high load factors.

shmmax should be maxed out which means 25% of system memory, which is defined as swap plus memory. shmseg should be set based on anticipated user load.

Having all the oracle stuff in the same volume group will not hurt performance, even with mixed Raid 10 and Raid 5 Luns. Just put them in different logical volumes, don't make one massive filesystem for everything.

The HP-UX OS will fit in less than 20 GB of space even with a ton of add in OS software.

Here is some good kmtune output. Attached.

SEP

Re: Planing Oracle database & application

Jean-Luc Oudart — Wed, 08 Oct 2003 07:15:28 GMT

We maximise the io with disk stripping.
We also alternate controler from disk to disk in the vg.
we use a 64Kb stripe size.

check these links
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=193104

http://forums1.itrc.hp.com/service/forums/parseCurl.do?CURL=%2Fcm%2FQuestionAnswer%2F1%2C%2C0xd6b250dde50cd71190050090279cd0f9%2C00.html&admit=716493758+1065615232533+28353475

http://forums1.itrc.hp.com/service/forums/parseCurl.do?CURL=%2Fcm%2FQuestionAnswer%2F1%2C%2C0x9338b941255cd71190080090279cd0f9%2C00.html&admit=716493758+1065615290814+28353475

Rgds,
Jean-Luc

Re: Planing Oracle database & application

A. Clay Stephenson — Wed, 08 Oct 2003 07:30:03 GMT

I would urge you to upgrade to HP-UX 11.11 - the filesystem performance is better. If at all possible, choose RAID 1/0 over RAID 5. For a database of this size I would split the I/O into at least datafiles, indices, and archive/redo logs each with it's own VG.
Don't get too hung up on which disks to do what because the array is going to split it anyway. Surprisingly, under 11.11, Oracle actually performs better (and I have measured it) using fully cooked files for everything. If you do choose to use raw/io, dont use devices like /dev/vg10/rlvol1 but instead use /u01/oradata/datafile01.dbf and then symbolically link /u01/oradata/datafile01.dbf to /dev/vg10/rlvol1. Using tyhis method of indirection will allow you to easily move the i/o around (or go to fully cooked files) with no Oracle changes.

My best advice is however much memory you think you need, double it. Oracle 9 needs huge SGA's to really perform well.

Re: Planing Oracle database & application

Alexander M. Ermes — Wed, 08 Oct 2003 07:40:20 GMT

Hi there.
Clay's advice is good as always.
You cannot have enough memory for Oracle, because Oracle eats resources like popcorn.
Try to spread the io-load through several channels ( alternate paths / devices ).
Mirror your control and logfiles on different disks and controlers if possible.
Go for two locations of offline log files
( running the database in archivelog mode ).

Did you take a look at the documentation ?
Install guide / release notes etc

http://otn.oracle.com/documentation/oracle9i.html

Rgds
Alexander M. Ermes

Re: Planing Oracle database & application

twang — Wed, 08 Oct 2003 10:17:58 GMT

In terms of administration, RAID is far simple than using Oracle
techniques for data placement and striping.

Recommendations:

In general, RAID usually impacts write operations more than read operation.
This is specially true where parity need to be calculated (RAID 3, RAID 5, etc).
Online or archived redo log files can be put on RAID 1 devices.
You should not use RAID 5. 'TEMP' tablespace data files should also go on
RAID1 instead of RAID5 as well. The reason for this is that streamed
write performance of distributed parity (RAID5) isn't as good as that of
simple mirroring (RAID1).

Swap space can be used on RAID devices without affecting Oracle.

====================================================================================
RAID Type of RAID Control Database Redo Log Archive Log
File File File File
====================================================================================
0 Striping Avoid* OK* Avoid* Avoid*
------------------------------------------------------------------------------------
1 Shadowing OK OK Recommended Recommended
------------------------------------------------------------------------------------
0+1 Striping + OK Recommended Avoid Avoid
Shadowing (1)
------------------------------------------------------------------------------------
3 Striping with OK Avoid Avoid Avoid
Static Parity (2)
------------------------------------------------------------------------------------
5 Striping with OK Avoid Avoid Avoid
Rotating Parity (2)
------------------------------------------------------------------------------------

* RAID 0 does not provide any protection against failures. It requires a strong backup
strategy.
(1) RAID 0+1 is recommended for database files because this avoids hot spots and gives
the best possible performance during a disk failure. The disadvantage of RAID 0+1
is that it is a costly configuration.
(2) When heavy write operation involves this datafile

Re: Planing Oracle database & application

Steven E. Protter — Wed, 08 Oct 2003 10:26:14 GMT

I didn't have this doc at home.

I can't overemphasize the RAID issue.

A. Clay makes excellent points with regards to the OS, I/O and RAID.

This tuning doc is maintainted by HP's top oracle tuning guru.

http://www2.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=-1335382922+1065626708921+28353475&searchCrit=allwords

SEP

Re: Planing Oracle database & application

Steven E. Protter — Wed, 08 Oct 2003 11:14:06 GMT

The doc I submitted is temporarily offline.

Attaching a text copy.

SEP

Re: Planing Oracle database & application

Yogeeraj_1 — Wed, 08 Oct 2003 13:08:03 GMT

hi,
-
To add to above replies, generally here is what I like (raid 0 = stripes, raid 1 = mirrors, raid 5 = striping+parity):
-
o no raid, raid 0 or raid 0+1 for online redo logs AND control files.
You should still let us multiplex them ourselves even if you mirror them. We have more opportunities for failure if the raid subsystem reports a "warning" back to us -- if we have multiplexed them -- we are OK with that.
-
o no raid or raid 0 for temporary datafiles (used with temporary tablespaces). no raid/raid 0 is sufficient. If you lose these, who cares? You want speed on these, not reliability. If a disk fails, drop and recreate temp elsewhere.
-
o no raid, raid 0 or raid 0+1 for archive. Again, let us multiplex if you use no raid or raid 0, let the OS do it (different from online redo log here) if you use 0+1.
-
o raid 0+1 for rollback. It get written to lots. It is important to have protected. We cannot multiplex them so let the OS do it. Use this for datafiles you believe will be HEAVILY written. Bear in mind, we buffer writes to datafiles, they happen in the background so the poor write performance of raid 5 is usually OK except for the heavily written files (such as rollback).
-
o raid 5 (unless you can do raid 0+1 for all of course) for datafiles that experience what you determine to be "medium" or "moderate" write activity. Since this happens in the background typcially (not with direct path loads and such) -- raid 5 can typically be safely used with these. As these files represent the BULK of your database and the above represent the smaller part -- you achieve most of the cost saving without impacting performance too much.
-
Try to dedicate specific devices to
-
o online redo
o archive
o temp
-
they should not have to share their devices with others in a "perfect" world (even with eachother).
-
Hope this helps too!
-
regards
Yogeeraj