When a thread needs a buffer to update and the page is not already in the buffer pool the thread uses a random hash to select an LRU to get the buffer from. If this LRU is not locked by another process doing the same thing, the thread acquires the lock on that LRU queue and takes the Least Recently Used clean buffer, moves it to the LRU queue's dirty list, locks the buffer page and releases the lock on the LRU. If the LRU is already locked it spins (BUFWAIT) for a while trying a few more times to get a lock on that LRU. If not, it rehashes to another queue. If there are many updaters and few LRUs it is likely that the LRU will also be locked already and so our poor thread spins again and so on. Eventually it gets a lock on some LRU and continues. If there are more LRUs then the probability of finding a free one improves greatly and BUFWAITS go down and throughput goes up.
If the page to be updated is already in the cache but has not yet been modified, the number of LRUs is still important as the size of the buffer cache increases. More cache means it is more likely that a page that is about to be modified is already in the clean cache (or the dirty cache for that matter but that is not affected by the number of LRUs). With more LRUs it is less likely that some other thread is in the process of moving another already cached clean page to the dirty list from the same LRU that contains the page that your thread needs. Again this reduces BUFWAITS and improves throughput.
Therefore set LRUS to 128 and leave well alone. As with everything there is a catch, certain numbers for LRUS have bugs associated with them, the consensus at the moment is to ensure that the number of LRUS is a power of 2, having said that there is a rumour that 96 is a bad number, so maybe it would be better to say an even power of two.
When setting LRUs to 128 some versions report a warning on startup. This is nothing to worry about but if the instance monitoring software used flags warning messages to an operator, setting LRUs to 127 will remove the problem.
Based on an article by Art Kagel
The values and descriptions are:
| LRUPOLICY 0x0 - Default. | Always hash to choose an LRU, rehash if waiting too long. |
| LRUPOLICY 0x1 | Use session ID to generate a consistent LRU selection per session for any 'get'. (I think a 'get' is obtaining the LRU's LRUed buffer before writing a clean page to it). |
| LRUPOLICY 0x2 | Wait on initial LRU for a 'get' operation. Do not rehash. |
| LRUPOLICY 0x4 | Wait on initial LRU for a 'clean put', ie replacing a buffer at the head of the clean LRU after reading a page from disk into an empty or recycled buffer. Do not rehash. |
| LRUPOLICY 0x8 | Wait on initial LRU for a 'dirty put', ie replacing a buffer at the head of the dirty LRU after updating its contents. Do not rehash. |
| LRUPOLICY 0x10 | Use session ID to generate a consistent LRU selection per session for any 'put' operation (ie returning a buffer to the LRU queues). |
These values can be mathematically OR'd to create values in the range 0-31 which can control all or part of the LRU selection and wait policies. A value of 0x11 would cause each session to initially always select the same LRU queue which MAY eliminate LRU contention when the number of sessions is not significantly larger than LRUS. If sessions < LRUS it will in effect make each LRU private for a session or two. The even values (2,4,8) will cause a session to remain in wait on the selected LRU and not rehash to try to find a less hotly contended one.
To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.