Many vs. few filesystems for small Oracle db on Hitachi Array

[Bob Sobey]

(hpux 11.11 N class)
The crux of my question is this.
Is there any real benefit anymore from 11 filesystems versus, say, 3 ?
We have one 36GB Hitachi Lun, which is a RAID made up of 4 9GB virtual disks, which are slices from different larger real disks, and given the cache front end....
For a small database (1GB) our DBAs continue to require 3 filesystems for indexes (data01-03), a data04 for data, .../dump, archive, redolog1 & 2, the software, etc. - 11 filesystems totalling 34GB.
I also realize that the filesystem level can slow down when you get over 10 or 15GB of millions of small files. ... but this isn't the typical case for our small oracle databases. How few of filesystems can we have without impeding performance? (2 pvlinks - fiber)
Thanks!
Bob S.

[RAC_1]

I would advise to keep seperate FS for oracle binaries, redo logs, for indexes and multiple FSs for data logs. (Depending upon the data size you can have two to three FS for data logs)

For no. of files, as files count go high, you would be better off, havinf seperate FS. Else, the backup times and restore times would be a problem for you. Also if something goes wrong with this FS, fsck would go mad correcting it.

Anil

[Chris Xu · Accepted solution]

With your current storage and small size database, there is not going to be noticeable performance gains with a lot more filesystems. I would suggest 4 or 5 filesystems should be enough. Our databases are over 200 gb in size, and use 5 filesystems built on EMC LUN's. No performance problems.

I think minimun number of filesystems to have is 3, and it is because of the nature of oracle data and ease of db maintenance, etc. You would at least have one filesystem for Oracle software installation, one for data, and one for archive.

Chris

[Mark Greene_1]

Part of the seperate file system arguement is to handle run-away process that could consume the redo logs without crashing the whole works. The real advantage is that segragating the archives and dump space on a RAID1 LUN and the rest of the db on RAID 5 will give you the best mix of performance and protection.

mark

[Steven E. Protter]

Oracle recommends data/index and redo be raid 1 for best performance.

If its all on one array, the advantages of multiple filesystems beyond that are limited.

The DBA's really don't know what they need under the hood, as far as raid level.

The RAID level on data/index and redo will have far more performance impact than number of filesystems.

SEP

[A. Clay Stephenson]

The only answer possible is "it depends". I can tell you that I now run 3 filesystems per Oracle Instance. 1) the Oracle applications 2) Data and Indices 3) Redo/archivelogs. Oh, I forgot to mention it's all one VG as well - horrors. My DBA's hated this (they had all been to the same Oracle classes I had been to BUT they failed to grasp that the Oracle instructors are hung in a time-warp and that not all disk devices are just simple disks -- they like to say spindles). They hated it more when I sandboxed it their way and then did it my way on the same database. Much to their surprise there was no significant difference in performance but obviously maintenance was now much easier. You really don't have to have a set of Oracle binaries per instance, I just do it that way because I always more Oracle binaries including listeners with a ServiceGuard package but that too is frowned upon in some quarters.



You do have to pay fully utilize as many SCSI paths to the array as you have. Generally, you carve up your array into as many equally sized LUN's as you have io paths. The total of all these LUN's should equal your required VG size. Next, each LVOL should be striped across all these LUN's typically in 64KB or so chunks. The other thing to watch is your kernel max_scsi_queue_depth setting.


The best (and really the only reliable) advice I can give you: try it both ways and measure.

[A. Clay Stephenson]

I should add that as important a choice as anything is the RAID Level. In general (and especially when you are carving things up 11 ways), you aren't interested so much in capacity as you are performance. Buy twice as much disk as you need (plus whatever you array requires for hot spare) and run everything at RAID 1/0. I've seen i/o degradations as large as 5-7x in going from RAID 1/0 to RAID5. Rarely do those result in that large a reduction in overall database performance.

Finally, all of this still is insignificant when compared to the efficiency of the SQL itself. No amount of hardware / OS tuning can overcome poorly written code.

My rule of thumb for databases is: Will a 2X
improvement be good enough? If so, hardware / database / OS tuning might help (1.2x is much, much more likely) but beyond that look at the code.

