Tonight as part of a scheduled maintenance operation, I was going to
perform a VACUUM FULL ANALYZE on a postgres 8.1.3 instance that had
just had it's FSM settings increased to account for about 2 years'
worth of growth (particularly in number of relations).
Shortly after I kicked it off, I watched the number of connections
trend upward as a result of the aggressive locking of FULL. I didn't
want to let this continue without notifying the developers about a
potential downtime for their application, so I killed the vacuumdb
process I had started, figuring that the existing connections would
go right through.
Unfortunately, my expectations were not met, and I wound up with a
bunch of connections in a waiting state. It seems as though the
connections that were waiting for the VACUUM locks were still waiting
even once the VACUUM was killed. Is that expected behavior?
Eventually, a timeout threshold must've been hit because everything
went back to normal, but it was not immediate. statement_timeout is
set to 0, so it wasn't that.
This postmaster does have autovacuum enabled. Could that have
interfered in any way?
On the one hand I'm curious about the behavior of postgres in this
scenario. On the other hand, though, I'm wondering if this operation
is necessary. The postmaster has been restarted such that the new FSM
settings are in effect. Can autovacuum recover for the months where
the FSM settings were not sufficient to cover the number of relations
in this cluster? Is a vacuumdb/reindexdb cycle necessary to reclaim
disk space?
--
Thomas F. O'Connell
Database Architecture and Programming
Sitening, LLC
http://www.sitening.com/
3004 B Poston Avenue
Nashville, TN 37203-1314
615-260-0005 (cell)
615-469-5150 (office)
615-469-5151 (fax)