total db lockup

From: <eugene1@sympatico.ca>
Date: 2005/08/18 Thu AM 09:24:30 EST
To: <pgsql-general@postgresql.org>
Subject: [GENERAL] 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

From: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2005/08/18 Thu PM 02:11:54 EST
To: eugene1@sympatico.ca
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] total db lockup

From: Eugene <eugene1@sympatico.ca>
Date: 2005/08/18 Thu PM 04:45:58 EST
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] total db lockup

It seems that I can't. For whatever reason, my messages are being blocked.

Eugene

From: Tom Lane <tgl@sss.pgh.pa.us>
Date: 2005/08/18 Thu PM 02:11:54 EST
To: eugene1@sympatico.ca
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] total db lockup

Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: 2005/08/18 Thu PM 08:52:17 EST
To: Eugene <eugene1@sympatico.ca>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] total db lockup

On Thu, Aug 18, 2005 at 05:20:55PM -0400, Eugene wrote:

Guys, I really need help on this. Can whoever is in charge of the
mailing list change it so that it doesn't delete my messages?

I am not in charge of the mail server. However: May I suggest you
change to a less broken mail client? Your messages show up featuring
really strange MIME content description. Even the standard gmail.com
accounts seem to work better, which is surprising for a web based email
setup. This brokenness may hint the spam processor on postgresql.org
servers to silently droo your emails.

Alternatively, you may want to "paste" your error messages and stuff on
places like http://rafb.net/paste, and then post the URLs here.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"El sentido de las cosas no viene de las cosas, sino de
las inteligencias que las aplican a sus problemas diarios
en busca del progreso." (Ernesto Hernández-Novich)

From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Date: 2005/08/19 Fri AM 11:22:04 EST
To: Eugene <eugene1@sympatico.ca>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] total db lockup

On Fri, Aug 19, 2005 at 10:54:35AM -0400, Eugene wrote:

Thanks Alvaro.

Here it is again:

problem description: http://rafb.net/paste/results/bLAtIk26.html
db state before first restart: http://rafb.net/paste/results/D1Bqe125.html
db state before second restart: http://rafb.net/paste/results/D1Bqe125.html
table definition: http://rafb.net/paste/results/W35ccD49.html

Ok, so it seems the lockup occured only with the hash indexes? Then it
means we still have bugs in the locking code for those. It doesn't
surprise me. There's a reason they are not recommended, you know?

Let us know if you find the problem showing up again with btree indexes.
The hash indexes bugs should be fixed, but they are not high priority ...

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"If it wasn't for my companion, I believe I'd be having
the time of my life" (John Dunbar)