Striping and mirroring Oracle DB

[Jim Mulshine]

Does anybody experience good performance on Oracle databases using HP-UX 11.11 extent-based mirrored stripes with a physical extent size of 8Mb? Does this extent size have any impact on performance?

I ask this since I've read that 1-2 Mb stripe (extent) width is optimal for Oracle database files. However, in my case it is not possible to use less than 8Mb when using extent-based mirrored stripes due to the size and number of disks that I have.

Today we're using conventional block-based striping with a stripe width of 1Mb, and we're getting very good performance. Now we want to have both striping and mirroring. Since mirroring of logical volumes created with block-based stripes is not an option we are thinking of using the extent-based technique.

[A. Clay Stephenson]

8MiB is much too large to efficiently spread the i/o for good performance. It's actually uncommon these days to see anybody running Oracle that is not using a disk array so that you get mirroring (or some form of redundancy implicitly.

In almost all cases, the data redundancy is more important than performance (if you can't have both) so choose a PE-size that is convenient. After you get above 2MiB (and that only with very fast hardware), you really aren't going to see any benefits from extent-based striping because the i/o is not switched between the various SCSI paths fast enough.

[TwoProc]

I agree with A.Clay - that is that those large extent based stripe sizes in and of themselves (and by themselves) don't do much in terms of speed access to a set of jbod disks. In fact, when used in place of proper layout and planning, they probably hurt. That is, instead of planning and thinking out a proper strategy, just take a boat load of disk, throw it in a single vg, and extent based stripe across all across it (basically close your eyes and it's all OK theory :-) ).

Except for one thing, if your goal is to "try" and get as much use as possible across all of the media, then it can be used help you "spread the stuff around" lots of disks. One theory is to spread your databases across lots of disks (all) evenly, using striping, in your case, extent based stripes. Sadly, I've tried this, (one big disk set of locally attached disk, and large extent based stripes across everything) - and it really doesn't work , in fact it, without measuring it, it for us at least, seems to perform pretty badly.

