Re: Auto-tuning work_mem and maintenance_work_mem

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +