On Sun, Jan 5, 2014 at 11:49 AM, Steve Singer <ssinger at ca.afilias.info>wrote:

> On 01/03/2014 02:42 PM, Tory M Blue wrote:
>
>
>  And since it appears that my index creation is the big time hole, I'm
>> going to be testing work mem, by going from 100M to 2GB+ and i'll reduce
>> my vacuum processes from 7 to maybe 2 or 3, so I don't blow anything out
>> . But you have intrigued me :)
>>
>>
> Have you actually confirmed that the index rebuild is what is taking most
> of the time?
>
> 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.
>
>
> 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.

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).

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?

thanks again I appreciate the assistance this group has given!

Tory
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20140105/c5b7d102/attachment.htm