Thread: BUG #1160: Postgres causing system to halt

The following bug has been logged online:

Bug reference:      1160
Logged by:          Neeraj K Sharma

Email address:      neeraj.sharma@arroyo.tv

PostgreSQL version: 7.3.4

Operating system:   Redhat linux 7.3

Description:        Postgres causing system to halt

Details:

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

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> 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.

Is it the tables that are growing, or the indexes?  (If you're not sure,
look at pg_class.relpages, or the output of vacuum verbose, to see which
files are getting out of hand.)

If it's the tables then you probably need to increase your FSM settings.
If it's the indexes then you're kind of stuck --- 7.3 doesn't have any
solution to index bloat other than periodic REINDEX, which may be too
much of a performance hit for you.

> Every time vacuum is triggered, the system goes extreamly slugginsh,
> and results in various errors like deadlock detected(confirmed in the
> $PGDATA/../LOG/logfile).

Plain vacuums shouldn't result in any deadlocks.  Are you doing anything
unusual like taking out explicit exclusive locks on tables?

> ++++++++++++++++++++++++++++++++++++++++++++++++++ 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).

Indeed, and you have not yet provided any information with which anyone
else could reproduce or track down the problem.  We're not ignoring you,
we just can't do anything without better information.

            regards, tom lane