<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Sun, Jan 5, 2014 at 11:49 AM, Steve Singer <span dir="ltr"><<a href="mailto:ssinger@ca.afilias.info" target="_blank">ssinger@ca.afilias.info</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">On 01/03/2014 02:42 PM, Tory M Blue wrote:<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
And since it appears that my index creation is the big time hole, I'm<br>
going to be testing work mem, by going from 100M to 2GB+ and i'll reduce<br>
my vacuum processes from 7 to maybe 2 or 3, so I don't blow anything out<br>
. But you have intrigued me :)<br>
<br>
</blockquote>
<br>
Have you actually confirmed that the index rebuild is what is taking most of the time?<br>
<br>
The other thing you might want to consider is dropping the indexes on the replica before you start the subscribe set. Then you can create new indexes outside of slon after the subscription is done. If you do it this way you can create multiple indexes on the table in parallel and use multiple cores.<br>
<br>
<br></blockquote><div>I believe so, based on how fast I get the copy statement, meaning that it copied the table over and the 5+hours has to be the index creation.</div><div><br></div><div>I did some tests today with my query DB's, which used to take 2-4 hours to replicate (from a ground up (drop/add)). Took 25 minutes today. So I think giving slony resources to create the indexes is vital, I had it set to 100MB, set it to 2000MB today and reduced the vacuum processes from 5 to 3 (just to be sure) and this really seems to help. I won't be able to test this on my insert db's for a bit (have to wait for a scheduled maintenance).</div>
<div><br></div><div>Droppping indexes on a production node before replicating, that seems like a really bad idea, it would cause my production system to drop to it's knees or fall over while trying to replicate to the secondary. The indexes are required and I really can't remove them. Now maybe you are meaning that I move the index creation to the end of the addnode, vs doing it immediately upon a table creation? </div>
<div><br></div><div>thanks again I appreciate the assistance this group has given!</div><div><br></div><div>Tory</div></div></div></div>