A buffer holds data for the purpose of caching. The database server uses the least-recently used (LRU) queues to replace the cached data. Dynamic Server also has a first-in first-out (FIFO) queue. When you set the number of LRU queues, you are actually setting the number of FIFO/LRU queues.
Use the BUFFERPOOL configuration parameter to specify information about the buffer pool, including information about the number of LRU queues to create when database server shared memory is set up and values for lru_min_dirty and lru_max_dirty, which control how frequently the shared-memory buffers are flushed to disk.
Each LRU queue is composed of a pair of linked lists, as follows:
The free or unmodified page list is referred to as the FLRU queue of the queue pair, and the modified page list is referred to as the MLRU queue. The two separate lists eliminate the need to search a queue for a free or unmodified page. Figure 30 illustrates the structure of the LRU queues.
When the database server processes a request to read a page from disk, it must decide which page to replace in memory. Rather than select a page randomly, the database server assumes that recently referenced pages are more likely to be referenced in the future than pages that it has not referenced for some time. Thus, rather than replacing a recently accessed page, the database server replaces a least-recently accessed page. By maintaining pages in least-recently to most-recently used order, the database server can easily locate the least-recently used pages in memory.
Before processing begins, all page buffers are empty, and every buffer is represented by an entry in one of the FLRU queues. The buffers are evenly distributed among the FLRU queues. To calculate the number of buffers in each queue, divide the total number of buffers by the number of LRU queues. The number of buffers and LRU queues are specified in the BUFFERPOOL configuration parameter.
When a user thread needs to acquire a buffer, the database server randomly selects one of the FLRU queues and uses the oldest or least-recently used entry in the list. If the least-recently used page can be latched, that page is removed from the queue.
If the FLRU queue is locked, and the end page cannot be latched, the database server randomly selects another FLRU queue.
If a user thread is searching for a specific page in shared memory, it obtains the LRU-queue location of the page from the control information stored in the buffer table.
After an executing thread finishes its work, it releases the buffer. If the page has been modified, the buffer is placed at the most-recently used end of an MLRU queue. If the page was read but not modified, the buffer is returned to the FLRU queue at its most-recently used end. For information on how to monitor LRU queues, refer to Monitoring Buffer-Pool Activity.
Multiple LRU queues have two purposes:
Initial values for the LRUS are recommended based on the number of CPUs that are available on your computer. If your computer is a uniprocessor, start by setting the lrus value in the BUFFERPOOL configuration parameter to 4. If your computer is a multiprocessor, use the following formula:
LRUS = max(4, (NUMCPUVPS))
After you provide an initial value for lrus in the BUFFERPOOL configuration parameter, monitor your LRU queues with onstat -R. If you find that the percentage of dirty LRU queues consistently exceeds the value specified for lru_max_dirty, increase the value specified for lrus to add more LRU queues.
For example, suppose you set lru_max_dirty to 70 and find that your LRU queues are consistently 75 percent dirty. Consider increasing the value of the lrus. If you increase the number of LRU queues, you shorten the length of the queues, thereby reducing the work of the page cleaners. However, you must allocate a sufficient number of page cleaners with the CLEANERS configuration parameter, as discussed in the following section.
In general, you should configure one cleaner for each disk that your applications update frequently. However, you should also consider the length of your LRU queues and frequency of checkpoints, as explained in the following paragraphs.
In addition to insufficient LRU queues, another factor that influences whether page cleaners keep up with the number of pages that require cleaning is whether you have enough page-cleaner threads allocated. The percent of dirty pages might exceed the BUFFERPOOL value specified for lru_max_dirty in some queues because no page cleaners are available to clean the queues. After a while, the page cleaners might be too far behind to catch up, and the buffer pool becomes dirtier than the percent that you specified in lru_max_dirty.
For example, suppose that the CLEANERS parameter is set to 8, and you increase the number of LRU queues from 8 to 12. You can expect little in the way of a performance gain because the 8 cleaners must now share the work of cleaning an additional 4 queues. If you increase the number of CLEANERS to 12, each of the now-shortened queues can be more efficiently cleaned by a single cleaner.
Setting CLEANERS too low can cause performance to suffer whenever a checkpoint occurs because page cleaners must flush all modified pages to disk during checkpoints. If you do not configure a sufficient number of page cleaners, checkpoints take longer, causing overall performance to suffer.
For more information, see Flushing Buffer-Pool Buffers.
Periodically, the page-cleaner threads flush the modified buffers in an MLRU queue to disk. To specify the point at which cleaning begins, use the BUFFERPOOL configuration parameter to specify a value for lru_max_dirty.
By specifying when page cleaning begins, the lru_max_dirty value limits the number of page buffers that can be appended to an MLRU queue. The initial setting of lru_max_dirty is 60.00, so page cleaning begins when 60 percent of the buffers managed by a queue are modified.
In practice, page cleaning begins under several conditions, only one of which is when an MLRU queue reaches the value of lru_max_dirty. For more information on how the database server performs buffer-pool flushing, refer to Flushing Data to Disk.
Figure 31 shows how the value of lru_max_dirty is applied to an LRU queue to specify when page cleaning begins and thereby limit the number of buffers in an MLRU queue.
Buffers specified as 8000 lrus specified as 8 lru_max_dirty specified as 60 percent Page cleaning begins when the number of buffers in the MLRU queue is equal to lru_max_dirty. Buffers per lru queue = (8000/8) = 1000 Max buffers in MLRU queue and point at which page cleaning begins: 1000 x 0.60 = 600
You can also specify the point at which MLRU cleaning can end. The lru_min_dirty value in the BUFFERPOOL configuration parameter specifies the acceptable percentage of buffers in an MLRU queue. For example, if lru_min_dirty is set to 50.00, page cleaning is not required when 50 percent of the buffers in an LRU queue are modified. In practice, page cleaning can continue beyond this point, as directed by the page-cleaner threads.
Figure 32 shows how the value of lru_min_dirty is applied to the LRU queue to specify the acceptable percent of buffers in an MLRU queue and the point at which page cleaning ends.
Buffers specified as 8000 lrus specified as 8 lru_min_dirty specified as 50 percent The acceptable number of buffers in the MLRU queue and the point at which page cleaning can end is equal to lru_min_dirty. Buffers per LRU queue = (8000/8) = 1000 Acceptable number of buffers in MLRU queue and the point at which page cleaning can end: 1000 x .50 = 500
You can use decimals for the lru_max_dirty and the lru_min_dirty values. For example, if you set lru_max_dirty to 1.0333 and lru_min_dirty to 1.0, this triggers the LRU to write at 3,100 dirty buffers and to stop at 3,000 dirty buffers.
For more information on how the database server flushes the buffer pool, refer to Flushing Data to Disk.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]