Re: Auto-tuning work_mem and maintenance_work_mem

On Thu, Oct 10, 2013 at 11:18:28AM -0700, Josh Berkus wrote:
> Bruce,
>
> >> That's way low, and frankly it's not worth bothering with this if all
> >> we're going to get is an incremental increase. In that case, let's just
> >> set the default to 4MB like Robert suggested.
> >
> > Uh, well, 100 backends at 6MB gives us 600MB, and if each backend uses
> > 3x work_mem, that gives us 1.8GB for total work_mem. This was based on
> > Andrew's concerns about possible over-commit of work_mem. I can of
> > course adjust that.
>
> That's worst-case-scenario planning -- the 3X work-mem per backend was:
> a) Solaris and
> b) data warehousing
>
> In a normal OLTP application each backend averages something like 0.25 *
> work_mem, since many queries use no work_mem at all.
>
> It also doesn't address my point that, if we are worst-case-scenario
> default-setting, we're going to end up with defaults which aren't
> materially different from the current defaults. In which case, why even
> bother with this whole exercise?

OK, here is an updated patch that is less conservative. FYI, this
thread has gone on for 80 messages, and I assume it will take many more
until we are done:

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

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

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

---------------------------------------------------------------------------

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

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

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

---------------------------------------------------------------------------

test=> SHOW shared_buffers;
shared_buffers
----------------
8GB
(1 row)

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

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

Patch attached.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +