BUFFERPOOL and its effect on page cleaning

The BUFFERPOOL configuration parameter specifies the number of least recently used (LRU) queues to set up within the shared-memory buffer pool. The buffer pool is distributed among LRU queues. Configuring more LRU queues allows more page cleaners to operate and reduces the size of each LRU queue.

For a single-processor system, set the lrus field of the BUFFERPOOL configuration parameter to a minimum of 8. For multiprocessor systems, set the lrus field to a minimum of 8 or to the number of CPU VPs, whichever is greater.

For a single-processor system, set the lrus field of the BUFFERPOOL configuration parameter to a minimum of 4. For multiprocessor systems, set the lrus field to a minimum of 4 or to the number of CPU VPs, whichever is greater.

The lrus, lru_max_dirty, and lru_min_dirty values control how often pages are flushed to disk between checkpoints. Automatic LRU tuning, as set by the AUTO_LRU configuration parameter, affects all buffer pools and adjusts the lru_min_dirty and lru_max_dirty values in the BUFFERPOOL configuration parameter.

If you increase the lru_max_dirty and lru_min_dirty values to improve transaction throughput, do not change the gap between the lru_max_dirty and lru_min_dirty.

When the buffer pool is very large and transaction blocking is occurring during checkpoint processing, look in the message log to determine which resource is triggering transaction blocking. If the physical or logical log is critically low and triggers transaction blocking, increase the size of the resource that is causing the transaction blocking. If you cannot increase the size of the resource, consider making LRU flushing more aggressive by decreasing the lru_min_dirty and lru_max_dirty settings so that the server has fewer pages to flush to disk during checkpoint processing.

To monitor the percentage of dirty pages in LRU queues, use the onstat -R command. When the number of dirty pages consistently exceeds the lru_max_dirty limit, you have too few LRU queues or too few page cleaners. First, use the BUFFERPOOL configuration parameter to increase the number of LRU queues. If the percentage of dirty pages still exceeds the lru_max_dirty limit, update the CLEANERS configuration parameter to increase the number of page cleaners.