MIME-Version: 1.0
From: Ramakrishna Reddy Nandyala <rkreddy.n@gmail.com>
Date: Mon, 4 Aug 2025 21:39:58 +0530
Message-ID: 
 <CA++_dZP-qVB9MkJd9YX5njMr6=wEn3-5D5PjgchT3ceMJK3pWg@mail.gmail.com>
Subject: Clearing of temp files is very slow in pg16
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000856432063b8c571e"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2B%2B_dZP-qVB9MkJd9YX5njMr6%3DwEn3-5D5PjgchT3ceMJK3pWg%40mail.gmail.com>
Precedence: bulk

--000000000000856432063b8c571e
Content-Type: text/plain; charset="UTF-8"

Hi Team

System details

We are using pg-16.8 on rhel 9.x
RAM-96GB
CPU-16

We have configured 25% RAM as shared buffers,50% as effective cache.we have
configured 50% RAM for Huge page settings

Max connection set to 2000
Work-mem -4 mb
Maintenance work mem set to 1 GB

Issue details
1) we have an application that selects the data and will process in
application and try to insert the data into an other table

During this process we are seeing so many temporary files been generated by
the system,after some time we are seeing the application Connection is
accumulating the memory at OS Level after reaching ~50%,we are getting Out
Of Memory error and Kernel is killing the top memory consumer process ,here
it is killing Postgres process that is doing the entire process.while
automatic Postgres Service restart ,system status showing as activating and
it is coming to Active state only after clearing of all the temporary files
crated during the query running.The clearing of temp files are very slow
such that it is causing unplanned outages to the system

how to fasten the Temp files removal?
How to get rid of this situation?
Any suggestions on the configuration to avoid the temp file generation

Thanks in advance
Ramakrishna

--000000000000856432063b8c571e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi Team=C2=A0<div dir=3D"auto"><br><div dir=3D"auto">System details<div dir=
=3D"auto"><br><div dir=3D"auto"><div dir=3D"auto">We are using pg-16.8 on r=
hel 9.x</div><div dir=3D"auto">RAM-96GB</div><div dir=3D"auto">CPU-16</div>=
<div dir=3D"auto"><br></div><div dir=3D"auto">We have configured 25% RAM as=
 shared buffers,50% as effective cache.we have configured 50% RAM for Huge =
page settings=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">Max =
connection set to 2000</div><div dir=3D"auto"><div><span style=3D"font-size=
:18px;font-style:normal;font-weight:400;letter-spacing:normal;text-indent:0=
px;text-transform:none;white-space:normal;word-spacing:0px;text-decoration:=
none;float:none;display:inline!important;background-color:rgba(0,0,0,0);bor=
der-color:rgb(0,0,0);color:rgb(0,0,0)">Work-mem -4 mb</span></div></div><di=
v dir=3D"auto">Maintenance work mem set to 1 GB</div><div dir=3D"auto">=C2=
=A0</div><div dir=3D"auto">Issue details=C2=A0</div><div dir=3D"auto">1) we=
 have an application that selects the data and will process in application =
and try to insert the data into an other table</div><div dir=3D"auto"><br><=
/div><div dir=3D"auto">During this process we are seeing so many temporary =
files been generated by the system,after some time we are seeing the applic=
ation Connection is accumulating the memory at OS Level after reaching ~50%=
,we are getting Out Of Memory error and Kernel is killing the top memory co=
nsumer process ,here it is killing Postgres process that is doing the entir=
e process.while automatic Postgres Service restart ,system status showing a=
s activating and it is coming to Active state only after clearing of all th=
e temporary files crated during the query running.The clearing of temp file=
s are very slow such that it is causing unplanned outages to the system</di=
v><div dir=3D"auto">=C2=A0</div><div dir=3D"auto">how to fasten the Temp fi=
les removal?</div><div dir=3D"auto">How to get rid of this situation?</div>=
<div dir=3D"auto">Any suggestions on the configuration to avoid the temp fi=
le generation=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">Than=
ks in advance=C2=A0</div><div dir=3D"auto">Ramakrishna=C2=A0</div><div dir=
=3D"auto"><br></div><div dir=3D"auto"><br></div></div></div></div></div>

--000000000000856432063b8c571e--