Re: Changing work_mem

On 08/13/2019 09:04 PM, rihad wrote:
> On 08/13/2019 08:44 PM, rihad wrote:
>> On 08/13/2019 08:22 PM, Luca Ferrari wrote:
>>> On Tue, Aug 13, 2019 at 5:59 PM rihad <rihad@mail.ru> wrote:
>>>> [dbname] LOG:  temporary file: path
>>>> "base/pgsql_tmp/pgsql_tmp93683.257381", size 594
>>>>
>>> The setting 'work_mem' is within context 'user', that means it will
>>> affect running sessione unless the session itself has already issued a
>>> SET work_mem to xxx.
>>> So this could be a reason why you don't seem to see any change.
>>>
>>> Also keep in mind that work_mem work on a connection basis, so you are
>>> going to possibly see 521MB x num_connections if all your clients are
>>> doig the same kind of sort concurrently, which probably causes
>>> PostgreSQL to go to disk due to memory unavailable.
>>>
>>> Hope this helps.
>>> Luca
>>> .
>>>
>> Thanks. The box has 15GB mem free (as in FreeBSD )))
>>
>> And it hasn't moved a notch after the increase.
>>
>> No code does SET work_mem=... AFAIK.
>>
>> My apologies to Mr. Peter but I still think that older processes, 
>> some of them started a couple of weeks ago, use the older setting.
> Sorry, I just decreased work_mem back to 256MB, reloaded, and 
> instantly started seeing 82mb temp file creation, not 165mb as was 
> usual with work_mem=512MB.
>
> So it indeed was applied immediately.
> Really weird figures )

Increased work_mem to 768MB and start seeing temp file creation log 
entries 331MB in size.

Bizzare ) It looks like the bigger it gets, the bigger temp files are 
created.

Why not decrease it to 64mb then...