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

"Ed L." wrote:
>
> On Friday April 11 2003 1:07, Jan Wieck wrote:
>
> > "the original transaction" - singular!!! Not a couple, few, maybe some,
> > part, fraction or anything in between, above or below. Exactly ONE.
>
> Repeated exclamation points, pedanticism, and all caps shouting duly noted.

That's not nice, it's so seldom that I have a chance to do that and you
won't even let me. ;-(

I apologize. Wasn't meant that way.

> Again, I have no interest in partial transactions, only groups of multiple
> transactions.

Okay, you want to lower the impact on the master by slurping the effects
of multiple transactions (across all tables they hit) in one
transaction.

Now you can do two basic things.

A) You apply those changes in the order you read them out of the master
on the slave. This requires that you do it all in one big transaction on
the slave side and that you can set all foreign key constraints to
deferred, getting you into the deferred trigger queue overflow risk
mentioned.

B) You read all the changes across all tables, but regroup them into
their correct order and original transaction boundaries for playback on
the slaves. Now you need some staging area where you collect and sort it
all running the risk to run out of disk space before you even begin the
first transaction on the slave side.

B2) You read all the changes across all tables simultaneously via
cursors. Worst case you need as many cursors as you have tables and it's
a bit complicated to keep track of all the group changes all over the
place, but you can rebuild the transaction groups on the fly. Certainly
doable, but I wouldn't want to maintain that software.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #