Tory M Blue <[email protected]> writes:
> On Wed, Jul 22, 2009 at 1:19 PM, Christopher
> Browne<[email protected]> wrote:
>> Tory M Blue <[email protected]> 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"
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general