Обсуждение: 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

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

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