Thread: Changing work_mem

Changing work_mem

From
rihad
Date:
If I increase it in postgresql.conf and SIGHUP the master server, will 
the change be applied to all running backends, or only to the ones 
started after the change? Thanks.




Re: Changing work_mem

From
Peter Eisentraut
Date:
On 2019-08-13 17:16, rihad wrote:
> If I increase it in postgresql.conf and SIGHUP the master server, will 
> the change be applied to all running backends, or only to the ones 
> started after the change? Thanks.

It will be applied to all running backends.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: Changing work_mem

From
rihad
Date:
On 08/13/2019 07:41 PM, Peter Eisentraut wrote:
> On 2019-08-13 17:16, rihad wrote:
>> If I increase it in postgresql.conf and SIGHUP the master server, will
>> the change be applied to all running backends, or only to the ones
>> started after the change? Thanks.
> It will be applied to all running backends.
>

Thanks, but this isn't what I'm seeing in the logs.

After I increased work_mem from 256MB to 512MB I still see lines even 
with very small files created as before, including much larger ones, 
hundreds of MB.


[dbname] LOG:  temporary file: path 
"base/pgsql_tmp/pgsql_tmp93683.257381", size 594


It seems unlikely that the temp file is still 594 bytes bigger than 
512MB after the change.

Maybe some other stuff unconditionally creates temp files regardless of 
what's in work_mem?

All these "tempies" are the reason our SSD disks hosting a single 
database are seeing 1TB writes in a day, according to SMART.




Re: Changing work_mem

From
Luca Ferrari
Date:
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



Re: Changing work_mem

From
rihad
Date:
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.

ps -auxww output:

postgres   2705   43.6 27.5 34668984 27486640  -  Rs 14:00         
3:15.31 postgres: dbname dbname 192.168.0.4(60614)  (postgres)
postgres   7135   25.6 29.5 34437560 29499336  -  Ss Mon07        
19:12.55 postgres: dbname dbname 192.168.0.4(23540)  (postgres)
postgres  99760   14.8 25.9 34425200 25901744  -  Ss 13:10        
57:31.86 postgres: dbname dbname 192.168.0.4(29650)  (postgres)
postgres  28308    9.2 32.0 34445752 32050372  -  Ss 5Aug19      
83:59.83 postgres: dbname dbname 192.168.0.3(59717) (postgres)
postgres  21835    6.8 32.7 34451896 32750048  -  Ss Tue18       
266:10.50 postgres: dbname dbname 192.168.0.3(60080)  (postgres)
postgres  31957    5.2 31.7 34443704 31703072  -  Ss Mon14        
29:21.74 postgres: dbname dbname 192.168.0.3(40905)  (postgres)
postgres   2640    4.0 28.7 34435512 28667216  -  Ss 13:59         
4:10.96 postgres: dbname dbname 192.168.0.4(60537)  (postgres)
postgres  16727    4.0 32.9 34439608 32948936  -  Ss 2Aug19     
316:14.67 postgres: dbname dbname 192.168.0.3(20897) (postgres)
postgres  99672    3.6 28.3 34439608 28347760  -  Ss 13:08         
7:05.25 postgres: dbname dbname 192.168.0.3(35980)  (postgres)
postgres  48532    3.2 33.1 34451896 33078900  -  Ss 23Jul19     
374:10.75 postgres: dbname dbname 192.168.0.3(59891)  (postgres)
postgres   7141    2.8 31.6 34441656 31622616  -  Ss Mon07        
38:19.36 postgres: dbname dbname 192.168.0.4(23618)  (postgres)
postgres  14065    2.8 30.6 34431404 30568776  -  Ss Mon10        
95:06.20 postgres: dbname dbname 192.168.0.4(65211)  (postgres)




Re: Changing work_mem

From
rihad
Date:
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 )



Re: Changing work_mem

From
rihad
Date:
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...




Re: Changing work_mem

From
Rob Sargent
Date:
On 8/13/19 11:04 AM, 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 )
>
>
The files are written because work-mem was insufficient to complete the 
task at hand, so some data was flushed to disk, more data read into 
memory, processed and eventually all merged into final result.  Larger 
work-mem means more to flush, but less often.





Re: Changing work_mem

From
Laurenz Albe
Date:
rihad wrote:
> > 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...

Temporary files are created whenever the data is estimated to not
fit into "work_mem".  So it is unsurprising that you see bigger
temporary files being created if you increase "work_mem".

Big temporary files will also be created when "work_mem" is small,
but maybe they got lost in the noise of the smaller files.
You should have noticed that fewer files are created when you increase
"work_mem".

Another thing to notice is that the temporary files use another, more
compact format than the data in memory, so you need to increase
"work_mem" to more than X if you want to avoid temporary files
of size X.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: Changing work_mem

From
rihad
Date:
On 08/14/2019 11:42 AM, Laurenz Albe wrote:
> rihad wrote:
>>> 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...
> Temporary files are created whenever the data is estimated to not
> fit into "work_mem".  So it is unsurprising that you see bigger
> temporary files being created if you increase "work_mem".
>
> Big temporary files will also be created when "work_mem" is small,
> but maybe they got lost in the noise of the smaller files.
> You should have noticed that fewer files are created when you increase
> "work_mem".
>
> Another thing to notice is that the temporary files use another, more
> compact format than the data in memory, so you need to increase
> "work_mem" to more than X if you want to avoid temporary files
> of size X.
>
> Yours,
> Laurenz Albe

Thanks. In the end I increased work_mem to 2GB but temporary files are 
still being created, albeit at a much smaller total size (around 
0.2-0.25TB/day compared to 1TB/day of total disk write activity as 
witnessed by SMART's "Host_Writes_32MiB" attribute. The size of each 
file is also limited fro a few tens of bytes to no more than 90KB, so 
given their very short lifetime hopefully some of them stay inside OS 
buffers and do not even land on the SSD.

It's good that the memory is allocated by Postgres on an as-needed basis 
and freed when it is no longer needed. Thankfully those heavy queries 
employing xml are run periodically from cron and aren't part of the 
normal website activity.