[Oracle-L][Long...] SHMMNI choice affect SHMMAX choice?

People,

I've been asked to document how a DBA should set values of some Linux kernel
parameters.

This is an easy task since it is a topic covered in the Oracle installation
documents.

While writing my document, my curiosity triggered some simple questions.

What is the purpose of SHMMNI?

If I set SHMMNI to a larger value,
can I lower SHMMAX to a value to support a given SGA?

I found this description of SHMMNI via google:

SHMMNI sets the maximum number of shared memory segments
which can be allocated at any one time.

The above description suggests that I can carve up my shared memory
allocation into a bunch of smaller pieces and I would be able to
have SHMMAX be a smaller number as I increase the value of SHMMNI.

Is this a correct understanding?

The Oracle docs suggest a SHMMNI starting value of 4096.

If I want an SGA sized at 2GB,
and if I have a Linux box with 3GB total RAM and 2GB RAM available for the
SGA,
and if I have SHMMNI set to 4096,
can I theoretically have SHMMAX set to a small value like
(2 * 1024 * 1024 * 1024 ) / 4096 = 524288
??

By the way here is the document I wrote up which discusses some kernel
parameters.

I'd welcome any comments (even if they are mean!) from DBA/SysAdmins out
there.



ARDBA Checklist

This checklist discusses six OS kernel parameters from the perspective
of an Oracle DBA.

We start the discussion with a decision.

-Decide on the size of various shared-memory structures in your database.

For 10g this should be an easy decision to make.  The 10g kernel is
sophisticated enough to dynamically and optimally set the size of
various shared-memory structures in your database.  The only guidance it
needs is a value for the overall size of the SGA.  You, the DBA, need
to decide on the overall size of the SGA for the database.  Common
sense dictates that you make the SGA as large as possible without
leaving the OS starved for memory.  Generally on small to midsize
hosts, an OS will perform well if it has 1GB of memory for its use.

A simple example illustrates this idea.  Suppose you have a host with
3GB of RAM.  A safe choice would be to allocate 1/3 of that RAM for
the OS and 2/3 for the SGA.  And, on a host with 128GB of RAM,
you could conceivably allocate 127GB (over 99%) of the RAM to the SGA.

For DB versions before 10g, the DBA is tasked with sizing various
memory structures within the SGA.  The smaller ones can usually be
ignored; we assume they will be sized correctly via default
settings: The three largest ones are listed below.

-Database Buffers
-Shared Pool
-Java Pool

A shared pool sized at 300 MB might be a good choice for an OLTP system
which
is supporting a website sustaining 100,000 transactions per day.
Unless your application depends upon Java stored in the database,
a Java pool of 30 MB should be plenty.  So if we have 2GB available
for the SGA and 330 MB is allocated to the Shared Pool and the Java Pool
we could safely allocate 1600 MB to Database Buffers.

Once you decide on the size of the SGA, you then need to configure the OS
kernel
to support the creation of the SGA.

On Linux, the kernel parameters which support the creation of the SGA are
listed below:

-SHMMAX
This parameter specifies the maximum (single) shared memory segment that an
Oracle kernel
can carve out of available memory on a host.  The unit of measure of this
parameter
is bytes.  So, following the example above, we would need to set SHMMAX to a
value of
2.01 x 1024 x 1024 x 1024 = 2158221066
On Linux we permanently set this parameter via an edit of the file named:
/etc/sysctl.conf
Linux Demo:
echo "kernel.shmmax=2158221066" >> /etc/sysctl.conf


-SHMMNI
We use this parameter to tell the OS maximum number of shared memory
segments
which can be allocated at any one time.
A value of 4096 is safe.
Linux Demo:
echo "kernel.shmmni=4096" >> /etc/sysctl.conf

-SHMALL
We use this parameter to specify the maximum number of shared memory pages
which can
be allocated in the system.  A safe value for the above scenario is 2097152.
This would allow the OS to allocate 2097152 pages.  If each page is 4k, we
would
then have enough pages to grab 8GB of shared memory.  So based on the
scenario
above, we would run out of shared memory before we would run out of pages
(since
SHMMAX limits us to 2GB).
Linux Demo:
echo "kernel.shmall=2097152" >> /etc/sysctl.conf


-Decide on your concurrency / synchronization requirements.

An Oracle RDBMS is a multi-user system.  It depends on locks and
latches to synchronize writing of data when multiple processes attempt
simultaneous access.  The underlying OS objects used to create these
locks and latches are semaphores.  Three OS kernel parameters specify
some characteristics of the pool of semaphores which Oracle draws from.

-SEMMNS
We use this parameter to specify the total number of semaphores created
during bootup.

-SEMMNI
We use this parameter to specify the number of sets of semaphores created
during bootup.

-SEMMSL
We use this parameter to specify the maximum number of semaphores in a set.

Obviously, SEMMNS is the most important parameter.  If you assume that each
connection
made to the database will generate 4 latches and if you assume that the
database
needs to support 1024 simultaneous connections, then you would need SEMMNS
to
be set to 4096.  This value of 4096 should be considered a bare minimum.
Compared to shared memory, semaphores are cheap; it's okay to splurge; we
have
no excuse for running out of semaphores.

SEMMNI is less important. Set it to a friendly round number like 100.

SEMMSL is more important than SEMMNI.  Assuming we have
SEMMNS == 4096 and SEMMNI == 100
then a good value of SEMMSL would be 4096/100 == 50

Here are some validation formulas which fit (and expand upon) the above
discussion:

SHMMAX = 2/3 to 3/4 available memory for smaller hosts.
SHMMAX = available memory for larger  hosts ( RAM > 24GB )

SHMMNI >= 1024

SHMALL >= SHMMAX / OS-page-size

SEMMNS >= 4 * number of DB connections
SEMMNI >= 100
SEMMSL >= SEMMNS / SEMMNI

This note makes a set of simplistic assumptions.  It should be considered
a basis for discussion rather than a rigorous set of rules.

...Peter
--
Peter Smith
GoodJobFastCar@xxxxxxxxx
http://GoodJobFastCar.com

Other related posts: