<div dir="ltr"><span style="font-family:arial,sans-serif;font-size:13px">I found that many transactions lock the sl_log_x tables. </span><div><br></div><div>Why slony can't wait for the lock? How about letting slony wait for the lock?</div>
<div><br style="font-family:arial,sans-serif;font-size:13px">I found that postgres 9.3 has lock_timeout parameter. Can this be used in slony?</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Wed, Jun 25, 2014 at 10:25 PM, Steve Singer <span dir="ltr"><<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.info</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 06/25/2014 11:21 AM, Soni M wrote:<br>
<br>
Find out why slony can't truncate sl_log_2<br>
<br>
Slony does<br>
lock table @NAMESPACE@.sl_log_2 in access exclusive mode nowait;<br>
<br>
so why can't it get the access exclusive lock on sl_log_2, (hint: maybe query pg_locks to see what process/transaction has a lock on sl_log_2).<br>
<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="">
this is what i found on slony log :<br>
<br>
postgres@myserver:~$ tail -f /var/log/slony1/slon-myserver.<u></u>log | grep -i<br>
-e lock -e "log switch"<br>
NOTICE: Slony-I: log switch to sl_log_2 complete - truncate sl_log_1<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: log switch to sl_log_1 still in progress - sl_log_2<br>
not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: log switch to sl_log_1 still in progress - sl_log_2<br>
not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
NOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br>
<br>
Truncating sl_log_2 always failed. and yes the slave is falling behind<br>
the master. usually the slave will catch up again later after the busy<br>
hours. Maybe this is due to high transactional load. But is there a way<br>
we can keep slave to catch up the master?<br>
<br>
Thanks....<br>
<br>
<br>
<br>
On Wed, Jun 25, 2014 at 10:04 PM, Steve Singer <<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.info</a><br></div><div class="">
<mailto:<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.<u></u>info</a>>> wrote:<br>
<br>
On 06/25/2014 11:00 AM, Soni M wrote:<br>
<br>
Hello everyone,<br>
<br>
Is it OK to delete event log that has been replicated in sl_log_x.<br>
<br>
<br>
<br>
No you shouldn't do that.<br>
<br>
You should instead figure out why the slony cleanup event isn't<br>
performing a log switch. Perhaps because you have a node behind or<br>
because confirmations are not making it back to the origin?<br>
<br>
<br>
<br>
Here's the function I created :<br>
<br>
CREATE OR REPLACE FUNCTION _slony_example.delete_sl_log()<br>
RETURNS void<br>
LANGUAGE plpgsql<br>
AS $function$<br>
DECLARE<br>
v_origin int8;<br>
v_seqno int8;<br>
v_xmin bigint;<br>
BEGIN<br>
<br>
<br>
for v_origin, v_seqno, v_xmin in<br>
select ev_origin, ev_seqno,<br></div>
"pg_catalog".txid_snapshot___<u></u>xmin(ev_snapshot) from<div class=""><br>
"_slony_example".sl_event<br>
where (ev_origin, ev_seqno) in (select<br>
ev_origin,<br>
min(ev_seqno) from "_slony_example".sl_event where ev_type = 'SYNC'<br>
group by ev_origin)<br>
loop<br>
<br>
delete from _slony_example.sl_log_1 where log_origin = v_origin and<br>
log_txid < v_xmin;<br>
delete from _slony_example.sl_log_2 where log_origin = v_origin and<br>
log_txid < v_xmin;<br>
end loop;<br>
<br>
END;<br>
$function$;<br>
<br>
The Slony failed to switch sl_log for several hours, and event<br>
log keep<br>
accumulated in a table.<br>
<br>
Will this action help to reduce slony lag that falls far behind ?<br>
<br>
Thanks all.<br>
<br>
--<br>
Regards,<br>
<br>
Soni Maula Harriz<br>
<br>
<br></div>
______________________________<u></u>___________________<br>
Slony1-general mailing list<br>
Slony1-general@lists.slony.__<u></u>info<br>
<mailto:<a href="mailto:Slony1-general@lists.slony.info" target="_blank">Slony1-general@lists.<u></u>slony.info</a>><br>
<a href="http://lists.slony.info/__mailman/listinfo/slony1-__general" target="_blank">http://lists.slony.info/__<u></u>mailman/listinfo/slony1-__<u></u>general</a><div class=""><br>
<<a href="http://lists.slony.info/mailman/listinfo/slony1-general" target="_blank">http://lists.slony.info/<u></u>mailman/listinfo/slony1-<u></u>general</a>><br>
<br>
<br>
<br>
<br>
<br>
--<br>
Regards,<br>
<br>
Soni Maula Harriz<br>
</div></blockquote>
<br>
</blockquote></div><br><br clear="all"><div><br></div>-- <br><div dir="ltr"><div>Regards,</div><div><br></div>Soni Maula Harriz</div>
</div>