Tuning Linux kernel for UDB

November 2005

Nadia Derbey
Nadia.Derbey@bull.net

Overview

The objective of this paper is to define a profile in terms of kernel tunables for Linux machines running a DB2 server.
First, it lists the various recommendations that have been collected from the following references:
Then, the various recommendations are compiled into a single profile.

DB2 recommendations for kernel parameters (see [1])

When a Linux system should be used as a database server, some of the Linux kernel parameters have to be changed. DB2 recommends to set the kernel parameters to the following values for the versions v8.1 and v8.2:

kernel parameter
default value
recommended value
usage
sem
250
32000
32
128
250
256000
32
1024
SEMMSL: max # of sems per id
SEMMNS: max # of sems system wide
SEMOPM: max # of ops / semop call
SEMMNI: max # of sem ids
msgmni
16
1024
max # of msg queues system wide
msgmax
8192
65536
max size of a single msg in bytes
msgmnb
16384
65536
max size of a msg queue in bytes

Recommended values for DB2 UDB to run optimally (see [2])

kernel parameter
default value
recommended value
usage

32 bits
64 bits
sem
250
32000
32
128
250
256000
32
1024

SEMMSL: max # of sems per id
SEMMNS: max # of sems system wide
SEMOPM: max # of ops / semop call
SEMMNI: max # of sem ids
msgmni
16
1024
max # of msg queues system wide
shmmax
0x2000000
0x10000000 (2^28)
0x40000000 (2^36)
max size of a shm segment in bytes

Values used in the FinTime benchmark (see [3]) - 32 bits

kernel parameter
default value
recommended value
usage

sem
250
32000
32
128
250
256000
32
1024

SEMMSL: max # of sems per id
SEMMNS: max # of sems system wide
SEMOPM: max # of ops / semop call
SEMMNI: max # of sem ids
msgmni
16
1024
max # of msg queues system wide
shmmax
0x2000000
0xFFFFFFFF (2^32 - 1)
max size of a shm segment in bytes
shmall
0x200000
amount of memory in bytes (8 GB)
max # of shm segment pages system wide

Values recommended for DB2 (see [4])

kernel parameter
default value
recommended value
usage

32 bits
64 bits
sem
250
32000
32
128
250
256000
32
1024

SEMMSL: max # of sems per id
SEMMNS: max # of sems system wide
SEMOPM: max # of ops / semop call
SEMMNI: max # of sem ids
msgmni
16
1024
max # of msg queues system wide
shmmax
0x2000000
0x100000000 (2^32)
amount of memory in bytes
max size of a shm segment in bytes
shmall
0x200000
amount of memory in bytes
amount of memory in bytes
max # of shm segment pages system wide

Configuring Linux for DB2 (see [5])

In addition to information on the overall memory usage of the system, DB2 determines the values of several kernel parameters. DB2 then adjusts some of these parameters.
DB2 automatically makes further system adjustments to maximize the use of the system memory. DB2 needs a contiguous address space for the shared memory area. The kernel, however, maps the attachment address for shared libraries to an address somewhere in the middle of the address space, limiting the contiguous area. DB2 will automatically move this map, allowing a larger contiguous area. This will allow an additional 500 MB of memory available for database shared memory, pushing total utilization slightly above 2 GB to about 2.2 GB. This is accomplished by modifying the mapped_base address, which specifies the attachment address of shared libraries. This will only affect the DB2 process address space and not the system as a whole. The following entry in the DB2 diagnostic log describes this event on my system:

kernel parameter
default value
recommended value
usage

sem
250
32000
32
128
250
256000
32
1024

SEMMSL: max # of sems per id
SEMMNS: max # of sems system wide
SEMOPM: max # of ops / semop call
SEMMNI: max # of sem ids
msgmni
16
1024
max # of msg queues system wide
shmmax
0x2000000
0x10000000 (2^28)
max size of a shm segment in bytes
mapped_base for the db2agent process
0x40000000
0x10000000
Base address for shared libraries (specific to some ditros, like RH)


Analysis

All the references agree on the following values:
Only one document gives recommendations about
msgmni * msgmnb = 1 KB * 64 KB = 64 MB. Since this seems to be a reasonable amount of memory, the recommended value for msgmnb will be kept. This is also true for the recommended value for msgmax: this means that a message queue might contain 1 message at a time.

The DB2 log file presented in reference [5] shows that mapped_base for the db2_agent process is set to 0x10000000. For this change to be effective, db2sysc re-execs with the new value value before spawning the agents.

The only value on which there seems to be a disagreement is kernel.shmmax.
Reference [1] gives recommendations for shmmax only in its "General recommendations" chapter (which is not dedicated to DB2). They give some precise values, without saying which database they have been used for.
Reference [5] agrees with Reference [2] for the 32 bits case.
References [2] to [4] have one thing in common: they make a distinction between the 32-bits and the 64-bits cases.

32-bits case:
Interpreting the various references, it seems that for a physical RAM of up to 4 GB, shmmax is set to 256 MB (0x10000000). Starting from 4 GB, shmmax is set to 4 GB (2^32) which is the maximum addressable value in 32 bits.
I think this setting can be refined if the hosting machine is exclusively used as a DB2 server:

Physical RAM
shmmax value
>
<=
256 MB
512 MB
<= 0x010000000 (256 MB)
512 MB
1 GB
<= 0x020000000 (512 MB)
1 GB
2 GB
<= 0x040000000 (1 GB)
2 GB
4 GB
<= 0x080000000 (2 GB)
4 GB

<= 0x100000000 (4 GB)


64-bits case:
The only reference that gives a "rule" is reference [4]: it suggests to set shmmax to the amount of memory in bytes. The other references give an unconditional value of 0x40000000.
I don't think 64 bits addressing has an impact on machines with a physical RAM lesser than 4 GB.
Thus, the previous array could be generalized for memory sizes higher that 4 GB, keeping shmmax as a power of 2:

Physical RAM
shmmax value
>
<=
4 GB
8 GB
0x0100000000 (4 GB)
8 GB
16 GB
0x0200000000 (8 GB)
16 GB
32 GB
0x0400000000 (16 GB)
32 GB
64 GB
0x0800000000 (32 GB)
64 GB
128 GB
0x1000000000 (64 GB)
etc,




shmall is only referenced in [3] and [4] (actually both references come from the same author): I guess there's a typo: "amount of memory in bytes" should be changed to "amount of memory in 4K pages". If we assume that we can have one segment system wide, with the maximum sizes specified above, we can consider the previous 2 arrays and divide the values by 4K.

End of Document