Answered Lruskips - Downsides?

Chris Hughes

ProgressTalk.com Sponsor
Hi all

I've read a lot about LRU skips and the benefits that setting it can bring, and I'm not going to argue with the slides that also show the benefits.

I'd like to increase my understanding and identify if their are downsides to it.

An example

I have 10,000 primary database buffers, and LRUskips is set to 10
I read the same 10,000 blocks from the database 10 times - I understand the LRU chain will contain these records.
If I now read 10,000 different blocks from disk, I understand they need to get into the chain first, so question is what would the chain look like during this process and what will it look like when it is finished?

Where I get confused is where new blocks that don't hit the skip number actually go, and which blocks get rejected out of the chain first in a real world database.

Thanks

Chris.
 

TomBascom

Curmudgeon
There is no downside. Even in that sort of very contrived example.

New blocks go to the MRU end of the chain the first time they are referenced. That will prioritize them over the old blocks. If you never touch them again and do other stuff they will eventually drift down to the LRU end and be evicted. If you reference them once in a while they will stay towards the MRU side and be kept in the buffer pool.
 

Cringer

ProgressTalk.com Moderator
Staff member
There is one downside Tom. The database starts running more effectively and people call you less meaning you have to do some real work ;)
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
There's no downside if you pick a reasonable number relative to -B. Start with 100.

It is possible to pick a value for -lruships that is so large that a block isn't re-accessed -lruskips times before it reaches the LRU end of the LRU chain and so it is evicted and this results in a cache miss the next time you access that block.

Where I get confused is where new blocks that don't hit the skip number actually go, and which blocks get rejected out of the chain first in a real world database.
I'm not sure what you mean exactly by that. When you don't use -lruskips, every time you access a DB block it is moved to the front (MRU end) of the LRU chain. So if you are reading and rereading a table, particularly a small table, very often then the table and index blocks you access will continually move to the front, move down a few spots, back to the front on next access, etc. Those blocks are always far from eviction but you're doing a lot of LRU chain maintenance and so LRU latch contention can become a bottleneck.

Let's say you set -lruskips to 10. This means that a given block won't be moved back to the front of the chain each time it is accessed. Instead it will be moved back to the front every 10th time it is accessed. The other nine accesses it just stays in its current position in the chain, still slowly being pushed down the chain as other blocks get accessed for the first or tenth time. The net effect is that frequently-accessed blocks now tend to get further down the chain than they would have before, but they still remain in -B. And LRU latch contention is reduced to a fraction (1/-lruskips) of what it would have been otherwise.
 

Chris Hughes

ProgressTalk.com Sponsor
Ok thanks all, it is clear in my mind now :D.

I need to weigh up how different customers use our databases, particularly those that have SQL connections that run mining reports - I'll set the skips lower for these to protect the core application performance.
 

Cringer

ProgressTalk.com Moderator
Staff member
I need to weigh up how different customers use our databases, particularly those that have SQL connections that run mining reports - I'll set the skips lower for these to protect the core application performance.
Not sure I completely understand the thinking here?
 

Chris Hughes

ProgressTalk.com Sponsor
My thinking being that if a big SQL query is ran a couple of times (against old trans records) I really want that to ejected from the buffer pool sooner rather than later. An lruskip setting of 100 on our apps may see useful records such as suppliers / customers etc ejected - It would take a while for them to be read 100 times IMO.

So in this scenario I'm thinking a setting of perhaps 10 would bring some benefit, whilst allowing the chain to realistically reflect the records the core users would want.
 

TheMadDBA

Active Member
In that exact scenario the customer and suppliers (and other important lookup/dimension data) should be in the alternate buffer pool. That is the only way to guarantee they don't get flushed by other activity.

With or without lruskips records are going to be flushed from the primary by large queries/reports/etc. Unless you have enough -B to cache the entire database :)
 

Chris Hughes

ProgressTalk.com Sponsor
Indeed I agree.

There are different scenarios for different customers though. For example some still have 32bit Progress on servers so I don't use alternate pool on those.

Multiple trans tables vary in size which I always tend to put in the primary pool on 64 bit - rest in alternate pool when I can.

Cheers
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I would say the use of 32-bit RDBMS is all the more reason to optimize with ABP. You are very constrained on the size of -B so you will tend to have lots of frequently-accessed data flushed from -B when large queries are run.

Even small amounts can be very helpful. I have a client that had a -B of about 315,000 in one production DB. It is about 9 GB I think; around 150 tables and 250-300 indexes. After analysis I assigned 19 tables and 42 indexes to ABP, all quite small. My -B2 is 6000 blocks, and even that is a lot more than I need. Even with that small allocation, I now have 60% of the logical I/O happening in the ABP and those objects are guaranteed not to be flushed out. This change alone significantly reduced LRU latch contention, but I also added -lruskips 100 to reduce it further.

But I think that's more of a concern on 64-bit. On 32-bit, assuming a working set larger than shared memory, your biggest bottleneck is likely disk I/O due to a low ratio of logical to physical reads. If you have small tables and indexes that are hot then using even a small ABP can help to improve that ratio, and overall application performance with it.
 

TomBascom

Curmudgeon
Sorry, I've been tied up this morning...

If the data for the big SQL query is not already in the cache a disk IO will be required to get that data. While that disk IO is taking place *thousands* of logical read operations on the core data can occur without any competition. If you assume the typical block has 64 records in it and we go with the tiny little -B 10,000 example you are going to get 10,000 / 64 = 156 breathing opportunities for core data to be touched before the buffer cache is completely fouled. There will be at least several hundred thousand touches of core data in that period. So even if your SQL query is reading bajillions of records any core application tables that are actually being used are going to get plenty of opportunities to be touched and for the counter to increment and the block to stay on the MRU end of the chain.

If those tables are not active enough to get touched while IO ops to support the big query are happening then I would argue that the benefit of keeping them in the cache is pretty minimal to start with.

Also -- rather than have to know in advance which tables might be part of these big queries and assigning them to the buffer pool there is another option explicitly designed to prevent fouling the buffer cache... private buffers. You can use -Bp as a SQL connection parameter *or* you can code it. Something like this (using sqlep):

Code:
@TransactionIsolation 0

To Enable it:
update PUB."_Myconnection" set "_MyConn-NumSeqBuffers" = 10;
commit work;

To use some (assuming not already in the -B):
select count(*) from PUB.customer;
rollback work;

To see # in use by me:
select "_MyConn-UsedSeqBuffers" from PUB."_MyConnection";

_MyConn-UsedSeqBuffers
----------------------
10

I wish I could take credit for having thought of that. But I got the code from Rich Banville ;)
 

TomBascom

Curmudgeon
FWIW -- if you are, for some obscure reason, unconvinced by my arguments setting -lruskips to 2 would reduce LRU activity by 50% and result in a buffer being evicted only if it was not touched at least twice in the period that it takes to completely fill the buffer pool with something else. Setting it to 10 would reduce LRU activity by 90%...
 

Chris Hughes

ProgressTalk.com Sponsor
Gentlemen

Thank you for all your help.

Rob - ABP on 32bit database - must say I'd never thought of making the effort to do it, usually assuming GB's on 64bit. You've provided me with another "to do" now for my list. I can imagine the benefit of caching the security, menus, lookups side of the system and like your example I would only need a few 1000 buffers ;)

Tom - that SQL sample is superb never would have thought of that in a million years. I used Bp for db analysis and backups so understand the benefit here. +1 more on the "to do" list.

I'll be at PUG Copenhagen this year and by my reckoning you all deserve a few beer tokens :D

Cheers
 
Top