Re: High consumns memory

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.