On Wed, 11 Dec 2002, Shridhar Daithankar wrote:
> On 11 Dec 2002 at 9:08, Ricardo Ryoiti S. Junior wrote:
> > > Initially upping the shared buffers help but at some pointthe advantage starts
> > > to disappear. Decide that figure with trial and error but certainly it will be
> > > around 100-200MB for most cases..
> >
> > Are there any studies around this? I remember that there where
>
> Well, you should be able to test it if you have big enough setup but.. anyway
> (I don't have it now either)
I have a machine with 1.5 Gigs of ram, and so far upping shared buffers
past 4096 (32 Megs) hasn't really seemed to make much of a difference in
performance.
I think what people may be forgetting here is that it is likely that the
Linux kernel level file cachine algorhythms are more efficient than the
ones in postgresql.
If the ones in the linux kernel are optimized to cache hundreds and
hundreds of megs of data, while the ones in postgresql were designed to
hand tens of megs of data, then it might well be slower to have postgresql
try to cache the files.
In the early days of CPU design, it was not uncommon to have chips run
slower as their caches got bigger due to issues of cache lookup taking
longer and longer. I.e. you've got to "index" your cache, and indexing
isn't free. So, if the kernel is signifigantly more efficient at caching
large datasets, then letting the kernel do it makes the most sense.
Don't ASSUME your database is better at caching then the kernel, prove it
to yourself first if you are gonna try huge caches.
My experience has been that beyond 200 megs or so, postgresql caching
doesn't seem to speed things up much, no matter how large the data set.