On 08/13/2019 08:22 PM, Luca Ferrari wrote:
On Tue, Aug 13, 2019 at 5:59 PM rihad <rihad@xxxxxxx> 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)
