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