HP-UX 11.11, Oracle 10g, high memory usage

[Greg OBarr]

Hi everyone,

I am running an rp5470 (L3000) with HP-UX 11.11 and Oracle 10g (10.1.0.4). I am seeing some high memory consumption lately and I don't think I have all my kernel parameters setup correctly. Please see below and any suggestions would be appreciated.

Total system memory: 8 Gb
Oracle using Automatic Shared Memory Management
Total SGA Size : 4592 Mb
Oracle Shared Pool : 2888 Mb
Oracle buffer cache: 2112 Mb

dbc_max_pct 50 - 50
dbc_min_pct 5 - 5

Not certain what dbc_max_pct should be set at, but from what I've been reading in ITRC, most seem to have it lower.
----------------------------------------------
Swapinfo:
# swapinfo -tam
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 1207 2889 29% 0 - 1 /dev/vg00/lvol2
dev 4096 339 3757 8% 0 - 0 /dev/vgP0/swap
reserve - 6581 -6581
memory 6236 2048 4188 33%
total 14428 10175 4253 71% - 0 -
---------------------------------------------
IPCS:
# ipcs -moba
IPC status from /dev/kmem as of Wed Jul 12 10:31:57 2006
T ID KEY MODE OWNER GROUP CREATOR CGROUP NATTCH SEGSZ CPID LPID ATIME DTIME CTIME
Shared Memory:
m 0 0x411c07ba --rw-rw-rw- root root root root 0 348 924 924 23:02:36 23:02:36 23:02:29
m 1 0x4e0c0002 --rw-rw-rw- root root root root 2 61760 924 926 21:39:17 23:02:36 23:02:29
m 2 0x412009b5 --rw-rw-rw- root root root root 2 8192 924 926 21:39:17 23:02:29 23:02:29
m 4 0x06347849 --rw-rw-rw- root root root root 0 77384 1693 28476 9:17:39 9:17:39 23:03:26
m 1205 0xffffffff --rw-r--rw- root root root root 0 22908 1672 1672 23:03:28 23:03:28 23:03:28
m 89406 0x00000000 --rw-r----- oracle10 dba oracle10 dba 380 1839104 11486 24657 10:31:33 10:31:53 1:50:33
m 10807 0x00000000 --rw-r----- oracle10 dba oracle10 dba 380 2415919104 11486 24657 10:31:33 10:31:53 1:50:33
m 4208 0x00000000 --rw-r----- oracle10 dba oracle10 dba 380 1224736768 11486 24657 10:31:33 10:31:53 1:50:33
m 609 0x7b83956c --rw-r----- oracle10 dba oracle10 dba 380 1207971840 11486 24657 10:31:33 10:31:53 1:50:33
m 10627810 0x00000000 --rw-r----- oracle10 dba oracle10 dba 0 1839104 8028 8028 12:47:34 no-entry 12:47:34
m 3011 0x00000000 --rw-r----- oracle10 dba oracle10 dba 0 620756992 8028 8028 12:47:34 no-entry 12:47:34
m 4212 0x00000000 --rw-r----- oracle10 dba oracle10 dba 0 150994944 8028 8028 12:47:34 no-entry 12:47:34
m 1997544013 0x00000000 --rw-r----- oracle10 dba oracle10 dba 0 33554432 8028 8028 12:48:22 no-entry 12:48:22
m 253637414 0x00000000 --rw-r----- oracle10 dba oracle10 dba 0 33554432 8028 8028 12:50:46 no-entry 12:50:46

Not sure if some shared memory segments are no longer attached. Should the PIDs shown above be running processes now?

Thanks in advance!

[Greg OBarr]

One more thing: Is anyone else running Oracle 10g? When I run "top", the SIZE of the programs seems much larger than it did in 8.1.7.4. Some are over 4Gb.

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
0 ? 8231 oracle10 154 20 4864M 2524K sleep 117:23 7.19 7.18 oraclePROD
0 ? 22118 oracle10 154 20 4858M 4368K sleep 5:09 4.28 4.28 oraclePROD
0 ? 3592 oracle10 154 20 4856M 3532K sleep 0:00 4.94 2.49 oraclePROD
0 ? 36 root 152 20 2144K 2144K run 535:11 1.91 1.91 vxfsd
0 ? 0 root 127 20 32K 0K sleep 221:18 1.75 1.74 swapper
0 ? 2125 oracle10 154 20 4858M 5096K sleep 0:05 1.57 1.56 oraclePROD
0 ? 24548 oracle10 154 20 4858M 4852K sleep 0:05 1.04 1.03 oraclePROD
0 ? 20526 oracle10 154 20 4857M 4356K sleep 1:19 0.82 0.82 oraclePROD
0 ? 3422 oracle10 154 20 4856M 3932K sleep 0:15 0.77 0.77 oraclePROD
1 pts/1 29844 oracle10 152 20 789M 64660K run 7:19 0.69 0.69 java
0 ? 2575 oracle10 154 20 4857M 4420K sleep 0:02 0.64 0.63 oraclePROD
1 ? 21082 oracle10 154 20 4857M 4436K sleep 0:51 0.46 0.46 oraclePROD
1 ? 21661 oracle10 154 20 4857M 3968K sleep 0:56 0.43 0.43 oraclePROD
0 ? 29896 oracle10 156 20 4857M 4084K sleep 11:57 0.38 0.38 oraclePROD
0 ? 17518 oracle10 154 20 4858M 4024K sleep 5:45 0.37 0.37 oraclePROD
0 ? 3192 oracle10 154 20 4856M 3344K sleep 0:00 0.32 0.32 oraclePROD

[Greg OBarr]

And one more thing I forgot, the vmstat output. It's been higher than this, but I still have a few page-ins, but no page-outs.

# vmstat 1 10
procs memory page faults cpu
r b w avm free re at pi po fr de sr in sy cs us sy id
1 0 0 492255 17748 125 5 2 1 0 0 87 1538 8020 897 28 4 68
1 0 0 492255 17700 277 1 1 0 0 0 0 646 6763 491 32 6 62
1 0 0 493525 17696 223 0 1 0 0 0 0 771 6776 581 25 3 73
1 0 0 493525 16721 294 99 12 0 0 0 0 857 9663 707 52 22 26
1 0 0 493525 17696 278 187 10 0 0 0 0 788 8923 671 22 1 77
1 0 0 493525 17695 223 149 8 0 0 0 0 749 8341 603 6 1 93
1 0 0 493525 17695 178 119 7 0 0 0 0 732 7811 562 15 1 84
1 0 0 493525 17695 149 95 8 0 0 0 0 808 8176 596 29 2 69
1 0 0 493525 17646 119 76 6 0 0 0 0 840 7610 599 7 0 93
1 0 0 493525 17646 96 60 4 0 0 0 0 783 6397 536 1 1 98

[Steven E. Protter · Accepted solution]

Shalom,

This is a problem:

dbc_max_pct 50 - 50
dbc_min_pct 5 - 5

Set the dbc_max_pct to 6 or 7.

This controls buffer cache, which double buffers oracle. Better to be low and leave resources open for the Oracle SGA. The Oracle SGA is more efficient than the OS for improving oracle performance.

SEP

[Greg OBarr]

Thanks Stephen! I have more question. Where is the "double buffering" done? I have my Oracle filesystems mounted so that there should be no vxfs buffering involved.
i.e.:

/m08 on /dev/vgP0/lvol8 ioerror=mwdisable,mincache=direct,delaylog,nodatainlog,convosync=direct

I guess the problem is that I don't know what dbc_max_pct means. Is it "database cache" or "double buffer cache" or what?

[Greg OBarr]

Oops, sorry... Steven, not Stephen.

[A. Clay Stephenson]

