> 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?
Sounds more likely to me, since you didn't originally have indexes that
you were getting a long seqscan for your DELETE statement which was
running while the sub-select was waiting.
If you were running the DELETE statement multiple times during a load
test you likely ran into a table bloat issues because of all the dead rows.
Joshua D. Drake
>
> thx,
> Peter
>
> 2006/10/21, Peter Bauer <peter.m.bauer@gmail.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@1006.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.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate