<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Thu, Jan 2, 2014 at 1:08 PM, Shamshinur <span dir="ltr"><<a href="mailto:shurale9811@gmail.com" target="_blank">shurale9811@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 lang="EN-US" link="blue" vlink="purple"><div><p class="MsoNormal">
<span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Very strange, shouldn’t take that long, you may try increase the value of maintenance_work_mem in postgres.conf, to speed up COPY<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">For instance:<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> maintenance_work_mem = 1024MB<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"><u></u> <u></u></span></p>
<div style="border-style:solid none none;border-top-color:rgb(181,196,223);border-top-width:1pt;padding:3pt 0in 0in"><p class="MsoNormal"><b><span style="font-size:10pt;font-family:Tahoma,sans-serif">From:</span></b><span style="font-size:10pt;font-family:Tahoma,sans-serif"> <a href="mailto:slony1-general-bounces@lists.slony.info" target="_blank">slony1-general-bounces@lists.slony.info</a> [mailto:<a href="mailto:slony1-general-bounces@lists.slony.info" target="_blank">slony1-general-bounces@lists.slony.info</a>] <b>On Behalf Of </b>Tory M Blue<br>
<b>Sent:</b> Thursday, January 02, 2014 2:35 PM<br><b>To:</b> slony1-general<br><b>Subject:</b> [Slony1-general] 6 hours to replicate single table, 12 hours to replicate DB<u></u><u></u></span></p></div><p class="MsoNormal">
<u></u> <u></u></p><div><p class="MsoNormal"><u></u> <u></u></p><div><p class="MsoNormal">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.<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">The table is<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><div><p class="MsoNormal">2013-12-21 19:17:58 PST CONFIG remoteWorkerThread_1: Begin COPY of table "impressions"<u></u><u></u></p>
</div><div><p class="MsoNormal">2013-12-21 19:37:03 PST CONFIG remoteWorkerThread_1: 12657163552 bytes copied for table ”impressions” <u></u><u></u></p></div><div><p class="MsoNormal">2013-12-22 01:40:22 PST CONFIG remoteWorkerThread_1: 22944.144 seconds to copy table ”impressions” <— 6 hours<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Postgres 9.2.4 slony 2.1.3<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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).<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Slony config , pretty much at default other than sync interval.<u></u><u></u></p></div>
<div><p class="MsoNormal"><u></u> <u></u></p></div><div><div><p class="MsoNormal"># Check for updates at least this often in milliseconds.<u></u><u></u></p></div><div><p class="MsoNormal"># Range: [10-60000], default 2000<u></u><u></u></p>
</div><div><p class="MsoNormal">sync_interval=1000<u></u><u></u></p></div><div><p class="MsoNormal">#sync_interval_timeout=10000<u></u><u></u></p></div><div><div><p class="MsoNormal"># apply every single SYNC by itself.<u></u><u></u></p>
</div><div><p class="MsoNormal"># Range: [0,100], default: 6<u></u><u></u></p></div><div><p class="MsoNormal">#sync_group_maxsize=6<u></u><u></u></p></div><div><p class="MsoNormal">#sync_max_rowsize=8192<u></u><u></u></p>
</div><div><p class="MsoNormal">#sync_max_largemem=5242880<u></u><u></u></p></div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">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 :)<u></u><u></u></p>
</div><div><p class="MsoNormal"><u></u> <u></u></p></div><div><p class="MsoNormal">Thanks<u></u><u></u></p></div><div><p class="MsoNormal">Tory<u></u><u></u></p></div></div></div></div></div></div></div></blockquote></div>
<br></div><div class="gmail_extra"><br></div><div class="gmail_extra">We are getting outside of slon and into postgres and i'll try to keep this light, here are my postgresql custom config aspects</div><div class="gmail_extra">
<br></div><div class="gmail_extra">shared_buffers = 2000MB<br></div><div class="gmail_extra">max_prepared_transactions = 0 <br></div><div class="gmail_extra">work_mem = 100MB <br></div><div class="gmail_extra">maintenance_work_mem = 128MB<br>
</div><div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><div class="gmail_extra">checkpoint_segments = 75 # in logfile segments, min 1, 16MB each</div><div class="gmail_extra">
checkpoint_timeout = 10min # range 30s-1h</div><div class="gmail_extra">#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0</div><div class="gmail_extra">checkpoint_warning = 3600s </div>
<div class="gmail_extra"><br></div><div class="gmail_extra">The hardware is being refreshed but right now it's an 8 core, 32GB CentOS system, being replaced with some pretty big hardware 256GB/32cores many SSD's. But tuning this now and knowing where I need to tweak when I add the added capacity would be great.</div>
<div class="gmail_extra"><br></div><div class="gmail_extra">thanks again</div><div class="gmail_extra">Tory</div></div><div class="gmail_extra"><br></div></div>