Fixed buffer pool thresholds

Some buffer pool thresholds cannot be changed change. You can monitor buffer pool usage and note how often the fixed thresholds are reached.

If the fixed thresholds are reached too often, the remedy is to use the ALTER BUFFERPOOL command to increase the size of the buffer pool. However, increasing the size of a buffer pool can affect other buffer pools, depending on the total amount of real storage that available for your buffers.

The fixed thresholds are more critical for performance than the variable thresholds. Generally, it is best to set buffer pool sizes large enough to avoid reaching any of the fixed thresholds, except occasionally.

Each of the fixed thresholds is expressed as a percentage of the buffer pool that might be occupied by unavailable pages. From the highest value to the lowest value, Db2 uses the following fixed buffer pool thresholds:

Immediate write threshold: 97.5%

The immediate write threshold is checked whenever a page is updated. If the threshold is exceeded, the updated page is written to disk as soon as the update completes. The write is synchronous with the SQL request; that is, the request waits until the write is completed. The two operations do not occur concurrently.

Reaching this threshold has a significant effect on processor usage and I/O resource consumption. For example, updating three rows per page in 10 sequential pages ordinarily requires one or two write operations. However, when the immediate write threshold is exceeded, the updates require 30 synchronous writes.

Sometimes Db2 uses synchronous writes even when the immediate write threshold was not exceeded. For example, when more than two checkpoints pass before a page is written, Db2 uses synchronous writes. Situations such as these do not indicate a buffer shortage.

Data management threshold: 95%

The data management threshold is checked before a page is read or updated. If the threshold is not exceeded, Db2 accesses each page in the buffer pool only one time, no matter how many rows are retrieved or updated in the page. If the threshold is exceeded, Db2 accesses the page in the buffer pool one time for each row that is retrieved or updated in that page.

Recommendation: Avoid reaching the data management threshold because it has a significant effect on processor usage.

The data management threshold is maintained for each individual buffer pool. When the data management threshold is reached in one buffer pool, Db2 does not release pages from other buffer pools.

Prefetch threshold: 90%

The prefetch threshold is checked at two different times:

• Before a prefetch operation is scheduled. If the prefetch threshold is exceeded, the prefetch is not scheduled.
• During buffer allocation for an already-scheduled prefetch operation, the prefetch is canceled if the prefetch threshold is exceeded.

When the prefetch threshold is reached, prefetch is inhibited until more buffers become available. Operations that use prefetch, such as operations that use large and frequent scans, are adversely affected.