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

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

From: James Robinson <jlrobins(at)socialserve(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
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 16:23:47
Message-ID: D3728DA7-11BE-46CE-8FB8-78FB8E3C08B0@socialserve.com (view raw or flat)
Thread:
Lists: pgsql-hackers
As fate would have it, the vacuumdb frontend and backend which were  
initially afflicted are still in existence:

sscadmin 19236 19235  0 Nov25 ?        00:00:00 /usr/local/pgsql/bin/ 
vacuumdb -U postgres --all --analyze --verbose
postgres 19244  3596  0 Nov25 ?        00:00:02 postgres: postgres  
social [local] VACUUM

pid 19244.

And here's pg_locks:

social=# select * from pg_locks;
relation | database | transaction |  pid  |           mode            
| granted
----------+----------+-------------+-------+-------------------------- 
+---------
           |          |    38790657 | 19244 |  
ExclusiveLock            | t
   6586066 |  6585892 |             | 28406 |  
ShareUpdateExclusiveLock | f
           |          |    39097312 | 28861 |  
ExclusiveLock            | t
           |          |    39089744 | 28756 |  
ExclusiveLock            | t
   6586066 |  6585892 |             | 28756 |  
ShareUpdateExclusiveLock | f
   6586066 |  6585892 |             | 19244 |  
ShareUpdateExclusiveLock | t
   6586066 |  6585892 |             | 19244 |  
ShareUpdateExclusiveLock | t
   8417138 |  6585892 |             | 19244 |  
ShareUpdateExclusiveLock | t
     16839 |  6585892 |             | 28861 |  
AccessShareLock          | t
           |          |    39063661 | 28560 |  
ExclusiveLock            | t
           |          |    39056736 | 28406 |  
ExclusiveLock            | t
   6586066 |  6585892 |             | 28560 |  
ShareUpdateExclusiveLock | f
(12 rows)


   pid
-------
19244
28406
28560
28756
28861
(5 rows)

Of those 5 pids:

19244 -- vaccuum backend initally afflicted -- status in argv:  
'postgres: postgres social [local] VACUUM'
28406 -- a 10AM today vacuum started up by cron this morning after I  
got things half-way working again early in the diagnosis of this  
situation. args: 'postgres: postgres social [local] VACUUM waiting'
28560 -- a 10:16 today by-hand vacuum session futher in diagnosis  
land. args: 'postgres: postgres social [local] VACUUM waiting'
28756 -- 11AM cron'd process. Yes, I see a quickly mounting issue  
here. args: 'postgres: postgres social [local] VACUUM waiting'
28861 -- production servicing backend, now back in idle state. [ not  
in tx idle by regular idle ].



On Nov 28, 2005, at 11:09 AM, Tom Lane wrote:

> James Robinson <jlrobins(at)socialserve(dot)com> writes:
>> Comparing the logs further with when it did complete, it seems that
>> one table in particular (at least) seems afflicted:
>
>> social=# vacuum verbose analyze agency.swlog_client;
>
>> hangs up forever -- have to control-c the client. Likewise for w/o
>> 'analyze'.
>
> Given that it's not eating CPU time, one would guess that it's blocked
> waiting for a lock.  Can you find any relevant locks in pg_locks?
>
> 			regards, tom lane

----
James Robinson
Socialserve.com


In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-11-28 16:28:34
Subject: Re: Help: 8.0.3 Vacuum of an empty table never completes ...
Previous:From: Tom LaneDate: 2005-11-28 16:20:20
Subject: Re: Getting different number of results when using hashjoin on/off

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