kernel config advice? (dbc_max_pct)

[Gordon Morrison]

Hi,
We hav an rp7410 running 11.11 with 4 800MHz CPU's & 12 GB RAM. It hosts an application using Oracle databases and typically has 200 - 300 users during the day. Memory utilisation is usually 90% - 100%, but there's hardly ever any swapping going on. Users sometimes complain of slow response, but whenever I've looked at the system in response to one of these complaints, everything looks OK.

I have just noticed today that Buffer Cache is maxed out at 1.18GB (dbc_max_pct is 10 and dynamic buffer cache is enabled) It has been at 1.18GB since I started up gpm this morning.
I have 5 questions:
1) Is this normal? (i.e. Buffer Cache hits dbc_max_pct and stays there?)
2) How can I tell whether or not this is a problem?
3) Is there a command I can use to periodically output the used buffer cache size to a file so I can build up a longer-term picture of what it's doing?
4) Would increasing dbc_max_pct be likely to improve the performance, or (bearing in mind the current RAM utilisation) drive it into the ground?
5) Or do we need more memory? (I've already recommended increasing RAM on 2 other systems, and I don't want to sound like a one-song band!)

Thanks

[Steve Steel]

Hi


Oracle is using its own buffer caching so what you are seeing is normal

for commands
look at the following web site

ftp://eh:spear9@hprc.external.hp.com/memory.htm


Memory Usage (What is using all of the memory?)

by:eric.herberholz@hp.com

Last modified: February 17, 2005

Full document is available at external ftp site: ftp://eh:spear9@hprc.external.hp.com/memory.htm
The "Table of Contents" is available in ITRC doc id MEMORYKBAN00000975




Table of Contents

--------------------------------------------------------------------------------

Introduction
The memory line in the output of swapinfo
swapinfo fully explained
A. Review Buffer Cache size

B. Monitoring Memory Usage

1. plain memory (malloc): ps, procsize, kmeminfo, and glance

2. shared memory (shmget): ipcs, shminfo and procsize

3. memory mapped (mmap): shminfo and procsize

C. OS Memory Leaks/Hogs

1. Check for OS memory leaks with kmeminfo.
2. Check for known memory hogs (e.g. JFS inode cache)

D. Application Memory Leaks
E. 32-bit memory limitation
F. SHMEM_MAGIC
G. How much data space can application get?
H. Memory Windows [ details patches how to check for Memory Windows memwin_stats ]
I. Memory Usage as seen in "dmesg", "swapinfo", "top", "sam", and "glance"
J. Troubleshooting â Not enough spaceâ , "out of memory", and "Not enough core"

Summary (i.e. memory report download info)
References

--------------------------------------------------------------------------------


There are monitoring tools there and a good explain of buffer caching

I would put dbc_max_pct = dbc_min_pct at 10%
and work on

use kmeminfo to decide if you need more memory


Steve S

[Steven E. Protter]

There should not be any swapping going on.

Oracle has its own buffering system. Having a lot of buffer merely double buffers, which provides little performance bounce. You can probably get away with less buffer.

1) Somethingto watch for the difference between dbc_min_pct and dbc_max_pct . Changing this value burns a lot of CPU cycles. We commonly set min at 5 and max at 7. WE pick a tight range of values to make our dtabase run well.

2) If your users are reporting slow response, then you have a problem.

3) There are sar commands that will let you monitor it precisely. sar -b

example sar -b 10
measures buffer cache activity for 10 secons
sar -b 1 10
measures for 10 1 second intervals.

4) Check the range, don't change it yet

5) if you are actually swapping a lot, not just reserving swap, then you need to either increase memory or decrease demand for memory.

SEP

[Jean-Luc Oudart]

I suppose I would start with Oracle monitoring and check with the help of statspack reports the possible contentions (at Oracle level).
Also, you can check in Glance (or gpm) the wait percentage for these oracle processes. Is it high on IO, priority , other ?

What is the memory allocated to Oracle ?

The last thing you want is the OS paging out.

Regards
Jean-Luc

[Bill Hassell]

The buffer cache size will incrase to the maximum and stay there until processes need more memory than is available. Then the cache will back off in size. But as mentioned, Oracle has it's own cache which is more effective in caching needed data, so keep the dbc_max_pct between 5-8 % (around 600-800 megs). That will free up a bit of RAM but it's not going to have much of an effect either way (larger or smaller).

By far the biggest improvement is profiling big SQL procedures and seeing if partial index searches are occurring, or if temp sorts take place on disk (and not RAM). If your SGA is only a few hundred megs, then you definitely can see improvement once the DBAs start using the extra SGA space efficiently.

Now if there are multiple instances of Oracle, there will be a point where paging occurs (with largher SGAs) and then all your benefits of large SGAs will be negated by paging. That's when you add RAM. Adding RAM without changing Oracle will have virtually no effect.

