Re: Auto-tuning work_mem and maintenance_work_mem

On Wed, Oct 9, 2013 at 8:06 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> 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.
>

Is this common to see? I ask because in my experience, having 100
connections all decide to do large sorts simultaneously is going to make
the server fall over, regardless of whether it tries to do them in memory
(OOM) or whether it does them with tape sorts (stuck spin locks, usually).

>
> 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 don't follow that. Why would using a connection pooler change the
multiples of work_mem that a connection would use?

Cheers,

Jeff