Thread: High consumns memory

High consumns memory

From

Anderson Valadares

Date:

31 March 2009, 23:44:59

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

postgresql.conf information:

name | setting | unit

---------------------------------+--------------------------------------+------

archive_command | wal_archive_command.sh %p %f |

archive_mode | on |

autovacuum_analyze_scale_factor | 0.4 |

autovacuum_analyze_threshold | 500 |

autovacuum_vacuum_threshold | 1000 |

checkpoint_segments | 15 |

checkpoint_timeout | 1800 | s

DateStyle | ISO, DMY |

default_statistics_target | 50 |

effective_cache_size | 249600 | 8kB

fsync | on |

lc_monetary | en_US.UTF-8 |

lc_numeric | en_US.UTF-8 |

lc_time | en_US.UTF-8 |

listen_addresses | * |

log_autovacuum_min_duration | 0 | ms

log_checkpoints | on |

log_destination | stderr |

log_directory | /p01/log |

log_filename | postgresql-%Y-%m-%d_%H%M%S.log |

log_line_prefix | %t [%p]: [%l-1] |

log_lock_waits | on |

log_min_duration_statement | 250 | ms

log_min_error_statement | error |

log_rotation_age | 1440 | min

log_rotation_size | 20480 | kB

log_temp_files | 10240 | kB

logging_collector | on |

maintenance_work_mem | 409600 | kB

max_connections | 100 |

max_fsm_pages | 3458000 |

shared_buffers | 64000 | 8kB

tcp_keepalives_idle | 0 | s

wal_buffers | 100 | 8kB

work_mem | 5120 | kB

Re: High consumns memory

From

Scott Marlowe

Date:

01 April 2009, 00:30:49

On Tue, Mar 31, 2009 at 5:44 PM, Anderson Valadares
<andervalbh@gmail.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.

Re: High consumns memory

From

Anderson Valadares

Date:

01 April 2009, 18:59:59

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.marlowe@gmail.com>

On Tue, Mar 31, 2009 at 5:44 PM, Anderson Valadares
<andervalbh@gmail.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.

Re: High consumns memory

From

Scott Marlowe

Date:

01 April 2009, 19:12:15

On Wed, Apr 1, 2009 at 12:59 PM, Anderson Valadares
<andervalbh@gmail.com> wrote:
> 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 ?

I think you just aren't familiar with how memory is accounted for in
top.  Honestly, nothing looks out of place there.  Do you know VIRT
RES and SHR mean in top?  There's a good post here that explains it
for the most part:

http://www.kdedevelopers.org/node/1445