File Striping for Oracle Database

[Logan Ramasamy_1]

I am a DBA. I am not a HP guru. I was listening to a presentation by Tom Kyte, VP, Oracle in the internet. He said, "Prior to RAID arrays and file system striping, DBAs attempt to spread I/O out by spreading data and index files. But now, underlying file system is striped; One big mount point is carved out of hundreds of physical devices, underneath. I/O is spread across all of those devices evenly.". I asked our system Administrator to comment on this with respect to our environment. Her answer was:"This is correct, however on the HPUX systems we do not use RAID, we use mirrors.". I did not understand this. As far as I understand, mirroring is to duplicate one physical disk by another. If a disk fails, repalce it. It will sync itself with the O.K. one. How this mirroring meets the requirement of file system striping? Please help me to understand this in a high level plain English. Thanks.

[Sandman!]

What kind of storage is your data on...JBOD or high-end arrays like XP's?

[Christian Tremblay]

If your storage sits on a RAID disk array, then volume disk striping makes no sense since the array itself will manage where on the physical disks your data will be written.

She may have been telling you that you don't have intelligent disk arrays in your environment then striping over multiple disks makes sense. Mirroring is just writing to 2 or more physical disks at once.

[Logan Ramasamy_1]

I can go back to her and find out what kind of storage is ours. But I doublt if I get any response which will make sense to me. It is a commin disk array shared between 2 HP boxes (HP-UX B.11.11 U 9000/800).

[Alzhy]

Logan,

INSIST that you as a DBA knows the storage infrastructure -- i.e. your Storage Arrays and how the "LUNS/Disks" are RAIDed. Also, whan a storage/system admin refer to a LUN/disk as mirrored -- it's just another level of RAIDing -- but does that not neccessarily mean you have striped LUNs.

Depending on your Storage Array -- it is always best to know its architecture as sometimes teh BEST I/O performance and scalability can be achieved by HOST BASED STRIPING (via LVM/VxVM, etc) of these already RAIDed LUNs/disks from yout array.

[Hein van den Heuvel]

I suspect a simple communication problem.

You are right, the goal of mirrorring is just that, mirror the data to keep it available in case a drive fails. It also tends to have performance benefits as reads (which tend to dominate the IO pattern) can come from the nearest, or least busy, disk. Writes may slow down, as both disks need to be written, but caches can take care of that.

The system administrator may have focussed on the mirrored aspect of a lun, while it offers striping at the same time. This combination is often refererred to as raid-01 or raid-10. It offers the best of both worlds.

Ask you question in simple terms... how many physical disks are behind my luns?
2 would be mirroring only and not good enough. 8 would be nice. 40+ would be the 'SAME' architecture Tom referred to.

>> I can go back to her and find out what kind of storage is ours. But I doublt if I get any response which will make sense to me.

How can that be? The SA is there to serve the DBA, which is there to serve the application no? If the SA can not communicate with the DBA then you can not run an optimal business solution.

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Yogeeraj_1]

hi Logan,

RAID, striping, mirroring are not just about HP systems - they are general concepts.

The following URL answers many of the questions people are asking about RAID for an Oracle database:
http://technet.oracle.com/deploy/availability/pdf/oow2000_sane.pdf

hope this helps!
kind regards
yogeeraj

[Steven E. Protter]

Shalom,

It is much better to handle this on a disk array and merely present a well configured LUN to the HP system.

The disk array is designed for this, the system is not as good at making the i/o most efficient.

Striping such that it is with the OS is not very effective and not even considered true striping.

SEP

[Ralph Grothe · Accepted solution]

You can try to find out for yourself
if you don't feel inclined to ask your Unix sysadmin who should know.
First add /usr/sbin to your path to abbreviate typing.
(assuming your dba account has a Bourne-like shell)

$ PATH=/usr/sbin:$PATH


