Thread: Caching of frequently used objects
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
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.
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
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