Backend hanging..

A comlete shot in the dark:

Would using ipcclean help? (Does ipcclean work in
FreeBSD?)

See:
http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/app-ipcclean.html

Andrew Gould

--- Mitch Vincent <mvincent@cablespeed.com> wrote:

Hello guys, I was workin on a project today when I
noticed something really
strange starting to happen...

I see backends hanging in mid-query, never to
return.. I've upped the debug
level to it's max and get nothing in the logs that
indicate a problem. If I
stop and restart the postmaster then the problem
goes away for a few
minutes, only to return...

Some other queries seem to go through fine, it seems
to be centered around
the table 'customers' as it *seems* like an update
against that table will
trigger the problem..

Here is what the processes look like -- what is it
"waiting" on?

61795 p2 S 0:00.38
/usr/local/pgsql/bin/postmaster -o -F -D
/usr/local/pgsql/data -i (postgres)

61812 p2 S 0:00.58 postmaster: postgres brw
[local] VACUUM waiting
(postgres)
61823 p2 I 0:01.18 postmaster: postgres brw
127.0.0.1 idle in
transaction (postgres)
61836 p2 S 0:01.03 postmaster: postgres brw
127.0.0.1 UPDATE waiting
(postgres)
61860 p2 S 0:00.99 postmaster: postgres brw
127.0.0.1 idle in
transaction waiting (postgres)
61880 p2 S 0:00.99 postmaster: postgres brw
127.0.0.1 idle in
transaction waiting (postgres)

I'm running this on a Dual PII400 and a Dual PII333
one with FreeBSD 4.2 and
one with 4.3 - Both running PG 7.1.2

Any ideas? Thanks!!

-Mitch

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Hey Andrew, thanks for the email...

Just as a shot in the dark, I tried ipcclean and it didn't help :-)

The problem didn't start happening until I took the data from a production
server to here and it only seems to happen on a single database.. I'm
thinking that something is corrupted but since I'm not really getting any
errors I don't know what that might be...

Thanks!

-Mitch

----- Original Message -----
From: "Andrew Gould" <andrewgould@yahoo.com>
To: "Mitch Vincent" <mvincent@cablespeed.com>;
<pgsql-general@postgresql.org>
Sent: Friday, August 17, 2001 1:08 PM
Subject: Re: [GENERAL] Backend hanging..

A comlete shot in the dark:

Would using ipcclean help? (Does ipcclean work in
FreeBSD?)

See:

http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/app-ipcclean.ht
ml

"Mitch Vincent" <mvincent@cablespeed.com> writes:

61812 p2 S 0:00.58 postmaster: postgres brw [local] VACUUM waiting
(postgres)
61823 p2 I 0:01.18 postmaster: postgres brw 127.0.0.1 idle in
transaction (postgres)
61836 p2 S 0:01.03 postmaster: postgres brw 127.0.0.1 UPDATE waiting
(postgres)
61860 p2 S 0:00.99 postmaster: postgres brw 127.0.0.1 idle in
transaction waiting (postgres)
61880 p2 S 0:00.99 postmaster: postgres brw 127.0.0.1 idle in
transaction waiting (postgres)

Looks to me like backend 61823 is the culprit. Probably it holds a read
or write lock on that table, and its client is twiddling its thumbs
instead of committing the transaction so the lock could be released.
This wouldn't hurt so much if it weren't for the VACUUM --- the VACUUM
wants an exclusive lock, so it's got to wait, and then subsequent lock
requests queue up behind the VACUUM (even though they'd not conflict
with the original read or write lock).

Clients that sit around with open transactions are bad news for concurrency.

regards, tom lane

Hi Mitch,

Any messages during vacuums?

Good luck,

Andrew

--- Mitch Vincent <mvincent@cablespeed.com> wrote:

Hey Andrew, thanks for the email...

Just as a shot in the dark, I tried ipcclean and it
didn't help :-)

The problem didn't start happening until I took the
data from a production
server to here and it only seems to happen on a
single database.. I'm
thinking that something is corrupted but since I'm
not really getting any
errors I don't know what that might be...

Thanks!

-Mitch

----- Original Message -----
From: "Andrew Gould" <andrewgould@yahoo.com>
To: "Mitch Vincent" <mvincent@cablespeed.com>;
<pgsql-general@postgresql.org>
Sent: Friday, August 17, 2001 1:08 PM
Subject: Re: [GENERAL] Backend hanging..

A comlete shot in the dark:

Would using ipcclean help? (Does ipcclean work in
FreeBSD?)

See:

http://www.ca.postgresql.org/users-lounge/docs/7.1/reference/app-ipcclean.ht

ml

Andrew Gould

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/

Ack, no wonder I couldn't find a problem with PG - the problem wasn't there
at all...

Sorry for wasting everyone's time, thanks to all that responded!

-Mitch

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Mitch Vincent" <mvincent@cablespeed.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, August 17, 2001 1:39 PM
Subject: Re: [GENERAL] Backend hanging..

"Mitch Vincent" <mvincent@cablespeed.com> writes:

61812 p2 S 0:00.58 postmaster: postgres brw [local] VACUUM

waiting

(postgres)
61823 p2 I 0:01.18 postmaster: postgres brw 127.0.0.1 idle in
transaction (postgres)
61836 p2 S 0:01.03 postmaster: postgres brw 127.0.0.1 UPDATE

waiting

(postgres)
61860 p2 S 0:00.99 postmaster: postgres brw 127.0.0.1 idle in
transaction waiting (postgres)
61880 p2 S 0:00.99 postmaster: postgres brw 127.0.0.1 idle in
transaction waiting (postgres)

Looks to me like backend 61823 is the culprit. Probably it holds a read
or write lock on that table, and its client is twiddling its thumbs
instead of committing the transaction so the lock could be released.
This wouldn't hurt so much if it weren't for the VACUUM --- the VACUUM
wants an exclusive lock, so it's got to wait, and then subsequent lock
requests queue up behind the VACUUM (even though they'd not conflict
with the original read or write lock).

Clients that sit around with open transactions are bad news for

concurrency.