Gavin Hamill <gdh at laterooms.com> writes:

> We have a simple 2-node master/slave system, and the sl_log_1 on the 
> master has currently half a million rows and growing at a rate of 
> roughly 10 per second, even though the slave is fully caught up. I do 
> see regular
>
> 2006-04-03 14:37:39 BSTLOG:  duration: 1336.384 ms  statement: delete 
> from "_replication".sl_log_1 where log_origin = '1' and log_xid < 
> '226300425'; delete from "_replication".sl_log_2 where log_origin = '1' 
> and log_xid < '226300425'; delete from "_replication".sl_seqlog where 
> seql_origin = '1' and seql_ev_seqno < '96499';
>
> and
>
> 2006-04-03 14:42:33 BSTLOG:  duration: 250432.316 ms  statement: vacuum 
> analyze "_replication".sl_log_1;
>
> queries on the master, yet half a million rows seems like an /awful/ 
> lot..  I would be worrying about it if it'd always been this large, but 
> sl_log_1 has been gradually growing. Does this sound normal?
>
> Oddly, pgadmin3 is telling me the estimated row count is only 30000 even 
> after a vacuum analyze.. Finally - should there be any kind of index set 
> on sl_log_1 ?

Ideally, there should be two indexes on sl_log_1 and sl_log_2...

create index sl_log_2_idx1 on @NAMESPACE at .sl_log_2
	(log_origin, log_xid @NAMESPACE at .xxid_ops, log_actionseq);

-- Add in an additional index as sometimes log_origin isn't a useful discriminant
create index sl_log_2_idx2 on @NAMESPACE at .sl_log_2
	(log_xid @NAMESPACE at .xxid_ops);

I'd suggest you run either test_slony_state.pl or
test_slony_state-dbi.pl (depending on whether you like Pg or DBI);
those scripts rummage through the cluster looking for some common
problems.
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)