Обсуждение: locked up backends
Hello, We are running Postgres 7.0.2 on Linux RH 6.2 Dell servers. When traffic gets heavy we often get one of the backend locked up in an UPDATE, INSERT or COMMIT request. Eventually all the other backends end up locking too as they try to access a row locked by the original backend. The output of 'ps' shows that the locked up backend is sleeping waiting for 'select' to return. This has been occurring once or twice a day for the past few months. We just stop the postmaster (sometimes we need to kill -USR1 one of the backends before they all disappear) and then we restart postmaster and everything works fine until the next lock up. Any idea what might be wrong? I'd be happy to provide more info, if only I knew what. Thanks in advance, --Maurice
Maurice Balick <balm@smiley.com> writes: > Hello, > > We are running Postgres 7.0.2 on Linux RH 6.2 Dell servers. As I understand it, 7.0.3 fixes some severe bugs in earlier 7.0 versions. You should upgrade to 7.0.3 at the very least, and consider upgrading to 7.1, which is currently in Release Candidate status and offers considerable improvements in performance, stability and features. -Doug
Maurice Balick <balm@smiley.com> writes: > We are running Postgres 7.0.2 on Linux RH 6.2 Dell servers. > When traffic gets heavy we often get one of the backend locked up in an > UPDATE, INSERT or COMMIT request. Eventually all the other backends end > up locking too as they try to access a row locked by the original > backend. Try updating to 7.0.3. You may be getting caught by 7.0.2's failure to release locks when a client disconnects partway through a transaction block. If you still see this in 7.0.3 then it'd be worth further investigation. regards, tom lane
I see this pretty often on 7.1b4, running on redhat linux 6.2 (or 7.0; same problem on both). My lockups are always either INSERT or UPDATE, and all the other backends are shown as "idle in transaction". I've been trying to track this down, but haven't had any luck. Suggestions? Thanks, -Randy > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Thursday, April 05, 2001 4:12 PM > To: Maurice Balick > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] locked up backends > > > Maurice Balick <balm@smiley.com> writes: > > We are running Postgres 7.0.2 on Linux RH 6.2 Dell servers. > > > When traffic gets heavy we often get one of the backend > locked up in an > > UPDATE, INSERT or COMMIT request. Eventually all the other > backends end > > up locking too as they try to access a row locked by the original > > backend. > > Try updating to 7.0.3. You may be getting caught by 7.0.2's > failure to > release locks when a client disconnects partway through a transaction > block. > > If you still see this in 7.0.3 then it'd be worth further > investigation. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"Randall F. Kern" <randy@spoke.net> writes: > I see this pretty often on 7.1b4, running on redhat linux 6.2 (or 7.0; > same problem on both). My lockups are always either INSERT or UPDATE, > and all the other backends are shown as "idle in transaction". Oh? 7.1 doesn't have the problem I was thinking of, so you may have a different issue. Could you attach to the stuck backends with gdb and get a backtrace from each one, so we can see approximately what they're doing? (It'd help if you've compiled with debug symbols.) regards, tom lane
On Thu, 5 Apr 2001, Tom Lane wrote: > "Randall F. Kern" <randy@spoke.net> writes: > > I see this pretty often on 7.1b4, running on redhat linux 6.2 (or 7.0; > > same problem on both). My lockups are always either INSERT or UPDATE, > > and all the other backends are shown as "idle in transaction". > > Oh? 7.1 doesn't have the problem I was thinking of, so you may have a > different issue. Could you attach to the stuck backends with gdb and > get a backtrace from each one, so we can see approximately what they're > doing? (It'd help if you've compiled with debug symbols.) How 'bout this version? PostgreSQL 7.0.2 on i386-unknown-freebsdelf3.2, compiled by gcc 2.7.2.1 I was away all day and came back to a logcheck message about running out of swap and a process being killed. ps -axww showed a number of these: /usr/local/pgsql/bin/postgres nobody 127.0.0.1 twig idle twig's a web based pim. I also noticed a bunch of messages that told me there were some messages that imapd had trouble with. ISTM that these should've been killed off by something before the OS did it. I killed them off (9 of them) by HUPping httpd. Is this closer to what you were thinking? Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
Vince Vielhaber <vev@michvhf.com> writes: > I was away all day and came back to a logcheck message about running > out of swap and a process being killed. ps -axww showed a number of > these: > /usr/local/pgsql/bin/postgres nobody 127.0.0.1 twig idle Those aren't deadlocked, they're just waiting for their clients to do something. You might have a problem on the client side ... regards, tom lane