Han He wrote:
> Hi, Karl
>
> Thanks a lot for your patient and helpful explanation. I do not quite
> understand the mechanism of slony. I am a newbie on slony. From the
> previous replies of you and others, I have the following conclusion.
> Am I right?
>
> SLONY sends over a block of updates/changes as a transaction. In one
> transaction, "a block of update/changes" is committed as a whole. The
> scenario that one update/change succeed and others fail never happen.
Correct, BUT Slony doesn't "know" when your application transactions are
committed.  In other words Slony's view of a "transaction" may contain
one or more application transactions.  You can control when Slony
considers a transaction for its purposes complete, but if you don't it
decides on its own.
> Then I have another question.
>
> Once there exists a block of updates/changes that application take
> them as a whole. However if SLONY split them into two or more
> transactions. On the slave node, one transaction succeed, while
> another might fail because of network problem.
>
> It seems this problem turns out to be that whether SLONY can define a
> transaction clearly. Or how frequent does SLONY construct a transaction?
On its own, unless you tell it otherwise, based on communication between
the clients and server.
>  =

> From your last reply, you mean "I and others use a timed job that
> forces segregation to prevent an outage creating one monster
> transaction that might never get across the wire, or might not for
> long enough as to be effectively never.".
> How could I define a timed job for SLONY?
>
I execute this on the master on a timed basis out of the cron:

/usr/local/pgsql/bin/generate_syncs.sh cluster-name localhost 5432
database-name

The script contains:

#!/bin/sh

# $Id: generate_syncs.sh,v 1.1 2004-11-25 21:57:18 cbbrowne Exp $

# Christopher Browne
# Afilias Canada
# 2004-11-15
# For the Slony-I project

# This script may be run every few minutes to force in syncs on a
# particular node.

# Supposing you have some possibly-flakey slon daemon that might not
# run all the time, you might return from a weekend away only to
# discover the following situation...

# On Friday night, something went "bump" and while the database came
# back up, none of the slon daemons survived.  Your online application
# then saw nearly three days worth of heavy transactions.

# When you restart slon on Monday, it hasn't done a SYNC on the master
# since Friday, so that the next "SYNC set" comprises all of the
# updates between Friday and Monday.  Yuck.

# If you run generate_syncs.sh as a cron job every 20 minutes, it will
# force in a periodic SYNC on the "master" server, which means that
# between Friday and Monday, the numerous updates are split into more
# than 100 syncs, which can be applied incrementally, making the
# cleanup a lot less unpleasant.

# Note that if SYNCs _are_ running regularly, this script won't bother
# doing anything.

export NAMESPACE=3D"_${1}"
export PGHOST=3D$2
export PGPORT=3D$3
export DATABASE=3D$4
INTERVAL=3D"10 minutes"   # Don't bother generating a SYNC if there has been
                        # one in the last $INTERVAL
PSQL=3D`which psql`    # Replace this with your favorite command to run psql

echo "select \"$NAMESPACE\".generate_sync_event('$INTERVAL');" | \
   ${PSQL} -h $PGHOST -p $PGPORT -d $DATABASE

------

The purpose behind this is as described in the comments; if there has
been a "sync event" generated in the last 10 minutes it does nothing,
otherwise, it generates a sync when run.  I run this out of the CRON
every 20 minutes so as to prevent the "day-long" transaction block if
fomr some reason (a long weekend, etc) I am not being particularly
attentive to the reporting that usually goes on.  (This shouldn't happen
as I also have a "surveillance" job that runs every couple hours and
emails me if it finds any SLONs down or a material backlog of
transactions, but you know Murphy and how he has a habit of showing up
at a bad time!) =


Since my replication systems are on the other end of a WAN it is
entirely possible for an extremely-long transaction, in an unstable
comms environment where a disruption may happen every few hours, to
never complete.  That would suck and this is what the script is intended
to avoid.

The general way to think of how SLONY deals with replication is that a
"Sync Set" comprises a transaction for SLONY.  Effectively, a "Sync Set"
has a "BEGIN/COMMIT" wrapped around it; if you look in your SLON logfile
you'll see each "block" of updates has a number of inserts, updates and
deletes reported on the detail line, one per update.

You could have the application execute the above SQL statement from the
script (with a "zero" interval - that should work although I haven't
attempted it) after each COMMIT in your application if you wanted to in
order to generate a SYNC SET for each committed transaction, but doing
so on a system with a lot of UPDATEs or INSERTs would generate an
obscene number of SYNC SET events.  I expect that it would also
dramatically reduce the latency between master and slave.  Be warned
that this may or may not have a nasty (and unacceptable) impact on
SLONYs performance - you'd have to benchmark it to see whether it does.

-- Karl

-------------- next part --------------
A non-text attachment was scrubbed...
Name: karl.vcf
Type: text/x-vcard
Size: 265 bytes
Desc: not available
Url : http://lists.slony.info/pipermail/slony1-general/attachments/20090826=
/1d830a37/karl.vcf