From: | rihad <rihad(at)mail(dot)ru> |
---|---|
To: | Luca Ferrari <fluca1978(at)gmail(dot)com> |
Cc: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Changing work_mem |
Date: | 2019-08-13 16:44:31 |
Message-ID: | 50c20aea-6f63-2cf7-1760-bb28679ff3e7@mail.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 08/13/2019 08:22 PM, Luca Ferrari wrote:
> On Tue, Aug 13, 2019 at 5:59 PM rihad <rihad(at)mail(dot)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)
From | Date | Subject | |
---|---|---|---|
Next Message | Bikram MAJUMDAR | 2019-08-13 16:50:02 | RE: Question on pgwatch |
Previous Message | Souvik Bhattacherjee | 2019-08-13 16:32:35 | Re: Bulk Inserts |