Skip site navigation (1) Skip section navigation (2)

Re: Help: 8.0.3 Vacuum of an empty table never completes ...

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: James Robinson <jlrobins(at)socialserve(dot)com>
Cc: Hackers Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Help: 8.0.3 Vacuum of an empty table never completes ...
Date: 2005-11-28 18:46:53
Message-ID: 13083.1133203613@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
James Robinson <jlrobins(at)socialserve(dot)com> writes:
> backtrace of the sshd doesn't look good:

Stripped executable :-( ... you won't get much info there.  What of
the client at the far end of the ssh connection?  You should probably
assume that the blockage is there, rather than in a commonly used bit
of software like ssh.

> At this moment in time, should we kill off the offending processes  
> from Nov 25 -- starting from client-most side all the way to the  
> vacuumdb process on the production server. The other vacuums would  
> probably then complete happily, and we'd be cool again, eh?

If you just want to get out of it, killing the vacuumdb should be the
least dangerous way to get out of the problem.  I'd suggest taking a
little more time to try to find out what's stuck though.

> I suppose we're darn lucky the process got ultimately gummed up on a  
> table that sees no traffic at all to it, eh? The lock that vacuum has  
> taken out on it would prevent at least some things happening to the  
> table in question -- possibly even new inserts or updates?

No, since it's a lazy vacuum it's not going to block either reads or
writes.  Just other vacuums and DDL changes on the table.

> Could this potentially be alleviated in the future by a little code  
> reordering in vacuumdb or postmaster by completing working on the  
> current table completely before emitting output,

Wouldn't help.  I would imagine that by the time we got to this state,
the backend side is quite a few tables past the point where the end
client stopped accepting output.  You'd normally expect there to be
several Kb worth of buffering in the network channel, and the backend
isn't going to be blocked till that's *all* used up.

BTW, I suppose this was a VACUUM VERBOSE?  Without the verbose output,
you'd likely not have seen any problem ...

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Marc G. FournierDate: 2005-11-28 19:06:47
Subject: Re: Anonymous CVS working?
Previous:From: Tom LaneDate: 2005-11-28 18:39:52
Subject: Hashjoin startup strategy (was Re: Getting different number of results when using hashjoin on/off)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group