On Wed, Aug 3, 2016 at 12:30 PM, Jan Wieck <jan at wi3ck.info> wrote:

>
>
> On Wed, Aug 3, 2016 at 11:14 AM, Tory M Blue <tmblue at gmail.com> wrote:
>
>> Hey folks
>>
>> Running into some more issues and i'm not finding a definitive tuning
>> guide nor can I tell why i'm backing up and why it takes so long to catch
>> up (so long is relative).
>>
>> Running Slony 2.2.3 (but this has been an issue for a long time).
>>
>> Image to show you where I am this AM , but this is a constant every AM.
>> I have large updates about 2.5 million between 12am and 4am, the job is
>> done but it will take Slon a couple of hours to catch up.
>>
>
> Are there long running transactions that start around the time, the sl_log
> starts
> growing? Any long running transaction prevents a log switch from
> finishing.
>
>
> Jan
>

Hi Jan,

There are really not many long queries during the big import, but things
start getting longer when the slon logs have 10m+ rows in them.

I actually log any transaction that takes over a second and there is
nothing that stands out, nothing that runs all that long. During this
period, I move my reporting to point to the master, because of the delay in
the standby, so there are some longer report queries, but the longest is 16
minutes

duration: 986036.592

So I don't see any really long running queries, but I do see lots of these;
 *Slony-I: could not lock sl_log_2 - sl_log_2 not truncated*

Looks like the last time it was able to complete was 10:07 but between 1am
and 10am, I see the earlier error many times, but it's not telling me what
is holding it up and I don't see a process that is holding it up during
this time, Backups are done on the standby (schema and stuff from the
primary, data from the standby, so there is no dump or anything holding it
up.

The gap between successful log switch is pretty substantial. (and the big
import Job was done at 5am)

*2016-08-03 01:14:57 PDT clsdb postgres 10.13.200.231(37864) 58874
2016-08-03 01:14:57.619 PDTNOTICE:  Slony-I: log switch to sl_log_2
complete - truncate sl_log_1*

*2016-08-03 10:07:08 PDT clsdb postgres 10.13.200.231(37864) 58874
2016-08-03 10:07:08.924 PDTNOTICE:  Slony-I: log switch to sl_log_1
complete - truncate sl_log_2*

Also of interest

*2016-08-03 02:15:01 PDT DEBUG2 SYNC Group sizing: prev state: 3 initial
proposed:2 k:1 maxsize:5000 ultimately proposed n:2*

it's constantly just pushing 2 over, even when I  "believe" it's backed up.

Actually it doesn't go over 4 records n:4 (is the highest I see, but 99% of
the time it's 2 records being sync'd).

I see nothing in the slon logs

Thanks again Jan!

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