Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct  9, 2013 at 11:06:07AM -0400, Andrew Dunstan wrote:
> 
> On 10/09/2013 10:45 AM, Bruce Momjian wrote:
> >On Wed, Oct  9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:
> >>     Effectively, if every session uses one full work_mem, you end up with
> >>     total work_mem usage equal to shared_buffers.
> >>
> >>     We can try a different algorithm to scale up work_mem, but it seems wise
> >>     to auto-scale it up to some extent based on shared_buffers.
> >>
> >>
> >>In my experience a optimal value of work_mem depends on data and load, so I
> >>prefer a work_mem as independent parameter.
> >But it still is an independent parameter.  I am just changing the default.
> >
> 
> The danger with work_mem especially is that setting it too high can
> lead to crashing postgres or your system at some stage down the
> track, so autotuning it is kinda dangerous, much more dangerous than
> autotuning shared buffers.

Good point.

> The assumption that each connection won't use lots of work_mem is
> also false, I think, especially in these days of connection poolers.

OK, makes sense because the sessions last longer.

> I'm not saying don't do it, but I think we need to be quite
> conservative about it. A reasonable default might be (shared_buffers
> / (n * max_connections)) FSVO n, but I'm not sure what n should be.
> Instinct says something like 4, but I have no data to back that up.

I am fine with '4' --- worked as an effective_cache_size multipler.  ;-)
I think we should try to hit the existing defaults, which would mean we
would use this computation:
(shared_buffers / 4) / max_connections + 768k / BUFSZ

This would give us for a default 128MB shared buffers and 100
max_connections:
(16384 / 4) / 100 + (768 * 1024) / 8192

which gives us 136, and that is 136 * 8192 or 1088k, close to 1MB.

For 10x shared buffers, 163840, it gives a work_mem of 4040k, rather
than the 10M I was computing in the original patch.

How is that?

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