Changing the default configuration (was Re: [pgsql-advocacy] PostgreSQL Benchmarks) - Mailing list pgsql-hackers

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> May I make a suggestion that maybe it is time to start thinking about
> tuning the default config file, IMHO its just a little bit too
> conservative,

It's a lot too conservative.  I've been thinking for awhile that we
should adjust the defaults.

The original motivation for setting shared_buffers = 64 was so that
Postgres would start out-of-the-box on machines where SHMMAX is 1 meg
(64 buffers = 1/2 meg, leaving 1/2 meg for our other shared data
structures).  At one time SHMMAX=1M was a pretty common stock kernel
setting.  But our other data structures blew past the 1/2 meg mark
some time ago; at default settings the shmem request is now close to
1.5 meg.  So people with SHMMAX=1M have already got to twiddle their
postgresql.conf settings, or preferably learn how to increase SHMMAX.
That means there is *no* defensible reason anymore for defaulting to
64 buffers.

We could retarget to try to stay under SHMMAX=4M, which I think is
the next boundary that's significant in terms of real-world platforms
(isn't that the default SHMMAX on some BSDen?).  That would allow us
350 or so shared_buffers, which is better, but still not really a
serious choice for production work.

What I would really like to do is set the default shared_buffers to
1000.  That would be 8 meg worth of shared buffer space.  Coupled with
more-realistic settings for FSM size, we'd probably be talking a shared
memory request approaching 16 meg.  This is not enough RAM to bother
any modern machine from a performance standpoint, but there are probably
quite a few platforms out there that would need an increase in their
stock SHMMAX kernel setting before they'd take it.

So what this comes down to is making it harder for people to get
Postgres running for the first time, versus making it more likely that
they'll see decent performance when they do get it running.

It's worth noting that increasing SHMMAX is not nearly as painful as
it was back when these decisions were taken.  Most people have moved
to platforms where it doesn't even take a kernel rebuild, and we've
acquired documentation that tells how to do it on all(?) our supported
platforms.  So I think it might be okay to expect people to do it.

The alternative approach is to leave the settings where they are, and
to try to put more emphasis in the documentation on the fact that the
factory-default settings produce a toy configuration that you *must*
adjust upward for decent performance.  But we've not had a lot of
success spreading that word, I think.  With SHMMMAX too small, you
do at least get a pretty specific error message telling you so.

Comments?

            regards, tom lane