IBM Security Directory Server, Version 6.3.1

DB2 buffer pool

You can use the DB2® buffer pools to cache entries and their attributes. If the entries are in cache, the search time reduces when querying for the cached data.

Tuning DB2 buffer pool is one of the most significant types of DB2 performance tuning. A buffer pool is a data cache between LDAP and the physical DB2 database files for both tables and indexes. If entries and their attributes are not found in the entry cache, the server searches the DB2 buffer pools for the values. You must tune the buffer pool when the database is initially loaded and when the database size changes significantly. Disabling file system caching is advisable when buffer pools are used. It improves the performance of utilities like bulkload, by removing a redundant level of caching.

IBM® Security Directory Server uses two buffer pools, one for the USERSPACE1 table space and the other for the LDAPSPACE table space. The buffer pool for USERSPACE1 is named IBMDEFAULTBP and the buffer pool for the LDAPSPACE table space is named LDAPBP. For more information about USERSPACE1 and LDAPSPACE, see Table spaces.

There are several points that you must consider are related to DB2 buffer pools. For example:

If there are no buffer pools, all database activity results in disk access.
If the size of each buffer pool is too small, LDAP must wait for DB2 disk activity to satisfy DB2 SQL requests.
If one or more buffer pools are too large, memory on the LDAP server might be wasted.
If the space that is used by the LDAP caches and the buffer pools is larger than the memory available on a server, operating system paging might occur. Operating system paging might result in increased disk activity.
Tuning buffer pools do not improve performance of a directory server significantly, it the server contains tens of millions of users. It is not possible or practical to cache a large enough percentage of the database to greatly improve performance. With directories up to millions of users, it is possible to cache a large enough percentage of the database to make an improvement to performance.
The DB2 buffer pools sizes must be low enough to prevent operating system paging, but high enough to provide maximum benefit.
Most importantly, current versions of DB2 support automatic tuning of the buffer pools.

The idsperftune performance tuning tool sets the DB2 configuration options so that the buffer pools automatically tuned.

To retrieve the current DB2 buffer pool sizes, run the following commands:

db2 connect to database_name
db2 select varchar(bpname,20) as bpname,npages,pagesize fromsyscat.bufferpools

where, database_name is the name of the database.

The following example output shows the default settings:

BPNAME	                NPAGES	        PAGESIZE
------------------ 		-----------		 -----------
IBMDEFAULTBP            29500	          4096
LDAPBP                   1230		       32768

2 record(s) selected.

To determine the current file system caching option for each of the table space, run the following commands:

db2 get snapshot for tablespaces on ldapdb2 | egrep ’tablespace name|File system caching’

To turn off file system caching with DB2, version 8.2 or later in operating systems and file system environments that support it, run the following command:

db2 connect toldapdb2
db2 altertablespace USERSPACE1no file systemcaching 
db2 altertablespace LDAPSPACEno file systemcaching 
db2 terminate
db2stop 
db2start

To set the buffer pool sizes, use the following commands:

db2 alter bufferpool ibmdefaultbp size new size in 4096 byte pages
db2 alterbufferpool ldapbpsize new size in 32768 byte pages
db2 terminate 
db2stop 
db2start

If these commands are run while the directory server is running, the db2stop command fails. An error message is generated indicating there are applications that are connected to the database. If an error is generated, stop the directory server and then run the following commands:

db2stop 
db2start

To assign optimum memory size for the DB2 buffer pools, you must determine the values. For more information, see Analyzing DB2 buffer pool performance.

If any of the buffer pool sizes are set too high, DB2 fails to start because of insufficient memory. If DB2 fails, the DB2 might generate a core dump file, usually there are no error messages. On AIX® systems, the system error log might report a memory allocation failure. To view the log, run the following command:

errpt –a | more

If DB2 fails to start because of large buffer pool size, set the buffer pool size to lower values and restart DB2. If you restore a database to a target system from a source system with large buffer pool sizes, the restore operation might fail.

On Windows systems, if you cannot connect to the database check the DB2INSTANCE environment variable. By default, the variable is set to DB2. To connect to the database, you must set the variable to the database instance name. You must also consider upgrading DB2 to latest fix pack level for stability and performance enhancements.

In DB2, version 9.1 and later, the self_tuning_mem database configuration parameter is automatically set to ON when you create a single-partition database. The value for the parameter is set to AUTOMATIC. The following memory consumers are enabled to tune automatically if you set the value to AUTOMATIC:

Buffer pools (controlled by the ALTER BUFFERPOOL and CREATE BUFFERPOOL statements)
Package cache (controlled by the pckcachesz configuration parameter)
Locking memory (controlled by the locklist and maxlocks configuration parameters)
Sort memory (controlled by the sheapthres_shr and the sortheap configuration parameter)
Database shared memory (controlled by the database_memory configuration parameter)

You must ensure that the database memory is optimally used when the Self Tuning Memory Manager (STMM) is set. You can determine the optimal value for the DATABASE_MEMORY parameter. For more information, see Determining the DATABASE_MEMORY parameter value.

You can limit DB2 buffer pools from using all the available memory. To limit the use of memory, consider the following settings before you enable the STMM:

To use the default automatic values, set the database shared memory size configuration parameter, DATABASE_MEMORY:
```
db2 ALTER BUFFERPOOL LDAPBP SIZE AUTOMATIC
db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE AUTOMATIC
```
Run the instance under normal load and monitor the value of DATABASE_MEMORY to determine an optimum size for the setting.
Set DATABASE_MEMORY to the determined size instead of automatic.
```
db2 ALTER BUFFERPOOL LDAPBP SIZE Determined_Value
db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE Determined_Value
```
The Determined_Value value optimizes the performance of database.

Feedback