> 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@xxxxxxxxx>: >> 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@xxxxxxxx>: >> > >> > > 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
