Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 7:11 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> There is definitely something to be said for simplicity and just up'ing
> the default would have a more dramatic impact with a setting like
> work_mem than it would with shared_buffers, imv.

Simplicity for us or for our users? Yes, shared_buffers is a decidedly
bad proxy for appropriate work_mem sizing. But we ought to do
something. The problem with setting work_mem to 4MB is that it's still
too low for the vast majority of users. And too high for a very small
number.

I wonder if we should just ship something like pgtune (in /bin, not in
/contrib) that can be optionally used at initdb time. Making something
like wal_buffers self-tuning is really compelling, but work_mem is
quite different.

I hear a lot of complaints about "the first 15 minutes experience" of
Postgres. It's easy to scoff at this kind of thing, but I think we
could do a lot better there, and at no real cost - the major blocker
to doing something like that has been fixed (of course, I refer to the
SysV shared memory limits). Is the person on a very small box where
our current very conservative defaults are appropriate? Why not ask a
few high-level questions like that to get inexperienced users started?
The tool could even have a parameter that allows a packager to pass
total system memory without bothering the user with that, and without
bothering us with having to figure out a way to make that work
correctly and portably.

--
Peter Geoghegan