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@socialserve.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