[c.d.o.s][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_at_gmail.com
http://GoodJobFastCar.com Received on Tue Dec 19 2006 - 15:39:56 CST