Tory M Blue <tmblue at gmail.com> writes:
> On Wed, Jul 22, 2009 at 1:19 PM, Christopher
> Browne<cbbrowne at ca.afilias.info> wrote:
>> Tory M Blue <tmblue at gmail.com> writes:
>>> So I've noticed recently that I'm vacuuming the sl_?.log files with
>>> postgres and this doesn't appear right. The fact is slon has it's own
>>> process for dealing with this and I believe it's a clean truncate.
>>
>> I would actually counsel taking the opposite approach, that it may be
>> preferable for autovacuum to handle vacuuming the Slony-I tables than
>> for Slony-I to do it itself.
>>
>> Autovacuum should have a better capability to cope with the dual factors
>> of:
>>  a) Needing to vacuum some tables "even more often", as well as
>>  b) Needing to not vacuum some tables very often.
>>
>> In principle, we could make the cleanup thread in Slony-I smarter, but
>> that would duplicate the good work that has gone into the PostgreSQL
>> built-in...
>
> Ahh good info, although I would think that a postgres vacuum, using
> delete's would be worse than a slon truncate of said table once
> everything was replicated?
>
> I have major index bloat and looking for anything and everything that
> could help with it.

I'll illustrate with a couple examples...

Consider the case where we have Slony-I do the vacuuming itself...

Comparison #1: sl_log_1

 - Every 3 iterations of the cleanup thread, by default, every 30
   minutes, it would vacuum all of its tables, bloated or not.

   Supposing sl_log_1 has lots of junk in it (deleted tuples or not),
   lots of time will be spent vacuuming it every 30 minutes,
   needed/useful or not.

On the other hand, if the autovac thread handles this, then...

 - If you're running Slony-I 2.0, where only TRUNCATE is used, there
   should never be a material # of dead tuples in sl_log_1,
   so you can expect it to *NEVER* vacuum sl_log_1.

   Winner: autovacuum :-)

Comparison #2: sl_confirm

   This table gets trimmed fairly often.  But let's suppose it's
   getting pretty bloated...

   - If we use Slony-I cleanup thread to vacuum, then it'll vacuum it
     every 30 minutes, regardless of usefulness.

   - If we use autovac, then:

       a) autovac may vacuum it *more* often, if it's getting tuples
          trimmed frequently

       b) On the other hand, if tuples don't get trimmed out for 2
          hours due to something holding onto data, then there will
          be NO deletes/updates to sl_confirm, and autovac won't
          bother vacuuming it.

   In either case, I expect autovacuum to be preferable.

There are various pathological cases characterized by the above where
"have the Slony-I cleanup thread do it" is distinctly inferior to "use
autovacuum."

I'm *STRONGLY* disinclined to try to improve the cleanup thread's
logic in this regard; relevant development effort would be *WAY*
better spent improving autovacuum, as that would be helpful to more
than just Slony-I users.
-- 
(reverse (concatenate 'string "ofni.sailifa.ac" "@" "enworbbc"))
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)
"Bother,"  said Pooh,  "Eeyore, ready  two photon  torpedoes  and lock
phasers on the Heffalump, Piglet, meet me in transporter room three"