346 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
Since most DB2 servers have a large amount of main memory that cannot be
fully used by DB2, consider setting DB2_PINNED_BP = YES to take advantage
of this unused memory and to improve the overall performance of your
3. Similarly, if the system uses SMS or DMS file containers, set
DB2_MMAP_READ=OFF and DB2_MMAP_WRITE=OFF (the default is ON for both
variables) so as to not use memory mapped files. This frees up one segment
register and takes advantage of the filesystem buffers.
Refer to IBM DB2 Universal Database Administration Guide: Performance V8,
Appendix A, “Registry and Environment Variables” for default values and valid
5.2.3 Disk and filesystem considerations
In this section, we provide the following:
1. Disk subsystem review
2. Disk subsystem recommendations
3. Filesystem recommendations
4. DB2-specific recommendations
Disk subsystem review
Disk subsystems tend to have the most significant performance impact on
applications by virtue of their electromechanical nature; in the time it takes to
move heads across disks, CPUs can execute millions of instructions.
A good disk subsystem design not only involves selecting the right number, size
and performance characteristics for the disks, but also the topology used in
connecting these disks via disk adapters and adapter buses.
In a balanced system, each bus such as a SCSI or Fibre bus will support the
bandwidth and I/Os per second capabilities of the disks on that bus. In addition,
the PCI bus that the disk adapters are connected to should be able to support the
bandwidth requirements of those adapters and whatever else is in the PCI slots
and so on. Figure 5-5 on page 347 illustrates a simplified view of a disk
subsystem architecture and potential bottleneck areas.
Note: Memory mapped files are a UNIX technique whereby programs can
“map” files or parts of files into memory and then address those parts as
though they were simply program data instead of having to read and write the
data file each time it is accessed. If a system has plenty of memory, this offers
some performance and programming advantages.
Chapter 5. Operating system considerations 347
Figure 5-5 Simplified view of disk subsystem architecture
In most cases, disk subsystem design is done with little or no DBA involvement.
However, it is important for DBAs to understand potential areas of bottlenecks in
the disk subsystem in order to be able to identify and resolve performance
problems relating to the disk subsystem.
The economics of disk technology are driving configurations towards fewer and
larger disks. However, there are performance benefits in using more, smaller
disks. These equations must be solved for each particular system.
Refer to IBM DB2 UDB DB2 V8 Administration Guide: Performance, SC09-4821,
Chapter 2 for details on how DB2 lays out control information and database
objects such as tables across disks when creating a database.
The next subsections provide a brief overview of the following:
Note: Chapter 9 of the IBM Redbook Database Performance Tuning on AIX,
SG24-5511, provides an extensive discussion of the design of disk
subsystems for database environments.
PCI Bus PCI Bus