Another consideration is how page cleaning is performed. If pages are not cleaned often enough, an sqlexec thread that performs a query might be unable to find the available pages that it needs. It must then initiate a foreground write and wait for pages to be freed. Foreground writes impair performance, so you should avoid them. To reduce the frequency of foreground writes, increase the number of page cleaners or decrease the threshold for triggering a page cleaning.
Use onstat -F to monitor the frequency of foreground writes.
The following configuration parameters affect page cleaning:
Information that was specified with the BUFFERS, LRUS, LRU_MAX_DIRTY, and LRU_MIN_DIRTY configuration parameters prior to Version 10.0 is now specified using the BUFFERPOOL configuration parameter.
RA_PAGES and RA_THRESHOLD describes the RA_PAGES and RA_THRESHOLD parameters.
The CLEANERS configuration parameter indicates the number of page-cleaner threads to run. For installations that support fewer than 20 disks, one page-cleaner thread is recommended for each disk that contains database server data. For installations that support between 20 and 100 disks, one page-cleaner thread is recommended for every two disks. For larger installations, one page-cleaner thread is recommended for every four disks. If you increase the number of LRU queues as the previous section describes, increase the number of page-cleaner threads proportionally.
The BUFFERPOOL configuration parameter contains information that 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, it is suggested that you set the BUFFERPOOL value of lrus parameter to a minimum of 4. For multiprocessor systems, set the lrus to a minimum of 4 or NUMCPUVPS, whichever is greater.
Use the lrus, lru_max_dirty, and lru_min_dirty values to control how often pages are flushed to disk between full checkpoints.
When the buffer pool is very large, set lru_max_dirty to less than 1 to reduce the time required for a full checkpoint.
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, use the CLEANERS parameter to increase the number of page cleaners.
With fuzzy checkpoints, the buffer pool does not need to be cleaned as often as with full checkpoints. Therefore, you might increase transactional throughput if you increase the values of lru_max_dirty and lru_min_dirty. Retain the same gap between lru_max_dirty and lru_min_dirty.
To monitor the percentage of fuzzy pages in the buffer pool, use the onstat -B command. The checkpoint message in the online message log also tells you the number of pages not flushed to disk.
Enterprise Edition Home | Express Edition Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]