DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI

Chapter 5. Operating system considerations 383

address space. Hardware address protection mechanisms prevent the user

process instructions from accessing kernel space.

Support for more than 4 GBytes of memory requires one or more startup or boot

options in addition to DB2 configuration. These include the /PAE and /3GB boot

options.

The /3GB option changes the process address boundary so that the user

address space is 3 GB and the kernel address space is 1 GB. However, /3GB

cannot be used if you want to use more than 16 GBytes of memory. The /PAE

option enables access to memory beyond 4 GB.

General performance recommendations

򐂰 Add the following useful columns to your Task Manager: Memory Usage,

Memory Usage Delta, I/O Reads, I/O Read Bytes, I/O Writes, I/O Write Bytes.

See the Tools section for a description of these columns.

DB2-specific recommendations

򐂰 Following the installation of DB2, check that the DB2 performance counters

are visible from Performance Monitor.

򐂰 To take advantage of memory greater than 4 GBytes using the Address

Windows Extensions (AWE) feature, the DB2 administrator must have the

“lock pages in memory” user privilege.

򐂰 To increase the user address space of a Windows process from 2 Gbytes to

3 Gbytes, set the /3GB boot option. Do this by editing the boot.ini file and

adding /3GB to the end of the line defining the boot image. For example:

multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000

Professional" /fastdetect /3GB

򐂰 If you intend to use AWE to access memory beyond 4 GBytes, add the /PAE

option to the boot.ini entry as above.

5.3.2 Memory considerations

In this section, we provide the following:

1. Windows virtual memory architecture review

2. General performance recommendations

3. DB2-specific performance recommendations

384 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI

Windows virtual memory architecture review

The DB2 implementation on Windows uses a threaded model: there is only one

process, and all threads can access the process’s address space. Therefore,

there is no equivalent of the UNIX shared memory model.

Windows 2000 uses a virtual memory management subsystem similar to UNIX

systems. The paging space file is called pagefile.sys and is normally found in the

root directory of the C: drive. Additional paging space may be allocated as

required.

You can determine the current paging file allocation via My Computer ->

System Properties -> Performance Options -> Change. This displays the

Virtual Memory panel showing paging space allocation on drives, and allows

you to change the size of the paging space files. Setting the Initial Size and

Maximum Size values to the same value will prevent fragmentation.

You can use DB2 Memory Visualizer to show memory consumption for database

shared and global memory and agent private heaps.

Windows 32-bit processes can access memory above the 4 Gbyte boundary

imposed by a 32-bit hardware address pointer by using the Microsoft Address

Windows Extensions (AWE). DB2 can address the memory above 4 GB by

setting the DB2_AWE registry variable, and can utilize memory up to 64 GBytes for

buffer pools.

DB2 makes use of a “window” within the process’s user address space to access

memory above 4 GBytes. The size of the window is configurable and must be

between two buffer pool pages and 1.5 GBytes (or 2.5 GBytes, when the /3GB

option is used).

Memory accessed using AWE is associated with one or more buffer pools. The

buffer pool must be defined (that is, it must exist in SYSCAT.BUFFERPOOLS)

before it can be used by AWE. The DB2_AWE registry variable is used to define the

buffer pool, the number of pages in the buffer pool, and the size of the window.

Memory accessed via AWE is also “locked’; that is, it will not get paged out. This

can provide a performance benefit—but be aware that it may affect the

performance of other, non-DB2 processes. The format of the registry variable

setting is as follows:

db2set DB2_AWE=<buffer_pool_id>,number_of_pages, size_of_window

Where buffer_pool_id is the identifier for the buffer pool. This can be found in

the BUFFERPOOLID column of the relevant row in SYSCAT.BUFFERPOOLS. The

buffer pool must have been defined before you define the DB2_AWE variable entry.