BUG #18569: Memory leak in Postgres Enterprise server

Started by PG Bug reporting formover 1 year ago13 messagesbugs

noreply@postgresql.org

over 1 year ago

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.

Daniel Gustafsson

daniel@yesql.se

over 1 year ago

In reply to: PG Bug reporting form (#1)

Re: BUG #18569: Memory leak in Postgres Enterprise server

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

Sahu, Abhisek Kumar

absahu@informatica.com

over 1 year ago

In reply to: Daniel Gustafsson (#2)

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 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

Mathias, Renci

rmathias@informatica.com

over 1 year ago

In reply to: Sahu, Abhisek Kumar (#3)

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

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 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

Yeddula, Madhusudhan reddy [CONTINGENT WORKER]

myeddula@informatica.com

over 1 year ago

In reply to: Mathias, Renci (#4)

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

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

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 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

Sahu, Abhisek Kumar

absahu@informatica.com

over 1 year ago

In reply to: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] (#5)

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

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

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

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 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

Tomas Vondra

tomas.vondra@2ndquadrant.com

over 1 year ago

In reply to: Sahu, Abhisek Kumar (#6)

Re: BUG #18569: Memory leak in Postgres Enterprise server

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

Yeddula, Madhusudhan reddy [CONTINGENT WORKER]

myeddula@informatica.com

over 1 year ago

In reply to: Sahu, Abhisek Kumar (#6)

RE: BUG #18569: Memory leak in Postgres Enterprise server

HI @Sahu, Abhisek Kumar

Have done analysis from my end and changed postgres parameters in postgresql.conf file regarding Memory/CPU and other parameters 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

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

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

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

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 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

Mathias, Renci

rmathias@informatica.com

over 1 year ago

In reply to: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] (#8)

RE: BUG #18569: Memory leak in Postgres Enterprise server

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
[cid:image001.png@01DAF22D.8F772050]

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.

Thanks,

Renci

-----Original Message-----
From: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com>
Sent: Wednesday, August 14, 2024 2:22 PM
To: Sahu, Abhisek Kumar <absahu@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 @Sahu, Abhisek Kumar

Madhusudhan | GOC Database Engineer

Informatica Global Operation Center

Office: Mob: +91 9502034172

Email: DL_GOC_Database_Services@informatica.com<mailto:DL_GOC_Database_Services@informatica.com>

-----Original Message-----

From: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>

Sent: Monday, August 12, 2024 11:08 AM

To: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>; Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>; Daniel Gustafsson <daniel@yesql.se<mailto:daniel@yesql.se>>; pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>; Sachan, Vishal <vsachan@informatica.com<mailto:vsachan@informatica.com>>

Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto:gaurkumar@informatica.com>>; Nayak, Deepak Vaikunta <dnayak@informatica.com<mailto: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<mailto:absahu@informatica.com>

-----Original Message-----

From: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>

Sent: Wednesday, August 7, 2024 4:45 PM

To: Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>; Daniel Gustafsson <daniel@yesql.se<mailto:daniel@yesql.se>>; pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>; Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>; Sachan, Vishal <vsachan@informatica.com<mailto:vsachan@informatica.com>>

Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto:gaurkumar@informatica.com>>; Nayak, Deepak Vaikunta <dnayak@informatica.com<mailto: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<mailto:DL_GOC_Database_Services@informatica.com>

-----Original Message-----

From: Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>

Sent: Wednesday, August 7, 2024 11:05 AM

To: Daniel Gustafsson <daniel@yesql.se<mailto:daniel@yesql.se>>; pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>; Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>; Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>; Sachan, Vishal <vsachan@informatica.com<mailto:vsachan@informatica.com>>

Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto: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<mailto:rmathias@informatica.com>

-----Original Message-----

From: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>

Sent: Tuesday, August 6, 2024 5:31 PM

To: Daniel Gustafsson <daniel@yesql.se<mailto:daniel@yesql.se>>; pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>; Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>; Sachan, Vishal <vsachan@informatica.com<mailto:vsachan@informatica.com>>

Cc: Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>; Kumar, Gaurav <gaurkumar@informatica.com<mailto: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<mailto:daniel@yesql.se>>

Sent: Tuesday, August 6, 2024 4:45 PM

To: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>; pgsql-bugs@lists.postgresql.org<mailto: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<mailto: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

#10

Tomas Vondra

tomas.vondra@2ndquadrant.com

over 1 year ago

In reply to: Mathias, Renci (#9)

Re: BUG #18569: Memory leak in Postgres Enterprise server

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

#11

Yeddula, Madhusudhan reddy [CONTINGENT WORKER]

myeddula@informatica.com

over 1 year ago

In reply to: Mathias, Renci (#9)

RE: BUG #18569: Memory leak in Postgres Enterprise server

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.

Please find Postgres parameters along with application logs.

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

Madhusudhan | GOC Database Engineer
Informatica Global Operation Center
Office: Mob: +91 9502034172
Email: DL_GOC_Database_Services@informatica.com<mailto:DL_GOC_Database_Services@informatica.com>
[cid:image002.png@01DB0B71.B4D6AF20]<http://www.informatica.com/>

From: Mathias, Renci <rmathias@informatica.com>
Sent: Monday, August 19, 2024 12:50 PM
To: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com>; Sahu, Abhisek Kumar <absahu@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 @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:

Below is the screenshot for your reference with reference to Operation Failure: 15th Aug 8:21 AM
[cid:image001.png@01DB0B6D.C4BF3E10]

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.

Thanks,

Renci

-----Original Message-----
From: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>
Sent: Wednesday, August 14, 2024 2:22 PM
To: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>; Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>; Daniel Gustafsson <daniel@yesql.se<mailto:daniel@yesql.se>>; pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>; Sachan, Vishal <vsachan@informatica.com<mailto:vsachan@informatica.com>>
Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto:gaurkumar@informatica.com>>; Nayak, Deepak Vaikunta <dnayak@informatica.com<mailto:dnayak@informatica.com>>
Subject: RE: BUG #18569: Memory leak in Postgres Enterprise server

HI @Sahu, Abhisek Kumar

Madhusudhan | GOC Database Engineer

Informatica Global Operation Center

Office: Mob: +91 9502034172

Email: DL_GOC_Database_Services@informatica.com<mailto:DL_GOC_Database_Services@informatica.com>

-----Original Message-----

From: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>

Sent: Monday, August 12, 2024 11:08 AM

Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto:gaurkumar@informatica.com>>; Nayak, Deepak Vaikunta <dnayak@informatica.com<mailto: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<mailto:absahu@informatica.com>

-----Original Message-----

From: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>

Sent: Wednesday, August 7, 2024 4:45 PM

Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto:gaurkumar@informatica.com>>; Nayak, Deepak Vaikunta <dnayak@informatica.com<mailto: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<mailto:DL_GOC_Database_Services@informatica.com>

-----Original Message-----

From: Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>

Sent: Wednesday, August 7, 2024 11:05 AM

Cc: Kumar, Gaurav <gaurkumar@informatica.com<mailto: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<mailto:rmathias@informatica.com>

-----Original Message-----

From: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>

Sent: Tuesday, August 6, 2024 5:31 PM

To: Daniel Gustafsson <daniel@yesql.se<mailto:daniel@yesql.se>>; pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>; Yeddula, Madhusudhan reddy [CONTINGENT WORKER] <myeddula@informatica.com<mailto:myeddula@informatica.com>>; Sachan, Vishal <vsachan@informatica.com<mailto:vsachan@informatica.com>>

Cc: Mathias, Renci <rmathias@informatica.com<mailto:rmathias@informatica.com>>; Kumar, Gaurav <gaurkumar@informatica.com<mailto: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<mailto:daniel@yesql.se>>

Sent: Tuesday, August 6, 2024 4:45 PM

To: Sahu, Abhisek Kumar <absahu@informatica.com<mailto:absahu@informatica.com>>; pgsql-bugs@lists.postgresql.org<mailto: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<mailto: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

#12

Tomas Vondra

tomas.vondra@2ndquadrant.com

over 1 year ago

In reply to: Yeddula, Madhusudhan reddy [CONTINGENT WORKER] (#11)

Re: BUG #18569: Memory leak in Postgres Enterprise server

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

#13

Tomas Vondra

tomas.vondra@2ndquadrant.com

over 1 year ago

In reply to: Tomas Vondra (#12)

Re: BUG #18569: Memory leak in Postgres Enterprise server

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

BUG #18569: Memory leak in Postgres Enterprise server

Attachments:

Attachments:

Attachments: