On Jul 13, 2010, at 2:36 PM, Steve Singer wrote:

>
> You don't say what version of postgresql your on or anything about  
> your autovacuum settings.
>

PG 8.4.2, Slony 1.2.17 (I'm aware it is not the latest)

AV is on - AV is cancelling the vacuuming of sl_event:

Jul 13 15:04:28 xxx postgres[29468]: [3-1] ERROR:  canceling  
autovacuum task
Jul 13 15:04:28 xxx postgres[29468]: [3-2] CONTEXT:  automatic vacuum  
of table "xxxxx._replication.sl_event"
Jul 13 15:05:27 xxx postgres[29829]: [3-1] ERROR:  canceling  
autovacuum task
Jul 13 15:05:27 xxx postgres[29829]: [3-2] CONTEXT:  automatic vacuum  
of table "xxxx._replication.sl_event"

> Do you have autovacuum set to skip the slony tables?
>

No. All the other slony tables are being successfully vacuumed. As  
evidenced above, it is trying to but it cannot as it cancels.   Slony  
takes a lock on sl_event and because of this if AV is working on the  
table it cancels itself so it will not hold up "real work".   The  
problem is if this happens constantly.  I think within the last few  
weeks I hit some tipping point causing it happen constantly.


>> [postgres at XXXX log]$  grep "Jul 13" pgsql.log | grep "automatic  
>> vacuum  of table \"XXXX._replication.sl_event\"" | wc
>>     540    6480   59228  (this morning)
>> [postgres at XXXX log]$  grep "Jul 12" pgsql.log | grep "automatic  
>> vacuum  of table \"XXXX._replication.sl_event\"" | wc
>>    1438   17256  157726
>> [postgres at XXXlog]$  grep "Jul 11" pgsql.log | grep "automatic  
>> vacuum  of table \"XXXX._replication.sl_event\"" | wc
>>    1164   13968  127691

As you can see this is happening thousands of times a day.

--
Jeff Trout <jeff at jefftrout.com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/