Thread: 8.3.1 autovacuum stopped doing anything months ago

<div dir="ltr">I have an 8.3.1 instance on Linux and since June 29th the autovacuum process has claimed to be working
onthe same three tables.  That's OK, I am a very patient man, and these are very large tables.  Today I started to get
transactionwraparound warnings, so I go and check it out.  Turns out the autovacuum processes are all just doing
nothing. When I strace them, they are all three blocked on syscalls.<br /><br />So I restart the database and run a
vacuum. Of course, once the wraparound warning is reached, there's no way to disable the autovac, so now my vacuum
maintenancejob is competing with three invulnerable autovacuum processes.  I am thinking of sending them SIGSTOP.<br
/><br/>Anyway, I have some issues.  One, of course, is that the autovacuum should not have been deadlocked or otherwise
stalledlike that.  Perhaps it needs a watchdog of some kind.  Has anyone else experienced an issue like that in 8.3.1? 
Theonly thing I can see in the release notes that indicates this problem may have been fixed is the following:<br /><br
/>"Repairtwo places where SIGTERM exit of a backend could leave corrupted state in shared memory (Tom)" <br /><br
/>HoweverI don't know who or what would have sent SIGTERM to the autovacuum children.<br /><br />Secondly, there really
doesneed to be an autovacuum=off,really,thanks so that my maintenance can proceed without competition for i/o
resources. Is there any way to make that happen?  Is my SIGSTOP idea dangerous?<br /><br />-jwb<br /></div> 

Jeffrey Baker wrote:
> Secondly, there really does need to be an autovacuum=off,really,thanks so
> that my maintenance can proceed without competition for i/o resources.  Is
> there any way to make that happen?

You could bump up autovacuum_freeze_max_age while you run the vacuums 
manually, and then set it back. It will require a restart, though.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On Friday 19 September 2008 00:23:34 Jeffrey Baker wrote:
> Anyway, I have some issues.  One, of course, is that the autovacuum should
> not have been deadlocked or otherwise stalled like that.  Perhaps it needs
> a watchdog of some kind.  Has anyone else experienced an issue like that in
> 8.3.1?  The only thing I can see in the release notes that indicates this
> problem may have been fixed is the following:
>

We have several checks in the check_postgres script which are in this area 
(warnings for approaching autovacuum freeze max age, warnings when approching 
xid wrap, monitoring of tables analyze/vacuum activity) Those can at least 
alert you to problems before they become too big a hassle. 

> Secondly, there really does need to be an autovacuum=off,really,thanks so
> that my maintenance can proceed without competition for i/o resources.  Is
> there any way to make that happen?  Is my SIGSTOP idea dangerous?

If Heikis solution applies, it's better (see also vacuum_freeze_min_age) , but 
if its too late for that, you can go into single user mode, which will  
prevent autovacuum; it's a bit more heavy handed though. 

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL