I have noticed that increasing the shared buffers has always had a
positive performance effect on my system. I'm not saying it will help
everyone but check out the attached (simple) benchmarks I ran. The
results have been repeatable.
I always use as many shared buffers as I can but right now I can't been
able to go above 2 GB worth until I reconfigure the kernel to take more
than 2 GB shared memory. Right now /proc/sys/kernel/shmmax is at
2192000000. Note that for some reason I was able to configure 2 GB shared
memory on a machine with 1.5 GB ram (was testing against my production
server which has 4 GB). Not sure why that is but definitely try this
yourself.
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster