Re: Changing work_mem

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)