Обсуждение: Clearing of temp files is very slow in pg16

Поиск
Список
Период
Сортировка

Clearing of temp files is very slow in pg16

От
Ramakrishna Reddy Nandyala
Дата:
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 


Re: Clearing of temp files is very slow in pg16

От
Peter Gram
Дата:
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. From the documentation It is primary shared buffers that used Huge Page.
You should start by checking how much of the Huge page memory that is allocated from Liunx by looking at /proc/meminfo for the values 1) to verify that you are using the huge page memory

1)
HugePages_Total:       
HugePages_Free:        
HugePages_Rsvd:        
HugePages_Surp:        
Hugepagesize:       


Med venlig hilsen

Peter Gram
Sæbyholmsvej 18 
2500 Valby

Mobile: (+45) 5374 7107



On Mon, 4 Aug 2025 at 18:10, Ramakrishna Reddy Nandyala <rkreddy.n@gmail.com> wrote:
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 


Re: Clearing of temp files is very slow in pg16

От
"DINESH NAIR"
Дата:


Hi , 



Thanks


Dinesh Nair


From: Ramakrishna Reddy Nandyala <rkreddy.n@gmail.com>
Sent: Monday, August 4, 2025 9:39 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Clearing of temp files is very slow in pg16
 
You don't often get email from rkreddy.n@gmail.com. Learn why this is important
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 


RE: Clearing of temp files is very slow in pg16

От
Дата:

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 <rkreddy.n@gmail.com>
Envoyé : lundi 4 août 2025 18:10
À : 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