Craig James <craig_james at emolecules.com> writes:
> Every 60 minutes, a vacuum process starts on my master node.  Since
> I am not running AutoVacuum at all, and have no cron jobs, that only
> leaves Slony ...
>
> The problem is that this vacuum is now taking over 5 minutes per
> hour to complete, and makes the system virtually unusable (CPU load
> goes to 400%).  I don't understand why this should happen -- the
> Slony replication set isn't very large, and the only tables that
> change much are log tables that are only appended.  And this is the
> master, not the slave...
>
> Questions:
>
> 1. Does Slony run vacuum every hour?

It shouldn't be exactly hourly, I wouldn't think; Slony-I *does* have
a cleanup thread which runs periodically.

In src/slon/slon.h, the #define SLON_CLEANUP_SLEEP, with default of
600 (indicating 600 seconds), combined with SLON_VACUUM_FREQUENCY
(default 3) combine so that I'd expect to see the cleanup thread
vacuum certain Slony-I tables roughly every 1/2 hour.

I say "roughly" because there is some conscious use of random values
to make it vary a bit.

> 2. If so, is it a requirement?

Probably.  If slon doesn't vacuum, and you don't have AutoVacuum doing
vacuums, and you don't have some custom scripts doing vacuums, then
there are a number of tables that would eventually bloat up into
ridiculous size.

Notably:
 - pg_catalog.pg_listener - which has some quantity of dead tuples
   created in it

 - sl_log_1/sl_log_2 - which collect all the tuples that are updated.
   
   These tables periodically (every couple of days, by default) get
   TRUNCATEd, as there is some tendancy for them to arbitrarily bloat
   up.

   In Slony-I 2.0 (e.g. - CVS HEAD), we have eliminated all DELETEs on
   these tables; the cleanup thread will periodically simply rotate
   between sl_log_1 and sl_log_2, and, once the "not-in-use" table
   has no data that needs to be kept, it will TRUNCATE that table.
   This makes the need to VACUUM disappear.

 - sl_event, sl_confirm - these tables have tuples deleted from them; 
   over time, without VACUUM, they would certainly "bloat" up.

> 3. Any ideas why the vacuum is taking longer and longer?

  Probably one of the above tables has bloated, and needs a CLUSTER to
  reorganize it completely.  You might run a VACUUM VERBOSE by hand on
  each table to see which ones may be bloated.

> 4.I don't even know what else to ask???
>
> This is PG 8.1, Slony 1.2.9.  (I know, I need to upgrade, we're
> getting new servers soon and will upgrade then.)

[We've got some elder versions still in use ;-)]
-- 
let name="cbbrowne" and tld="ca.afilias.info" in String.concat "@" [name;tld];;
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)