On Thu, Feb 5, 2015 at 10:45 AM, Steve Singer <ssinger at ca.afilias.info>
wrote:

> On 02/05/2015 01:41 PM, Tory M Blue wrote:
>
>>
>>
>
>>
>>     Everything must be replicated the confirmation must make it back to
>>     the origin before a log can be truncated?
>>
>>     What does your sl_status on your origin show?
>>
>>     The most common reasons why log truncation hasn't happened are
>>
>>     1) Replication is behind
>>     2) Confirmations aren't making it back to the origin
>>     3) You have a long running transaction on the origin.  A log running
>>     transaction can prevent log switching from taking place even if that
>>     transaction doesn't actually change replicated tables.
>>
>>
>>
>>
>>         I'm not hurting, just stressing at this point
>>
>>         Thanks
>>         tory
>>
>>
>> Thanks Steve
>>
>>   st_origin | st_received | st_last_event |      st_last_event_ts
>> | st_last
>> _received |      st_last_received_ts      |   st_last_received_event_ts
>>    | st_l
>> ag_num_events |   st_lag_time
>> -----------+-------------+---------------+------------------
>> -----------+--------
>> ----------+-------------------------------+-----------------
>> --------------+-----
>> --------------+-----------------
>>           1 |           3 |    5003919991 | 2015-02-05 10:39:51.6253-08
>> |       5
>> 003919976 | 2015-02-05 10:39:38.031286-08 | 2015-02-05 10:39:35.615581-08
>> |
>>             15 | 00:00:16.763937
>>           1 |           2 |    5003919991 | 2015-02-05 10:39:51.6253-08
>> |       5
>> 003919865 | 2015-02-05 10:37:37.127661-08 | 2015-02-05 10:37:34.596838-08
>> |
>>            126 | 00:02:17.78268
>>           1 |           4 |    5003919991 | 2015-02-05 10:39:51.6253-08
>> |       5
>> 003919982 | 2015-02-05 10:39:43.971439-08 | 2015-02-05 10:39:42.618684-08
>> |
>>              9 | 00:00:09.760834
>> (3 rows)
>>
>> Not sure what this means exactly, but again if we are fully replicated,
>> that would tell me that we have syncs being replied to.  Obviously we
>> have new data coming in but it's just slowing growing sl_log_2, which
>> can't switch to sl_log_1, because _log_1 has not been able to truncate.
>> And this is after restarting slon (for grins!)
>>
>
>
> Based on that I'd say the most likely culprit is (3) since it doesn't
> appear to be case 1 or 2. Do you have any long running transactions on your
> origin?
>
>
I'm looking and don't see any long running queries, obviously with such a
large sl_log table now, updates are in the 5-9 second range

2015-02-05 10:50:58 PST clsdb postgres 10.13.200.242(45605) 39989
2015-02-05 10:50:58.522 PSTLOG:  duration: 6350.246 ms  statement: COPY (
select log_origin, log_txid,LOTS OF STUFF DELETED.

Also the below tells me unless 2.2 is totally different that everything in
my log tables has been syncd and just needs to be deleted?

 ev_origin |  ev_seqno  | txid_snapshot_xmin
-----------+------------+--------------------
         1 | 5003918695 |          459837392
         2 | 5000984115 |           34125442
         3 | 5000016464 |           15773130
         4 | 5000878307 |           15901785
(4 rows)

 Thanks Steve!

Tory
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20150205/ca86036c/attachment.htm