Re: Auto-tuning work_mem and maintenance_work_mem

Bruce,

* Bruce Momjian (bruce@momjian.us) wrote:
> On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
> > I'm really not impressed with this argument.  Either the user is going
> > to go and modify the config file, in which case I would hope that they'd
> > at least glance around at what they should change, or they're going to
> > move off PG because it's not performing well enough for them- which is
> > really what I'm trying to avoid happening during the first 15m.
>
> Well, they aren't going around and looking at other parameters now or we
> would not feel a need to auto-tune many of our defaults.

I think you're confusing things here.  There's a huge difference between
"didn't configure anything and got our defaults" and "went and changed
only one thing in postgresql.conf".  For one thing, we have a ton of the
former.  Perhaps there are some of the latter as well, but I would argue
it's a pretty small group.

> How do we handle the Python dependency, or is this all to be done in
> some other language?  I certainly am not ready to take on that job.

I agree that we can't add a Python (or really, perl) dependency, but I
don't think there's anything terribly complicated in what pgtune is
doing that couldn't be pretty easily done in C..

> One nice thing about a tool is that you can see your auto-tuned defaults
> right away, while doing this in the backend, you have to start the
> server to see the defaults.  I am not even sure how I could allow users
> to preview their defaults for different available_mem settings.

Agreed.

> > Actually, it *is* good, as Magnus pointed out.  Changing a completely
> > unrelated parameter shouldn't make all of your plans suddenly change.
> > This is mollified, but only a bit, if you have a GUC that's explicitly
> > "this changes other GUCs", but I'd much rather have a tool that can do a
> > better job to begin with and which helps the user understand what
> > parameters are available to change and why there's more than one.
>
> Well, the big question is how many users are going to use the tool, as
> we are not setting this up for experts, but for novices.

The goal would be to have the distros and/or initdb use it for the
initial configuration..  Perhaps by using debconf or similar to ask the
user, perhaps by just running it and letting it do whatever it wants.

> I think one big risk is someone changing shared_buffers and not having
> an accurate available_memory.  That might lead to some very inaccurate
> defaults.  Also, what happens if available_memory is not supplied at
> all?  Do we auto-tune just from shared_buffers, or not autotune at all,
> and then what are the defaults?  We could certainly throw an error if
> shared_buffers > available_memory.  We might just ship with
> available_memory defaulting to 256MB and auto-tune everything from
> there.

These questions are less of an issue if we simply don't have this
"available_memory" GUC (which strikes me as just adding more confusion
for users anyway, not less).  If no '--available-memory' (or whatever)
option is passed to initdb then we should have it assume some default,
yes, but my view on what that is depends on what the specific results
are.  It sounds like --avail-memory=256MB would end up setting things to
about what we have now for defaults, which is alright for
shared_buffers, imv, but not for a default work_mem (1MB is *really*
small...).
Thanks,
    Stephen