Re: Changing work_mem
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.
Re: Changing work_mem
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
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 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
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 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
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 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
On 08/13/2019 08:22 PM, Luca Ferrari wrote: On Tue, Aug 13, 2019 at 5:59 PM rihad 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
On Tue, Aug 13, 2019 at 5:59 PM rihad 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
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
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
