Re: performance problem - Mailing list pgsql-general

As discussed on previous posts and mentioned below, the kernel is often
better at handling large amounts of buffers than Postgres.

From researching previous posts and testing on my own setups, 256mb is
about the max you want to go with shared buffers (if you have at least 1
gig ram).  After that the smaller queries (which there are usually a lot
of) slow down, and you only gain marginal improvement on the larger
queries.  You'll see an improvement slightly on the large queries going
over 256 but not enough to improve the overall performance.  There's a few
more technically detailed explanations of this in the list archive I
believe, by those who can explain it better than I.

>> > > That's VERY high.  When postgresql has to manage a lot of buffers it
>> > > actually is slower than letting the kernel in Linux or BSD do it for
> you.
>
> I am confused.  In this tutorial (by Bruce Momjian)
> http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node8.html
> it says: "As a start for tuning, use 25% of RAM for cache size, and 2-4%
> for
> sort size."
>
> If I've got 2g of RAM then that is 2097152k.  25% of that = 524288k.  So
> the
> tutorial is saying that 524288k is a good starting point for shared
> buffers
> with this amount of RAM.
>
> If each buffer is 8k
> (http://candle.pha.pa.us/main/writings/pgsql/hw_performance/node3.html)
> then that would be 65536 buffers.
>
> I'm pretty sure that that is a lot more than I need to cache every tuple
> in
> my database.  Now everytime I see someone comment on this list about
> appropriate numbers for shared buffers they say something like "(65536
> buffers is) VERY high.  Now since I obviously don't need that much shared
> cache so I am not concerned but it seems to me that one of the following
> must be true.
>
> My calculations here are wrong.
> or The tutorial is not accurate in saying that 25% is a good starting
> point.
> or The people making comments that 65536 is "VERY high" are wrong.
>
> Am I just confused or does this make sense?
>
>> > Even if you've got the memory to spare?  Does postgres actually slow
> down
>> > just because it's slower to manager a lot of them just or because
>> you're
>> > taking the memory away from the kernel so the kernel has to swap more?
>>
>> The latter, mainly, I think.  Also you *really* don't want your kernel
>> to swap out any of your shared buffers, which can happen if they take
>> up a significant portion of RAM...
>
> So if I'm not swapping at all and I've got over 1g of unused memory then
> I'm
> probably ok even with the very high buffer setting? (Although I will
> probably reduce it anyway since it is unnecessary).
>
> rg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>