Using those mount options, you are not double buffering -- although double buffering may not be a bad thing. Since you are bypassing the buffer cache for Oracle (and I assume this box's primary task is as a database server) then I would set the buffer cache somewhere in the 600MiB range by using a static buffer cache -- this is done by setting bufpages to a non-zero value and will remain fixed regardless of the amount of physical memory in the box. bufpages=153600 will give you a buffer cache of 153600 X 4KiB = 600MiB.

This will also make overall tuning easier because one of the big variables is now a constant.

[Greg OBarr]

Thanks, Clay. When you say buffer cache, are you referring to Oracle buffer cache, or vxvm buffer cache? I am using Automatic Memory Management in Oracle, and it has automatically set the Oracle buffer cache to 2112 Mb. We run pretty much 24x7x365 here and I can't shut the instance down very often. Next shutdown opportunity (2 months?) I'm going to bump the RAM up to 16 Gb and I'd like to increase the Oracle buffer cache to about 4 Gb at that time - it does wonders for performance. If you're talking about Oracle buffer cache, why would I set it to 600 Mb rather than 2112 as the Oracle AMM has set it to?

[A. Clay Stephenson]

It should have been obvious when I referred to "bufpages" that I was talking about the UNIX disk buffer cache. The idea is that when using fully cooked files with Oracle is that Oracle first does a logical write to its own buffer in the SGA. At some time later that logical write in the SGA becomes a physical write (ie a write() UNIX system call is made). That, in turn, then becomes a logical write in the UNIX buffer cache and at some time later that finally becomes a physical write to disk (although to be accurate, in most cases that becomes a logical write to the on-board array cache or disk cache which finally becomes an honest-to-God physical write on the disk itself). So you could have double, triple, or quadruple buffering depending upon the underlying hardware.

The vxfs mount options convosync=direct,mincache=direct
completely bypass the UNIX buffer cache
so that (with respect to UNIX write()/read() system calls) every logical i/o is a physical i/o. The same effect is achieved using raw devices.

Because of your high-memory usage and because you are bypassing the UNIX buffer cache for the database, I am suggesting
that you reduce the UNIX buffer cache to something below the typical 11.11 sweet spot of 800-1600MiB. 600MiB should be a good value -- remember you still have tons of i/o that is not database related and that should use the buffer cache.

Finally (and after you get more memory), you just might find that that dreaded, terrible "double-buffering" ain't so bad for Oracle after all. You might find that a large SGA (several GiB) combined with about 1600MiB of UNIX buffer cache and without the convosync=direct,mincache=direct mount options actually performs better than the options which bypass the (UNIX) buffer cache. It's easy to change the mount options so gather some metrics; change the mount options; and gather more metrics.

[Steven E. Protter]

Shalom again,

My understanding of double buffering, is when the OS is buffering the disk and the Oracle SGA is buffering the database.

Your milage may vary, but Oracle is a little better at caching its data for good oracle performance.

Of course if its a multi purpose server you may want to go with another configuration.

I've had good results by freeing up system resources for my DBA and having the DBA consult with me about the SGA configuration.

The buffer cache does benefit other OS functionality and if it has any value at all there will be some double buffering.

My recommendation is based on making a choice with usually works.

My dbc_max_pct settings are also for another reason. Its extremely expensive in CPU terms when the system adjusts this figure and it can be a real drag on performance.

If your do a lot of i/o on database, kernel parameters such as vx_inode (do a kmtune | grep node I'm home and my system is shut). Leaving this as a zero value lets the system pick its own value and change it on the fly. These changes are also really really bad for system performance.

If choosing between a recommendation of A. Clay or myself (I see no conflict at this point) go with his first. Feel free to test mine, but he's got about 20 years more systems administration experience than I have.

I'll monitor the thread though its late here and hopefully can provide further assistance as you need it.

Have fun. Oracle tuning is an art. Thank G-d I've always worked with excellent DBA's. Even when I was the DBA and sysadmin (9 miserable, sleepless months) it wasn't so bad.

:-)

SEP

[TwoProc]

Greg,

The thing I noticed that was probably way out of whack was the following:

Total system memory: 8 Gb
Oracle using Automatic Shared Memory Management
Total SGA Size : 4592 Mb
Oracle Shared Pool : 2888 Mb
Oracle buffer cache: 2112 Mb

I like the size of your SGA, but you're probably way misallocated in what you're spending your little bit of cache on. Your shared pool (which stores primarily running code) is MUCH bigger than your buffer cache.

Now, this *could* actually be correct, if after some correct tuning it was determined that you actually have that much code in the shared pool at one. If this number has been determined after careful tuning - then fine, but I would think your box would run better if you started ratcheting down your shared pool size and move those memory areas over to the Oracle Buffer cache. I'd move 200 Meg or so over for starters and keep doing that until you see code start having to be reloaded from disk.

Also, you should take your dbc_max_pct variable ( as discussed ) down, and give that seriously reduced amount to the Oracle buffer_cache, leaving a bit of it for the general running of the server (as you said that you were tight on memory).

[Greg OBarr]

Thanks everybody.