Re: Overload after some minutes, please help!

Hi all,

here comes an update: Currently a loadtest is running since 4 hours
which did not cause any problems so far. There are about 200000 rows
inserted, updated and deleted per hour.

We made column_indexes for location1, location2, location3, register,
type and multi_column_index for isbackup, callednumber, physicalnumber
uus1, sourcemask and priority. Additionally a BEGIN; LOCK table
tableregistrations IN EXCLUSIVE mode; before the DELETE statement and
a END afterwards.
vacuumdb for tableregistrations is running with 10sec of sleep in
between and the suggested changes of Jim to the postgresql.conf so
autovacuum should run properly too.

All other loadtests (no locking or no indexing) ended up in very high
load and an unusable system after max. one hour because of the very
long running sub-SELECT of the DELETE statement.

So i think that sometimes there were deadlocks between these 3
statements which were detected and reported by Postgre (not sure if it
could be resolved). This should be solved by locking the whole table.
Additionally the sub-SELECT took so lang that vacuum couldnt clean up
the dead rows caused by the UPDATEs and the next runtime of it was
extremely high which lead to a unrecoverable situation because there
was constant load.

Is this a reasonable assumption or impossible nonsense?

thx,
Peter

2006/10/21, Peter Bauer <peter(dot)m(dot)bauer(at)gmail(dot)com>:
> Hi,
>
> we had these problems with Version 7.4.7, you can find the old thread here:
> http://archives.postgresql.org/pgsql-general/2006-09/msg00079.php
>
> br,
> Peter
>
> 2006/10/21, Chris Mair <chrisnospam(at)1006(dot)org>:
> >
> > > its just a vacuumdb --all. We already learned that full vacuums are
> > > evil because the database was carrupted after some time.
> >
> > Wait a sec...
> > vacuum full maybe evil in the 'locks stuff and takes long to run'-sense,
> > but it should definitly NOT corrupt your database.
> >
> > Are you sure there's no issues on the hardware / system administration
> > side of things?
> >
> > Bye, Chris.
> >
> >
> >
> >
> >
> >
> >
>