Re: Batch replication ordering (was Re: [GENERAL] 32/64-bit

On Friday April 11 2003 6:08, Stephan Szabo wrote:
> On Fri, 11 Apr 2003, Ed L. wrote:
> > For those of you who do see the validity in batching multiple
> > transactions, my question is restated here:
> >
> > My question: Is there an ordering algorithm that would make a
> > consistent but limited batchsize replication possible? I propose one
> > below.
> >
> > Suppose you have a replication queue on the master, with dbmirror-like
> > trigger-based insertions, that looks something like this:
> >
> > create table replication_queue (
> > xid integer,
> > seqid serial primary key,
> > data...
> > );
> >
> > Here, 'xid' is the transaction ID, 'seqid' is the queue insertion
> > order, and 'data' has all the info needed to replicate the update.
> > Every row update/delete/inserte results in a row in this queue, and a
> > transaction may consist of one to many rows in the queue.
> >
> > The algorithm I'm considering right now is the following:
> >
> > select xid, max(seqid) as "max_seqid"
> > into temp replication_order
> > from replication_queue
> > group by xid
> > order by max(seqid)
> > limit N;
> >
> > Then, to get the actual queue replication order,
> >
> > select q.xid, q.seqid, q.data
> > from replication_queue q, replication_order o
> > where q.xid = o.xid
> > order by o.max_seqid, q.seqid;
> >
> > [This is a batched variation of dbmirror's original algorithm.]
> >
> > So, replication is done by transaction groupings, in ascending order
> > according to the maximum seqid in each transaction. I'm hoping someone
> > can poke holes in this algorithm if they exist.
>
> Does it matter if transactions that do not affect each other are
> committed on the slave in a different order than they were on the master?
> I don't think that's guaranteed by the above (unless the inserts into
> replication_queue were deferred to transaction end), but I'm also
> uncertain if it's a constraint you're concerned with.

I appreciate your pointing that out. It is pretty undesirable for data to
appear on the slave in an order different from the one in which it appears
on the master. I guess that's another downside to batching. I'm not sure
this approach can do any better than approximating the order since there is
no knowledge of the commit order.

Ed