Re: Auto-tuning work_mem and maintenance_work_mem

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.

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

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.

cheers

andrew