High consumns memory
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
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 cachedPID 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.
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>
Show quoted text
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 cachedPID 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 postgresThis 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.
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: