Slony1 PostgreSQL Replication - cleanupEvent() doesn't clean out sl_log

Author

cleanupEvent() doesn't clean out sl_log_1?

Len Walter

2006-01-09, 3:24 am

G'day ,

My environment is postgres 7.4.8 / Slony 1.1.0 / RHEL4ES on x86.

I've got a three-or-four month old slony cluster which currently has a
large sl_log_1 table:
db=# select count(*) from _slony.sl_log_1;
count
---------
1031377
(1 row)

I've run the step at http://cbbrowne.com/info/faq.html#AEN42511 and
deleted a few old entries from sl_confirm, then waited for the cleanup
thread to purge the log table, as well as running the task manually.

The FAQ says (in the same entry) "each cleanupEvent run .... is the
event in which old data is purged from sl_log_1 and sl_seqlog".
However, I've looked at the function as installed in the slony schema
and it doesn't do that, as far as I can tell. (attached below)

Can anyone give me a pointer to finding where this task does get run?

Thanks,
Len

db=# \df+ _slony.cleanupevent

declare
v_max_row record;
v_min_row record;
v_max_sync int8;
begin
-- ----
-- First remove all but the oldest confirm row per origin,receiver pair
-- ----
delete from "_slony".sl_confirm
where con_origin not in (select no_id
from "_slony".sl_node);
delete from "_slony".sl_confirm
where con_received not in (select
no_id from "_slony".sl_node);
-- ----
-- Next remove all but the oldest confirm row per origin,receiver pair.
-- Ignore confirmations that are younger than 10 minutes. We currently
-- have an not confirmed suspicion that a possibly lost transaction due
-- to a server crash might have been visible to another session, and
-- that this led to log data that is needed again got removed.
-- ----
for v_max_row in select con_origin, con_received,
max(con_seqno) as con_seqno
from "_slony".sl_confirm
where con_timestamp <
(CURRENT_TIMESTAMP - '10 min'::interval)
group by con_origin, con_received
loop
delete from "_slony".sl_confirm
where con_origin = v_max_row.con_origin
and con_received = v_max_row.con_received
and con_seqno < v_max_row.con_seqno;
end loop;

-- ----
-- Then remove all events that are confirmed by all nodes in the
-- whole cluster up to the last SYNC
-- ----
for v_min_row in select con_origin, min(con_seqno) as con_seqno
from "_slony".sl_confirm
group by con_origin
loop
select coalesce(max(ev_seqn
o), 0) into v_max_sync
from "_slony".sl_event
where ev_origin = v_min_row.con_origin
and ev_seqno <= v_min_row.con_seqno
and ev_type = 'SYNC';
if v_max_sync > 0 then
delete from "_slony".sl_event
where ev_origin = v_min_row.con_origin
and ev_seqno < v_max_sync;
end if;
end loop;

return 0;
end;
| cleaning old data out of sl_confirm, sl_event. Removes all but the
last sl_confirm row per (origin,receiver), and then removes all events
that are confirmed by all nodes in the whole cluster up to the last
SYNC.
(1 row)

--
Len Walter len.walter- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org http://crookedtimbre.net skype:len.walter

Christopher Browne

2006-01-09, 8:26 pm

Len Walter wrote:

>G'day ,
>
>My environment is postgres 7.4.8 / Slony 1.1.0 / RHEL4ES on x86.
>
>I've got a three-or-four month old slony cluster which currently has a
>large sl_log_1 table:
>db=# select count(*) from _slony.sl_log_1;
> count
>---------
> 1031377
>(1 row)
>
>I've run the step at http://cbbrowne.com/info/faq.html#AEN42511 and
>deleted a few old entries from sl_confirm, then waited for the cleanup
>thread to purge the log table, as well as running the task manually.
>
>The FAQ says (in the same entry) "each cleanupEvent run .... is the
>event in which old data is purged from sl_log_1 and sl_seqlog".
>However, I've looked at the function as installed in the slony schema
>and it doesn't do that, as far as I can tell. (attached below)
>
>Can anyone give me a pointer to finding where this task does get run?
>
>
The trouble here is that "cleanupEvent" is overloaded. There are two
things by that name:

1. There is a pl/pgsql function, cleanupEvent(), in
src/backend/slony1_funcs.sql, which is strictly about cleaning out events

2. There is a C function, cleanupEvent_main(),
in
src/slon/cleanup_thread.c, which runs the pl/pgsql function *as well as*
doing other things that notably includes purging data from sl_log_1.

You might look at the queries in src/slon/cleanup_thread.c to see what
it is doing.

The query most likely to be useful is the following...

"select ev_origin, ev_seqno, ev_minxid "
"from %s.sl_event "
"where (ev_origin, ev_seqno) in "
" (select ev_origin, min(ev_seqno) "
" from %s.sl_event "
" where ev_type = 'SYNC' "
" group by ev_origin); ",

If confirmations haven't made it back from some node, that would block
deletions from taking place...

In the past, the following query used to be useful; it ought to be
obsolete in 1.1...

select * from _oxrsbar.sl_confirm where con_origin not in (select no_id
from _oxrsbar.sl_node) or con_received not in (select no_id from
_oxrsbar.sl_node)

Len Walter

2006-01-12, 3:25 am

Right you are! That query returns five rows from sl_event from 2005-08-18 -
one's got ev_origin = <master node> and the other four are from the four
slave nodes.

I don't think I should be getting messages from the slaves to the master in
the first place - I deleted the rows from sl_listen where li_origin !=
<master_node> because it seemed to solve a problem I was having with the
whole thing falling over, plus it cut down on the logging - but that's
another story.

I deleted the five rows from sl_event, there's nothing in sl_confirm, and
I'm still waiting for it to clean out sl_log_1...I'll see how we go!

thanks,
Len

On 1/10/06, Christopher Browne <cbbrowne-swQf4SbcV9C7WVzo/KQ3Mw@public.gmane.org> wrote:
>
> Len Walter wrote:
>
> The trouble here is that "cleanupEvent" is overloaded. There are two
> things by that name:
>
> 1. There is a pl/pgsql function, cleanupEvent(), in
> src/backend/slony1_funcs.sql, which is strictly about cleaning out events
>
> 2. There is a C function, cleanupEvent_main(),
in
> src/slon/cleanup_thread.c, which runs the pl/pgsql function *as well as*
> doing other things that notably includes purging data from sl_log_1.
>
> You might look at the queries in src/slon/cleanup_thread.c to see what
> it is doing.
>
> The query most likely to be useful is the following...
>
> "select ev_origin, ev_seqno, ev_minxid "
> "from %s.sl_event "
> "where (ev_origin, ev_seqno) in "
> " (select ev_origin, min(ev_seqno) "
> " from %s.sl_event "
> " where ev_type = 'SYNC' "
> " group by ev_origin); ",
>
> If confirmations haven't made it back from some node, that would block
> deletions from taking place...
>
> In the past, the following query used to be useful; it ought to be
> obsolete in 1.1...
>
> select * from _oxrsbar.sl_confirm where con_origin not in (select no_id
> from _oxrsbar.sl_node) or con_received not in (select no_id from
> _oxrsbar.sl_node)
>

--
Len Walter len.walter- Re5JQEeQqe8AvxtiuMwx
3w@public.gmane.org http://crookedtimbre.net skype:len.walter

Sponsored Links