Section 16.2. SystemServer Configuration

16.2. System/Server Configuration

The three commandments in designing your database server are

1. Ensure there is enough memory available.

2. Ensure there are enough physical disks to handle the I/O requirements.

3. Ensure there are enough CPUs to handle the workload.

16.2.1. Ensuring There Is Enough Memory Available

As discussed earlier in the book, all DB2 data and indexes are manipulated in the database buffer pools. The buffer pools are allocated in memory, so you need to ensure that you have enough real memory to hold the buffer pools that you have defined. If you have more buffer pool space defined than real memory, your system has to perform a lot of paging operations that have a very large negative affect on performance. While the buffer pools are normally the largest piece of DB2 memory used, there are other memory segments that DB2 also allocates, such as:

• Sort space

• The lock list

• Package cache

• Catalog cache

• Log buffer

NOTE

For more information about other memory segments, refer to Chapter 15, The DB2 Memory Model. Chapter 15 also discusses the limitations imposed by 32-bit operating systems. With full 64-bit support in DB2 Version 8, and the gaining popularity and availability of 64-bit hardware, we recommend the use of 64-bit whenever possible, especially for the DB2 server.

Depending on your workload, allocate the available memory to different DB2 components. For a Decision Support System (DSS) or OLAP workload with large reports that scan vast amounts of data, the overall affect of the buffer pool is not as significant since DB2 typically has to scan a lot of data. In this type of workload, DB2 usually also needs to perform a number of sorts to help join and/or order the data. Therefore, in a DSS-type workload, try to divide your available memory evenly between the buffer pools and sort heaps.

Online Transaction Processing (OLTP) applications typically read and write a single row in the database, and therefore get a very big benefit if the data is already in the buffer pool. This type of workload is also good at keeping "hot" or often-accessed pages in the buffer pool for reuse. In addition, if you make the sort heap space too large, DB2 can sometimes favor a sort over an index scan, which has a negative affect on overall performance. Therefore, in an OLTP-type workload, dedicate approximately 75 percent of your available memory to the buffer pool and divide the rest among the sort heaps.

16.2.2. Ensuring There Are Enough Disks to Handle I/O

Do not size your disk requirements based on the disk capacity! In other words, if you have a 500GB database, do not buy only 500GB of disk. I/O performance comes from having enough physical disk drives to provide and sustain the throughput required for your workload. With the advent of 100GB and 200GB disks, it is easy to think that you simply need five disks to store a 1TB database. But when you want to read and scan your data, you have only five disks trying to supply all of the data to the processors to be read.

Having too few physical disks is the number one source of poor database performance, so do not get caught in this trap. Make sure that you have at least six to ten physical disks per CPU in your server; otherwise, you very likely will be building a system that will be I/O-starved and will constantly be waiting for I/O, and not working on processing your requests.

16.2.3. Ensuring There Are Enough CPUs to Handle the Workload

Just like a car, the bigger the engine, the faster it can go. In a computer the CPUs are the engine, and the power of the system depends on the number and speed of the CPUs. To handle a large amount of data and a number of concurrent users, you need to be sure that the system has enough CPUs so that you are not constantly utilizing 100 percent of the available CPUs.

It is important to strive for a balanced system that under normal operations is only consuming at most 80 percent of the available CPUs, so that you have room for spikes in activity such as month-end processing and morning application sign-ons.