Re: High consumns memory

Scott

the problem is that the memory gets higher and higher each PL/SQL procedure
call.

Some “I don’t know what” is not been freed(released) from the memory after
execution.

There’s any way that I can see what is allocated and released when the
PL/SQL procedure is called or finished ?

2009/3/31 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>

> On Tue, Mar 31, 2009 at 5:44 PM, Anderson Valadares
> <andervalbh(at)gmail(dot)com> wrote:
> > I have a software developed in Delphi as a Windows Service, but, i don't
> > know why, it consumns an unexpected large system memory (1.3g).
> >
> > The service access PostgresSQL by ODBC driver (psqlodbc_08_03_0400) and
> it
> > consist simply of a loop calling a procedure PL/PGSQL. How to discover
> what
> > is causing or why this high memory usage ? What objects are being used on
> > this session ?
> >
> > Software developed in Delphi 7 as a windows service.
> >
> > PostgresSQL 8.3.6 Database with PostGis extension
> >
> > Server p52a
> >
> > S.O.: Red Hat Enterprise Linux AS release 4 (Nahant Update 1)
> >
> > linux 2.6.9-11.EL #1 SMP ppc64 ppc64 ppc64 GNU/Linux
> >
> > S.O. information
> >
> > top - 11:39:15 up 6 days, 19:15, 1 user, load average: 2.15, 2.02,
> 1.86
> > Tasks: 127 total, 1 running, 126 sleeping, 0 stopped, 0 zombie
> > Cpu(s): 9.5% us, 2.6% sy, 0.0% ni, 71.2% id, 16.3% wa, 0.1% hi, 0.2%
> si
> > Mem: 4107392k total, 4101520k used, 5872k free, 17708k buffers
> > Swap: 2031608k total, 244k used, 2031364k free, 3091708k cached
> >
> > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> > 32662 postgres 16 0 1317m 1.3g 516m D 52.5 32.1 349:57.48 postgres
> > 8953 postgres 17 0 548m 482m 479m S 33.5 12.0 2:50.09 postgres
> > 1944 postgres 16 0 550m 520m 516m S 7.3 13.0 165:30.47 postgres
> > 32659 postgres 15 0 544m 516m 514m S 1.3 12.9 16:42.60 postgres
> > 1935 postgres 15 0 543m 514m 513m S 1.0 12.8 15:15.56 postgres
>
> This doesn't look bad at all. The pgsql instances are using a pretty
> reasonable amount of memory for caching (somewhere in the 512Meg
> range) and one long running query is using a lot more memory (in the
> 600M range) Your machine has 3G of cache out of 4G of ram, and it's
> using almost not swap.
>
> Now, when this is running next time, using psql, try something like:
>
> select * from pg_stat_activity where procpid=32662;
>
> or whatever pid is using up a fair chunk of memory to see the query
> that's doing it.
>