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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

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.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly