Re: Keep (Recycle) Buffer Question

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Wed, 4 Sep 2002 01:14:28 +1000
Message-ID: <3d74d3dd$0$31144$afc38c87@news.optusnet.com.au>

Tue, 3 Sep 2002 07:30:54 +0000 (UTC), Peter Bruhn said (and I quote):
> I am not a DBA, but I have followed the long thread of the cach/keep-buffer
> discussion a bit.

I'm reluctant to give advice on this to a non-DBA. Please DO CHECK WITH A QUALIFIED DBA before recommending/making ANY changes.

> We have performance problems in our DB all the time.

Have you identified where the performance problem is? You cannot fix an I/O problem with cache partitioning. Nor can you fix bad index usage or an optimizer problem with it. Or inefficient SQL.

> I have understood from the above discussion that it is useful to put small
> lookup tables (+indexes) and some large tables (+indexes) that are heavily
> used into a seperate buffer that is large enough to hold all these objects.

Only for tables/indexes that SELDOM receive any updates/inserts/deletes. And the buffer does NOT need to be large enough to contain all these objects. Just big enough to contain the most active sub-set of these. You need to do some research on which tables/indexes fit that pattern before you can start to make them KEEP. This is done by sampling the buffer contents for a while, knowing the pattern of use of the application during that sampling. Not just an arse-pluck.

> This buffer can be called keep-buffer. Then you put large tables (+indexes
> [?]) that are used very seldom into a third rather small buffer, call it the
> recycle buffer.

Not quite. You put tables/indexes that have LOTS of update/inserts/deletes/truncates/populates into RECYCLE. Ie, what is normally called "highly volatile objects". Regardless of their size. Again, you size this buffer based on active sets. You don't want it to have less than about 30% hit ratio. More is better, although above 80 and something may be wrong with your selection.

> Now we have a large database with many, many tables. How do I identify what
> tables to put in what buffer. Do I have to make these decisions "by hand". Or
> is there any tool (query) to help me make this decision?

There are.  www.ixora.com.au has a few if you don't want to roll-out your 
own.  But don't.  Consult with an experienced DBA.  This technique is a 
fine tuning one.  Not a coarse tuning technique.  

It won't fix incorrectly coded SQL or lack of suitable indexes. It won't fix a hardware I/O bottleneck. It won't fix an optimizer gone waco.

It is a FINE TUNING technique. That means you do it LAST, when you have fixed everything else or are reasonably sure nothing else is causing problems.

> Thanks for any hints, comments, flames,

No flames. Just warnings. Use the right tool for the job. Cure the disease, not the symptom.

-- 
Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam

Received on Tue Sep 03 2002 - 10:14:28 CDT