System Setup HP UX/11 and Oracle 8i

[Tracey]

Between myself and several consultants, we have come up with an uneasy truce on how our kernel and oracle init.ora file is setup. Our machine is a N4000 running 11.0, directly 50 users, indirectly via weblogix, another 60 users. 4 processors, 6GB memory, 12H autoraid, 2 gigabit ethernet cards using auto-port aggregation.

I'd like some independent opinions on our current HP/Oracle setup. Currently we have :

Sys Mem: 300 MB
Buff Cache: 1.5 GB
User Mem: 2.24 GB
Free Mem: 1.97 GB

VM: 629 MB
Active VM: 415 MB

[Tracey]

Here is the init.ora attachment.

Thanks for any help.

[Sanjay_6]

Hi Tracy,

you can get the oracle docs for hp-ux from this site. It list the specific kernel parameters that are must for oracle installation,

http://docs.oracle.com/database_mp.html

Once you have finished the installation. you can use the hp tuned kernel parameter set to tune your system for oracle database server system. To do that use sam --> Kernel Parameter --> Configurable kernel parameters --> Action --> Apply tuned kernel parameter set --> General OLTP / database Server system.

Apply this parameter set and rebuild the kernel. Hopefully, you should not have any of your oracle users complaining after thins.

Hope this helps.

regds

[James R. Ferguson]

Hi Tracy:

I see a couple of things that I'd strongly urge you to chance "up-front".

First, I'd reduce the 'dbc_max_pct' way down. With 6GB of memory, that's a very large chuck. Oracle is going to do its own buffering, so why impose it twice -- once by Oracle, and once by the filesystem buffer cache.

Second, I'd turn OFF 'fs_async'. You're risking data integrity to boost performance slightly when you set this on. I assume that your filesystems, including Oracle's tables and indices, are VxFS ones. In that case, mount them with 'nodatainlog,mincache=direct,con
vosync=direct' for performance gains.

Regards!

...JRF...

[Jim Turner]

Hi Tracey,

* Buffer Cache: I sure hope you're talking abou the Oracle SGA. If you're talking about OS dynamic buffer cache, then 1.5G is way, way, WAY too high. The first essential is OnlineJFS. You will want all of your data and index mountpoints to have options of "convosync=direct" and "mincache=direct" which will cause data from said mountpoints to bypass OS buffer cache. Oracle already caches this data. Why pay the price of having the OS do it to? I'd shoot for a dbc of no greater than 300MB or so. Anything more is just a senseless waste of RAM.

* LAN: 2 GB enet cards with apa? You shouldn't want for bandwidth! Just make sure your switch sets up the aggregate properly. I've had mixed results with leaving the HP-UX side on auto and the switch on auto. Your best bet is to hard-code both ends of the aggregate manually.

* Sys/User/Free Mem: I presume you're getting these figures from Glance from your running system. I don't see any red flags with what you are indicating.

* VM: As in swap? I'm having a difficult time understanding why you have any in active use (415 MB) if you don't have any memory pressure (Free Mem: 1.97GB). I think I'm misinterpreting what you mean by VM. The fact that you only show 629MB on a system with 6GB only leads me further in that direction. Since Oracle has an insatiable appetite for swap reservation, I wouldn't dream of running Oracle on a 6GB memory model without at least 12GB of swap (which can be real swap + pseudoswap).

You've got a pretty nice system there. My only fear is that the 12H AutoRAID is going to be a bottleneck for you. You've got a screaming system hooked up to a disk subsystem that can only do two channels of 40MB/s each. In the real world, you'll probably not achieve more than 10% of that or 4MB/s/chan. Watch your disk device i/o when they rev this thing up, and see what I mean. You're likely to drive both channels right to 100% and start queueing requests. If that happens, user response time will go straight down the toilet.

This very weekend I'm moving one of our big Oracle apps and db from a K570 with AutoRAID to an N4000 with VA7100. My testing so far has indicated that a 4-hour Oracle process on the K570/AutoRAID runs in 10 minutes on the N4000/VA7100. The K570 is being constrained by disk i/o to the AutoRAID. Everything else on the K looks fine.

Ah, one last piece of advice. Create two LUNs on your AutoRAID. Put both LUNs in one VG. Diddle with vgreduce and vgextend to give the LUNs unique primary paths. Stripe (64k) all of your LVOLs across both LUNs. Voila! Now access to anything on your AutoRAID lights up both paths equally. BTW, make your DBA's use an Oracle block size of 8k and a multi-block read count of 8 (8k*8=64k).

Cheers,
Jim

[James R. Ferguson]

Hi Tracy:

One other note. You currently have the kernel 'timeslice' set to 10. That's good. The standard DB templates that SAM provides for kernel tuning use a value of one (1) for databases. *Don't* use that value! You will waste time process switching far too often. The 'timeslice' value should remain at ten (10).

Regards!

...JRF...

[Steven Gillard_2]

I'll second the command on dbc_pct_max - 25% is much too high on a system with 6Gig of memory. I'd go for less than 10%, depending on what else is running on the system.

Are you running the 64bit version of Oracle? If so you can increase the SGA beyond 1.75Gig in the event that you still have a few Gig of free memory to play with...

Cheers,
Steve

[Krishna Prasad]

I guess you know by now that the buffer cache is to high.

Since the buffer cache will be reduced I would increase the SGA it looked like it was set to 600 MG. I would also increase the number of process in your init.ora. With a possible 100 users logging in and possible running more then one proc at a time 250 maybe to low...but it will not hurt anything being higher.

