Re: Increased memory utilization by pgsql backend after upgrade from 9.1.3 to 9.2.6

Hi,

I managed to get a valgrind (--tool=massif) dump and ms_print output of the
memory utilization by the backend process. Attached is the ms_print output.
It reports a peak heap utilization of 616 MB.

Looks like this memory is being used to cache the query plan. Any ideas,
why it is occupying such a huge heap in 9.2.6 as compared to 9.1.3 ? I know
that the same SQL function occupies around 25MB heap in 9.1.3.

Any thoughts/comments?

Thanks,
Dattaram.

On Mon, Jan 27, 2014 at 2:27 PM, Dattaram Porob <dattaram(dot)porob(at)gmail(dot)com>wrote:

> Hi,
>
> We upgraded our PG version from 9.1.3 to 9.2.6. After that, noticed a huge
> jump in the memory consumed by PG backend process during a delete query on
> one of our DB tables. The heap memory as reported in /proc/PID/smaps
> increased from 25MB to 600 MB. There are quite a few triggers setup on this
> table and I determined that the jump happens when one particular trigger
> function (SQL function) is exceuted. The queries executed in this function
> (around 3000 line SQL function) are quite complex, quite a few unions,
> joins on tables containing around 100K records. The function is created
> using the syntax:
>
> CREATE OR REPLACE FUNCTION FUNC1 (p_old TABLETYPE, p_new TABLETYPE, p_id
> character(50), p_event_type text)
> RETURNS integer AS
> $BODY$
>
> ......
> ......
> ......
>
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
>
>
> The memory gets released once the backend process exits but in our
> application we have multiple threads opening connections to PG and
> executing these delete queries. Connection pooling is being used and this
> memory is not getting released when the connection is idle; so at one point
> the machine goes OOM.
>
> For the upgrade, we have migrated the data used pg_dump and pg_restore.
>
> System Configuration:
> CentOS release 5.6 (Final)
> Mem: 9 GB
> CPU : 4 core - Intel(R) Xeon(R) CPU E5620 @ 2.40GHz
>
> PostgresQL configuration:
> max_connections = 250
> shared_buffers = 1536MB
> work_mem = 12MB
> maintenance_work_mem = 384MB
> effective_cache_size = 3072MB
>
> I have used psql to test the delete queries and used /proc/PID/smaps to
> check the memory usage of the launched backend.
>
> The heap in 'smaps' is shown as below:
> 04e3c000-29a70000 rw-p 04e3c000 00:00 0
> [heap]
> Size: 602320 kB
> Rss: 596596 kB
> Shared_Clean: 0 kB
> Shared_Dirty: 60 kB
> Private_Clean: 0 kB
> Private_Dirty: 596536 kB
> Swap: 0 kB
> Pss: 596544 kB
>
>
> We are using the RHEL-5 64-bit PotgresQL RPMs present on the PG website:
> postgresql92-9.2.6-1PGDG.rhel5.x86_64.rpm
> postgresql92-contrib-9.2.6-1PGDG.rhel5.x86_64.rpm
> postgresql92-libs-9.2.6-1PGDG.rhel5.x86_64.rpm
> postgresql92-server-9.2.6-1PGDG.rhel5.x86_64.rpm
>
> I have now compiled the PG source code and installed a version with debug
> symbols on the same machine. But I do not know how to determine what is
> taking up so much memory. Is there any data, that I can collect by
> connecting gdb, which will help ?
>
> I tried getting the memory stats (enabled SHOW_MEMORY_STATS) and captured
> the attached data but I do not know how to interpret this data and also not
> sure whether this statistics captures the heap memory used by the SQL
> function.
>
> Queries I have are:
>
> 1. Has there been any change in the way memory is allocated/released when
> SQL functions are triggered in a backend?
>
> 2. How can I determine what is taking up so much memory; basically how do
> I proceed further on this one?
>
> 3. I guess it is some data which is cached when the SQL function runs the
> first time in the backend because if I delete another row of the same table
> in the same PSQL session the memory does not jump again by that amount. Is
> there a way to indicate that such caching should not be done?
>
> Thanks,
> Datta.
>