On 2003-06-10 15:08:22 -0400, Bruce Momjian wrote:
> For PostgreSQL, if your working set is X, if you set your shared buffers
> to X, you will get optimal performance (assuming there is no memory
> pressure). If set allocate X/2, you will probably get worse
> performance. If you allocate X*2, you will also probably get slightly
> worse performance.
>
> Now, let's suppose you can't allocate X shared buffers, because of
> memory pressure. Suppose you can allocate X/2 shared buffers, and that
> will leave X/2 kernel buffers. It would be better to allocate X/4
> shared buffers, and leave X*3/4 kernel buffers. If you can only
> allocate X/5 shared buffers, you might be better with X/10 shared
> buffers because you are going to be doing a lot of I/O, and you need
> lots of kernel buffers for that.
>
> I think that is what people are seeing when modifying shared buffers:
>
> X shared buffers is best
> >X shared buffers is too much overhead and starves kernel
> <X might be better by not maximizing shared buffers and have
> more kernel buffers
>
> Add to this that it is very hard to estimate working set.
>
Makes a lot of sense to me. We're doing a lot of I/O on a small part of that
7GB, and the rest is accessed in a more or less random fashion, so 256MB of
shared buffers sounds about right. I'll play more with this in the future to
see at what setting it performs best.
Is there any information available in the system tables or statistics
collector that can help determine X? Could PostgreSQL be easily modified to
provide more information in this area?
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/