What does work "a bit" better is to cut up your disks into smaller volume groups so that the extent sizes stay smaller. And, what I mean by "a bit" better, is that while certainly better than the big extent big vg striping theory - this method (more vg's smaller striped extents) seems to be at least not so bad, maybe even a bit better than plain old mirroring.

However, NOTHING works nearly as well separating your I/O areas properly, paying attention to your scsi priority schedule, etc. Many people find this hard to do with the availability of large capacity disk drives. That is, if you're standing up a 30 to 100Gig database, you might be tempted to use just a couple of disks and put the whole thing on a couple of those mirrored - and you'd be very dissapointed. For Oracle to run with any kind of a load, it needs controllers and disk spindles, the more the merrier. To make this all affordable, you can often buy used older Ultra3 SCSI containers and load them with lots of older smaller sized disks which can be bought refurbed for cheap. This gives you more hardware to give to Oracle, and have less problems with performance.

That being said, the best way to increase I/O is ...

not to have any.

Tune your queries, intelligently cache all the stuff that would require reloading and reparsing, and keep those highly hit tables in memory. This has the best and biggest positive impact on I/O performance, and in the case when Oracle caches are not appropriately sized - ram is cheaper than disk and controllers, to a point that is (law of diminishing returns).

So, my recommendation is to do both, make sure that you don't have undo, rollback, redo, and archive logs on the same disk spindles, controllers (when possible) etc. And now you can make those disk sets smaller extent based stripes across their sets of disks (with separated vgs).

[Geoff Wild]

For our SAP DB, we also striped at the LVM level - can't remember the exact performance increase we got - but I know it was worth it.

We present 74 GB LUNs from an EMC frame - which are striped and mirrored on the fram.

Then in my VG, I have 5 lvols, each consisting of 7 LUNS, and striped.

# lvdisplay /dev/vg61/lvsapd01
--- Logical volumes ---
LV Name /dev/vg61/lvsapd01
VG Name /dev/vg61
LV Permission read/write
LV Status available/syncd
Mirror copies 0
Consistency Recovery MWC
Schedule striped
LV Size (Mbytes) 531328
Current LE 33208
Allocated PE 33208
Stripes 7
Stripe Size (Kbytes) 128
Bad block NONE
Allocation strict
IO Timeout (Seconds) default

Created like so:

lvcreate -L 531328 -n lvsapd01 -i 7 -I 128 /dev/vg61

For mounting, I added these options (need Online JFS):

mincache=direct,convosync=direct


Rgds...Geoff

[Jim Mulshine]

I can't get mirrored LUN's from our Storage folks for some reason. If I want mirroring I have to use LVM. And I want mirroring. And striping.

I have been measuring disk read i/o by reading database files with dd in a script and measuring the total time. The time it takes in the prod system when using conventional block-based striping with 1Mb stripe width is about 32% of the time it takes when there is no striping.

Yesterday in our "prod-identical" test environment, which has disk LUNs allocated from the same disk subsystem as the prod system, we converted from no striping/mirroring to extent-based mirrored stripes using 8Mb extents. The dd script runs in about 34% of the time it did before the change, showing times equivalent to the prod systems (which uses conventional stripes, no mirroring, 1Mb stripe-width).

How would you interpret these results? I figure it's pretty safe to convert the prod system once again, to the same config as the test system, while barely affecting performance.

dd script times...
(no striping or mirroring)
prod 39.6m
test 40.0m
(conventional striping 1Mb, no mirroring)
prod 12.6m
(mirrored stripes 8Mb)
test 13.5m

[TwoProc]

Good numbers, for testing linear large block sequential i/o.

Try other stuff too.

The following is a link to a program called Bonnie. It has been around forever and is small and compiles easily and you can get more information on what you're seeing. I've compiled it and used on in several different types of Unix flavors with no problems.

It's good to have in the admin toolbox anyways.

http://www.textuality.com/bonnie/download.html

[Geoff Wild]

As in this post:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1175629

If you want true stipped based mirroring, then you will have to upgrade to 11.31.

I will be upgrading our prod SAP/DB servers to 11.31 in February...so far on test - it runs flawlessly...

Here are the release notes for LVM and MirrorDisk UX on 11.31:

http://docs.hp.com/en/5991-7518/5991-7518.pdf



Rgds...Geoff

[Hein van den Heuvel]

Standard disclaimer: 'It depends'.
You knew that, and it was not what you wanted to hear, but it is still true!

Is this application OLTP-ish or datawarehouse like?
Highly concurrent or single long running jobs?
99.9%read or a fair amount of writes? Parallel queries?

With a large stripe (extent), you run the risk of overloading a single disk too long.

On the write-side, the controller provided write back cache (assuming there is one!) might have a fixed per unit allocation and it can fill up writing more than 1mb to a single unit at which point you'll have to wait for the disk. (think REDO log destination!).

On the read side, for large stripes you might effectively only have one disk active most the time. A different disk per stripe of course, but just one each time. So that disk migh quickly deplete its track-read-ahead data and you'll have to wait for real IO. Then at the end of the stripe, the disk might fill the read ahead cache, but it may be too long (7, 8 stripes worth?) before the reader comes back and other use may have now voided the attempt to read ahead.

With a small stripe, the ideal read scenario might be: get some data, triggering read ahead, move to next extent, repeat untill at first disk. This disk now has the read ahead. Use it, trigger further read-ahead and move on, often no more having to wait for actual disk reads. I don't know what a typical drive read-ahead buffer is in your case, but it is unlikely to be as big as 8mb(which is often the size of a whole drive cache).

This is what your dd test would measure (btw... what was the IO size tested?).
However, I suspect that the bulk of your Oracel IO is NOT strictly sequential (except for the already mentioned REDO log). Thus the effect will be softened a lot and the 1mb vs 8mb question will not show the 300% difference you measured but is more likely to be less than 30%, probably less that 10% difference. Still... I'll take any improvement over 2% any day!

>> I can't get mirrored LUN's from our Storage folks for some reason. If I want mirroring I have to use LVM. And I want mirroring. And striping.

That answer is just not good enough. I hope they provided details and argumentation. The storage foloks are there to serve, not to rule, right!? Be sure to insist on the storage folks explaining why. Is it about single points of failure? Controller functionality? Disk provisioning?
Hey, you are going to mirror right?... it can be done the easy way (hardware) or the hard way (software) , but it will eventually happen. no?. You typical storage controller is better equiped to mirror and stripe than the OS is.
What controller is it? How much (battery back-upped) cache? How many drives behing it?

Fun stuff!

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

[Jim Mulshine]

Thanks for all the replies to my question!