[Leif Halvarsson_2]

Hi,
It also may depend on the type of RAID controllers. With traditional RAID controller you can get a significant performance improvemnt using RAID 10 compared to RAID 5. On the other hand, if an EVA array, Vraid1 has not better performance then Vraid5 (but better fault tolerance).

[Alzhy]

Aha.. a Hitachi Array.. My Favourite.

Yes there may be benefits and "it depends". The depends is how many tablespaces (or table space groupings) you have in your DB instance. Say if you have 3 groupings - you certainly can have 3 "large and growable" filesystems that are "stripes".

I used to be a believer of wide thin stripes in the SAME fashion (stripe and mirror everyhing) but in this case.. you can drop the mirror since the LUNs (aka disks) you'll be dealing with are already "protected" - either as carvings of 3+1P RAID5 Array groups, 7+1P, 2+2 or 4+4 RAID10.

I will leave it up to you how you carve your Hitachi/XP array groups or even if you want RAID5 or RAID10 -- personally I do not see any GREAT difference at all when it comes to Hitachi's implementation of RAID5 (specially the 3+1P or 7+1P implementation)..

On to how I usually build my HDS+UNIX Oracle configuration.

1. Come up with a standard LUN size from your Hitachi. These days, 25-50-100GB LUNS are ideal or come up with custom LUN sizes that will not leave an odd slice out of your array group.
2. Present the LUNS equally among however many FC connections you have to your servers.
3. Build your VOLUMES (or LVOL) as stripes - 4 or 8 ways with a stripe width of 16-64-128-256Kb (The lower the stripe size.. the more conducive for OLTP type instances - the higher.. DSS) Each LUN must come and be spread out from different array groups and different ACP's for really good performance.

Once you do this.. you'll have filesystems that have components that are totally spread out and using more "spindles" inside the array.

And to maintain top notch performance.. do remember to observer the spread out rule when growing your volumes.

I've applied the above scheme to most Hitachi's and XP's I've encountered and the result were just fantastic..


HTH and Good Luck.

[Alzhy]

But then again.. the issue with very large filesystems is when you decide to push them to tape. You will have to instruct the backup software to stream each file or subdir of your big filesystem to a different tape drive.

But with disk based solutions these days.. it really does not matter. Also... since split-mirror backups are more often utilized these days - it does not matter if it takes 24 hours to push one filesystem to a lone tape drive as long as you can isolate it -- ie. after mirror splitting.. present the disk/LUN on to a different server...

[Bob Sobey]

Thank you all VERY much.
Clay,
My manager asks if you can give us a idea of the % better/worse regarding the "...no significant difference..." between the databases in your sandbox test.
Thx
Bob

[A. Clay Stephenson]

I'm a physicist. When I say no significant differences I mean just that. This was several years ago on Oracle 8.0.6 under 11.0. I let the DBA's create several batch sql scripts to do updates, inserts, and selects and timed the results. The total size of the database was around 60GB. What we observed (and to the DBA's surprise) was that the variability of repeated runs of the same script under the same disk layout exceeded any variability that could be attributed to disk layout. In short, it mattered as much (if not more) what else the box was doing at the time (and here, I'm referring to routine UNIX houskeeping activity) than how Oracle was spread out.

Not surprisingly, the first runs of any given script tended to be the slowest because those saw the smallest fraction of
buffer cache hits (both UNIX buffer cache and the array cache); significantly, the first runs under either disk layout took about the same time.

My tests (your mileage may vary) indicated that I couldn't measure any differences in the two vastly different layouts BUT one method was vastly easier from an administrative perspective.

There is no doubt that if this were just a bunch of disks then the more the merrier (and you would see measurable differences) but the array does all of this for you as long as you use all the available i/o channels.

I will add one more thing that you should be aware of. If you reduce the number of LUN's, host-based performance tools like Glance will tell you you have a disk bottleneck. Glance has no way of knowing that what it thinks is one disk is actually 10 physical disks; all it knows (and can know) is that a herd of i/o is going through one device. You can "fix" this problem by carving out more LUN's but on most arrays the actual throughput is the same.