Slony1 PostgreSQL Replication - Re: How do I know when a subscriber has caught up?

Author

Re: How do I know when a subscriber has caught up?

Ujwal S. Setlur

2006-01-09, 8:26 pm

--- Vivek Khera <vivek- 5icKCuqbiQzYtjvyW6yD
sg@public.gmane.org> wrote:

>
> On Jan 9, 2006, at 4:39 PM, Ujwal S. Setlur wrote:
>
> look
>
> check the sl_status view on the master.

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.

Ujwal

Vivek Khera

2006-01-10, 11:24 am

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.

Ujwal S. Setlur

2006-01-10, 11:24 am

--- Vivek Khera <vivek- 5icKCuqbiQzYtjvyW6yD
sg@public.gmane.org> wrote:

>
> On Jan 9, 2006, at 5:29 PM, Ujwal S. Setlur wrote:
>
> Right
> the
> is
>
> 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.

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

Thanks,

Ujwal

Christopher Browne

2006-01-10, 1:24 pm

"Ujwal S. Setlur" <uvsetlur-/ E1597aS9LQAvxtiuMwx3
w@public.gmane.org> writes:

> --- Vivek Khera <vivek- 5icKCuqbiQzYtjvyW6yD
sg@public.gmane.org> wrote:
>
>
> 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)

Vivek Khera

2006-01-10, 1:24 pm

On Jan 10, 2006, at 11:57 AM, Ujwal S. Setlur wrote:

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

since you've caught up, no point in deleting the indexes now. i
usually create the schema on the replica without indexes, then add
them later once the initial copy is done, if that replica actually
runs queries. otherwise there's no point in having them...

Sponsored Links