From: Jeff Lightner (jlightner@water.com)
Date: Mon Dec 06 2004 - 11:16:12 EST
All,
There were a fair number of responses on this one.
ORIGINAL QUESTION:
Is there any value in increasing buffer cache for Oracle databases when
using EMC Symmetrix?
If so are there guidelines for how much to increase it?
PLEASE: Do not respond with dynamic vs. static kernel parameters or HOW to
increase it or thrashing concerns if it is increased as I am aware of these
things. The questions are aimed at understanding WHY increasing it would
be of value.
BACKGROUND:
Based on information gained at HP World some years back and later
reconfirmed in this forum for 11.0 we are reluctant to set system buffer
cache beyond 300 MB. This is because our understanding is that the caching
algorithm isn't efficient beyond that level (at least in 10 and 11.0 -
haven't heard about 11i). Also our understanding from our DBAs is that
Oracle recommends not using system buffer cache as it has its own caching
mechanism. On top of that the Symmetrix itself has its own cache.
Despite all of this it is being suggested by an EMC person that we increase
our buffer cache at the OS level. He believes this will allow us to hit the
"normal" 90% rcache level he would expect to see.
The 11.0 system is running Oracle 8x on vxfs filesystems built under Veritas
Volume Manager volumes.
TIA and I will summarize.
SUMMARIZED RESPONSES:
1) Guideline for buffer cache (independent of databases) now indicate
600MB to 800MB is the upper limit range for 11.0 and 11i. A couple of
responses indicated HP performance classes are suggesting 400MB. The 600MB
to 800MB came from Bill Hassell (a/k/a "The Man"), among others, so this is
likely the correct one. It can and should be set lower for a database only
server (see note 2b below).
2) Most folks concurred that OS buffer cache is not the way to go for
Oracle. It is much better to:
a) Set the options mincache=direct, convosync=direct on the filesystem
mounts (for DATA filesystems only - not for the binaries or other stuff) so
that it does not attempt to use OS buffer cache at all. (Several people
suggested double buffering is a bad thing.)
b) Insure that the SGA used by Oracle is as large as possible. If it
is a database only server it is suggested to actually reduce buffer cache in
favor of allowing more memory for the SGA. The SGA handles buffering for
random reads done by Oracle much more efficiently than the OS buffer cache
would.
3) There were various responses indicating performance is more of an
art than a science and/or suggesting other things to look out from layout to
tuning within Oracle itself. My question was mainly aimed at checking what
seemed to me to be irrelevant focus by EMC on OS buffer cache. The
responses by and large agreed this was not a good focus as indicated above.
However due to these responses I'll include Rita Workman's response for the
archives as I think she summarized it well. (Note that her comments about
lvols/pvmove wouldn't be relevant to Veritas Volume Manager but would for HP
LVM) :
Performance takes on many areas, and obviously if you're talking to EMC your
concern is I/O.
Take a look at each change that you make, and then make the changes
one-at-a-time, and note the improvement.
The first thing I would do is take note of where everything IS on my disks.
In other words, where are log files writing out to....and what else is
hitting the same disk. Which dbf files get the most activity....and what
else is hitting the same disk. See my point.
You may need to move things around to improve performance first from the
hardware level, by moving your lvols around on disk. You can do this with
things running, although I don't recommend it. I prefer to have my systems
down and quiet when I'm moving data in mass. But the choice is yours.
Take a look at pvmove....and see if this will buy you some improvement.
Next...and this is where I think those folks are attempting to (albeit
inaccurately) point you.
Look at how your things are set out...now by mount point. Read up on the
options you can use to mount your mount points. You can actually bypass the
buffer cache by utilizing options like mincache=direct, convosync=direct.
See my point ! Not increase the buffer cache, bypass
it......[READ,READ,READ] Take note that you must control what data is under
what mount point, as you don't want to mount everything this way.
But you should see improvement once you have your filesystems tuned as well.
Now here is one you'll have to consider with your Oracle folks and maybe
even mgmt. Exactly what are you logging ? Look closely what has been
turned on and decide. Are you doing Oracle transaction logging? That can
be a huge Oracle resource hog that will greatly impact your end user
performance.
Do you really, really, really need to do that kind of logging? Decisions...
And the last thing....look to your kernel parms and swap. I say this as the
last, as you will want to change only a couple parms here at a time. This
last tuning effort takes the longest to get things just the way you want.
And a lot depends on what your running.
Hope this has giving you some ideas to investigate. Remember, tuning is as
much an art as a science.
THANKS TO THOSE WHO RESPONDED:
Gary Paveza
Cindy Yoho
Ryan Green
Steve Illgen
Kevin O'Donovan
Steve Bonds
David R. Antioch
Daniel Copeland
David Lodge
Bill Ryan (extra thanks to Bill Ryan for answering follow up questions I
sent directly to him)
Tom Meyers
Dan Zucker
Jim Turner
Alex Vinson
David Lee Totsch
Bill Hassell
Magnus Anderson
Rita Workman
My apologies if I've left anyone out.
P.S. Nice to be back on the list after having been exiled to a Solaris only
shop for a couple of years. Also nice to see familiar names like Rita, Bill
H., David T. and others still being helpful as ever.
Jeffrey C. Lightner
Unix Systems Administrator
DS Waters of North America
678-486-3516
--
---> Please post QUESTIONS and SUMMARIES only!! <---
To subscribe/unsubscribe to this list, contact majordomo@dutchworks.nl
Name: hpux-admin@dutchworks.nl Owner: owner-hpux-admin@dutchworks.nl
Archives: ftp.dutchworks.nl:/pub/digests/hpux-admin (FTP, browse only)
http://www.dutchworks.nl/htbin/hpsysadmin (Web, browse & search)
This archive was generated by hypermail 2.1.7 : Sat Apr 12 2008 - 11:02:44 EDT