Re: Auto-tuning work_mem and maintenance_work_mem - Mailing list pgsql-hackers

On Thu, Oct 10, 2013 at 12:00:54PM -0400, Stephen Frost wrote:
> * Bruce Momjian (bruce@momjian.us) wrote:
> > Well, I like the idea of initdb calling the tool, though the tool then
> > would need to be in C probably as we can't require python for initdb. 
> > The tool would not address Robert's issue of someone increasing
> > shared_buffers on their own.
> 
> 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.

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.

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.

> > In fact, the other constants would still
> > be hard-coded in from initdb, which isn't good.
> 
> 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.

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.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +