DB2 and Memory Requirements

来源：作者：出处：巧巧读书 2006-09-17 进入讨论组

DB2 and Memory Requirements
From the client- Memory Requirements
50 Concurrent Connections 186 MB

Kernel Parameters needed for Server:
The kernel maintains certain resources for the implememtation of shared mem.
Specifically a shared mem identifier is initialized and maintained by the OS
whenever a shmget system call is executed successfully. The shmid identifies
a shared segment which has two components:
The actual shared RAM pages and a data structure that maintains information
about the shared segment
ipcs -ma on server

Shared Memory:
T ID  GROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME
m 0   root             1                  68         351      351     9:38:32      9:38:32     9:38:32
m 1    dba            25                524288  392     12102  20:54:35   13:25:55    9:38:39
m 2    dba            21               4407296 392     12102  13:25:55   13:25:55    9:38:39
m 3    dba      20    5308416   400 12102  9:38:41 13:25:55  9:38:41

Physical memory usage can be classified into four groups:
1.  Kernel memory mapped into kernel address space
2.  Process memory is mapped into a process address space
3.  Filesystem cache memory that is not mapped into any address space
4.  Free memory that is not mapped into any address space

1.Kernel memory is allocated to hold the initial kernel code at boot time, then
  grows dynamically as new device drivers and kernel modules are used.
  Kernel tables also grow dynamically unlike older versions of Unix.
  To check usage
  sar or kmastat from crash will show kernel usage
  On DB2server
  sar -k -1
  sml_mem   alloc  fail  lg_mem   alloc  fail  ovsz_alloc  fail
  78356480 70380352     0 202645504 191583000     0   185065472     0
  #crash
   kmastat


