Changing work_mem

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Changing work_mem

rihad
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.



Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

Peter Eisentraut-6
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


Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

rihad
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.



Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

Luca Ferrari-2
On Tue, Aug 13, 2019 at 5:59 PM rihad <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

rihad
On 08/13/2019 08:22 PM, Luca Ferrari wrote:

> On Tue, Aug 13, 2019 at 5:59 PM rihad <[hidden email]> 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)



Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

rihad
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 <[hidden email]> 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 )


Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

rihad
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 <[hidden email]> 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...



lup
Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

lup
In reply to this post by rihad

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 <[hidden email]> 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.




Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

Laurenz Albe
In reply to this post by rihad
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



Reply | Threaded
Open this post in threaded view
|

Re: Changing work_mem

rihad
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.