Re: Replication Using Triggers

Andrew Sullivan wrote:
> On Fri, Jan 18, 2008 at 04:09:45PM +0000, gordan(at)bobich(dot)net wrote:
>> That's just it - I don't think any user-land libraries would actually be
>> required. One of supposed big advantages of MySQL is it's straightforward
>> replication support. It's quite painful to see PostgreSQL suffer purely
>> for the sake of lack of marketting in this department. :-(
>
> The "straigtforward" replication support in MySQL is seriously broken.

I am not arguing that it isn't! :-)
I am merely trying to implement something at least as good (or rather,
no more broken) for PostgreSQL with a minimum of effort.

> We
> (by which I really mean "Jan") spent a great deal of time on the design of
> Slony (and it's add-on nature is a feature, not a bug -- one thing it can do
> is cross-version upgrades on PostgreSQL versions that were out before Slony
> was finished being dfesigned) to avoid several nasty corner cases that are
> sort of waved aside in the MySQL documentation. Designing a replication
> system that works well 80% of the time is a waste of effort, because the
> times when you really need it are all already in that 20% of cases that you
> won't cover with the simple-minded solution.
>
> Specifically,
>
>> 1) That's what MySQL does (it either ignores errors or stops replication
>> on encountering an error, which of those two it does is selectable, but
>> that's about it).
>
> That's got to be _the_ most brain-dead approach to replication I've ever
> heard. It chooses the two least good of all possible worlds, and when you
> get into your particular version of hell at 0-dark:30, you have to spend
> some time first figuring out which hell you happen to be in.

I couldn't agree more. But I don't see another multi-master replication
solution on the horizon.

> In any case,
>
>> fire and forget asynchronous replication a-la MySQL. Having a choice
>> between transactions and speed is good. :-)
>
> if this is what you believe, then you don't need a database to store your
> data anyway. I can make your data system faster by storing all your data on
> /dev/null. Writes will be very fast indeed.

Fantastically put. :-)

But in the meantime, until a better multi-master replication solution
becomes available, I think I'll stick with the current plan.

I suppose some kind of a write counter with a rolling write query hash
could be implemented. Replicator function issues locks and compares the
counters/hashes to establish whether a state is consistent on all nodes
before a write query is replicated. It's a kludge and a horrible one at
that, and it will slow down the writes under load, but I think it would
work for ensuring ordering consistency with not-commutative write
operations.

Gordan