Thread: BUG #18569: Memory leak in Postgres Enterprise server
The following bug has been logged on the website: Bug reference: 18569 Logged by: Abhisek Sahu Email address: absahu@informatica.com PostgreSQL version: 16.3 Operating system: Redhat Linux8 Description: We are encountering a issue with our PostgreSQL database, which is impacting our operations. Issue Description: Our Informatica repository setup is enabled with PostgreSQL 16.2 database. We are running multiple concurrent operations for extended periods (10 to 12 hours), which is resulting in database connection errors. Error Details: We have observed that memory consumption by PostgreSQL processes increases gradually over time. Eventually, this leads to out-of-memory errors and process termination. The log entries are as follows- We also noticed that until the application either hangs or we manually terminate the connection on our side, the memory used by these processes on the PostgreSQL server continues to increase. This has led to severe performance issues and frequent database connection errors. Additional Information: PostgreSQL Server/Client Version: PostgreSQL 16.2 Connector in Use: LibPQ We have also attached our Repository logs, PostgreSQL server logs and DB logs. Based on our analysis, it seems that processes are being killed due to out-of-memory errors, as indicated by the logs from around 17:02 We would appreciate your assistance in analyzing and resolving this memory leak issue as soon as possible.
> On 6 Aug 2024, at 10:34, PG Bug reporting form <noreply@postgresql.org> wrote: > PostgreSQL Server/Client Version: PostgreSQL 16.2 Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB or are you running postgres installed by yourself via a package manager (or similar)? If you are buying postgres from a vendor then please contact them, if not you are in the right place. Upgrading to the latest 16.3 is also recommended even though there are no references to memory leak fixes in the release notes. > We have also attached our Repository logs, PostgreSQL server logs and DB > logs. No, there are no logs attached (I don't think the bugreporting form support attaching any files). Please provide additional information as there is nothing to go in the report. -- Daniel Gustafsson
Hi Daniel, Thank you for your response. @Sachan, Vishal please attach all the logs, including the ODL tracing. Adding @Yeddula, Madhusudhan reddy [CONTINGENT WORKER] DBA to answer the below queries. Regards Abhisek -----Original Message----- From: Daniel Gustafsson <daniel@yesql.se> Sent: Tuesday, August 6, 2024 4:45 PM To: Sahu, Abhisek Kumar <absahu@informatica.com>; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18569: Memory leak in Postgres Enterprise server Caution: This email originated from outside of the organization. Review for Phishing! > On 6 Aug 2024, at 10:34, PG Bug reporting form <noreply@postgresql.org> wrote: > PostgreSQL Server/Client Version: PostgreSQL 16.2 Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB or are you running postgresinstalled by yourself via a package manager (or similar)? If you are buying postgres from a vendor then please contactthem, if not you are in the right place. Upgrading to the latest 16.3 is also recommended even though there are no references to memory leak fixes in the releasenotes. > We have also attached our Repository logs, PostgreSQL server logs and > DB logs. No, there are no logs attached (I don't think the bugreporting form support attaching any files). Please provide additionalinformation as there is nothing to go in the report. -- Daniel Gustafsson
Hi @Daniel, The log files are attached for your reference. Please let us know if you have any queries. Thanks, Renci Monthi Mathias | QA Engineer Bagmane Tech Park, C V Raman Nagar, Bangalore - 560093 Email: rmathias@informatica.com -----Original Message----- From: Sahu, Abhisek Kumar <absahu@informatica.com> Sent: Tuesday, August 6, 2024 5:31 PM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Mathias, Renci <rmathias@informatica.com>; Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi Daniel, Thank you for your response. @Sachan, Vishal please attach all the logs, including the ODL tracing. Adding @Yeddula, Madhusudhan reddy [CONTINGENT WORKER] DBA to answer the below queries. Regards Abhisek -----Original Message----- From: Daniel Gustafsson <daniel@yesql.se> Sent: Tuesday, August 6, 2024 4:45 PM To: Sahu, Abhisek Kumar <absahu@informatica.com>; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18569: Memory leak in Postgres Enterprise server Caution: This email originated from outside of the organization. Review for Phishing! > On 6 Aug 2024, at 10:34, PG Bug reporting form <noreply@postgresql.org> wrote: > PostgreSQL Server/Client Version: PostgreSQL 16.2 Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB or are you running postgresinstalled by yourself via a package manager (or similar)? If you are buying postgres from a vendor then please contactthem, if not you are in the right place. Upgrading to the latest 16.3 is also recommended even though there are no references to memory leak fixes in the releasenotes. > We have also attached our Repository logs, PostgreSQL server logs and > DB logs. No, there are no logs attached (I don't think the bugreporting form support attaching any files). Please provide additionalinformation as there is nothing to go in the report. -- Daniel Gustafsson
Attachment
RE: BUG #18569: Memory leak in Postgres Enterprise server
From
"Yeddula, Madhusudhan reddy [CONTINGENT WORKER]"
Date:
HI @Daniel Gustafsson Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB ANS : We are not using Postgres Enterprise Server from EDB. Are you running postgres installed by yourself via a package manager (or similar)? Ans : Installed postgres myself through YUM menthod If you are buying postgres from a vendor then please contact them, if not you are in the right place. We did not buy postgres from any vendor . NOTE : We are using opensource postgresql 16.2 server on RHEL9. Madhusudhan | GOC Database Engineer Informatica Global Operation Center Office: Mob: +91 9502034172 Email: DL_GOC_Database_Services@informatica.com -----Original Message----- From: Mathias, Renci <rmathias@informatica.com> Sent: Wednesday, August 7, 2024 11:05 AM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sahu, Abhisek Kumar <absahu@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi @Daniel, The log files are attached for your reference. Please let us know if you have any queries. Thanks, Renci Monthi Mathias | QA Engineer Bagmane Tech Park, C V Raman Nagar, Bangalore - 560093 Email: rmathias@informatica.com -----Original Message----- From: Sahu, Abhisek Kumar <absahu@informatica.com> Sent: Tuesday, August 6, 2024 5:31 PM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Mathias, Renci <rmathias@informatica.com>; Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi Daniel, Thank you for your response. @Sachan, Vishal please attach all the logs, including the ODL tracing. Adding @Yeddula, Madhusudhan reddy [CONTINGENT WORKER] DBA to answer the below queries. Regards Abhisek -----Original Message----- From: Daniel Gustafsson <daniel@yesql.se> Sent: Tuesday, August 6, 2024 4:45 PM To: Sahu, Abhisek Kumar <absahu@informatica.com>; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18569: Memory leak in Postgres Enterprise server Caution: This email originated from outside of the organization. Review for Phishing! > On 6 Aug 2024, at 10:34, PG Bug reporting form <noreply@postgresql.org> wrote: > PostgreSQL Server/Client Version: PostgreSQL 16.2 Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB or are you running postgresinstalled by yourself via a package manager (or similar)? If you are buying postgres from a vendor then please contactthem, if not you are in the right place. Upgrading to the latest 16.3 is also recommended even though there are no references to memory leak fixes in the releasenotes. > We have also attached our Repository logs, PostgreSQL server logs and > DB logs. No, there are no logs attached (I don't think the bugreporting form support attaching any files). Please provide additionalinformation as there is nothing to go in the report. -- Daniel Gustafsson
Hi Daniel, Kindly let us know if you have any updates. Regards Abhisek Kumar Sahu Senior Manager Main: +91 80 40203000 Ext : 3135 Mobile: 9886680490 Email: absahu@informatica.com -----Original Message----- From: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com> Sent: Wednesday, August 7, 2024 4:45 PM To: Mathias, Renci <rmathias@informatica.com>; Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Sahu,Abhisek Kumar <absahu@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Kumar, Gaurav <gaurkumar@informatica.com>; Nayak, Deepak Vaikunta <dnayak@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server HI @Daniel Gustafsson Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB ANS : We are not using Postgres Enterprise Server from EDB. Are you running postgres installed by yourself via a package manager (or similar)? Ans : Installed postgres myself through YUM menthod If you are buying postgres from a vendor then please contact them, if not you are in the right place. We did not buy postgres from any vendor . NOTE : We are using opensource postgresql 16.2 server on RHEL9. Madhusudhan | GOC Database Engineer Informatica Global Operation Center Office: Mob: +91 9502034172 Email: DL_GOC_Database_Services@informatica.com -----Original Message----- From: Mathias, Renci <rmathias@informatica.com> Sent: Wednesday, August 7, 2024 11:05 AM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sahu, Abhisek Kumar <absahu@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi @Daniel, The log files are attached for your reference. Please let us know if you have any queries. Thanks, Renci Monthi Mathias | QA Engineer Bagmane Tech Park, C V Raman Nagar, Bangalore - 560093 Email: rmathias@informatica.com -----Original Message----- From: Sahu, Abhisek Kumar <absahu@informatica.com> Sent: Tuesday, August 6, 2024 5:31 PM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Mathias, Renci <rmathias@informatica.com>; Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi Daniel, Thank you for your response. @Sachan, Vishal please attach all the logs, including the ODL tracing. Adding @Yeddula, Madhusudhan reddy [CONTINGENT WORKER] DBA to answer the below queries. Regards Abhisek -----Original Message----- From: Daniel Gustafsson <daniel@yesql.se> Sent: Tuesday, August 6, 2024 4:45 PM To: Sahu, Abhisek Kumar <absahu@informatica.com>; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18569: Memory leak in Postgres Enterprise server Caution: This email originated from outside of the organization. Review for Phishing! > On 6 Aug 2024, at 10:34, PG Bug reporting form <noreply@postgresql.org> wrote: > PostgreSQL Server/Client Version: PostgreSQL 16.2 Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB or are you running postgresinstalled by yourself via a package manager (or similar)? If you are buying postgres from a vendor then please contactthem, if not you are in the right place. Upgrading to the latest 16.3 is also recommended even though there are no references to memory leak fixes in the releasenotes. > We have also attached our Repository logs, PostgreSQL server logs and > DB logs. No, there are no logs attached (I don't think the bugreporting form support attaching any files). Please provide additionalinformation as there is nothing to go in the report. -- Daniel Gustafsson
Hi Abhisek, On 8/12/24 07:37, Sahu, Abhisek Kumar wrote: > Hi Daniel, > > Kindly let us know if you have any updates. > It's hard to say what happened, based on the information provided so far. It might be that there really is a bug / memory leak, or maybe the selected query plan is not great, or maybe there's a configuration issue. We'll need more information to find out. 1) What query was the failed backend running? Was it a big long-running query (e.g. in ETL/BI application), or many small OLTP queries? 2) Do you know what execution plan was used? 3) If you rerun the query, does the memory usage grow the same way? This would allow us to collect important data. For example, it would be very useful if you could attach a debugger to a backend consuming a lot of memory, and get memory context stats. 4) Can you share the configuration parameters? I think the memory config is the most interesting one (shared_buffers, work_mem, ...). How much RAM does the system have? 5) Is this a parallel query? In the snippet from /var/log/messages there are 3-5 postgres processes that look like they might be parallel workers (nearly sequential PIDs, same memory pattern). 6) Is this a long-running problem, or did it just start to happen recently? Perhaps after some change/upgrade? regards -- Tomas Vondra
RE: BUG #18569: Memory leak in Postgres Enterprise server
From
"Yeddula, Madhusudhan reddy [CONTINGENT WORKER]"
Date:
HI @Sahu, Abhisek Kumar Have done analysis from my end and changed postgres parameters in postgresql.conf file regarding Memory/CPU and otherparameters as well. Kindly re run the repo and let me know if it is getting failed with same error. Madhusudhan | GOC Database Engineer Informatica Global Operation Center Office: Mob: +91 9502034172 Email: DL_GOC_Database_Services@informatica.com -----Original Message----- From: Sahu, Abhisek Kumar <absahu@informatica.com> Sent: Monday, August 12, 2024 11:08 AM To: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com>; Mathias, Renci <rmathias@informatica.com>;Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Sachan, Vishal <vsachan@informatica.com> Cc: Kumar, Gaurav <gaurkumar@informatica.com>; Nayak, Deepak Vaikunta <dnayak@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi Daniel, Kindly let us know if you have any updates. Regards Abhisek Kumar Sahu Senior Manager Main: +91 80 40203000 Ext : 3135 Mobile: 9886680490 Email: absahu@informatica.com -----Original Message----- From: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com> Sent: Wednesday, August 7, 2024 4:45 PM To: Mathias, Renci <rmathias@informatica.com>; Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Sahu,Abhisek Kumar <absahu@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Kumar, Gaurav <gaurkumar@informatica.com>; Nayak, Deepak Vaikunta <dnayak@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server HI @Daniel Gustafsson Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB ANS : We are not using Postgres Enterprise Server from EDB. Are you running postgres installed by yourself via a package manager (or similar)? Ans : Installed postgres myself through YUM menthod If you are buying postgres from a vendor then please contact them, if not you are in the right place. We did not buy postgres from any vendor . NOTE : We are using opensource postgresql 16.2 server on RHEL9. Madhusudhan | GOC Database Engineer Informatica Global Operation Center Office: Mob: +91 9502034172 Email: DL_GOC_Database_Services@informatica.com -----Original Message----- From: Mathias, Renci <rmathias@informatica.com> Sent: Wednesday, August 7, 2024 11:05 AM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sahu, Abhisek Kumar <absahu@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi @Daniel, The log files are attached for your reference. Please let us know if you have any queries. Thanks, Renci Monthi Mathias | QA Engineer Bagmane Tech Park, C V Raman Nagar, Bangalore - 560093 Email: rmathias@informatica.com -----Original Message----- From: Sahu, Abhisek Kumar <absahu@informatica.com> Sent: Tuesday, August 6, 2024 5:31 PM To: Daniel Gustafsson <daniel@yesql.se>; pgsql-bugs@lists.postgresql.org; Yeddula, Madhusudhan reddy [CONTINGENT WORKER]<myeddula@informatica.com>; Sachan, Vishal <vsachan@informatica.com> Cc: Mathias, Renci <rmathias@informatica.com>; Kumar, Gaurav <gaurkumar@informatica.com> Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server Hi Daniel, Thank you for your response. @Sachan, Vishal please attach all the logs, including the ODL tracing. Adding @Yeddula, Madhusudhan reddy [CONTINGENT WORKER] DBA to answer the below queries. Regards Abhisek -----Original Message----- From: Daniel Gustafsson <daniel@yesql.se> Sent: Tuesday, August 6, 2024 4:45 PM To: Sahu, Abhisek Kumar <absahu@informatica.com>; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #18569: Memory leak in Postgres Enterprise server Caution: This email originated from outside of the organization. Review for Phishing! > On 6 Aug 2024, at 10:34, PG Bug reporting form <noreply@postgresql.org> wrote: > PostgreSQL Server/Client Version: PostgreSQL 16.2 Your report mentions "Postgres Enterprise Server", are you running a product from a vendor like EDB or are you running postgresinstalled by yourself via a package manager (or similar)? If you are buying postgres from a vendor then please contactthem, if not you are in the right place. Upgrading to the latest 16.3 is also recommended even though there are no references to memory leak fixes in the releasenotes. > We have also attached our Repository logs, PostgreSQL server logs and > DB logs. No, there are no logs attached (I don't think the bugreporting form support attaching any files). Please provide additionalinformation as there is nothing to go in the report. -- Daniel Gustafsson
On 8/19/24 09:19, Mathias, Renci wrote: > Hi @Yeddula, Madhusudhan reddy <mailto:myeddula@informatica.com>, > > Post re-running the operations, the repository experienced downtime > after 32 hours and the PID was subsequently changed. > > > > Here are the details of the run: > > * *Run Start Time:* 14th Aug 8:00 PM > * *Operation Failure:* 15th Aug 8:21 AM (The repository PID 16320 did > not change and the operation hang) > * *All Operations Hang:* 16th Aug 10:38 AM > * *Repository PID Change:* 16th Aug 11:01 AM (PID changed to 10544) > > > > Below is the screenshot for your reference with reference to Operation > Failure*:* 15th Aug 8:21 AM > > > Attached is the file with reference to Repository Logs. > > Could you please investigate the DB server logs on your end and address > the queries raised by the PostgreSQL team? Your prompt attention to this > issue would be of great help. > I'm sorry, but those errors / logs are apparently from some proprietary Informatica product. I doubt anyone here is going to trawl 7.5MB of verbose logs in search of what might have gone wrong. It's not even clear to me what should we be looking for and at what time. You'll have to do that yourself and tell us what the interesting bits are. In my previous reply from August 12 I asked for a number of things (what query was running/failed, explain plan, info on memory usage, info on config parameters, ...). AFAIK we got exactly zero of those responses. I'm sorry, but that's pretty crucial information for issues like this. I doubt anyone will be able to help you until you provide that. regards -- Tomas Vondra
On 9/20/24 12:03, Yeddula, Madhusudhan reddy [CONTINGENT WORKER] wrote:
> HI @Daniel Gustafsson <mailto:daniel@yesql.se>
>
>
>
> Postgres server is restarting multiple times when we run the use case
> with 3 JOBS. We have enough resources and never seen CPU and memory is
> gong high.
>
This thread started by claim of a memory leak, but now you claim you
don't see CPU or memory usage going up? Or do I misunderstand?
If it is a memory leak, and the memory usage grows over time, you should
be able to monitor the system, see which backend(s) allocate the memory
and inspect what queries are being executed.
>
>
> Please find Postgres parameters along with application logs.
>
Application log is not very useful for investigating this, because it
only says this:
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
So, the database connection went away, but we're none the wiser why. We
don't even know if it was the query reported in the application log
causing the issue, it might have been some other process causing the
crash, and this query is just a victim.
You need to inspect the database log, which might tell you more - either
which backend actually crashed / why, possibly some additional info.
Also look at dmesg, which should have info about OOM killer, if that's
what killed one of the processes.
>
>
> MEMORY : 128GB
>
> CPU : 32 Cores.
>
>
>
>
>
> max_connections = 200
>
> shared_buffers = 32GB
>
> effective_cache_size = 96GB
>
> maintenance_work_mem = 2GB
>
> checkpoint_completion_target = 0.9
>
> wal_buffers = 16MB
>
> default_statistics_target = 100
>
> random_page_cost = 1.1
>
> effective_io_concurrency = 200
>
> work_mem = 41943kB
>
> huge_pages = try
>
> min_wal_size = 1GB
>
> max_wal_size = 4GB
>
> max_worker_processes = 32
>
> max_parallel_workers_per_gather = 4
>
> max_parallel_workers = 32
>
> max_parallel_maintenance_workers = 4
>
These parameters seem reasonable. I don't see any obvious problem.
regards
--
Tomas Vondra
On 9/21/24 02:23, Tomas Vondra wrote: > > ... > > Application log is not very useful for investigating this, because it > only says this: > > server closed the connection unexpectedly > > This probably means the server terminated abnormally > > before or while processing the request. > > So, the database connection went away, but we're none the wiser why. We > don't even know if it was the query reported in the application log > causing the issue, it might have been some other process causing the > crash, and this query is just a victim. > > You need to inspect the database log, which might tell you more - either > which backend actually crashed / why, possibly some additional info. > Also look at dmesg, which should have info about OOM killer, if that's > what killed one of the processes. > BTW I noticed there's a separate "memory leak" report from you. Is that related to the same database/instance? -- Tomas Vondra