<div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Aug 3, 2016 at 12:30 PM, Jan Wieck <span dir="ltr"><<a href="mailto:jan@wi3ck.info" target="_blank">jan@wi3ck.info</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir="ltr"><br><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Aug 3, 2016 at 11:14 AM, Tory M Blue <span dir="ltr"><<a href="mailto:tmblue@gmail.com" target="_blank">tmblue@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hey folks<br><br>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).<br><br><div>Running Slony 2.2.3 (but this has been an issue for a long time).</div><div><br></div><div>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.</div></div></blockquote><div><br></div><div>Are there long running transactions that start around the time, the sl_log starts</div><div>growing? Any long running transaction prevents a log switch from finishing. </div><div><br></div><div><br></div><div>Jan</div></div></div></div></blockquote><div><br></div><div>Hi Jan,</div><div><br></div><div>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.</div><div><br></div><div>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 </div><div><br></div>
<div>duration: 986036.592 </div><div><br></div><div>So I don't see any really long running queries, but I do see lots of these; <b>Slony-I: could not lock sl_log_2 - sl_log_2 not truncated</b></div><div><p class="gmail-p1">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.<br></p><p class="gmail-p1">The gap between successful log switch is pretty substantial. (and the big import Job was done at 5am)</p><p class="gmail-p1"><b>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</b><br></p><p class="gmail-p1">
</p><p class="gmail-p1"><span class="gmail-s1"><b>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</b></span></p><p class="gmail-p1">Also of interest</p><p class="gmail-p1">
</p><p class="gmail-p1"><span class="gmail-s1"><b>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</b></span></p><p class="gmail-p1"><span class="gmail-s1">it's constantly just pushing 2 over, even when I "believe" it's backed up.</span></p><p class="gmail-p1">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).</p><p class="gmail-p1"><span class="gmail-s1">I see nothing in the slon logs</span></p><p class="gmail-p1">Thanks again Jan!</p><p class="gmail-p1">-Tory</p></div></div></div></div>