2. Application process memory
   Application processes consist of an address space divided into segments
   where each segment maps either to a file, anonymous memory(swap), Shared
   memory or a memory mapped device.
   /usr/proc/bin/pmap 19660 (db2inst1
   shows the base address and size of each segment
   total Kb   62432   28232    1968   2626
              total   resident shared private

   resident - indicates resident in main memory
   shared   - with other processes
   private  - taken from the free list

3. Cached memory

4. Free memory
Free memory is maintained so that when a new process starts up or an existing
process needs to grow, the additional memory is immediately available.


Memory Sizing Rules and Example: (for a 2GIG)
Item:                               Rule                    EG              Comment:
System Processes:        +system               10MB
System Libraries:          +sys libraries        15MB
User-Processes            +proc_user           9.7MB per user  (For large apps)
                                       +proc_sys-wide  15.6
Background Process     +background priv
                                       +background shar 27MB
Buffer-Cache Memory:    +buffer cache   100MB           if DB is running RAW
Sys V Shared Memory:    +Shar-mem       560MB      The db2  Dba set             params  that generated a 560MB  shared memory segment
Kernel Memory:          +kernel_mem     150MB           Because this is a
                                                        RAW db sys, no tuning
Total:                                  900MB


Memory is allocated in units called pages.
On Db2server it's 8192 bytes per page


IPC'S Interprocess Communication

IPC encompasses facilities provided by the operating system to enable the
sharing of data (shared memory segments), the exchange of information and
data(message queues) , and the syncronization of access to shared resourses
(semaphores) between processes and threads on the same system.
Contrast IPC to networking based facilities such as sockets and RPC
interfaces. which enabled communication over a network link between
distributed systems

Other facilities for IPC's include memory mapped files (mmap), named pipes and
Solaris doors which provide a RPC like facility fro threads running on the
same system.

Shared memory is an interprocess communication  (IPC)facility and is used extensively by by relational databases as a means of implementing a cache

- sharing of the same physical RAM pages by multiple processes
  These processes have mappings to the same physical pages

The kernel implementation of shared memory requires two loadable kernel
modules : the shmsys module and the ipc module

Semaphores are another IPC facility
Semaphores provide a means of synchronizing access to shared resources
           by multiple processes.
Each time a process needs a resource, a semaphore value gets decremented
and when the process is done the value gets incremented.
These are controlled by the following tunable parameters

a.   semmap - determines the max number of entries in a semaphore map
        The memory space given to the creation of semaphores is taken from
        the fixed number of entries set to semmap
        Therefore kernel memory is allocated up front and semaphore subsystem
        controls the allocation and deallocation dynamically

        on Artie: set semsys:seminfo_semmap = 1026

b.   semmni - semaphore identifiers
        should never be greater than the semmap .
        The semmni tunable establishes the maximum number of semaphore
        sets system wide. Every semaphore set in the system has a
        unique indentifier and control structure. During initialization,
        the system allocates kernel memory for semmni control structures.
        Each control structure is 84 bytes
        On Artie: set semsys:seminfo_semmni = 1024

c.   semmns - max number of semaphores in system (SEMMNS)
        A semaphore set may have more than one semaphore associated with it
        On artie: set semsys:seminfo_semmns = 2048

d.   semmnu - semaphore undo structures in system (SEMMNU)
:
        set semsys:seminfo_semmnu = 2048


During initialization of the semaphore code, the system checks for the
maximum amount of available kernel memory and divides that number by 4
ensuring that that semaphore requirements do not exceed 25 % of the
available kernel memory.

maximum proceses is determined during startup based on the amount of RAM
sysdef | grep v_proc

jaguar
  % sysdef | grep v_proc
   3914        maximum number of processes (v.v_proc)
artie:
  % sysdef | grep v_proc
16394        maximum number of processes (v.v_proc)


Asyncronous I/O:
---------------
Databases often use a modern method of queuing I/O requests to the devices
known as asyncronous I/O. using this method, multiple I/O's can be requested
at once, with a asyncronous notification via a signal when the I/O has
completed.

Tunable parameters:
1.   Parameters msgmnb and msgmax must be set to at least 65535
       65535  max message size (MSGMAX)
       65535  max bytes on queue (MSGMNB)

2.   Parameter shmmax should be set to 100% of the physical memory (in bytes)
     eg
     if you have 196 MB of physical memory,
     196*0.9*1024*1024 = 184 968 806
     in artie we have shmmax set to
     536870912 max shared memory segment size (SHMMAX)

How to Check Interprocess Communication (sar)
Use the sar -m command to report interprocess communication activities.

$ sar -m
00:00:01   msg/s  sema/s
01:00:01    0.00    0.00

The figures will be zero unless you are running applications that use
messages or semaphores

msg/s    The number of message operations (sends and receives) per second
sema/s   The number of semaphore operations per second

On jaguar:

jaguar  % sar -m | head

SunOS jaguar 5.7 Generic_106541-12 sun4u    11/24/00

00:01:02   msg/s  sema/s
00:11:01   78.17    1.36
00:21:01   74.87    3.83
00:31:01   11.95   44.84
00:41:01   66.80    9.66


sar -q
00:01:02 runq-sz %runocc swpq-sz %swpocc
Average      1.9       2    40.8     100

runq-sz -The number of kernel threads in memory waiting for a CPU
                  to run. Typically, this value should be less than 2.
                  Consistently higher values mean that the system may be
                  CPU-bound.

runocc - The percentage of time the dispatch queues are occupied

swpq-sz The average number of swapped out LWPs.

%swpocc The percentage of time LWPs are swapped out

The following example shows output from the sar -q command. If %runocc is
high (greater than 90 percent) and runq-sz is greater than 2, the CPU is
heavily loaded and response is degraded. In this case, additional CPU
capacity may be required to obtain acceptable system response.

How to Check Unused Memory (sar)
sar -r

00:01:02 freemem freeswap
Average     1655  2072084

freemem  The average number of memory pages available to user
                  processes over the intervals sampled by the command. Page
                  size is machine-dependent.

freeswap The number of 512-byte disk blocks available for page swapping


How to Check CPU Utilization (sar)
sar -u

00:01:02    %usr    %sys    %wio   %idle
verage       19       8      70       2

A high %wio generally means a disk slowdown has occurred


%sys
                  Lists the percentage of time that the processor is in system
                  mode
  %user
                  Lists the percentage of time that the processor is in user
                  mode
  %wio
                  Lists the percentage of time the processor is idle and waiting
                  for I/O completion
  %idle
:How to Check System Table Status (sar)
Use the sar -v command to report the status of the process table, inode table,
file table, and shared memory record table.


$ sar -v
00:00:01  proc-sz    ov  inod-sz    ov  file-sz    ov   lock-sz
15:41:01   73/3914    0 3785/16952    0  502/502     0    0/0
15:51:01   73/3914    0 3785/16952    0  502/502     0    0/0

proc-sz The number of process entries (proc structs) currently being
                  used, or allocated in the kernel.

inod-sz The total number of inodes in memory verses the
                  maximum number of inodes allocated in the kernel. This is
                  not a strict high water mark; it can overflow.

file-sz  The size of the open system file table. The sz is given as 0,
                  since space is allocated dynamically for the file table.

ov  -     The number of shared memory record table entries
                  currently being used or allocated in the kernel. The sz is
                  given as 0 because space is allocated dynamically for the
                  shared memory record table.

lock-sz The number of shared memory record table entries
                  currently being used or allocated in the kernel. The sz is
                  given as 0 because space is allocated dynamically for the
                  shared memory record table.

When the system first loads the shared memory module, it allocates kernel
memory to support the shmid structures and other required kernel support
structures. The kernel memory required is based on the shmmni tunable, since
that defines the requested number of unique shared memory identifiers the
system maintains. Each shmid_ds structure is 112 bytes in size and has a
corresponding kernel mutex lock, which is an additional eight bytes. Thus,
the amount of kernel memory required by the system to support shared
memory can be calculated as ((shmmni * 112) + (shmmni * ). The default value
of 100 for shmmni requires the system to allocate about 13 kilobytes of kernel
memory for shared memory support. The system makes some attempt at
protecting itself against allocating too much kernel memory for shared
memory support by checking for the maximum available kernel memory,
dividing that value by four, and using the resulting value as a limit for
allocating resources for shared memory. Simply put, the system will not allow
more than 25 percent of available kernel memory to be allocated.

------------------------------------------------------------------------
It should be clear that one should not set shmmni to an arbitrarily large value
simply to ensure sufficient resources. There are limits as to how much kernel
memory the system supports. On sun4m-based platforms, the limits are on
the order of 128 megabytes (MB) prior to Solaris 2.5, and 256 MB for 2.5,
2.5.1, and 2.6. On sun4d systems (SS1000 and SC2000), the limits are about
576 MB in 2.5 and later. On UltraSPARC[sun4u]-based systems, the kernel
has its own four gigabyte (GB) address space, so it's much less constrained.
Still, keep in mind that the kernel is not pageable, and thus whatever kernel
memory is needed remains resident in RAM, reducing available memory for
user processes. Given the fact that Sun ships systems today with very large
RAM capacities, this may not be an issue, but it should be considered
nonetheless. (sun4m, sun4d, and sun4u is Sun nomenclature for defining
different "kernel architectures.") Every operating system has some
hardware-independent and hardware-dependent components to it -- the
hardware-dependent components are at the lower levels, where hardware
:registers, and things get touched. Different kernel architectures exist for
different Sun desktop and server systems and vary due to processor
technology (SuperSPARC, UltraSPARC, etc.) and system infrastructure
(Mbus, XDbus, Gigaplane, etc.). Use uname(1M) with the -m flag to determine
what your systems kernel architecture is:

% uname -m

Note that the maximum value for shmmni listed in Table 2 is 2 GB. This is a
theoretical limit, based on the data type (a signed integer) and should not be
construed as something configurable today. Applying the math from above,
you see that two billion shared memory identifiers would require over 200 GB
of kernel memory! One should assess to the best of their ability the number
of shared memory identifiers required by the application and set shmmni to that
value plus 10 percent or so for headroom.

The remaining three shared memory tunables are quite simple in their
meaning. Shmmax defines the maximum size a shared segment can be. The size
of a shared memory segment is determined by the second argument to the
shmget(2) system call. When the call is executed, the kernel checks to ensure
that the size argument is not greater than shmmax. If it is, an error is returne
d.
Setting shmmax to its maximum value does not effect the kernel size -- no
kernel resources get allocated based on shmmax, so this can be tuned to its
maximum value of 4 GB (0xffffffff), as in this (/etc/system) entry:

The shmmin tunable defines the smallest possible size a shared segment can be,
as per the size argument passed in the shmget(2) call. There's no real
compelling reason to set this from the default value of 1. Lastly, there's
shmseg, which defines the number of shared segments a process can attach
(map pages) to. Processes may attach to multiple shared memory segments
for application purposes, and this tunable determines how many mapped
shared segments a process can have attached at any one time. Again, the
32-kilobyte (K) limit (maximum size) in Table 2 is based on the data type
(short), and does not necessarily reflect a value that will provide application
performance that meets business requirements if some number of processes
attach to 32,000 shared memory segments. Things like shared segment size
and system size (amount of RAM, number/speed of processors, etc.) will all
factor into determining the extent to which you can push the boundaries of
:factor into determining the extent to which you can push the boundaries of
this facility.

t should be pointed out that the kernel makes no attempt at coordinating
concurrent access to shared segments. This must be done by the software
developer using shared memory in order to prevent multiple processes
attached to the same shared pages from writing to the same locations at the
same time. There are several ways this can be done, the most common of
which is the use of another IPC facility, semaphores.

Semaphores provide a means of
synchronizing access to shared resources and
are used quite often to synchronize access to
shared memory segments by multiple
processes in applications.

The semmap parameter determines the maximum number of entries in a
semaphore map. The memory space given to the creation of semaphores is
taken from the semmap, which is initialized with a fixed number of map entries
based on the value of semmap. The implementation of allocation maps is
generic within Unix SVR4 and also Solaris, supported with a standard set of
kernel routines (rmalloc(), rmfree(), etc.). The use of allocation maps by the
semaphore subsystem is just one example of their implementation. They
basically prevent the kernel from having to deal with allocating and
de-allocating additional kernel memory as semaphores get initialized and
freed. By initializing and using allocation maps, kernel memory is allocated
upfront, and map entries are allocated and freed dynamically from the semmap
allocation maps. The semmni tunable should never be larger than semmap. In fact,
semmap should be set to the product of semmni and semmsl: (semmap = semmni *
semmsl). If you make semmap to small for the application, you'll get
"WARNING: rmfree map overflow" messages on the console. Tweak it
higher and reboot.URL:http://www.qqread.com/db2/q209278.html