Slony1 PostgreSQL Replication - sl_log_1 not getting cleaned out?

Author

sl_log_1 not getting cleaned out?

Gavin Hamill

2006-04-03, 11:31 am

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 ?

Cheers,
Gavin.

Christopher Browne

2006-04-03, 8:27 pm

Gavin Hamill <gdh- jB9c8NvlSj2akBO8gow8
eQ@public.gmane.org> 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@.sl_log_2
(log_origin, log_xid @NAMESPACE@.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@.sl_log_2
(log_xid @NAMESPACE@.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)

Gavin Hamill

2006-04-04, 11:33 am

Christopher Browne wrote:

>Gavin Hamill <gdh- jB9c8NvlSj2akBO8gow8
eQ@public.gmane.org> writes:
>
>

>Ideally, there should be two indexes on sl_log_1 and sl_log_2...
>
>create index sl_log_2_idx1 on @NAMESPACE@.sl_log_2
> (log_origin, log_xid @NAMESPACE@.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@.sl_log_2
> (log_xid @NAMESPACE@.xxid_ops);
>
>
>
OK two indexes on each of sl_log_1 and 2 giving four indexes on each node?

>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.
>
>

Anyway, from this morning, slony does appear to be maintaining itself,
the number of rows in sl_log_1 has dropped right back to 30000, so the
half-million must really be simply due to the large number of db updates
we do during our normal daily churn - I'd no idea we'd be doing that
much traffic :)

Plus the fact that the db hadn't seen a VACUUM ANALYZE in over a week
due to a broken cronjob won't have helped - whoops :)

Anyway, I'll certainly give the Perl state-tester a go - thank you
kindly {:-)

Cheers,
Gavin.

Christopher Browne

2006-04-04, 1:32 pm

Gavin Hamill <gdh- jB9c8NvlSj2akBO8gow8
eQ@public.gmane.org> writes:

> Christopher Browne wrote:
>
>
> OK two indexes on each of sl_log_1 and 2 giving four indexes on each node?

Right.

At present, sl_log_2 isn't used. (That will change in version 1.2...)

The first index is supposed to be good most of the time; in cases
where there are a bunch of sets, and replication has fallen behind,
sometimes the second index is the one that does good.

>
> Anyway, from this morning, slony does appear to be maintaining
> itself, the number of rows in sl_log_1 has dropped right back to
> 30000, so the half-million must really be simply due to the large
> number of db updates we do during our normal daily churn - I'd no
> idea we'd be doing that much traffic :)

Sounds good...

> Plus the fact that the db hadn't seen a VACUUM ANALYZE in over a week
> due to a broken cronjob won't have helped - whoops :)

You might find you need to reindex/VACUUM FULL some tables, if you
have blown out the free space map :-(.

> Anyway, I'll certainly give the Perl state-tester a go - thank you
> kindly {:-)

Glad to be of some help...
--
let name="cbbrowne" and tld="ca.afilias.info" in String.concat "@" [name;tld];;
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)

Sponsored Links