Hi

I am using Postgres 7.3.4 over linux Redhat 7.3 on
i686 machine.

My app has one parent table and five child tables. I
mean the parent table has a primary key and child
tables have foreign key relationship with parent. 
My App is doing 500 inserts initially in each table.
After all this done, we inserting 50 in each table and
deleting previous 50 records every seconds. System
performs well for awhile (<30 Hrs). After 30 hrs I
seen that dir size of $PGDATA/base dir is keep on
growing and to goes up to 2G in 48 hrs. App is also
doing vacuum every 45 seconds. Every time vacuum is
triggered, the system goes extreamly slugginsh, and
results in various errors like deadlock
detected(confirmed in the $PGDATA/../LOG/logfile).
vmstat is also showing that blocks sents to the block
device (disk) is going crazy.
I do not know what is the remedy for this problem. If
someone has come across to the issue, please help me
as soon as possible. 
++++++++++++++++++++++++++++++++++++++++++++++++++
NOTE: I can not use Postgres 7.4 and higher releases
beacuse postmaster crashes gauranteed in (20hrs). I
have already reported this bug many times (Bug # 1104
is one of them). All crashes show the same behavior 
and error messages. 
(specified item offset is too large)
++++++++++++++++++++++++++++++++++++++++++++++++++

I appreciate if some one have the solution for my
problem. otherwise it looks like all our app
development done on top of Postgres is going in vain.

Thanking you in advance.

Neeraj K Sharma
email: neeraj.sharma@arroyo.tv
       neerajsharma@hotmail.com

Sun, 6 Jun 2004 14:41:06 -0700 (PDT)
Neeraj Sharma <neerajk_s@yahoo.com>
Postres dilemma
Hello,

Perhaps you could provide some more detailed information?

Example of queries?
Type of hardware?
Operating system?

Why are you running a vacuum every 45 seconds? Increase your fsm_pages and
run it every hour.

Are you sure the vacuums are trampling eachother and thus getting more 
than one
vacuum running at a time?

J


Neeraj Sharma wrote:

>Hi
>
>I am using Postgres 7.3.4 over linux Redhat 7.3 on
>i686 machine.
>
>My app has one parent table and five child tables. I
>mean the parent table has a primary key and child
>tables have foreign key relationship with parent. 
>My App is doing 500 inserts initially in each table.
>After all this done, we inserting 50 in each table and
>deleting previous 50 records every seconds. System
>performs well for awhile (<30 Hrs). After 30 hrs I
>seen that dir size of $PGDATA/base dir is keep on
>growing and to goes up to 2G in 48 hrs. App is also
>doing vacuum every 45 seconds. Every time vacuum is
>triggered, the system goes extreamly slugginsh, and
>results in various errors like deadlock
>detected(confirmed in the $PGDATA/../LOG/logfile).
>vmstat is also showing that blocks sents to the block
>device (disk) is going crazy.
>I do not know what is the remedy for this problem. If
>someone has come across to the issue, please help me
>as soon as possible. 
>++++++++++++++++++++++++++++++++++++++++++++++++++
>NOTE: I can not use Postgres 7.4 and higher releases
>beacuse postmaster crashes gauranteed in (20hrs). I
>have already reported this bug many times (Bug # 1104
>is one of them). All crashes show the same behavior 
>and error messages. 
>(specified item offset is too large)
>++++++++++++++++++++++++++++++++++++++++++++++++++
>
>I appreciate if some one have the solution for my
>problem. otherwise it looks like all our app
>development done on top of Postgres is going in vain.
>
>Thanking you in advance.
>
>Neeraj K Sharma
>email: neeraj.sharma@arroyo.tv
>       neerajsharma@hotmail.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>  
>


-- 
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL


Sun, 06 Jun 2004 19:58:08 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: Postres dilemma
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Why are you running a vacuum every 45 seconds? Increase your fsm_pages and
> run it every hour.

If I understood his description correctly, he's turning over 10% of a
500-row table every minute.  So waiting an hour would mean 3000 dead
rows in a 500-live-row table, which seems excessive.  I'd agree with
running a vacuum on this specific table every five minutes or so.

Given that he is doing more than enough vacuums, I think that the
problem is probably not table bloat, but index bloat (ie, from a
constantly shifting range of live index keys, which pre-7.4 btrees
didn't handle well at all).  This is just speculation though, without
proof as yet.

			regards, tom lane

Sun, 06 Jun 2004 23:39:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Postres dilemma 
Tom Lane wrote:

> "Joshua D. Drake" <jd@commandprompt.com> writes:
> 
>>Why are you running a vacuum every 45 seconds? Increase your fsm_pages and
>>run it every hour.
> 
> 
> If I understood his description correctly, he's turning over 10% of a
> 500-row table every minute.  So waiting an hour would mean 3000 dead
> rows in a 500-live-row table, which seems excessive.  I'd agree with
> running a vacuum on this specific table every five minutes or so.
> 
> Given that he is doing more than enough vacuums, I think that the
> problem is probably not table bloat, but index bloat (ie, from a
> constantly shifting range of live index keys, which pre-7.4 btrees
> didn't handle well at all).  This is just speculation though, without
> proof as yet.

Another information to know is if there are connection in the
"unfamous" state: "Idle in transaction".

Is usefull if the OP show us the vacuum verbose output.


Regards
Gaetano Mendola




Mon, 07 Jun 2004 11:25:35 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: Postres dilemma