Re: postgresql.conf - Mailing list pgsql-hackers

Tom Lane wrote:

> mlw <markw@mohawksoft.com> writes:
> > "Server" would have a huge number of buffers,
>
> Do you have any evidence whatsoever that that's actually a good idea?
>
> Certainly the existing default configuration is ridiculously cramped
> for modern machines.  But I've always felt that NBuffers somewhere in
> the low thousands should be plenty.  If you have lots of main memory
> then the kernel can be expected to use it for kernel-level disk
> buffering, which should be nearly as good as buffering inside Postgres.
> (Maybe better, considering that we have some routines that scan all our
> buffers linearly ...)  Moreover, if you request a huge chunk of shared
> memory then you run a significant risk that the kernel will decide to
> start swapping parts of it, at which point it definitely becomes a
> loser.  Swapping a dirty buffer out and back in before it finally gets
> written to disk is counterproductive.  You want to keep the number of
> buffers small enough that they all stay pretty "hot" in the swapper's
> eyes.

I can't speak about routines that scan buffers linearly, but I have noticed
HUGH performance gains by increasing the number of buffers. Queries that
normally hit a few thousand blocks, can have a cache hit rate of about 80%.
(Very query specific I know)

Sort memory is also a huge gain for large queries.

I understand that there is a point of diminishing returns, cache management
vs disk access. Cache too large and poorly managed costs more than disk.
I'm not sure I've hit that point yet. On an SMP machine, it seems that a
CPU bottle neck is better than an I/O bottleneck. The CPU bottleneck is
scalable, where as an I/O bottleneck is not. Perhaps on a single process
machine, fewer buffers would be more appropriate.

>
> Basically, I think that it's best to give the kernel plenty of elbow
> room to deal with memory pressures on its own terms.  Even on a machine
> that's nominally dedicated to running Postgres.

In our database systems we have 1G of ram. Postgres is configured to use
about 1/4~1/2. (Our number of buffers is 32768). Our sort memory is 32768.

> Awhile back I suggested raising the default configuration to 1000 or
> so buffers, which would be slightly less silly than the current default
> even if it's not optimal.  Didn't get much feedback about the idea
> though.

(I am really glad we are talking about this. )

Surely your recommendation of using 1000 buffers is a great step. 8M of
shared memory on a modern system is trivial, and would make a huge impact.
Sort memory also seems exceedingly small as well, when machines ship with a
practical minimum of 256M RAM, and a probable 512M~1G, 512K seems like a
very small sort size.

Regardless of the actual numbers, I still think that more than one
"default" needs to be defined. I would bet that Postgres runs as much as a
stand-alone server as it does as a workstation database ala Access.