So can anyone offer any insight on this? BTW, I tried reposting it with attachments and it didn't show up. thanks, Eugene > > From: <eugene1@xxxxxxxxxxxx> > Date: 2005/08/18 Thu AM 09:24:30 EST > To: <pgsql-general@xxxxxxxxxxxxxx> > Subject: total db lockup > > (NOTE: reposting this for the *fifth* time because my previous messages didn't go through). > > > Hi all, > > We have experienced a really weird problem with > postgresql yesterday. When I was called in to take a > look, all the non-superuser connections were used up > and they were all in a waiting state (SELECT waiting, > UPDATE waiting, etc.). I couldn't figure out what the > problem is, but I saved the ps, pg_stat_activity, and > pg_locks state at the time (attached as > condor_db_stats.txt). BTW, when looking at the queries > in pg_stat_activity, postgresql cuts them off so you > can't see the entire string. Is there any way to > prevent that, or at least increase the character > limit? > > We restarted postgresql server and the problem > reappeared a few minutes later. I saved that state > also (condor_db_stats2.txt). At that point, after we > restarted postgresql (again), I ran VACUUM on the > entire database and did a few more things that seem to > have solved the problem (see below). > > This particular database is essentially just one flat > table (level) with a few small supporting tables. Only > the level table is heavily used. (table definition is > attached as table.txt). I noticed that one of the > indexes (level_owner_index) was a hash index. I > remembered what postgresql manual says about hash > indexes and concurrency > (http://www.postgresql.org/docs/7.4/interactive/locking-indexes.html) > and, after VACUUM finished, replaced the hash index > with a btree. I then did a REINDEX of the level table > and ANALYZE. This seems to have solved the problem -- > at least as of this morning we still have not seen any > deadlocks. > > My question is, what could have caused this to happen? > Can anyone explain this paragraph from the manual: > > "Share/exclusive page-level locks are used for > read/write access. Locks are released after the page > is processed. Page-level locks provide better > concurrency than index-level ones but are liable to > deadlocks." > > > Any other pointers to help me figure out what went > wrong and how to fix it? > > thanks, > > Eugene > > WTF? My message doesn't appear. Trying again without > attachments or inline text. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