With sqlplus you should be able to find out $ with an SQL query the paths to the *dbf files (if you don't know already, anyway).
Sorry, I would have to look up the correct SQL statement as I am no DBA.

Then bdf should show you the logical volume (LV) of the mount that bears the filesystem.

$ bdf /path/to/some/file.dbf

The LV path of the bdf will contain the volume group (VG) that LV belongs to.

To find out what disks are contained in that VG you can run verbose vgdisplay.

$ vgdisplay -v vgXX | more

At the end will be listed the physical volumes (PV) aka disks.

$ vgdisplay -v vgDat1|awk '/PV Name/&&!/lternate/{print$NF}'

You also can pipe this to lssf which should disclose the HW paths of the disks

$ vgdisplay -v vgDat1|awk '/PV Name/&&!/lternate/{print$NF}'|xargs lssf

By the HW paths you are likely to discover how the used disks are attached.

Compare the beginning of HW paths to output of

$ ioscan -knfCext_bus

to find out the controllers the disks are attached to.

You may also follow a whole HW path by

e.g. (replace with your HW path)

$ ioscan -knf -H 1/0/6/1/0.114.4.19.0

To find out if any of the LVs are striped you can do this.

$ $ vgdisplay -v vgXX|awk '/LV Name/{print$NF}'|xargs lvdisplay|grep -E 'LV Name|Stripes'


HTH

[Logan Ramasamy_1]

Thanks everybody who took sometime to part with knowledge. I have attached the output for the unix commands. I would appreciate very much if you help me to conclude my original question. ( I know you guys are real Unix gurus. Some of the names are familar to me).

[David Bellamy]

Logan: as your output shows you are not doing filesystem striping.

[A. Clay Stephenson]

You are running RAID 1 (mirrored w/o stripes) and your array is a JBOD "Just a Bunch of Disks". None of your LVOL's are stripped. You are not allowed under LVM to do striping with mirrors. You could stripe each LVOL across your disks for RAID0 -- which would perform better but have no protection against disk failure --- and you are extremely vulnerable because the failure of a single disk could cause loss of lots of data. Currently, you are erring on the side of data redundancy rather than performance --- and that is a wise choice.

There is a 3rd option and that is extent-based stripping. Here the stripping is occurring at the extent level and mirroring is possible. The problem with this scheme is that the smallest possible extent (1MiB) is too large to be a good stripe size for i/o distribution (ideally ~ 64KiB-256Kib) and moreover the smallest possible PE severely limits the maximum size of your disks/LUN's.
In your case, about the least-evil approach is to carefully distribute the i/o over as many separate mountpoints as possible because rather than being the "throw everything at the array as fast as you can" model, you are the "I have many disks so spread as much as I can across them" model (but make sure all your LVOL's are mirrored on separate disks).

[Logan Ramasamy_1]

Thanks Clay. I got my answer.

[Logan Ramasamy_1]

David, Thanks much for your response.

[TwoProc]

re: extent based striping...

I'd go ahead and use it. Then you can add to this idea by striping in your Oracle data files themselves. This is done by merely creating your tablespaces across the file systems, and this would give you a pretty good similarity to a nicely striped jbod storage solution.

For instance your busiest biggest tablespaces could be "INVDATA", and it could be comprised of one or two very large data files:
'/u5/data/PROD/invdata1.dbf', and '/u5/data/PROD/invdata2.dbf'. Let's say each of these are roughly 5G or so.

Instead you could set this up like:
'/u5/data/PROD/invdata1.dbf',
'/u6/data/PROD/invdata2.dbf'.
'/u7/data/PROD/invdata3.dbf',
'/u8/data/PROD/invdata4.dbf'.
'/u9/data/PROD/invdata5.dbf'.
'/u10/data/PROD/invdata6.dbf'.
'/u5/data/PROD/invdata7.dbf',
'/u6/data/PROD/invdata8.dbf'.
'/u7/data/PROD/invdata9.dbf',
'/u8/data/PROD/invdata10.dbf'.
'/u9/data/PROD/invdata11.dbf'.
'/u10/data/PROD/invdata12.dbf'.
...
repeat as necessary, having 100Mg or so in each data file.
...

Make sure these files have room to grow by leaving some headroom in each. Make the files 32k uniform allocation when you create them, or try 16k or 64k if you like as well.

Now, as data begins to populate the tables the I/O is spread across your mirrored disks and gives better performance, providing you've got enough controllers and separate disk spindles to support such an operation. The data won't "arrive" in one data file filling it fully, and then moving onto the next one, it will actually create the data *across* the data files *relatively* equally (depending) and you'll begin to see performance that will start to look more like that of a striped jbod system, even though HPUX really can't do this in software.

If you want to put this in incrementally, you could build some tablespaces in the above mentioned manner, and then export/import your data into them - or simply do an "alter table move" command and move them to the new tablespace one at a time (if it is not too many tables or objects).

Back to extent based striping idea: by the time you extent base stripe, and then add to that the idea of spreading your data across multiple mount points (mirrored drives in your case) you'll start to get improved disk balancing and I/O access times. Whether or not it is "enough" depends on whether or not your users are satisfied with the performance of their applications (talk about stating the obvious :-) ), but it should be better than what you started with.

BTW, I always stripe all of my high growth or highly accessed tablespaces this way (I don't bother with the little ones), even on the big XP class storage servers, so that when the database is "cloned down" to a smaller test server - it works as well as it possibly can, given the hardware limitations.

Hope this helps

[Yogeeraj_1]

Hi again Logan,

Below some of the guidelines that have been recommended by the Oracle Guru (Tom Kyte):

o No RAID, RAID 0 or RAID 0+1
for online redo logs AND control files.
In Oracle, you should still multiplex them even if you mirror them.

o No RAID or RAID 0 for temporary datafiles (used with temporary tablespaces).

o No RAID, RAID 0 or RAID 0+1 for archive logs. Again, in Oracle, you should still multiplex them.

o raid 0+1 for rollback.
It get written to lots. We cannot multiplex them at the Oracle Level. We use this for datafiles that we believe will be HEAVILY written. Bear in mind, Oracle 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.


Also, we should try to dedicate specific devices to:
o online redo
o archive
o temp

These should not share their devices with others in a "perfect" world (even with
each other).

hope this helps too!

kind regards
yogeeraj

[Eric Antunes]

Hi Logan,

If you have root access, then you can know by yourself:

#cd /opt/raidsa/bin
#ioscan -fnkCext_bus
#./sautil /dev/ciss

A good performance strategy is to avoid redolog files on raid5. Furthermore, multiplexing of the controlfiles and redolog members is always a good idea and, of course, a good Buffer Cache Hit Ratio.

Best Regards,

Eric Antunes

[Logan Ramasamy_1]

John/ Yogeeraj/ Eric, Thanks a bunch.

[Alzhy]

Greetings!

Just a word of caution to the previous poster who said (no offense SEP!):

"It is much better to handle this on a disk array and merely present a well configured LUN to the HP system.

The disk array is designed for this, the system is not as good at making the i/o most efficient.

Striping such that it is with the OS is not very effective and not even considered true striping."

As an Admin who's had to educate DBAs through these years, the above should be treated with extreme caution. Why?

Not ALL arrays (specially the cache-centric ones) manage the striping or performance in whatever virtualization/RAIDing they offer. This is TRUE for most high end arrays like Hitachi's (USP/Tagmastore and HP's XP Line) as well as EMCs, etc. BEST Practice for these arrays remain to be that you use your host/OS volume manager to stripe your storage volumes/filesystems. In fact, Oracle ASM and ODM even have specific ASLs or APIs so these automated storage allocation schemes "know" the internal layout of the array's innards so it is able to layout your volumes on the OS/host optimally for performance.

In the case of Highly Virtualized Arrays like the EVA line - the above "possibly" holds true BUT we are actually getting performance by still striping EVA LUNS on the host level.

JBODs - or ordinary disks in array enclosures are of course a different matter. YOU MUST use your host/OS volume manager to RAID them both for protection/redundancy and performance.