On 8/1/07, Laurent Raufaste <laurent at over-blog.com> wrote:
> Here's our issue: In the sl_status view I notice that the st_lag_time is
> always between 1 and many seconds: it goes up to 10 seconds regularly,
> and approximatively one time a day, there is always a slave reaching 1
> min, for example while vacuuming.
>
> I tried playing with the folllowing options:
>      -s <milliseconds>     SYNC check interval (default 10000)
>      -t <milliseconds>     SYNC interval timeout (default 60000)
>      -o <milliseconds>     desired subscriber SYNC processing time
>      -g <num>              maximum SYNC group size (default 6)
>
> Now on the master I have:
> -s 1000 -g 50
> On the fast slaves I have:
> -s 1000
> And on the slow slaves:
> -s 10000 -g 10
>
> I tried lowering the SYNC check interval to 500ms with no real effect,
> and the master is already loaded enough anyway ;)
>
> Is there an effective way to shorten the replication lag time ?

As Chris mentioned, in terms of reducing replication lag time using
slony configuration options, setting -s (sync check interval) lower on
the set origin servers is the only way to reduce replication lag.

My understanding is if there is no database insert/update activity on
the master, no syncs will be generated no matter how low the sync
check interval is set until the sync interval timeout is reached. This
means that your sl_lag_time will be higher than your actual
replication lag time (unless of course you hit the race condition
mentioned in the docs related to the sync interval timeout setting).

So on your slaves where there is no insert/update activity, the
st_lag_time will always run up to your sync interval timeout before
going to zero.

On your masters sl_lag_time will increase until slon processes a sync
while polling at the sync check interval or until a sync is generated
because of the sync interval timeout.

You can verify this by setting your sync interval timeout to a large
value. If your database is consistently updated st_lag_time will
remain low, otherwise it will increase until a sync is generated.

IMO a better indication of how far back the slave is to compare
st_last_event, st_last_received_event (they should match up or be
close) and st_last_received_ts and st_last_received_event_ts (this
should be appx how long it took the last received event to get
replicated and for the slave to notify the master).

Perhaps renaming st_lag_time to st_sync_lag_time and making
st_lag_time (st_last_received_ts - st_last_received_event_ts) would be
more intuitive.

I use a sync check interval of 250 and a sync interval timeout of
10000. The higher timeout setting increases the risk of the race
condition mentioned in the docs resulting in occasional sync intervals
which are longer, but that's OK for my case and I'd rather have fewer
syncs generated during idle periods. If you want to be sure that you
get replication events processed as quickly as possible set the check
interval and interval timeout low for the master slon process.

I'm still pretty new to Slony, too so if I am incorrect in any of my
understandings, please correct me!

-Dave