Peter Smith [gjfc] wrote:
> 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:56:31 CST
