public inbox for [email protected]  

help / color / mirror / Atom feed

From: DINESH  NAIR <[email protected]>
To: Ramakrishna Reddy Nandyala <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Clearing of temp files is very slow in pg16
Date: Tue, 5 Aug 2025 01:13:54 +0000
Message-ID: <PN4P287MB43813F5DB1150E4685153FA79C22A@PN4P287MB4381.INDP287.PROD.OUTLOOK.COM> (raw)
In-Reply-To: <CA++_dZP-qVB9MkJd9YX5njMr6=wEn3-5D5PjgchT3ceMJK3pWg@mail.gmail.com>
References: <CA++_dZP-qVB9MkJd9YX5njMr6=wEn3-5D5PjgchT3ceMJK3pWg@mail.gmail.com>



Hi ,


  1.
Try increasing work_mem from 4MB.
  2.
optimize Temp_file_limit
  3.
Check the queries which are causing memory intensive and try to optimize them using pg_stat_statements
  4.
Add swap space




Thanks


Dinesh Nair


________________________________
From: Ramakrishna Reddy Nandyala <[email protected]>
Sent: Monday, August 4, 2025 9:39 PM
To: [email protected] <[email protected]>
Subject: Clearing of temp files is very slow in pg16

You don't often get email from [email protected]. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification;
Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.
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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Clearing of temp files is very slow in pg16
  In-Reply-To: <PN4P287MB43813F5DB1150E4685153FA79C22A@PN4P287MB4381.INDP287.PROD.OUTLOOK.COM>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox