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.