Since you have Glance, you also have gpm which can be set to watch the system over many minutes to many hours, and the MeasureWare stats are available to analyze using the extract command. You can export the data from extract to a spreadsheet for weekly/monthly reports.

[Gordon Morrison]

Thanks to all,
Steve; That URL looks good but will take some time to digest. Is there a printer-friendly version of it anywhere?

Steven; dbc_min_pct is 5, dbc_max_pct is 10. By following the formula for calculating the optimum value for dbc_min_pct at http://www.docs.hp.com/en/939/KCParms/KCparams.OverviewAll.html I came up with a rough figure of 6.6666667, which I figure is close enough for jazz. Swapping is extremely rare on this machine, and then only for a few minutes at a time, and never (so far) at the same time as performance problems are reported. I've attached some output from sar -b 1 10 and kmeminfo and I can't see anything obvious, but then I don't really know what to look for. Any insights on what this data means would be appreciated.
Jean-Luc & Bill; I'll have to have a word with a DBA. In the meantime, wait states you requested are:
IO : 0.04%
Priority : 0.01%
Other : 8.27%

What's an SGA?

Thaks again

[TwoProc · Accepted solution]

Gordon, If I've got cache to use - I usually prefer to give it to Oracle in db_cache_size, rather than give it to HP for further use. In my comparisons with Mercury tools (to standardize tests for comparisons) I get more bang for the buck. Also, make sure that before you give more for cache buffering you've already taken care of the shared pool first. Of course, I agree with the others that the most effective way to reduce I/O problems is attack the list of worst-running sql code. This will make hardware go a lot further than most anything else you can do. But, once this is done - good buffering and keeping the system off of the physical disk I/O's is your next best bet.

[Gordon Morrison]

Another related question:
Elsewhere in these forums, I found this command:
UNIX95= ps -e -o ruser,vsz,pid,args
to report on which processes are using the most memory.
Can anyone clarify exactly what the VSZ column means, and what it does and doesn't include (data space, etc.)? The ps man page is a bit fuzzy about whether it's in KB or pages.
I found a script to sort it's output, and if VSZ is in KB, then the top 700 processes on this box are using 37GB, which is quite impressive for a box with 12GB, and 1400+ processes!

[Bill Hassell]

SGA = Oracle's System Global Area, which is a shared memory area. Shared memory can be seen with: ipcs -bmop. In ps, -o vsz is the core image of the process in Kbytes (from the man page). In general, it is the resident set size (RSS in Glance). Now Glance does a very complex set of calculations to figure out RSS while ps is the Unix classic utility and is limited in how sophisticated it can be.

Processes don't simply use a fixed amount of RAM. The closest (stable) measurement of a process is the data area since it is local to the process and is often the largest portion. The executable code (unchanging instructions, no data) is virtually always shared with other copies of the same program. So accounting for shared text area is tricky, typically it is text-size/copies so for 10 copies of the program, each will be computed as having 1/10 of the text-size. There are also shared libraries which are even more complicated because not all programs use all the available shared libraries in RAM. Shared memory is the same problem, only more complicated. Programs can request a shared memory segment, then tell other programs about it and terminate. And programs may open and close the segment several times, which makes assigning the shared memory area mostly guesswork. So the rule is to add the private data areas and a portion of the shared areas.

So in general, vsz (and RSS in Glance) is a best guess for each program's size. It is not uncommon to find a lot more memory used than is present in the system (37Gb used vs. 12Gb installed) since HP-UX is a virtual memory design. You'll probably find swapinfo -tm reporting some significant swap usage. In an interactive system with 200-330 users, paging out to swap is probably not a big issue unless the page rate is high (2 digits or more for a long time). Use vmstat (or vmstat -s | grep out) to monitor the page-out rate for the busiest periods. Page-ins are anot meaningful because they are intermingled with new process starts. Once page-outs are more than 20-30 for long periods, then you'll have a case for more RAM.

[Bill Hassell]

And for the DBAs, some details on SGA management and fine-tuning:

http://www.oracle.com/technology/oramag/webcolumns/2003/techarticles/burleson_auto_pt1.html

http://www.dba-oracle.com/oracle_tips_sga_regions.htm
http://www.dbazine.com/burleson1.shtml

(Don Burleson has a book on tuning Oracle)

[Gordon Morrison]

I spoke to a friendly DBA, and preliminary checks show that Oracle is configured just about as well as it could be. But some of the SQL code running on this machine has been around since version 7, and we're now on version 9 which does things a bit differently, so that code could more than likely be optimised for better performance - Good call, John!

Bill; Thanks for the explanation, that makes it as clear as it's ever likely to be. I've passed on your Oracle URL's to my DBA.

I think that just about covers it, but I'll leave this thread open, as it's such a huge subject, I'm sure someone will be able to add something of value in the future if they feel so inclined.
Thanks to all!