On Fri, 11 Apr 2003, Ed L. wrote:
>
> Again, if there are a large number of transactions queued on the master,
> possibly constituted by an even much larger number of actual row changes, a
> batch size of 1 transaction means, in this context, the master will be hit
> once for every single transaction to retrieve the queued rows corresponding
> to that transaction. A batchsize of, say 1000 transactions, on the other
> hand, will save 999 queries. Granted, you may not share my concern about
> the impact of that on master or network.
How you query things on the master and when you commit things on the slave
aren't all that related. As far as I can tell your batch version of the
query using the temporary table will give you the same ordering of the
updates as the current dbmirror implementation. As your reading the
result of the second query(that gives data to make the SQL statements to
send to the slave) you can decide when to do a comit on teh slave.
Reading the discussion it sounds like your talking about sending this
batch of 1000 transactions to teh slave in 1 transaction. Why not issue a
commit on the slave everytime the xid changes? From the slaves point of
view the behaviour is the smae as in teh current dbmirror(the original
transactions are preserved with an approximation of the ordering based on
the last update/insert in each transaction)
I still don't see why you would want to only do 1 commit per group on the
slave though.
>
> 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.
>
> Ed
>
--
Steven Singer ssinger@navtechinc.com
Dispatch Systems Phone: 519-747-1170 ext 282
Navtech Systems Support Inc. AFTN: CYYZXNSX SITA: YYZNSCR
Waterloo, Ontario ARINC: YKFNSCR
