public inbox for [email protected]
help / color / mirror / Atom feedClearing of temp files is very slow in pg16
4+ messages / 4 participants
[nested] [flat]
* Clearing of temp files is very slow in pg16
@ 2025-08-04 16:09 Ramakrishna Reddy Nandyala <[email protected]>
2025-08-04 19:23 ` Re: Clearing of temp files is very slow in pg16 Peter Gram <[email protected]>
2025-08-05 01:13 ` Re: Clearing of temp files is very slow in pg16 DINESH NAIR <[email protected]>
2025-08-18 21:21 ` RE: Clearing of temp files is very slow in pg16 [email protected]
0 siblings, 3 replies; 4+ messages in thread
From: Ramakrishna Reddy Nandyala @ 2025-08-04 16:09 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clearing of temp files is very slow in pg16
2025-08-04 16:09 Clearing of temp files is very slow in pg16 Ramakrishna Reddy Nandyala <[email protected]>
@ 2025-08-04 19:23 ` Peter Gram <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Peter Gram @ 2025-08-04 19:23 UTC (permalink / raw)
To: Ramakrishna Reddy Nandyala <[email protected]>; +Cc: [email protected]
--000000000000eecc5b063b8f0ae6
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Hi
Are you sure that postgres uses all the memory that you have set as Huge
Pages otherwise it is wasted and can not be used by anybody.
This could be 25 % of your memory that is wasted in your configuration.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Clearing of temp files is very slow in pg16
2025-08-04 16:09 Clearing of temp files is very slow in pg16 Ramakrishna Reddy Nandyala <[email protected]>
@ 2025-08-05 01:13 ` DINESH NAIR <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: DINESH NAIR @ 2025-08-05 01:13 UTC (permalink / raw)
To: Ramakrishna Reddy Nandyala <[email protected]>; [email protected] <[email protected]>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Clearing of temp files is very slow in pg16
2025-08-04 16:09 Clearing of temp files is very slow in pg16 Ramakrishna Reddy Nandyala <[email protected]>
@ 2025-08-18 21:21 ` [email protected]
2 siblings, 0 replies; 4+ messages in thread
From: [email protected] @ 2025-08-18 21:21 UTC (permalink / raw)
To: 'Ramakrishna Reddy Nandyala' <[email protected]>; [email protected]
Hi
You said : “We have configured 25% RAM as shared buffers,50% as effective cache.we have configured 50% RAM for Huge page settings”
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 <[email protected]>
Envoyé : lundi 4 août 2025 18:10
À : [email protected]
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-08-18 21:21 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-04 16:09 Clearing of temp files is very slow in pg16 Ramakrishna Reddy Nandyala <[email protected]>
2025-08-04 19:23 ` Peter Gram <[email protected]>
2025-08-05 01:13 ` DINESH NAIR <[email protected]>
2025-08-18 21:21 ` [email protected]
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox