<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote">On Thu, Jan 2, 2014 at 2:35 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-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">
<div dir="ltr"><br><div class="gmail_extra">Wondering what settings I need to speed this up. To do a rebuild of a db it takes a long time, 6 hours for a singe table. No I/O issues, no load, just slon postgres taking their sweet old time. I would like to use the resources available to speed this up.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">The table is</div><div class="gmail_extra"><br></div><div class="gmail_extra"><div class="gmail_extra">2013-12-21 19:17:58 PST CONFIG remoteWorkerThread_1: Begin COPY of table "impressions"</div>
<div class="gmail_extra">2013-12-21 19:37:03 PST CONFIG remoteWorkerThread_1: 12657163552 bytes copied for table ”impressions” </div><div class="gmail_extra">2013-12-22 01:40:22 PST CONFIG remoteWorkerThread_1: 22944.144 seconds to copy table ”impressions” <— 6 hours</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">Postgres 9.2.4 slony 2.1.3</div><div class="gmail_extra"><br></div><div class="gmail_extra">This is a larger table, but because of bloat etc, we need to do ground ups to clean it out every so often (Vacuums don't do it).</div>
<div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra">Slony config , pretty much at default other than sync interval.</div><div class="gmail_extra"><br></div><div class="gmail_extra">
<div class="gmail_extra"># Check for updates at least this often in milliseconds.</div><div class="gmail_extra"># Range: [10-60000], default 2000</div><div class="gmail_extra">sync_interval=1000</div><div class="gmail_extra">
#sync_interval_timeout=10000<br></div><div class="gmail_extra"><div class="gmail_extra"># apply every single SYNC by itself.</div><div class="gmail_extra"># Range: [0,100], default: 6</div><div class="gmail_extra">#sync_group_maxsize=6</div>
<div class="gmail_extra">#sync_max_rowsize=8192</div><div class="gmail_extra">#sync_max_largemem=5242880</div><div class="gmail_extra"><br></div><div class="gmail_extra">I either need some advanced settings for when we are doing a rebuild, to speed up the process, or I need to do some configurations that stay during normal workloads as well. But normal workloads things are replicated and keep in sync, it's just the rebuild portion. I would like to see it actually stressing my boxen :)</div>
<div class="gmail_extra"><br></div></div></div></div></div></blockquote><div><br></div><div>The one place where it might be worth modifying configuration is to change the amount of memory being used for sorts, as the step *after* the COPY of table "impressions" (which likely takes most of the remaining 6h of the subscription process) is to reindex that table. If the reindex takes 4h, changing the GUC might reduce that 4h significantly.</div>
<div><br></div><div><a href="http://www.postgresql.org/docs/9.2/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">http://www.postgresql.org/docs/9.2/static/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM</a> </div>
<div><br></div><div>Aside from that, there's not too much else to improve in practice, and not too terribly much even in theory.</div><div><br></div><div>We've had discussions (not too lately; I don't think anything relevant has changed too terribly much) about the idea of doing some multi-threading of the SUBSCRIBE/COPY process. The notable idea was to split off the REINDEX process to a series of extra threads so that it could be done in parallel. In that you have one Very Very Large Table, only a limited amount of benefit could be gotten from that; if the biggest table involves 6h of COPY and 4h of REINDEX, then the fastest, in theory, that the subscription could get done is 10h, which probably isn't enough improvement to be worth the added code + fragility that would result.</div>
<div><br></div><div>The last time the concept was discussed, the conclusion was made to not implement parallel processing of REINDEX, on the basis that: </div><div>a) It would be a fair bit of code, adding potential for new bugs;</div>
<div>b) It would add more potential for lock and deadlock conditions;</div><div>c) In the common case where one table's data dominates the database (which sure seems to be the case for you), there's little benefit as the theoretical minimum subscription time is *at minimum* </div>
<div> time-to-COPY biggest table + time to reindex biggest table</div><div>which makes the optimization pretty futile.</div><div><br></div><div>Your case doesn't seem to invalidate the reasoning.</div><div><br></div>
<div>You could get *some* benefit from splitting things into two sets, one consisting of the table "impressions" and the other consisting of the rest. I'd hazard the guess that there's not too much need to clean up the tables other than "impressions"; you could drop the 'outage' to the theoretical minimum if you don't bother re-subscribing the other tables.</div>
<div><br></div><div>I wonder if there's merit to trying to split "impressions" into several tables, one which would contain the "hot, heavily updated" data, and others containing "cooked/complete" data that doesn't need to be reorganized. It may not be easy to distinguish between data that is "complete" and data that is still changing a lot.</div>
</div></div></div>