Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uo7Ii-00GYGJ-9D for pgsql-admin@arkaria.postgresql.org; Mon, 18 Aug 2025 21:21:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uo7Ig-002Ope-5W for pgsql-admin@arkaria.postgresql.org; Mon, 18 Aug 2025 21:21:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uo7If-002OpW-KT for pgsql-admin@lists.postgresql.org; Mon, 18 Aug 2025 21:21:54 +0000 Received: from mout.kundenserver.de ([212.227.17.10]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uo7Ic-000XCh-1T for pgsql-admin@lists.postgresql.org; Mon, 18 Aug 2025 21:21:53 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=msym.fr; s=s1-ionos; t=1755552107; x=1756156907; i=msalais@msym.fr; bh=BT3v+1chVVRIxTYrABwAsvN+77HKYh3GJFygTs5NWZU=; h=X-UI-Sender-Class:From:To:References:In-Reply-To:Subject:Date: Message-ID:MIME-Version:Content-Type:cc:content-transfer-encoding: content-type:date:from:message-id:mime-version:reply-to:subject: to; b=LBFpfKTF0ithH9aggBkdxnAQew6pPyY3Oa1cWcDNJ7UhWnHoXuwtXUQzPhOQ3aJd XfBV8Wx1cwTHJ8J9+F75WJw5hR6WHy1uFvH/6rmlHOIADOjxIwR4pxS/+RQh6VEI4 avIjXJjklQJdV8yS619829ZfN5I3Fo6FPQJ4NL7WdKWzXHGwrWahcxNTUKTktN7a4 Atzg/uXk3eoSo4NeFvyj1157qxyWk5H0vY1W01kNxCv7JbuLAYEU5AeMmVpMTv7PE 5hHvqKZx61Ru3MsVLKMu3mOChkD6jVAqOAeB7v/gY2ydc9ZFqi0HO/MkZ38NQIIVn R1ehpe4L+qmyOWvM5w== X-UI-Sender-Class: 55c96926-9e95-11ee-ae09-1f7a4046a0f6 Received: from msim1 ([78.194.114.252]) by mrelayeu.kundenserver.de (mreue106 [212.227.15.184]) with ESMTPSA (Nemesis) id 1MxVfj-1uckNu1EZl-010RiP; Mon, 18 Aug 2025 23:21:47 +0200 From: To: "'Ramakrishna Reddy Nandyala'" , References: In-Reply-To: Subject: RE: Clearing of temp files is very slow in pg16 Date: Mon, 18 Aug 2025 23:21:46 +0200 Message-ID: <007501dc1086$1a5f5670$4f1e0350$@msym.fr> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0076_01DC1096.DDE8C2B0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQGFcAX2Iy6xzeOTjkhVwzt/Qa28VLUWKixw Content-Language: fr X-Provags-ID: V03:K1:5vKc+pbk2beJgbqGKFborXNYAgwKwes5DY9yEv9vC6vTbZE2+Ty P9Bajpviv7gb/LYeSYIH2httIqnHdCnoGHNkDfNMSuVcmzUZfgq6YI9u9myeri2qcRKJIPi AT3vK86dq0JjpS/I4YuKBr/mem7zGeh7xHAIuKSSIeHqltUdzaX56PwewrzTSQRzTM9PFs+ S4OJVJY2rTHTtJuhjWKVQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:WHQUVy3DRGM=;oO5k0dTEnMqxyMuPgBSvFdWslbx BNFYjgVX9WgyYBPK7zhv/gNorYUa+lDVKh1oTnrydpju7ThX+QXSu+awgvQfligd8YXEgjnZq oT+ohnRnGUu1V4g7ZgkRRXUAQIpx4TT/U7LgqR5qr9vXI68iZCESSIagGGGRw9fq/cqexaNiy B9nEHKs/cgvxZph1ozhN6lJHfVpmYaacoYpizxt46scXPJ1j/G7BFjdvYfefxNfVqoMoUf3Bx kxxMIWh8N3bdZ8CxaPuJDzwO90810Lrxt3p8ni+LFMpVzDut7yJerkW6IJiLup1XgzHr/EMmR 1//tb1Ucsb82RiVO9eJE3jj6DbI6k2Uvhw412LiNfZ93qQwnEZmj0O8aMtHTvPrFz6g6ES1wJ qQjM4ux9NPTmSI8aS0LUxOp53vCHN+BkACRmkwvNi5cS8HVoBYKeVtOCGM8okPAr7ghEI4Kt4 iOlm7dNhM1KAEurtMlS3f4RP/modreR0kEDLd++qKWe3Cx1IIn8EVcsQJjjjuBbt3CPLpcxvO EMxr+O+LYgFHL54y9tg7w6hR50d4V6tncMxKR9ySkCU07uRORalt8yDSrch1MjqR0veAtTE1L QgwKyibUrbZW7z0Q7ehCjBtP7AOls+8Ia1rH/JglhIjaQ4097s+awVQd9EbVVTN7lJzVtIDvN gU3hlKr5v0IRkxhZmCzw5dUbTB0krUrgbYMia8IbU9K26bGlc3EO/9DaCxWkhXwQ48F22P6ij fk0ItIvMrCKCl7qE5f5AcUrdkJs/p5vFHh2sxlj4goOaRHn2AYaGzsQ6dRK763CA6qIlNYqHO WsOkCu1wk50LfDgbOuu2Z34PgcHcXSbU7satVfvYCWKYH7z723zRFZACj7bp8TUU4f0PRmPWe fdaxKKJE2ayjs4Oov/pyu/Q1wlqz0/sIaEIBoboRgOXEcdXIajkqp61aml259gjS8JoidGEkc 8w2wb2xbLnJ64LIi9HH92wmeReBTK9uqL1F+3qBXAepcyMHkNMnp+Jp9iiqNME4DUTAqYEids CgeNw0wSEIygG4ZlBIBNwJP5fmCen0gug0AtSBJxvsKsISleN3DMiaOTDdedSdDoHVbCEKrtL uxT42+xkDoY5mPFtO0K7fDciOsRlu67u8aNGRXa6as+kFQlCu1vz2dscnQK+UYvyEQt6d4aaH K5klrlof5Kaxr0olFThwiFPdpC6dcYeL4x3zVw35mpC46Z5IJtDL1D0KjTyz+KBHNNXnkUaf+ JSQV8V4Jlfr+5NYGSyhV9JbAzlkeGdqPOOm8I9GAgdppLjtO1pEKebnMm6jBNcsLIc6YlSwVl +AJMGSm5NcEjYVftEDLvSgin0zNTeXMiGnZtqqaDk437Jik5gEKqqDwclQEwq+uIbjshbW9I/ 6bS9BojKIucdm2nkQHCOGRHyGf6nFOgNOVvsxnsMLPXNt9Az1Ag9stXIEO09+R5ex7pHy/CVO nBoJc+oktS0AduPGVc7YHqfwxpZ1n0Hfvy5AAcsJIJSXnl8uRKAaiSt6yCX/NtWT0I9xsajdm rYQ6hncpEeFA40fW338iA3kFn0FS+gETCzA6hMp8sslgCo99z05wVAqWu+t/zbV7ZS5sMx0ed qVm87xYzo18p28kiWSrZ++4uUOQ7jJV2Cfwgr2NCyD2qLPQzCvFjQ== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0076_01DC1096.DDE8C2B0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Hi =20 You said : =E2=80=9CWe have configured 25% RAM as shared buffers,50% as = effective cache.we have configured 50% RAM for Huge page = settings=E2=80=9D So I can say that you are wasting about 25% of your RAM! =20 Apart from shared buffers (and dynamic shared memory), no other memory = areas in PostgreSQL use huge pages. =20 Regards=20 =20 Michel SALAIS De : Ramakrishna Reddy Nandyala =20 Envoy=C3=A9 : lundi 4 ao=C3=BBt 2025 18:10 =C3=80 : pgsql-admin@lists.postgresql.org Objet : Clearing of temp files is very slow in pg16 =20 Hi Team=20 =20 System details =20 We are using pg-16.8 on rhel 9.x RAM-96GB CPU-16 =20 We have configured 25% RAM as shared buffers,50% as effective cache.we = have configured 50% RAM for Huge page settings=20 =20 Max connection set to 2000 Work-mem -4 mb Maintenance work mem set to 1 GB =20 Issue details=20 1) we have an application that selects the data and will process in = application and try to insert the data into an other table =20 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 =20 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=20 =20 Thanks in advance=20 Ramakrishna=20 =20 =20 ------=_NextPart_000_0076_01DC1096.DDE8C2B0 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

Hi

 

You said : =E2=80=9CWe have = configured 25% RAM as shared buffers,50% as effective cache.we have = configured 50% RAM for Huge page = settings=E2=80=9D

So I can say that you are wasting about 25% of your = RAM!

 

Apart from shared buffers (and dynamic shared memory), no = other memory areas in PostgreSQL use huge pages.

 

Regards

 

= Michel SALAIS

De : Ramakrishna = Reddy Nandyala <rkreddy.n@gmail.com>
Envoy=C3=A9 : = lundi 4 ao=C3=BBt 2025 18:10
=C3=80 : = pgsql-admin@lists.postgresql.org
Objet : Clearing of temp = files is very slow in pg16

 

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 

 

 

------=_NextPart_000_0076_01DC1096.DDE8C2B0--