Thread: 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 (515m).
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
Date 29/06/2009
top - 07:58:49 up 21 days, 7:47, 1 user, load average: 0.73, 0.74, 0.71
Tasks: 131 total, 1 running, 129 sleeping, 0 stopped, 1 zombie
Cpu(s): 13.2% us, 1.3% sy, 0.0% ni, 83.1% id, 1.9% wa, 0.2% hi, 0.2% si
Mem: 4107392k total, 3764272k used, 343120k free, 24760k buffers
Swap: 2031608k total, 592k used, 2031016k free, 3522224k cached
PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796) SELECT
32731 postgres 16 0 854m 741m 112m 109m 3880 3540 S 12.9 2.8 11:52.47 postgres: dbtest test 10.255.100.65(57470) idle
Date 29/06/2009
top - 10:37:11 up 21 days, 10:25, 1 user, load average: 1.50, 1.60, 1.46
Tasks: 130 total, 3 running, 126 sleeping, 0 stopped, 1 zombie
Cpu(s): 13.3% us, 1.2% sy, 0.0% ni, 84.4% id, 0.7% wa, 0.2% hi, 0.2% si
Mem: 4107392k total, 4103184k used, 4208k free, 49036k buffers
Swap: 2031608k total, 592k used, 2031016k free, 3698156k cached
PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test 10.255.100.73(4796) idle
32731 postgres 16 0 854m 666m 188m 184m 3888 3540 R 25.5 4.7 25:03.44 postgres: dbtest test 10.255.100.65(57470) PARSE
Date 29/06/2009
top - 19:05:03 up 21 days, 18:53, 1 user, load average: 0.95, 0.91, 0.90
Tasks: 131 total, 1 running, 129 sleeping, 0 stopped, 1 zombie
Cpu(s): 9.2% us, 0.5% sy, 0.0% ni, 88.7% id, 1.2% wa, 0.3% hi, 0.2% si
Mem: 4107392k total, 4094680k used, 12712k free, 18320k buffers
Swap: 2031608k total, 592k used, 2031016k free, 3331036k cached
PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEM TIME+ COMMAND
9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796) SELECT
32731 postgres 16 0 853m 305m 302m 3540 3176 S 0.0 7.6 47:38.95 postgres: dbtest test 10.255.100.65(57470) idle
postgresql.conf information:
name | setting | unit
---------------------------------+--------------------------------------+------
archive_mode | on |
autovacuum_analyze_scale_factor | 0.4 |
autovacuum_analyze_threshold | 500 |
autovacuum_vacuum_threshold | 1000 |
checkpoint_segments | 15 |
checkpoint_timeout | 1800 | s
default_statistics_target | 50 |
effective_cache_size | 249600 | 8kB
fsync | on |
logging_collector | on |
maintenance_work_mem | 409600 | kB
max_connections | 100 |
max_fsm_pages | 3458000 |
shared_buffers | 64000 | 8kB
wal_buffers | 100 | 8kB
work_mem | 5120 | kB
On Mon, Jun 29, 2009 at 6:14 PM, Anderson Valadares<andervalbh@gmail.com> wrote: > Hi all > I have a software developed in Delphi as a Windows Service, but, i don't > know why, it consumns an unexpected large system memory (515m). > 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 ? > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ > COMMAND > 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 > postgres: dbtest test 10.255.100.73(4796) SELECT > 32731 postgres 16 0 854m 741m 112m 109m 3880 3540 S 12.9 2.8 11:52.47 > postgres: dbtest test 10.255.100.65(57470) idle Generally speaking, the actual delta for memory usage is the res - shared memory, which puts both of those backends at using an individual amount of memory at somewhere in the 5 to 8 meg range. The rest is shared memory, including shared_buffers and such. Seeing as you say your shared_buffers is 512M, I'm not sure where the rest of the shared memory is coming from here in top. > Mem: 4107392k total, 4103184k used, 4208k free, 49036k buffers > Swap: 2031608k total, 592k used, 2031016k free, 3698156k cached Note that your machine is still showing 3.6G or so used for caching our of 4G, so you're only using an actual amount of about 400 Meg Are you having any measurable performance issues, or just curious / worried about what seems like high memory usage? Your numbers look pretty normal to me otherwise.
Hi,
Thanks for the answer ...
But honestly I think that was a misunderstood.
The memory increase issue is showed in the DATA column.
Look how day by day it increases exponencially.
In a few days PostGres goes out of memory, close the connections and enter in a recovery mode.
I really don’t know what is causing it.
Date 29/06/2009
top - 07:58:49 up 21 days, 7:47, 1 user, load average: 0.73, 0.74, 0.71
Tasks: 131 total, 1 running, 129 sleeping, 0 stopped, 1 zombie
Cpu(s): 13.2% us, 1.3% sy, 0.0% ni, 83.1% id, 1.9% wa, 0.2% hi, 0.2% si
Mem: 4107392k total, 3764272k used, 343120k free, 24760k buffers
Swap: 2031608k total, 592k used, 2031016k free, 3522224k cached
PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796) SELECT
32731 postgres 16 0 854m 741m 112m 109m 3880 3540 S 12.9 2.8 11:52.47 postgres: dbtest test 10.255.100.65(57470) idle
Date 29/06/2009
top - 10:37:11 up 21 days, 10:25, 1 user, load average: 1.50, 1.60, 1.46
Tasks: 130 total, 3 running, 126 sleeping, 0 stopped, 1 zombie
Cpu(s): 13.3% us, 1.2% sy, 0.0% ni, 84.4% id, 0.7% wa, 0.2% hi, 0.2% si
Mem: 4107392k total, 4103184k used, 4208k free, 49036k buffers
Swap: 2031608k total, 592k used, 2031016k free, 3698156k cached
PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test 10.255.100.73(4796) idle
32731 postgres 16 0 854m 666m 188m 184m 3888 3540 R 25.5 4.7 25:03.44 postgres: dbtest test 10.255.100.65(57470) PARSE
Date 29/06/2009
top - 19:05:03 up 21 days, 18:53, 1 user, load average: 0.95, 0.91, 0.90
Tasks: 131 total, 1 running, 129 sleeping, 0 stopped, 1 zombie
Cpu(s): 9.2% us, 0.5% sy, 0.0% ni, 88.7% id, 1.2% wa, 0.3% hi, 0.2% si
Mem: 4107392k total, 4094680k used, 12712k free, 18320k buffers
Swap: 2031608k total, 592k used, 2031016k free, 3331036k cached
PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEM TIME+ COMMAND
9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796) SELECT
32731 postgres 16 0 853m 305m 302m 3540 3176 S 0.0 7.6 47:38.95 postgres: dbtest test 10.255.100.65(57470) idle
Date 30/06/2009
top - 07:41:43 up 22 days, 7:30, 1 user, load average: 0.60, 0.75, 1.16
Tasks: 136 total, 2 running, 133 sleeping, 0 stopped, 1 zombie
Cpu(s): 6.6% us, 0.9% sy, 0.0% ni, 91.7% id, 0.3% wa, 0.3% hi, 0.2% si
Mem: 4107392k total, 4101088k used, 6304k free, 18480k buffers
Swap: 2031608k total, 592k used, 2031016k free, 2971740k cached
PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND
9943 postgres 17 0 1724m 30m 1.7g 821m 873m 3540 R 27.2 42.2 325:54.83 postgres: citgis citgis 10.255.100.73(4796) SELECT
32731 postgres 16 0 853m 500m 353m 350m 2980 3540 S 0.0 8.8 61:25.21 postgres: citgis citgis 10.255.100.65(57470) idle
2009/6/30 Scott Marlowe <scott.marlowe@gmail.com>
On Mon, Jun 29, 2009 at 6:14 PM, Anderson Valadares<andervalbh@gmail.com> wrote:
> Hi all
> I have a software developed in Delphi as a Windows Service, but, i don't
> know why, it consumns an unexpected large system memory (515m).
> 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 ?> PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+Generally speaking, the actual delta for memory usage is the res -
> COMMAND
> 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33
> postgres: dbtest test 10.255.100.73(4796) SELECT
> 32731 postgres 16 0 854m 741m 112m 109m 3880 3540 S 12.9 2.8 11:52.47
> postgres: dbtest test 10.255.100.65(57470) idle
shared memory, which puts both of those backends at using an
individual amount of memory at somewhere in the 5 to 8 meg range. The
rest is shared memory, including shared_buffers and such.
Seeing as you say your shared_buffers is 512M, I'm not sure where the
rest of the shared memory is coming from here in top.Note that your machine is still showing 3.6G or so used for caching
> Mem: 4107392k total, 4103184k used, 4208k free, 49036k buffers
> Swap: 2031608k total, 592k used, 2031016k free, 3698156k cached
our of 4G, so you're only using an actual amount of about 400 Meg
Are you having any measurable performance issues, or just curious /
worried about what seems like high memory usage? Your numbers look
pretty normal to me otherwise.
Anderson Valadares wrote: [this is on Windows, DB is accessed with ODBC driver 8.4.3] > Thanks for the answer ... > > But honestly I think that was a misunderstood. > > > > The memory increase issue is showed in the DATA column. > > Look how day by day it increases exponencially. > > In a few days PostGres goes out of memory, close the > connections and enter in a recovery mode. > > I really don’t know what is causing it. > > > > > Date 29/06/2009 > > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND > 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796) SELECT > > Date 29/06/2009 > > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND > 9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test 10.255.100.73(4796) idle > > Date 29/06/2009 > > PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEM TIME+ COMMAND > 9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796) SELECT > > Date 30/06/2009 > > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND > 9943 postgres 17 0 1724m 30m 1.7g 821m 873m 3540 R 27.2 42.2 325:54.83 postgres: citgis citgis 10.255.100.73(4796)SELECT Now that is weird. How can the same backend process suddenly be connected to database "citgis" as user "citgis"? Do you have an explanation? What is your work_mem setting? This influences the amount of "private" memory a backend will allocate. Can you say more that "executes a PL/pgSQL function in a loop" about the workload? Are there long transactions? Which version of PostgreSQL is this? Yours, Laurenz Albe
On Mon, Jun 29, 2009 at 8:14 PM, Anderson Valadares<andervalbh@gmail.com> wrote: > Hi all > I have a software developed in Delphi as a Windows Service, but, i don't > know why, it consumns an unexpected large system memory (515m). > 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 ? you are definitely leaking. resident memory size (RES) of 1gb+ is not a normal situation. I bet that you have a transaction that is not being completed. First thing to check is: select * from pg_stat_activity; and see if your backend (by pid) is in IDLE, running a query, or 'IDLE in transaction.' Also, try not to top-post(paste your response _below_ mine), and send plain text email where possible. merlin
2009/7/1 Albe Laurenz <laurenz.albe@wien.gv.at> > > Anderson Valadares wrote: > [this is on Windows, DB is accessed with ODBC driver 8.4.3] > > > Thanks for the answer ... > > > > But honestly I think that was a misunderstood. > > > > > > > > The memory increase issue is showed in the DATA column. > > > > Look how day by day it increases exponencially. > > > > In a few days PostGres goes out of memory, close the > > connections and enter in a recovery mode. > > > > I really don’t know what is causing it. > > > > > > > > > > Date 29/06/2009 > > > > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND > > 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796)SELECT > > > > Date 29/06/2009 > > > > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND > > 9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test 10.255.100.73(4796)idle > > > > Date 29/06/2009 > > > > PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEM TIME+ COMMAND > > 9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796) SELECT > > > > Date 30/06/2009 > > > > PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND > > 9943 postgres 17 0 1724m 30m 1.7g 821m 873m 3540 R 27.2 42.2 325:54.83 postgres: citgis citgis 10.255.100.73(4796)SELECT > > Now that is weird. > How can the same backend process suddenly be connected to database "citgis" as user "citgis"? > Do you have an explanation? > > What is your work_mem setting? > This influences the amount of "private" memory a backend will allocate. > > Can you say more that "executes a PL/pgSQL function in a loop" about the workload? > Are there long transactions? > > Which version of PostgreSQL is this? > > Yours, > Laurenz Albe Answering ... 1. PostGre SQL 8.3.6 2. Work_mem – 5MB 3. About the connection citgis citgis ... it was my mistake. Is the same database and the same user, I was renaming to dbtest teste only for security ... 4. About the “function loop”. I have a windows service that execute a select (limit 200) each 500ms and after that it calls a PostGre procedure (developed for us). for each row returned in my select. Thanks again
2009/7/1 Merlin Moncure <mmoncure@gmail.com>: > On Mon, Jun 29, 2009 at 8:14 PM, Anderson Valadares<andervalbh@gmail.com> wrote: >> Hi all >> I have a software developed in Delphi as a Windows Service, but, i don't >> know why, it consumns an unexpected large system memory (515m). >> 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 ? > > you are definitely leaking. resident memory size (RES) of 1gb+ is > not a normal situation. > > I bet that you have a transaction that is not being completed. First > thing to check is: > > select * from pg_stat_activity; > > and see if your backend (by pid) is in IDLE, running a query, or 'IDLE > in transaction.' > > Also, try not to top-post(paste your response _below_ mine), and send > plain text email where possible. > > merlin > Well, Thank for your answer. I double check what you ask me ... About the leaking memory, i think as you that i’m having it ... but i can find where is it or in which part of the procedure is responsable. The pg_start_activity does not return any uncompleted transation. I work with subtransations inside the main transaction. Any ideia ?