<div dir="ltr">How are you monitoring the number of rows in the sl_log_* tables?<div><br></div><div><br></div><div>Jan</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Aug 4, 2016 at 3:04 PM, 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:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><br><div class="gmail_extra"><div><div class="h5"><br><div class="gmail_quote">On Thu, Aug 4, 2016 at 12:02 PM, 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"><br><br>On Thu, Aug 4, 2016 at 10:29 AM, Tory M Blue <<a href="mailto:tmblue@gmail.com" target="_blank">tmblue@gmail.com</a>> wrote:<br>> On Thu, Aug 4, 2016 at 8:39 AM, Jan Wieck <<a href="mailto:jan@wi3ck.info" target="_blank">jan@wi3ck.info</a>> wrote:<br>>> Another problem we recently ran into is that after bulk operations, the<br>>> queries selecting the log data on the data provider can degrade. A<br>>> workaround<br>>> in one case was to ALTER the slony user and set work_mem to 512MB. The<br>>> work_mem of 100MB, used by the application, was causing the scans on<br>>> the sl_log table to become sequential scans.<br>>><br>>> The problem is amplified if the tables are spread across many sets. Merging<br>>> the sets into few larger ones causes fewer scans.<br>>><br>>><br>>> Regards, Jan<br>>><br>><br>> Unfortunately, I've never been able to migrate to a slony user, so<br>> slony runs as postgres and my setting are<br>><br>> work_mem = 2GB<br>><br>> maintenance_work_mem = 2GB<br>><br>> So don't think I can test this theory.<br>><br>> Also of note, we have 3 sets<br>><br>> Same today, just don't see any long running queries where slony has no<br>> time to truncate the table, so I'm not clear what is preventing the<br>> slony table from being truncated. ( it really sounds like something<br>> that Jan has run into before, just a huge table and for some reason<br>> the truncate can't complete before another job or request comes in?<br>> Even now the slon table is over 12Million and I expect it to be able<br>> to truncate/clear in the next 30 minutes or so, but why.. the big bulk<br>> operation finishes at 5am, not sure why it takes another almost 6<br>> hours to truncate that table..<br>><br>> Thanks again!<br>> Tory<br><br>Also interesting today, is that it's getting worse :) but the same pattern, other then it's taking longer and today I saw something really weird. <br><br>slon ran a truncate but only cleared 50% of the sl_log1, How is that possible, I mean a truncate is a truncate, I have no idea how this table went from 14M to 7M<br><br>2016-08-04 10:43:03 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 10:43:03.237 PDTNOTICE: Slony-I: could not lock sl_log_2 - sl_log_2 not truncated<br><br>2016-08-04 10:53:43 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 10:53:43.573 PDTNOTICE: Slony-I: log switch to sl_log_1 complete - truncate sl_log_2<br><br>2016-08-04 11:05:41 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 11:05:41.750 PDTNOTICE: Slony-I: Logswitch to sl_log_2 initiated<br><br><b>2016-08-04 11:16:54 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 11:16:54.783 PDTNOTICE: Slony-I: log switch to sl_log_2 complete - truncate sl_log_1 <--- didn't actually "truncate" the table, there is still 7.5M rows and yet it's moving ahead with logswitch to sl_log_1 (which again was just supposedly truncated, had 14 million rows but has 7.5 Million rows still)..</b><br><br>2016-08-04 11:29:06 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 11:29:06.332 PDTNOTICE: Slony-I: Logswitch to sl_log_1 initiated<br><br>2016-08-04 11:40:43 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 11:40:43.048 PDTNOTICE: Slony-I: log switch to sl_log_1 complete - truncate sl_log_2<br><br>2016-08-04 11:51:44 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 11:51:44.660 PDTNOTICE: Slony-I: Logswitch to sl_log_2 initiated<div><br></div><div>So what I'm also noticing and maybe not related, but my standby node has lots of locks and long running reports, I'm wondering if it's causing some of the backup seen on the master. So many questions still :))</div><div><br></div><div>Thanks for listening and being my wall to bounce stuff off of</div><span><font color="#888888"><div><br></div><div>Tory</div><div><br></div></font></span></div>
</blockquote></div></div></div>Sent to soon</div><div class="gmail_extra"><br></div><div class="gmail_extra">
<p>2016-08-04 12:02:50 PDT clsdb postgres 10.13.200.231(37864) 58874 2016-08-04 12:02:50.556 PDTNOTICE: Slony-I: log switch to sl_log_2 complete - truncate sl_log_1 <b>(This truncate actually removed the 7.5 Million remaining rows), <br></b></p><p><b>I'm confused how can a truncate leave rows?!</b></p><span class="HOEnZb"><font color="#888888"><p><b><br></b></p><p>Tory</p></font></span></div></div>
<br>______________________________<wbr>_________________<br>
Slony1-general mailing list<br>
<a href="mailto:Slony1-general@lists.slony.info">Slony1-general@lists.slony.<wbr>info</a><br>
<a href="http://lists.slony.info/mailman/listinfo/slony1-general" rel="noreferrer" target="_blank">http://lists.slony.info/<wbr>mailman/listinfo/slony1-<wbr>general</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr">Jan Wieck<br>Senior Postgres Architect<font color="#000000"></font></div></div><div><a href="http://pgblog.wi3ck.info" target="_blank">http://pgblog.wi3ck.info</a></div></div></div></div></div>
</div>