VACUUM ANALYZE Issues

Greetings,

My firm manages a somewhat large PostgreSQL database (400'ish tables, 5
TiB'ish on disk, 200'ish GiB pg_dumps, etc.). We're presently running
PostgreSQL 8.4.22 on Linux 2.6.32 with Slony-I 2.0.8 for replication.

Over the past several weeks, our weekly VACUUM ANALYZE job has become
disruptive to other queries, seemingly spinning on one particular table
(one of our largest in rows, columns, indexes, foreign keys, and general
use), ultimately requiring that the job be terminated. With the number of
INSERTs, UPDATEs, and DELETEs going through our system, it's imperative
that VACUUM ANALYZEs be completed periodically. In the past, and given our
usage patterns, we've opted for weekly VACUUM ANALYZEs in lieu of
autovacuum, which is presently disabled. If necessary, we can schedule
downtime, dereplicate the affected table, entirely rebuild the affected
table, and rereplicate. Though, if possible, we're looking for a solution
without significant downtime.

After about 60-90 minutes working on the aforementioned table, and with the
VACUUM ANALYZE at the top of our longest running queries list, other
operations attempting to read from or write to on that table either
complete extremely slowly or are awaiting locks held by the VACUUM
ANALYZE. Sampling during the most recent disruptive period, the VACUUM
ANALYZE pid held 20 locks: 17 RowExclusiveLocks, one AccessExclusiveLock,
and one ShareUpdateExclusive, all on the aforementioned table, and one
ExclusiveLock for the containing transaction. After sending SIGTERMs to
all other pids enumerated in pg_locks as holding or wanting locks on that
table, the VACUUM ANALYZE continued without apparent progress. At the
time, the containing process was consuming a rough average of about 6% of
one CPU and 4.9% memory (no swapping), was holding 641 descriptors, was in
a seeming loop of lseek, read, memcpy, and memcmp, with occasional semop
calls. During this process, we attempted to SIGTERM all other connections
to ensure no stale transactions, locks, etc. were preventing the VACUUM
ANALYZE from finishing. Overall system load and I/O activity was quite low.

Ultimately, we've felt forced to terminate the VACUUM ANALYZEs. After
sending it a SIGTERM and after about 30 seconds of seeing no change, we
attempted a service postgresql-8.4 stop. After 30 additional seconds, we
sent a SIGQUIT to the VACUUM ANALYZE process (which we always try to
avoid), which terminated it and, in turn, PostgreSQL stopped. Thereafter,
the restarts seemed quick and uneventful, with cleanup happening without
reported exception. Only once in the last month or so have we seen a
VACUUM ANALYZE on this table complete.

Thank you in advance,

Harold Falkmeyer
hfalkmeyer(at)yahoo(dot)com