Thread: Backend hanging..
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
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
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/
"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
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. > > 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/