PostgreSQL 9.2.4 using large amount of memory

Bhushan Pathak <bhushan.pathak02@gmail.com> writes:

In 9.1.3, this usage was upto 25MB with the same load on the same server.
With 9.2.4 it has jumped upto ~580 MB. We are monitoring the RES column
from top output to get the memory usage.

On most versions of "top", examining RES alone gives a completely
misleading impression of what's happening. RES minus SHR is a better
estimate of what the process has really consumed. I don't know why the
behavior changed from 9.1 to 9.2, but this measurement alone is not
evidence that you have an actual problem.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jan 27, 2014 at 12:12:13PM -0500, Tom Lane wrote:

Bhushan Pathak <bhushan.pathak02@gmail.com> writes:

In 9.1.3, this usage was upto 25MB with the same load on the same server.
With 9.2.4 it has jumped upto ~580 MB. We are monitoring the RES column
from top output to get the memory usage.

On most versions of "top", examining RES alone gives a completely
misleading impression of what's happening. RES minus SHR is a better
estimate of what the process has really consumed. I don't know why the
behavior changed from 9.1 to 9.2, but this measurement alone is not
evidence that you have an actual problem.

These blog entries cover memory consuption analysis:

http://momjian.us/main/blogs/pgblog/2012.html#January_30_2012
http://momjian.us/main/blogs/pgblog/2012.html#February_1_2012

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks
On 27 Jan 2014 22:35, "Bhushan Pathak" <bhushan.pathak02@gmail.com> wrote:

Hello,

We have recently shifted to postgresql version 9.2.4 from 9.1.3. After

the migration, we observed that some of our delete queries on single table
[which have triggers, which in turn call other functions] have started
consuming large amounts of memory.

In 9.1.3, this usage was upto 25MB with the same load on the same server.

With 9.2.4 it has jumped upto ~580 MB. We are monitoring the RES column
from top output to get the memory usage.

Our migration method from 9.1.3 to 9.2.4 was take a dump, un-install

9.1.3, install 9.2.4 & restore the dump.

I also went through the thread -

http://postgresql.1045698.n5.nabble.com/Memory-usage-after-upgrade-to-9-2-4-td5752733.html

In the thread in the end it is mentioned that there was some data

corruption & points to 9.1.6 release notes. I went through the release
notes & only thing of note that I found was the re-indexing or performing
vacuum operation in case of in-place upgrade, which is not the case for me.

Any help/pointers in debugging would be helpful.

Thanks
Bhushan

Just wanted to know, after upgrade as a part of process have you performed
ANALYZE on the database. I agree this might not relate to the question but
am curious to know this issue raised after proper upgrade method.

--Raghav

Bruce -
Will go through the blog posts. Thanks for the info.

Raghav -
I have not executed analyze after the upgrade.

-- Bhushan

On Wed, Jan 29, 2014 at 7:49 AM, Raghavendra <
raghavendra.rao@enterprisedb.com> wrote:

Is there any way to set max memory a postgres connection can use in
postgres or linux[centOS 5.6 64bit]?

Thanks
Bhushan

On Wed, Jan 29, 2014 at 8:39 PM, Bhushan Pathak
<bhushan.pathak02@gmail.com>wrote: