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

On Wed, Oct  9, 2013 at 09:52:03AM -0700, Josh Berkus wrote:
> On 10/09/2013 09:30 AM, Stephen Frost wrote:
> >>> I went with shared_buffers because unlike the others, it is a fixed
> >>> > > allocation quantity, while the other are much more variable and harder
> >>> > > to set.  I figured we could keep our 25% estimate of shared_buffers and
> >>> > > everything else would fall in line.
> >>> > >
> >> >
> >> > I understand, but your proposal change a logic to opposite direction. Maybe
> >> > better is wait to new GUC parameter, and then implement this feature, so be
> >> > logical and simply understandable.
> > I disagree- having a better default than what we have now is going to
> > almost certainly be a huge improvement in the vast majority of cases.
> > How we arrive at the default isn't particularly relevant as long as we
> > document it.  Users who end up using the default don't do so because
> > they read the docs and said "oh, yeah, the way they calculated the
> > default makes a lot of sense", then end up using it because they never
> > open the config file, at all.
>
> FWIW,  I've been using the following calculations as "starting points"
> for work_mem with both clients and students.  In 80-90% of cases, the
> user never adjusts the thresholds again, so I'd say that passes the test
> for a "good enough" setting.

OK, I have developed the attached patch based on feedback.  I took into
account Andrew's concern that pooling might cause use of more work_mem
than you would expect in a typical session, and Robert's legitimate
concern about a destabalizing default for work_mem.  I therefore went
with the shared_buffers/4 idea.  Josh had some interesting calculations
for work_mem, but I didn't think the max value would work well as it
would confuse users and not be properly maintained by us as hardware
grew.  I also think changing those defaults between major releases would
be perhaps destabilizing.  Josh's observation that he rarely sees more
than 3x work_mem in a session helps put an upper limit on memory usage.

I did like Josh's idea about using autovacuum_max_workers for
maintenance_work_mem, though I used the shared_buffers/4 calculation.

Here are the defaults for two configurations;  first, for the 128MB
default shared_buffers:

    test=> SHOW shared_buffers;
     shared_buffers
    ----------------
     128MB
    (1 row)

    test=> SHOW work_mem;
     work_mem
    ----------
     1095kB
    (1 row)

    test=> SHOW maintenance_work_mem;
     maintenance_work_mem
    ----------------------
     10922kB
    (1 row)

and for shared_buffers of 2GB:

    test=> show shared_buffers;
     shared_buffers
    ----------------
     2GB
    (1 row)

    test=> SHOW work_mem;
     work_mem
    ----------
     6010kB
    (1 row)

    test=> SHOW maintenance_work_mem ;
     maintenance_work_mem
    ----------------------
     174762kB
    (1 row)


--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +