Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct  9, 2013 at 12:25:49PM -0400, Bruce Momjian wrote:
> > 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?

In summary, that would be 615MB for shared_buffers of 2GB, assuming one
work_mem per session, and assuming you are running the maximum number of
sessions, which you would not normally do.

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