[Tracey]

Sanjay,

Thanks for your reply, we've followed the Oracle recommendations for the kernel parameters and from what I just looked at the tuned kernel on HP. The main differences I could see are:

Parameter Tuned Value (Ours)
UNLOCKABLE_MEM 2000 (0)
BUFPAGES 1992 (0)
DBC_MAX_PCT 2 (25)
DBC_MIN_PCT 2 (5)

Since we have about 50 direct connects, I hadn't wanted to set the Buffers so low, opinions?

[James R. Ferguson]

Hi (again) Tracy:

There are two ways to establish the buffer cache -- dynamically with 'dbc_max_pct' and 'dbc_min_pct' and statically with 'bufpages'. To enable the dynamic method, 'bufpages' and 'nbuf' must be set to zero. See here for more information:

http://docs.hp.com/hpux/onlinedocs/os/KCparam.BufPages.html

Regards!

...JRF...

[Jim Turner]

Tracey, JRF, et. al.

If you allow your dbc to be truly dynamic by having min -ne max, there is a danger. If you experience memory pressure, you'll most likely see vhand go apesh*t because he'll steal from dbc, pressure will drop a tad, the dbc will see pages on the free list and take 'em back, pressure again, vhand frees, pages on free list, dbc takes back, and on and on and on. I remember this vividly from our zesty session with Stephen "THEIR DEAD!" Cuillo in Chicago.

Whether it is done with nbuf, bufpages, or setting dbc min -eq max, I would definitely assign a static value to "dynamic" buffer cache. My personal recommendation is 300MB, but YMMV.

Cheers,
Jim

[Santosh Nair_1]

Tracey

Also keep in mind that dbc_max_pct (set to 25%, i.e. 1.5 GB) refers to filesystem buffer caches, which are different from database buffer caches (db_block_buffers=50,000 * 8k blocks ~ 400MB) (similar in concept but not quite the same). Besides, you shouldn't be relying on filesystem buffer caches for the database as it manages that by itself. So its very safe to lower the dbc_max_pct, perferrably to something like 5-10%. This will free up some valuable memory which can better be used by the database.

Hope this helps.

-Santosh

[Tracey]

Thanks for all of your responses and comments! The buffer cache is definately going to be reduced. :)

I do have OnlineJFS, so I will change the disk parameters as suggested. Do you think it is safe to do this while the DB is running?

the VM number is coming from glance, they are on the Memory report on the lefthand side, bottom. Originally this machine had only 4GB ram, and 8GB swap. We've increased the memory to 6GB, but the swap is still 8GB.

So far the 12H is doing well, it is set up with dual controllers/paths to the 2 LUN's that are setup with 64k striping (database is at 8k blocks too!)

SGA is currently set to 600 MB, I will suggest to the DBA's that we increase it.

[James R. Ferguson]

Hi Tracy:

With regard to your last question of "disk parameters":

You will need to edit /etc/fstab, unmount and re-mount your filesystems to add the VxFS mount options.

Regards!

...JRF...

[Tracey]

Another question: my original setup has swapmem_on=1, the tuned parameter set has swapmem_on=0

Which is better, on or off?

[harry d brown jr]

Tracey,

swapmem_on=1

is what you want


live free or die
harry

[James R. Ferguson]

Hi Tracey:

The recommendation from Stephen Ciullo (HP internals & performance guru) is *not* to enable pseudo-swap unless you are short on disk to use as swapspace. Enabling pseudoswap comes with some management overhead and some performance penalty. Ideally you will never use device swap and therefore will not be doing I/O to it.

Regards!

...JRF...

[Sanjay_6]

Hi Tracy,

I guess you should have swapmem_on=1 since you are having 6GB of RAM.

Here is a thread on swapmem_on,

http://docs.hp.com//hpux/onlinedocs/os/11i/kcparams/KCparam.SwapMemOn.html

Here is a link from ITRC supporting this,

http://us-support.external.hp.com/cki/bin/doc.pl/sid=856a8b95052e2bf2af/screen=ckiDisplayDocument?docId=200000024605263

Hope this helps.

Regds

[Wodisch]

Hello Tracey,

since you are already convinced on the HUNIX buffer cache (max of ~300MB for 11.00, max of ~800MB for 11i), you will not want Oracle to use the UNIX buffer cache for the data files, and/or the redo-logfiles at all. Hence keep those on file-systems of their own (nothing else there), and use options like "-o mincache=direct,convosync=direct,log". Since you can only use those with the Online-JFS (and you already have that), you can modify your "/etc/fstab" for the next reboot, but change them on-line with the additional "remount" option from the command-line (while everything is up and running). "glance" might show you then that your UNIX buffer cache is almost NOT used any more (which is what you what).
Chances are pretty low that you will be hurt by "swapmem_on=1", so go for it, but if you could give both a test, I would be glad to hear about your experience :-)

Just my ???0.02,
Wodisc

[Tracey]

I've reset the dbc_min and max to 5%, modified the oralce drives with the perviously mentioned parameters (and swapmen_on=1). Since no double buffering is going on, things seem to be going a tad speedier. In glance I am still getting the private virtual memory listed on the memory info page:
Total VM: 362.1 mb
Active VM: 140.8mb

Total free memory is 3.72GB. I am still also getting a high number of Page Faults, and Page Ins.