Re: [GENERAL] Momentary Delay
2015-06-15 18:19 GMT-03:00 Bill Moran : > On Mon, 15 Jun 2015 17:48:54 -0300 > Anderson Valadares wrote: > > > 2015-06-12 19:56 GMT-03:00 Bill Moran : > > > > > Please do not remove the mailing list from replies. See below. > > > > > > On Fri, 12 Jun 2015 09:21:19 -0300 > > > Anderson Valadares wrote: > > > > > > > 2015-06-08 20:33 GMT-03:00 Bill Moran : > > > > > > > > > On Mon, 8 Jun 2015 11:59:31 -0300 > > > > > Anderson Valadares wrote: > > > > > > > > > > > Hi > > > > > > We are experiencing an intermittent problem in a GIS database > from a > > > > > > client. Some times of the day is as if the PostgreSQL executed > the > > > > > slowest > > > > > > operations see below an example. The query was performed three > times, > > > > > twice > > > > > > I canceled and the third time I left it to end. The consultation > > > took 10 > > > > > > seconds to finish, but the measured execution time is 20 ms. As > we > > > have > > > > > > several queries that run every 2 seconds when this momentary > delay > > > occurs > > > > > > queries accumulate and burst the limit of 203 connections > allowed. > > > The > > > > > > interval of "momentary stop" are approximately 2 seconds > occurring at > > > > > > random times and during these stoppages occur no locks and no > > > increased > > > > > > load on the server is identified. There is a job 2/2 seconds > locks > > > > > > collection of information, running queries, etc., nmon also > collects > > > > > > information every 2 seconds. My client asks what causes these > > > momentary > > > > > > stops? because it affects all operations of the database? etc. > How > > > do I > > > > > > identify what is causing these delays in executions of > operations in > > > the > > > > > > database? > > > > > > > > > > Number of points to consider: > > > > > * Check the contents of pg_locks and see if something is waiting > on a > > > > > lock when the query is slow. > > > > > > > > There is nothing waiting when the query is slow, see: > > > > > > > > snap_datetime | waiting | count > > > > ---+-+--- > > > > 2015-06-05 09:25:00.954731-03 | f |74 > > > > 2015-06-05 09:26:00.249187-03 | f | 205 > > > > 2015-06-05 09:27:00.826874-03 | f | 207 > > > > > > I don't know what that means. Since you don't show the query that > > > generated that output, I have no idea if your statement is valid, or > > > if you're running a query that will inherently produce incorrect > > > results. > > > > Sorry the information was incomplete. Below is the query that was > > performed to extract information from tables that used to monitor the > > database. > > The snap_datetime column indicates the time that occurred monitoring, > > the waiting column tells you whether any connection was on hold and the > > column > > count tells how many connections existed at the time. > > > > select > > s.snap_datetime > > ,a.waiting > > , count(*) > > from stat_snapshot s, stat_activity a > > where s.snap_id = a.snap_id > > and s.snap_datetime >= '2015-06-05 09:25:00' > > and s.snap_datetime <= '2015-06-05 09:28:00' > > group by > > s.snap_datetime > > ,a.waiting > > order by s.snap_datetime > > ; > > snap_datetime | waiting | count > > ---+-+--- > > 2015-06-05 09:25:00.954731-03 | f |74 > > 2015-06-05 09:26:00.249187-03 | f | 205 > > 2015-06-05 09:27:00.826874-03 | f | 207 > > (3 rows) > > Given what I'm seeing above, there's still a lot of information > missing. Those tables are not standard PostgreSQL tables, and I'm > not aware of any tool that creates them, thus I have to assume > it's a cron job or something similar created in-house. Given the > query you ran and the resultant data, my first guess is that the > data in stat_snapshot and stat_activity is worthless: i.e. it's > captur
Re: [GENERAL] Momentary Delay
2015-06-12 19:56 GMT-03:00 Bill Moran : > > Please do not remove the mailing list from replies. See below. > > On Fri, 12 Jun 2015 09:21:19 -0300 > Anderson Valadares wrote: > > > 2015-06-08 20:33 GMT-03:00 Bill Moran : > > > > > On Mon, 8 Jun 2015 11:59:31 -0300 > > > Anderson Valadares wrote: > > > > > > > Hi > > > > We are experiencing an intermittent problem in a GIS database from a > > > > client. Some times of the day is as if the PostgreSQL executed the > > > slowest > > > > operations see below an example. The query was performed three times, > > > twice > > > > I canceled and the third time I left it to end. The consultation > took 10 > > > > seconds to finish, but the measured execution time is 20 ms. As we > have > > > > several queries that run every 2 seconds when this momentary delay > occurs > > > > queries accumulate and burst the limit of 203 connections allowed. > The > > > > interval of "momentary stop" are approximately 2 seconds occurring at > > > > random times and during these stoppages occur no locks and no > increased > > > > load on the server is identified. There is a job 2/2 seconds locks > > > > collection of information, running queries, etc., nmon also collects > > > > information every 2 seconds. My client asks what causes these > momentary > > > > stops? because it affects all operations of the database? etc. How > do I > > > > identify what is causing these delays in executions of operations in > the > > > > database? > > > > > > Number of points to consider: > > > * Check the contents of pg_locks and see if something is waiting on a > > > lock when the query is slow. > > > > There is nothing waiting when the query is slow, see: > > > > snap_datetime | waiting | count > > ---+-+--- > > 2015-06-05 09:25:00.954731-03 | f |74 > > 2015-06-05 09:26:00.249187-03 | f | 205 > > 2015-06-05 09:27:00.826874-03 | f | 207 > > I don't know what that means. Since you don't show the query that > generated that output, I have no idea if your statement is valid, or > if you're running a query that will inherently produce incorrect > results. > Sorry the information was incomplete. Below is the query that was performed to extract information from tables that used to monitor the database. The snap_datetime column indicates the time that occurred monitoring, the waiting column tells you whether any connection was on hold and the column count tells how many connections existed at the time. select s.snap_datetime ,a.waiting , count(*) from stat_snapshot s, stat_activity a where s.snap_id = a.snap_id and s.snap_datetime >= '2015-06-05 09:25:00' and s.snap_datetime <= '2015-06-05 09:28:00' group by s.snap_datetime ,a.waiting order by s.snap_datetime ; snap_datetime | waiting | count ---+-+--- 2015-06-05 09:25:00.954731-03 | f |74 2015-06-05 09:26:00.249187-03 | f | 205 2015-06-05 09:27:00.826874-03 | f | 207 (3 rows) > > * Also, ensure log_lock_waits is turned on for the server and check > > > PostgreSQL's logs for messages about long lock waits. > > > > There is also nothing > > This is even worse than the previous statement. What did you _do_ to > determine this? > > Since you're unable to diagnose and fix the problem on your own, the > possibility exists that the problem is with your diagnostic steps. If > you don't share the details of those steps, I have no way to help you > with them. > See the log in https://github.com/andervalbh/serv-gis01/blob/master/postgresql-2015-06-05_00.log > > > * Based on the connection behavior you describe, I'm guessing it's a > > > Tomcat app using some sort of conection pool. Whatever it is, tune > > > your connection pool settings so that the max size of the pool > doesn't > > > exceed the available PostgreSQL connections. At least that will > prevent > > > errors from happening when the problem occurs. > > > > Yes it is a tomcat application using connection pooling and will be set > to > > not exceed the limit of PostgreSQL connections, but the central point is > > that the number of connections has increased due to what I am calling > > "momentary stoppages" > > I understand that. I made the suggestion as a
Re: [GENERAL] Momentary Delay
2015-06-08 13:58 GMT-03:00 Adrian Klaver : > On 06/08/2015 09:25 AM, Anderson Valadares wrote: > >> 2015-06-08 12:30 GMT-03:00 Adrian Klaver > <mailto:adrian.kla...@aklaver.com>>: >> >> > >> >> >> Adrian >> forgiveness for my english the meant "Exists a job run every 2 seconds >> capturing information of locks, long running queries, etc." >> > > So have you looked at the Postgres logs during these time periods to see > if there is anything relevant? > > > >> >> Anderson >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > I saw the logs and there is nothing wrong.
Re: [GENERAL] Momentary Delay
2015-06-08 12:30 GMT-03:00 Adrian Klaver : > On 06/08/2015 07:59 AM, Anderson Valadares wrote: > >> Hi >> We are experiencing an intermittent problem in a GIS database from a >> client. Some times of the day is as if the PostgreSQL executed the >> slowest operations see below an example. The query was performed three >> times, twice I canceled and the third time I left it to end. The >> consultation took 10 seconds to finish, but the measured execution time >> is 20 ms. As we have several queries that run every 2 seconds when this >> momentary delay occurs queries accumulate and burst the limit of 203 >> connections allowed. The interval of "momentary stop" are approximately >> 2 seconds occurring at random times and during these stoppages occur no >> locks and no increased load on the server is identified. There is a job >> 2/2 seconds locks collection of information, running queries, etc., nmon >> also collects information every 2 seconds. >> > > I do not have an answer, but I do have a question. What does: > > "There is a job > 2/2 seconds locks collection of information, running queries, etc" > > mean? > > > > My client asks what causes > >> these momentary stops? because it affects all operations of the >> database? etc. How do I identify what is causing these delays in >> executions of operations in the database? >> >> The Server is a IBM P720 128G RAM PostgreSQL 9.2.9 on >> powerpc-ibm-aix7.1.0.0, compiled by gcc (GCC) 4.6.4, 64-bit >> >> Evolution of the number of connections for a period >> >> snap_datetime | #connections >> --+- >> 2015-06-05 09:25:00.954731-03 |74 >> 2015-06-05 09:26:00.249187-03 |205 >> 2015-06-05 09:27:00.826874-03 |207 >> 2015-06-05 09:28:00.374666-03 |73 >> 2015-06-05 09:29:00.690696-03 |75 >> >> >> Occurrence of the problem >> >> citgis=# select max(dvi_sub.dvi_cod_dados_via_iti) as >> dvi_cod_dados_via_iti, >> dvi_sub.via_cod_viagem >> from gis_dvi_dados_viagem_itinerario dvi_sub, >> gis_via_viagem via_sub, gis_iti_itinerario iti_sub >> where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem >> and via_sub.via_status_viagem = 'A' >> and via_sub.via_dt_hora_ini > now() - interval '9 hours' >> and iti_sub.lin_cod_linha = 389 >> and iti_sub.iti_sentido_itinerario = 'I' >> and iti_sub.iti_cod_itinerario_linha = >> via_sub.iti_cod_itinerario_linha >> and dvi_sub.vlo_cod_localizacao is not null >> group by dvi_sub.via_cod_viagem, >> iti_sub.iti_cod_itinerario_linha, via_sub.equ_cod_eqpto >> ; >> Cancel request sent >> ERROR: canceling statement due to user request >> Time: 2555.557 ms >> citgis=# select max(dvi_sub.dvi_cod_dados_via_iti) as >> dvi_cod_dados_via_iti, >> dvi_sub.via_cod_viagem >> from gis_dvi_dados_viagem_itinerario dvi_sub, >> gis_via_viagem via_sub, gis_iti_itinerario iti_sub >> where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem >> and via_sub.via_status_viagem = 'A' >> and via_sub.via_dt_hora_ini > now() - interval '9 hours' >> and iti_sub.lin_cod_linha = 389 >> and iti_sub.iti_sentido_itinerario = 'I' >> and iti_sub.iti_cod_itinerario_linha = >> via_sub.iti_cod_itinerario_linha >> and dvi_sub.vlo_cod_localizacao is not null >> group by dvi_sub.via_cod_viagem, >> iti_sub.iti_cod_itinerario_linha, via_sub.equ_cod_eqpto >> ; >> Cancel request sent >> ERROR: canceling statement due to user request >> Time: 2192.572 ms >> citgis=# explain analyze select max(dvi_sub.dvi_cod_dados_via_iti) as >> dvi_cod_dados_via_iti, >> dvi_sub.via_cod_viagem >> from gis_dvi_dados_viagem_itinerario dvi_sub, >> gis_via_viagem via_sub, gis_iti_itinerario iti_sub >> where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem >> and via_sub.via_status_viagem = 'A' >> and via_sub.via_dt_hora_ini > now() - interval '9 hours' >> and iti_sub.lin_cod_linha = 389 >> and iti_sub.iti_sentido_itinerario = 'I' >> and iti_sub.iti_cod_itinerario_linha = &g
[GENERAL] Momentary Delay
Hi We are experiencing an intermittent problem in a GIS database from a client. Some times of the day is as if the PostgreSQL executed the slowest operations see below an example. The query was performed three times, twice I canceled and the third time I left it to end. The consultation took 10 seconds to finish, but the measured execution time is 20 ms. As we have several queries that run every 2 seconds when this momentary delay occurs queries accumulate and burst the limit of 203 connections allowed. The interval of "momentary stop" are approximately 2 seconds occurring at random times and during these stoppages occur no locks and no increased load on the server is identified. There is a job 2/2 seconds locks collection of information, running queries, etc., nmon also collects information every 2 seconds. My client asks what causes these momentary stops? because it affects all operations of the database? etc. How do I identify what is causing these delays in executions of operations in the database? The Server is a IBM P720 128G RAM PostgreSQL 9.2.9 on powerpc-ibm-aix7.1.0.0, compiled by gcc (GCC) 4.6.4, 64-bit Evolution of the number of connections for a period snap_datetime| #connections --+- 2015-06-05 09:25:00.954731-03 | 74 2015-06-05 09:26:00.249187-03 | 205 2015-06-05 09:27:00.826874-03 | 207 2015-06-05 09:28:00.374666-03 | 73 2015-06-05 09:29:00.690696-03 | 75 Occurrence of the problem citgis=# select max(dvi_sub.dvi_cod_dados_via_iti) as dvi_cod_dados_via_iti, dvi_sub.via_cod_viagem from gis_dvi_dados_viagem_itinerario dvi_sub, gis_via_viagem via_sub, gis_iti_itinerario iti_sub where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem and via_sub.via_status_viagem = 'A' and via_sub.via_dt_hora_ini > now() - interval '9 hours' and iti_sub.lin_cod_linha = 389 and iti_sub.iti_sentido_itinerario = 'I' and iti_sub.iti_cod_itinerario_linha = via_sub.iti_cod_itinerario_linha and dvi_sub.vlo_cod_localizacao is not null group by dvi_sub.via_cod_viagem, iti_sub.iti_cod_itinerario_linha, via_sub.equ_cod_eqpto ; Cancel request sent ERROR: canceling statement due to user request Time: 2555.557 ms citgis=# select max(dvi_sub.dvi_cod_dados_via_iti) as dvi_cod_dados_via_iti, dvi_sub.via_cod_viagem from gis_dvi_dados_viagem_itinerario dvi_sub, gis_via_viagem via_sub, gis_iti_itinerario iti_sub where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem and via_sub.via_status_viagem = 'A' and via_sub.via_dt_hora_ini > now() - interval '9 hours' and iti_sub.lin_cod_linha = 389 and iti_sub.iti_sentido_itinerario = 'I' and iti_sub.iti_cod_itinerario_linha = via_sub.iti_cod_itinerario_linha and dvi_sub.vlo_cod_localizacao is not null group by dvi_sub.via_cod_viagem, iti_sub.iti_cod_itinerario_linha, via_sub.equ_cod_eqpto ; Cancel request sent ERROR: canceling statement due to user request Time: 2192.572 ms citgis=# explain analyze select max(dvi_sub.dvi_cod_dados_via_iti) as dvi_cod_dados_via_iti, dvi_sub.via_cod_viagem from gis_dvi_dados_viagem_itinerario dvi_sub, gis_via_viagem via_sub, gis_iti_itinerario iti_sub where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem and via_sub.via_status_viagem = 'A' and via_sub.via_dt_hora_ini > now() - interval '9 hours' and iti_sub.lin_cod_linha = 389 and iti_sub.iti_sentido_itinerario = 'I' and iti_sub.iti_cod_itinerario_linha = via_sub.iti_cod_itinerario_linha and dvi_sub.vlo_cod_localizacao is not null group by dvi_sub.via_cod_viagem, iti_sub.iti_cod_itinerario_linha, via_sub.equ_cod_eqpto ; Cancel request sent ERROR: canceling statement due to user request Time: 10164.015 ms citgis=# select now(); now --- 2015-06-05 09:27:22.006072-03 (1 row) Time: 0.152 ms citgis=# select max(dvi_sub.dvi_cod_dados_via_iti) as dvi_cod_dados_via_iti, dvi_sub.via_cod_viagem from gis_dvi_dados_viagem_itinerario dvi_sub, gis_via_viagem via_sub, gis_iti_itinerario iti_sub where dvi_sub.via_cod_viagem = via_sub.via_cod_viagem and via_sub.via_status_viagem = 'A' and via_sub.via_dt_hora_ini > now() - interval '9 hours' and iti_sub.lin_cod_linha = 389 and iti_sub.iti_sentido_itinerario = 'I' and iti_sub.iti_cod_itinerario_linha = via_sub.iti_cod_itinerario_linha and dvi_sub.vlo_cod_localizacao is not null group by dvi_sub.via_cod_viagem, iti_sub.iti_cod_itinerario_linha, via_sub.equ_cod_eqpto ; dvi_cod_dados_via_iti | via
[GENERAL] AIX GCC
Hello, we have a client with AIX version 7.1 what is using PostgreSQL 9.3.5 compiled with GCC 4.6.4 wonder if it's appropriate or would you rather use the XLC to obtain a better performance? Regards Anderson
Fwd: [GENERAL] High consumns memory
2009/7/1 Merlin Moncure : > On Mon, Jun 29, 2009 at 8:14 PM, Anderson Valadares > 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 ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: [GENERAL] High consumns memory
2009/7/1 Albe Laurenz > > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High consumns memory
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, 354k cached PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEMTIME+ 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 %MEMTIME+ 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 %MEMTIME+ 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 %MEMTIME+ 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 > On Mon, Jun 29, 2009 at 6:14 PM, Anderson Valadares > 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. >
[GENERAL] High consumns memory
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 ? 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, 354k cached PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEMTIME+ 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 %MEMTIME+ 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 %MEMTIME+ 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 As shown in column DATA(PID 9943) on 07:58:49 and on 19:05:03(515m) been a significant increase in the consumption of memory. 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
Re: [GENERAL] 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 > On Tue, Mar 31, 2009 at 5:44 PM, Anderson Valadares > 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 %MEMTIME+ 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. >
[GENERAL] 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 %MEMTIME+ 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
