Oracle 9i running on HP-UX with vpars, is Asynch IO possible? and other questions.

[Yoann Mainguy]

Hello all,

I'm a beginner on both Oracle and HP-UX. We have recently had installed the following system:

Oracle 9i (9.2.0.4.0)
running on HP-UX B.11.11 U 9000/800
using vpars A.02.03.02
on a RP7410
and a EVA3000 (which includes HSV100 controllers)

here is our /etc/fstab:
/dev/vg02/lvoracle /opt/oracle vxfs largefiles 0 2
/dev/vg03/lvdata /dbprod/data vxfs delaylog,largefiles,nodatainlog,mincache=direct,convosync=direct 0 2
/dev/vg03/lvindex /dbprod/index vxfs delaylog,largefiles,nodatainlog,mincache=direct,convosync=direct 0 2
/dev/vg03/lvroll /dbprod/roll vxfs delaylog,largefiles,nodatainlog,mincache=direct,convosync=direct 0 2
/dev/vg03/lvtempo /dbprod/tempo vxfs delaylog,largefiles,nodatainlog,mincache=direct,convosync=direct 0 2
/dev/vg03/lvredo /dbprod/redo vxfs delaylog,largefiles,nodatainlog,mincache=direct,convosync=direct 0 2
/dev/vg04/lvarch /dbprod/archive vxfs log,largefiles,nodatainlog,mincache=direct,convosync=direct 0 2
/dev/vg05/lvgenrx /dbprod/app vxfs rw,suid,largefiles,delaylog,datainlog 0 2

You can see we don't use RAW files for oracle, and Oracle is configured with disk_asynch_io=false. As far as I can tell the HPUX was never installed and configured with the async drivers.

We are a bit concerned that our system is not optimally configured, So I have spent a lot of time reading these lists and HP/Oracle docs, especially this very usefull doc "http://h21007.www2.hp.com/dspp/files/unprotected/database/HP3KOracle.ppt"


My questions are:
1-- Is Async IO possible on Vpars? (I read one doc that said no, but the install doc above does not seem to specifically say yes or no).
2-- Should we reinstall our Database and use a RAW file system for datafiles, indexes, logs etc? (The DB is currently 30Gbytes but we are planning for 300Gb so if we need to anything no matter how difficult - now is the time.)
3-- As you can see we use one "large LUN for everything execpt archive logs, we have talked about splitting that up to several LUN's. But from what I have read with this sort of hardware envoronment its not really necessary. What do you think?
4-- If anyone else has a similar config are there any glaring changes that we could make, let me know!! I would be very happy for any thoughts.

Thanks very much, Simon.

[Fred Ruffet]

1. I think Asynch IO is independant from Vpars, but maybe I'm wrong. You should be able to set it. You're right in the fact that HP3KOracle.ppt is a very good doc.

2. Setting Asynch IOs, using RAW devices and such things implies big changes and often gives only a few better perfs. It depends on usage. How many users are you plannings ? How many transactions ? I see you're using good parms for your Oracle FS and RAW will bring very few gain. (One thing : I'm not sure you should mount arch log FS with those options)

3. I don't thing it's necessary.

4. Not exactly same hardware, but... You seem to install a well tuned machine (keep some improvments for future :). Info we can give are only feelings about info you gave. There may be points we don't know so nothing replaces tests. You can test RAW and see if you see improvments and if you are feeling comfortable with it.

Regards,

Fred

[bhavin asokan]

hi,

1.i think async io is independent from vpars.
you can enable async io in hpux by setting fs_async parameter to 1.

regds,

[Yoann Mainguy]

Thanks for the rapid responses Fred, Bhavin.

In response to your responce Fred,

We have about 50 users, it is a transaction type DB that contains our stock infomation for a very large online reseller. So at the moment we make maybe 100,000 inserts and 100,000 updates per day, with a probable total of 500,000 transactions per day.

According to the doc:
---- Snip
"Tru64â s Advanced File System (clustered) comes within 5% of the performance of raw, but AFS is not available on HP-UX yet. For now, the best we can do is with JFS (with some non-default mount options), which is about 25% slower than raw (thus, raw is about 33% better than JFS). HFS is about 50% slower than raw (raw is 100% better than HFS, in terms of throughput).

Reminder: HP-UX will incorporate Tru-Cluster features in the future.
Note: In the lab, weâ ve seen huge performance increases by using raw. However, our Performance Consultants tell us that with real customers, this is not necessarily the case."
--------End Snip

I not sure how to tell the difference between JFS and HFS but I'm guessing we have JFS.

So we should be able to expect between 25-33% increase in throughput if we switch to RAW, - unless the "Performance Consultants" are correct.

Which whould I believe?

S

[Fred Ruffet]

You have OnlineJFS, otherwise you could have used convosync and micache options.

I tell you, you should run tests using RAW, in particular if you are not already in prod.
Such ratio as 25% may be something you obtain under certain circumstances. You may have gain, but maybe not that much.
Fact is that many people do not feel comfortable with RAW devices as they can't see the files, but it's a good thing.

Regards,

Fred

[Alzhy · Accepted solution]

1. vPars is just a partitioning scheme so you can have multiple OS instances running on systems that support it - to better make use of CPU and memory resources. Async-IO is a mode of I/O that is independent of the partitioning - you treat vPars as if it is a normal server.

2. It is up to you really whether you'd want to use RAW or continue to use Filesystem with Forced DirectIO (or with fstab entries of "log,mincache=direct,convosync=direct"). Contrary to what others may claim -- converting to RAW and managing it is really no big deal.

3. If the EVA is properly configured.. yes you can stick with that large LUNs.

4. The only change you need to make and it is a common "recipe these days" is to:

a. ensure your oracle datafile mount is mounted with "log,mincache=direct,convosync=direct"
b. ensure your dbc_max_pct is such that what is available for caching is no more than 800MB to 1 GB.
c. your SHMMAX is enough to hold your SGA (and that your SGA is sufficiently high)

[Yoann Mainguy]

Ok, thanks for the replys again.

Since we have Vpars I'll install a copy of the DB on another virtual server and make some tests with a RAW FS (It will be good practice anyway). I'll post the results if I get it done this year.

I'll have a go at activating Async IO on the current base, I'm asumming its not quite as simple as Bhavin suggests though "you can enable async io in hpux by setting fs_async parameter to 1"?

I should follow through all the documentation previously mentioned?

Simon.

[Yoann Mainguy]

Hang on!
Just going through the doc slowly, and on page 9 I find:
"Implementing Asynchronous I/O NOTE: Done ONLY if using RAW (not fs) storage"

Does this mean what I understand it means: I can't use Asynch IO if I'm using FS storage?

If this is the case I'm back to square one. Should I use Async IO on a FS based database?

Simon.

[Alzhy]

Olivier..

You read it right.. ASYNCIO is only available if you are using RAW storage.

There is however a Veritas Product called QuickIO (from its Veritas Vatabase Edition) that you can use ASYNCH IO on Filesystems. The QuickIO product mimics RAW IO on datafiles resident on Filesystems.

The HP version though which is is at version 3.0 or 3.5 is a bit behind than its Solaris/AIX versions but you can look at it if it fits your need. Pricey so your best bet is to probably stay with your current JFS/VxFS filesystems with the directIO mount option or switch to RAW and enable AsyncIO...