10. Improving DB2 Version 7.1 performance on Linux

Database administrators running DB2 on Linux often run into problems attempting to perform the following tasks:

The following sections attempt to address those problems.

10.1. Increasing maximum connections

One of the most frequently reported problems with running DB2 on Linux is that DB2 seems to quickly start refusing connections to the server. You can alleviate this problem by examining an altering several kernel parameters that control inter-process communication (ipc) limits.

The number of connections that DB2 can support depends on kernel parameters that, in the 2.2 kernel, are #define variables included in the kernel source header files. In the 2.4 kernel, some of these parameters can be set through the /proc filesystem. Following the description of these variables is a table listing the default values by distribution. The table includes the values of these variables in the updated kernel packages for each distribution.

_SHM_ID_BITS

This variable, defined in /usr/src/linux/include/asm/shmparam.h, determines the number of shared memory segment identifiers available to Linux. The default value for _SHM_ID_BITS in the kernel source is 7, which allows for a total of 2^7, or 128, shared memory segment identifiers.

On a single-processor machine, DB2 itself uses a set number (~15) of shared memory segment identifiers. On a machine with multiple processors, DB2 also requires one shared memory segment identifier per agent to take advantage of the Fast Communication Manager (FCM) feature of DB2. Because each DB2 connection requires one agent per processor, on a quad-processor machine the default value of _SHM_ID_BITS allows less than 32 simultaneous connections to a DB2 instance.

If you recompile the kernel yourself, you should be able to safely increase this value to 9.

MSGMNI

This variable determines the maximum number of message queue identifiers. For DB2 Version 7.1 to function correctly, the minimum value is 128, but for heavier use consider setting this value to 1024 or higher.

In the 2.2 kernel source, this variable is defined in /usr/src/linux/include/linux/msg.h. The default value of 128 is acceptable for small-scale use of DB2 Version 7.1.

In the 2.4 kernel source, this variable is defined in /proc/sys/kernel/msgmni. The default value of 16 must be increased to enable DB2 Version 7.1 to function correctly. The good news is that you can change this value without recompiling the kernel or rebooting your machine. For more information, see Section 12.

NR_TASKS

This variable, defined in /usr/src/linux/include/linux/tasks.h, determines the number of simultaneous processes that Linux supports. A second variable, MAX_TASKS_PER_USER, is defined as NR_TASKS/2. Since DB2 instances are treated as users by Linux, and each connection uses a single process, the maximum number of connections per instance is capped at the value of NR_TASKS/2.

The default value for NR_TASKS in the kernel source is 512, allowing a maximum of 256 simultaneous connections to a single DB2 instance. DB2 itself requires a few connections for overhead processes. If you recompile the kernel yourself, you should increase this value to something like 2048. The stock kernels shipped with the Red Hat, SuSE, and TurboLinux distributions increase the value of NR_TASKS to 2560 or above. However, Caldera OpenLinux eDesktop 2.4 ships with a kernel in which NR_TASKS is set to the default value of 512. Caldera users should increase the value of this variable and recompile the kernel.

SEMMNI

This variable, defined in /usr/src/linux/include/linux/sem.h, determines the number of semaphore identifiers that Linux supports. This variable is particularly important on symmetric multi-processing (SMP) machines. A unique semaphor identifier is required for each processor per agent (or connection); therefore, on a quad-processor machine, four semaphore identifiers are required per connection.

The default value for SEMMNI in the kernel source is 128, which, on a quad-processor machine, will only allow 32 simultaneous connections to a DB2 instance. If you recompile the kernel yourself, increase this value to something like 1024.

The following table shows the default values of the kernel parameters set in the stock kernel source and in the kernels provided by each distribution.

Table 2. Default kernel parameter values, by distribution

Distribution_SHM_ID_BITSMSGMNINR_TASKSSEMMNI
2.2 kernel source7128512128
2.4 kernel source??**16??128
Caldera OpenLinux eDesktop 2.47Unknown512128
Caldera OpenLinux eServer 2.37Unknown512128
Mandrake Linux 7.2105124090512
Red Hat 6.2

kernel-2.2.14-57
kernel-2.2.14-129
kernel-2.2.16-39

Unknown

kernel-2.2.14-52560
kernel-2.2.14-122560
kernel-2.2.16-34090

kernel-2.2.14-5128
kernel-2.2.14-12512
kernel-2.2.16-3512

Red Hat 7.1n/a (2.4 kernel)16n/a (2.4 kernel)128
SuSE 6.2UnknownUnknownUnknownUnknown
SuSE 6.3UnknownUnknownUnknownUnknown
SuSE 7.091282048512
TurboLinux 6.0

kernel-2.2.14-57
kernel-2.2.16-0.47

Unknown

kernel-2.2.14-52560
kernel-2.2.16-0.42560

kernel-2.2.14-5128
kernel-2.2.16-0.4128

10.2. Creating and configuring buffer pools

A buffer pool is a database object representing system memory used to cache table and index data as it is read from disk or modified. DB2 allocates a default buffer pool of 4 megabytes of memory. This is a ridiculous default for a production database because it will inhibit performance greatly. To get good performance out of DB2, you must create one or more buffer pools and associate them with the tablespaces used to hold the tables in your database. For more information on buffer pools and increasing performance, see the IBM DB2 Administration Guide: Performance.

On a system with a 2.2 kernel compiled with support for >1 gigabyte of RAM, the practical upper limit for buffer pools is about 1 gigabyte of memory due to the location in memory in which Linux loads shared libraries.