temporary files

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

temporary files

dangal
Hello everyone, I have a question to see if you can help me, I have set
work_mem in 100 MB but I have the following in the pgbadger

Queries generating the most temporary files (N)

Count Total size   Min size      Max size    Avg size
   58   3.24 GiB     57.15 MiB  57.15 MiB 57.15 MiB

How could it be that if you require 57 MB, 100 MB will not reach ?
thanks a lot



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


Reply | Threaded
Open this post in threaded view
|

Re: temporary files

Tomas Vondra-4
On Mon, Oct 07, 2019 at 01:52:41PM -0700, dangal wrote:

>Hello everyone, I have a question to see if you can help me, I have set
>work_mem in 100 MB but I have the following in the pgbadger
>
>Queries generating the most temporary files (N)
>
>Count Total size   Min size      Max size    Avg size
>   58   3.24 GiB     57.15 MiB  57.15 MiB 57.15 MiB
>
>How could it be that if you require 57 MB, 100 MB will not reach ?
>thanks a lot
>

The on-disk and in-memory representations are not the same, and the
on-disk one is often significantly more efficient. Data that needs 57MB
temporary file may beed ~150MB memory to perform in-memory sort, for
example.

Yet another reason may be that e.g. hash join splits the memory into
batches, and each one has to fit into work_mem. And whevener we hit the
limit, we double the number of batches, i.e. we cut the batch size in
half. Thus the size is somewhere between 50 and 100MB, with ~75MB on
average. Furthermore, we also estimate the batch size before running the
query, so we may start with unnecessarily many batches, pushing the
average batch size down even more.


FWIW it's somewhat suspicious you have 58 temp files with almost no
variability in size. That seems as if a single query was executed
repeatedly. Maybe try looking into what query that is.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: temporary files

dangal
thank you very much you take for your time
We raised the work_mem to 130 mb and there was no more problem!
Now we are seeing to improve the query, it is complicated because it is
generated by a product that we have installed in production!



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html