"Ujwal S. Setlur" <uvsetlur at yahoo.com> writes:

> --- Vivek Khera <vivek at khera.org> wrote:
>
>> 
>> On Jan 9, 2006, at 5:29 PM, Ujwal S. Setlur wrote:
>> 
>> > Thank you. I guess I look at the st_lag_num_events
>> > field and wait for it to be 0 or close to it?
>> Right
>> > now, that number is 6000 and climbing. I started
>> the
>> > subscription about 6 hours ago. One of the tables
>> is
>> > very large with ~50 million rows or more.
>> 
>> yes, it could take a while to copy the data...
>> especially if you left  
>> the indexes on the replica.
>
> Interesting. It took about 24 hours to completely catch
> up. st_lag_num_events is now 0. How do I not leave indexes on the
> replica? Do I just get rid of the indexes in the slave database? I
> have quite a few indexes on that large table.

What you could do on the slave is to, yes, indeed, drop as many
indices as possible, before requesting SUBSCRIBE SET.  Not the primary
key one; Slony-I will complain about that :-).  But you could drop all
the others.

Then, after the subscription is done (probably it's best to let things
catch up somewhat, too...), you could run "CREATE INDEX ..." on the
subscriber to recreate the appropriate indices.  If sort_mem is set
appropriately high, that will do you better than having the indices
being incrementally populated while the COPY_SET event runs.

> Also, I understand that slonyI 1.1.5 will have greatly
> improved subscribtion performance. Might I see some
> performance in my case?

I ran a test just before leaving for Christmas vacation; the results
were *less* exciting than I had hoped for.

We had a nice, new p570 cluster with some real nice, fast disk.

I ran subscriptions twice:
 - One with Slony-I 1.0.5, without the "subscription tweak"
 - One with 1.1.5-RC1, with the tweak

Tables with a bunch of indices were copied *considerably* faster than
before.  Sometimes as good as 3x as fast.  

But there were a lot of tables where performance had evidently *not*
been dominated by the cost of incrementally building indices.

Overall, the difference was only and improvment of something like 15%.

If you have Real Big Sets, and plenty o indexes, you'll probably do
better.  But it wasn't as if subscriptions ran 3x faster overall, at
least not for our cases.
-- 
let name="cbbrowne" and tld="ca.afilias.info" in name ^ "@" ^ tld;;
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)