[Pgpool-general] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

[Lonni J Friedman]

Thanks for your reply.  No, I do not regularly schedule any vacuum
cronjobs, as I was led to believe that this was no longer necessary
from past discussions on this and other mailing lists.  I had been
running vacuum full as frequently as twice/month earlier this year,
but it was basically making the database unusable for over 12 hours,
which wasn't acceptable in a production environment.  That's when I
did some research and saw numerous discussions commenting that
autovacuum is generally sufficient for most use cases.

The official documentation seems to suggest that the 'freeze' option
is deprecated, and that 'full' should only be run under special
circumstances (and not regularly):
http://www.postgresql.org/docs/9.0/static/sql-vacuum.html

Are you basically telling me that there is no way to stop the hung
autovacuum process other than shutting down  and restarting the entire
cluster?


On Tue, Nov 22, 2011 at 5:42 PM, Paul Robert Marino <prmarino1 at gmail.com> wrote:
> The autovacuum process is suppose to reduce the frequence of needing a
> vacuum full or vacuum freeze not completly replace it.
> Do you regularly schedule at least a vacuum freeze if not this may be your
> problem.
> Also I've seen this happen when a server went too long betwean running
> vacuum full and there was a transaction id wrap around issue, but you would
> see that in the log.
> The first piece of advice is to restart the cluster and imeadiatly do a
> vacuum freeze this will do every thing short of doing an exclusive table
> lock. The next step is to plan a vacuum full on each the tables. The vacuum
> full will reqiure an exclusive table lock so you will not be able to query
> or update the tables durring this time but the earrlier vacuum freeze will
> have accelerated the process. note you may do multiple vacuums concurently
> on seperate tables (but not multiple on the same at once) if downtime is an
> issue, you'll still run into io contention but may be better able to take
> advantage of your cpu and ram if you do it