I found that many transactions lock the sl_log_x tables.

Why slony can't wait for the lock? How about letting slony wait for the
lock?

I found that postgres 9.3 has lock_timeout parameter. Can this be used in
slony?


On Wed, Jun 25, 2014 at 10:25 PM, Steve Singer <ssinger at ca.afilias.info>
wrote:

> On 06/25/2014 11:21 AM, Soni M wrote:
>
> Find out why slony can't truncate sl_log_2
>
> Slony does
> lock table @NAMESPACE at .sl_log_2 in access exclusive mode nowait;
>
> 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).
>
>
>
>  this is what i found on slony log :
>>
>> postgres at myserver:~$ tail -f /var/log/slony1/slon-myserver.log | grep -i
>> -e lock -e "log switch"
>> NOTICE:  Slony-I: log switch to sl_log_2 complete - truncate sl_log_1
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: log switch to sl_log_1 still in progress - sl_log_2
>> not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: log switch to sl_log_1 still in progress - sl_log_2
>> not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>> NOTICE:  Slony-I: could not lock sl_log_2 - sl_log_2 not truncated
>>
>> Truncating sl_log_2 always failed. and yes the slave is falling behind
>> the master. usually the slave will catch up again later after the busy
>> hours. Maybe this is due to high transactional load. But is there a way
>> we can keep slave to catch up the master?
>>
>> Thanks....
>>
>>
>>
>> On Wed, Jun 25, 2014 at 10:04 PM, Steve Singer <ssinger at ca.afilias.info
>> <mailto:ssinger at ca.afilias.info>> wrote:
>>
>>     On 06/25/2014 11:00 AM, Soni M wrote:
>>
>>         Hello everyone,
>>
>>         Is it OK to delete event log that has been replicated in sl_log_x.
>>
>>
>>
>>     No you shouldn't do that.
>>
>>     You should instead figure out why the slony cleanup event isn't
>>     performing a log switch.  Perhaps because you have a node behind or
>>     because confirmations are not making it back to the origin?
>>
>>
>>
>>         Here's the function I created :
>>
>>         CREATE OR REPLACE FUNCTION _slony_example.delete_sl_log()
>>            RETURNS void
>>            LANGUAGE plpgsql
>>         AS $function$
>>         DECLARE
>>                   v_origin        int8;
>>                   v_seqno         int8;
>>                   v_xmin          bigint;
>>         BEGIN
>>
>>
>>                           for v_origin, v_seqno, v_xmin in
>>                             select ev_origin, ev_seqno,
>>         "pg_catalog".txid_snapshot___xmin(ev_snapshot) from
>>
>>         "_slony_example".sl_event
>>                             where (ev_origin, ev_seqno) in (select
>>         ev_origin,
>>         min(ev_seqno) from "_slony_example".sl_event where ev_type =
>> 'SYNC'
>>         group by ev_origin)
>>                           loop
>>
>>         delete from _slony_example.sl_log_1 where log_origin = v_origin
>> and
>>         log_txid < v_xmin;
>>         delete from _slony_example.sl_log_2 where log_origin = v_origin
>> and
>>         log_txid < v_xmin;
>>                           end loop;
>>
>>         END;
>>         $function$;
>>
>>         The Slony failed to switch sl_log for several hours, and event
>>         log keep
>>         accumulated in a table.
>>
>>         Will this action help to reduce slony lag that falls far behind ?
>>
>>         Thanks all.
>>
>>         --
>>         Regards,
>>
>>         Soni Maula Harriz
>>
>>
>>         _________________________________________________
>>         Slony1-general mailing list
>>         Slony1-general at lists.slony.__info
>>         <mailto:Slony1-general at lists.slony.info>
>>         http://lists.slony.info/__mailman/listinfo/slony1-__general
>>
>>         <http://lists.slony.info/mailman/listinfo/slony1-general>
>>
>>
>>
>>
>>
>> --
>> Regards,
>>
>> Soni Maula Harriz
>>
>
>


-- 
Regards,

Soni Maula Harriz
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20140626/5ae43d2c/attachment.htm