Thread: Caching of frequently used objects

Caching of frequently used objects

From
Yann Michel
Date:
Hi,

as there were several questions about increasing performance in dwh
systems I was looking for some hints about how to "pin" an object to the
buffer so that they are not aged out due to the space is needed by any
other process or object. I know that in oracle you can do this by
defining a seperate buffer cache and to asign an object to that special
buffer cache. So you could assign objects to one pool and all other
objects will use the default one. I think even count(*) queries could
benefit from this buffer-splitting due to indexes might be pinned to
this buffer pool. 
So my question is if there is already any comparable functionality or if
it is planed. I didn't find any comparable feature or TODO on the list.

Regards,
Yann


Re: Caching of frequently used objects

From
Bruno Wolff III
Date:
On Wed, Jan 19, 2005 at 17:44:00 +0100, Yann Michel <yann-postgresql@spline.de> wrote:
> 
> as there were several questions about increasing performance in dwh
> systems I was looking for some hints about how to "pin" an object to the
> buffer so that they are not aged out due to the space is needed by any
> other process or object. I know that in oracle you can do this by
> defining a seperate buffer cache and to asign an object to that special
> buffer cache. So you could assign objects to one pool and all other

You can't really do this. You could use a ram file system and use
tablespaces to put specified tables in that tablespace. However if
your system goes down, the ram fs goes away and that might not be so
good.

> objects will use the default one. I think even count(*) queries could
> benefit from this buffer-splitting due to indexes might be pinned to
> this buffer pool. 

This wouldn't have any special effect on count(*) queries.

> So my question is if there is already any comparable functionality or if
> it is planed. I didn't find any comparable feature or TODO on the list.

The developers seem to feel that having postgres and the os decide
what should be cached based on observed usage is better than having
the DBA do this.


Re: Caching of frequently used objects

From
Yann Michel
Date:
Hi,

On Wed, Jan 19, 2005 at 11:54:50AM -0600, Bruno Wolff III wrote:
> 
> > objects will use the default one. I think even count(*) queries could
> > benefit from this buffer-splitting due to indexes might be pinned to
> > this buffer pool. 
> 
> This wouldn't have any special effect on count(*) queries.

O.K. not full, but due to indexes may be used for some of this queries.
the indexes themselves could be pinned into the special buffer pool and
need not to be loaded into the cache.

> > So my question is if there is already any comparable functionality or if
> > it is planed. I didn't find any comparable feature or TODO on the list.
> 
> The developers seem to feel that having postgres and the os decide
> what should be cached based on observed usage is better than having
> the DBA do this.

The effect while using a seperate buffer cache for different objects,
i.e. using a lru list would stay the same. There would be "only" two
more than one buffer cache for a certain object gourp or class. In dwh
systems you would normally use a special buffer pool for your dimensions
to pin them into memory so that they are not rolled out by any large
fact table at all. In fact they can become rolled out but this may only
happen if an object belonging to the same pool should be loaded into the
cache. This is more or less the fact if the dba has sized the pin-cache
to small.

Regards,
Yann


Re: Caching of frequently used objects

From
Neil Conway
Date:
On Wed, 2005-01-19 at 19:14 +0100, Yann Michel wrote:
> The effect while using a seperate buffer cache for different objects,
> i.e. using a lru list would stay the same. There would be "only" two
> more than one buffer cache for a certain object gourp or class.

Multiple buffer caches and pinning objects in the buffer cache have both
been discussed extensively in the past. Please check the -hackers
archives.

-Neil