topic Re: Configuring Disk for database in Operating System - HP-UX

Configuring Disk for database

Ron Cornwell — Mon, 14 Jun 2004 12:25:19 GMT

I have to rebuild an Oracle database server shortly with new disk. The server is a N4000 4way and the disk will be (8) 36GB drives mirrored in an FC10 disk subsystem. I know there is a rule of thumb to put the indexes on their own disk, but would it not improve performance to stripe the indexes and the table spaces across all 4 disk ?

Re: Configuring Disk for database

Steven E. Protter — Mon, 14 Jun 2004 12:33:28 GMT

Oracle recommends:

index and data raid 1
rollback raid 10.

Other files can be raid 5.

If you are going to stripe anyway the more disks the better.

SEP

Re: Configuring Disk for database

Fred Ruffet — Mon, 14 Jun 2004 12:39:41 GMT

SEP,

I do not agreee on this one :
data and index on RAID5
redo, undo on RAID1

data and indexes are accessed rather randomly, where redo and undo are accessed sequentially.

Regards,

Fred

Re: Configuring Disk for database

David Child_1 — Mon, 14 Jun 2004 13:12:49 GMT

Ron,

Since the indexes are usually quite small when compared to the table spaces you wouldn't of course set up a 4-LUN striped volume just for indexes. The alternative is of course putting indexes on a single LUN. We usually put our indexes on the same LUNs as our table spaces as we believe we get better performance by striping those as you stated (instead of putting indexes on a single disk).

To be honest however, we have not actually tested performance differences between putting the indexes on a single, separate LUN over including them in the striped data volume group. I must get around to that one of these days.

The more important thing is to try and keep the redo/undo logs off table space disks.

David

Re: Configuring Disk for database

Fred Ruffet — Mon, 14 Jun 2004 13:47:40 GMT

David,

Data and indexes can be put on the same VG with stripping without affecting performances (whereas puting redo logs on RAID 1 improves it a lot).
But saying indexes are rather small compared to data is not so right. On common OLTP DBs, what can be observed is a ratio of 1:1 between indexes and databases.

Regards,

Fred

Re: Configuring Disk for database

Uwe Zessin — Mon, 14 Jun 2004 14:14:31 GMT

Doesn't Oracle tell today to use S.A.M.E. ?
(Stripe And Mirror Everything)

Re: Configuring Disk for database

Fred Ruffet — Mon, 14 Jun 2004 14:17:32 GMT

Uwe,

Maybe on 10g, but I'am actually setting up some machines on a SAN with multiple Oracle DBs, and making RAID1 LUNs for logs, undo and temp really helped.

Regards,

Fred

Re: Configuring Disk for database

Fred Ruffet — Mon, 14 Jun 2004 14:20:52 GMT

As a final point and if you have access to metalink, check this document (recently updated) :
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=30286.1

Regards,

Fred

Re: Configuring Disk for database

Geoff Wild — Mon, 14 Jun 2004 14:22:12 GMT

I stripe across multiple disks at the hardware level (over 100) (EMC) - you don't have that luxury, but I would surmize that dedicating a 36 GB disk for redo logs would be a waste. What you (or Oracle) wants is 1 disk for redoA, 1 disk for redo B, 1 disk for index and 1 disk for data and 1 for archiving...

In my opinion, you would be best to stripe what you got...if you have time, you could set it up with separate disks - benchmark, then set it up with striping, benchmark again and compare the results..

Here's a standard I tend to follow:

/app/oracle - .5GB to 1GB - oracle userid home

/app/oracle/product - 2GB to 4GB - oracle software installation

/data/oracle - .5GB to 1GB - miscellaneous storage

/data/oracle/xxxx - .5GB to 1GB - miscellaneous storage

/data/oracle/xxxx/datayy - data volumes for tablespaces - generally 2GB to 8GB - xxxx is the Oracle instance name, yy is volume number

/data/oracle/xxxx/indxzz - index volumes for tablespaces - generally 2GB to 8GB - zz is volume number

/data/oracle/xxxx/redo01a - odd numbered redo logs go here - generally .5GB

/data/oracle/xxxx/redo01b - odd numbered mirrored redo logs go here - generally .5GB

/data/oracle/xxxx/redo02a - even numbered redo logs go here - generally .5GB

/data/oracle/xxxx/redo02b - even numbered mirrored redo logs go here - generally .5GB

/data/oracle/xxxx/arch - archived redo logs - only required for production databases - generally 8GB or larger - if this fills up database hangs.

/data/oracle/xxxx/exports - database export files go here -generally 4GB or larger.

Rgds...Geoff