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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
