On 02/05/2015 02:03 PM, Tory M Blue wrote:
>
>
> On Thu, Feb 5, 2015 at 10:45 AM, Steve Singer <ssinger at ca.afilias.info
> <mailto: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 <tel: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 <tel: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 <tel: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


Your looking for long running transactions not queries.

What does this give you?

select state,min(xact_start) from pg_stat_activity group by state ;

and what is
select txid_current_snapshot()

>
> 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 <tel:5003918695> |          459837392
>           2 | 5000984115 |           34125442
>           3 | 5000016464 |           15773130
>           4 | 5000878307 |           15901785
> (4 rows)
>
>   Thanks Steve!
>
> Tory