Consolodating into Master DB from multiple Geographically Distributed DBs

[KTWalrus]

I have 4 geographically distributed DBs.  The DBs only need to be sync'd once a night with all changes made to each DB during the day.

I'm thinking of keeping a Master DB up to date and rsync'ing to each of the geographically distributed DBs in the early morning.  Each DB would record its changes to its binlog and I would apply the changes to the Master DB every few hours using mysqlbinlog piped to mysql.

Any problems with this approach? 

I would do the autoincrement trick and I don't think the same rows would be updated in more than one of the distributed DBs (so out of sequence updates should be okay).  In the middle of the night, I would have a cron job sync all of the distributed DBs to the Master DB and then perform some maintenance updates on the master, and rsync the master to each distributed DB.  All DBs may be taken offline during this synchronization time.

I've been thinking, even if I think it is okay to not keep the temporal order of the updates when applying to the master, that I might need to modify mysqlbinlog to open all the input binlog files on start up and replay them in timestamp order (or implement some sort of binlog timestamp merge so the transactions are stored in chronological order).

[Jay Janssen]

Coincidentally, I'm doing a talk on this underlying question at our conference in a few weeks: http://www.percona.com/live/nyc-2012/sessions/hazards-multi-writing-dual-master-setup, and I spent a good amount of time yesterday thinking about it.

Technically what you are suggesting is possible, but it's impossible to say for sure that it's bullet proof because so much depends on the actual schema, and how the data inside it is used.  

One of the points I'll be making in the talk is that the data we store in and RDBMS tends to be, well, relational.  Writing across multiple masters simultaneously can be done if all the modifications are completely isolated from the other masters until at least replication occurs.    By "isolated", I mean not modified *or* read elsewhere, or if read then an out of date version of the data doesn't matter to associated writes (this is probably a crude definition).  

Because relational data implies relationships and multi-writing depends on isolation (read: the absence of relationships), then whether or not this architecture will "work" depends entirely on where on the continuum your data falls between relational and non-relational.  

That's my take on the subject (whether useful or not, I'm not sure).  

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To view this discussion on the web visit https://groups.google.com/d/msg/percona-discussion/-/h9jDeNu9o24J.
To post to this group, send email to percona-d...@googlegroups.com.
To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.

Jay Janssen, Senior MySQL Consultant, Percona Inc.

http://about.me/jay.janssen

Percona Live in NYC Oct 1-2nd: http://www.percona.com/live/nyc-2012/

[Peter Zaitsev]

Jay,

Yep.  I thing the examples could be in the Sharded world different shards... or  Different Applications, like  Forums going in one direction while Blog in other.  

The other practical issue is recovery. What if replication between nodes becomes badly broken ?  Single direction replica allows reclone as last resort activity while bi-directional might be a lot harder to

recover as you need to decide where data is authoritative. It is easiest if it  can be done per table/schema or something like it.

One myth you might want to dispell is what auto_increment_increment solves all the problems....  it helps inserts but does nothing for other potentially conflicting update.

If you look at the general "thery" of such disconnected operationsyou may think about "eventually consistent" model - after applying all events the data should be same on all nodes... which often would require what events from both direction streams can be applied in any order between them with end result being the same.  Working on different data as stated above is a simple subset of such requirement.

If you can't get such property when you get into conflict detection and resolution piece which gets pretty tricky as application itself can't detect the conflict because replication is asynchronous and you need detection and also resolution to happen

at different level.  This is the theory of it but there is not much support for it in conventional MySQL (there is some in cluster)

--
Peter Zaitsev, CEO, Percona Inc.
Tel: +1 888 401 3401 ext 501   Skype:  peter_zaitsev
24/7 Emergency Line +1 888 401 3401 ext 911

[KTWalrus]

My approach of using mysqlbinlog to apply transactions from multiple distributed DBs asynchronously to a central Master really doesn't involve using the built-in MySQL replication (other than relying on binlogs for row updates).

I've been thinking I should look into modifying the Slave IO Thread logic to accept relay log updates from multiple Masters.  Then, I could configure my central DB as a true MySQL Master and each of the distributed DBs as true Slaves.  In addition, I could make the central DB a multi-slave to each of the distributed DBs using my modifications to the Slave IO Thread logic.

Basically, the modification to the Slave IO Thread logic would better simulate what I was thinking of doing with mysqlbinlog except that transactions would be ordered closer to chronological order.  But, because I would now be using MySQL replication to apply updates from the central DB to the distributed DBs (skipping applying the updates that originated from a distributed DB to itself), I avoid having to rsync at night and the updates are applied much sooner (minimizing some collisions).

This approach is kind of a mixture of MySQL ring replication and an asynchronous Galera cluster (where only a single DB receives all updates and all other DBs get their non-local updates from the central DB).

How hard do you think it would be to modify the Slave IO Thread logic to read updates from multiple masters?

Has anyone done this before and were there any lessons learned?

I realize that my needs are simpler than implementing this for the general case.  For one, I don't care if my non-local updates take a while to be seen in the distributed DBs.  Also, admin updates can be applied to the central DB for normal MySQL replication so the only updates originating from the distributed DBs are simple row updates (insert, update, and delete all by primary key).  And, I don't care if there is a large window where some user sees "stale" data (that has been updated at a separate location, but not yet locally applied).

[KTWalrus]

Just discovered Tungsten Replicator and, according to this Link, the Replicator can set up a Star Topology.  For my purposes, I want the updates to flow from the distributed DBs into the central DB all the time.  For the updates in the other direction, I can live with them being applied continuously.  But, I hope that I can delay the updates to the distributed DBs until off hours (when very few, if any, users are using the distributed DBs).

[KTWalrus]

Thanks.  I intend to handle conflicts by treating them as bugs in my applications that use the database.  I believe there is no reason for conflicts since my applications are designed to not require updates until they sync over night and I believe the rows that are updated in a distributed database are not updated in any other database during the day.  I believe my data is naturally shard'ed in regards to updates.

I intend to eventually use Galera for in-datacenter clustering and Tungsten for asynchronous replication between datacenters.  I believe this architecture will allow "huge" scalability and high availability.  The only scalability issue I see is when the asynchronous updates overwhelm the ability to apply them within 24 hours to all distributed databases, but I can't imagine this ever really happening since slaves will "catch up" during off hours at night.  Also, this architecture  requires complete copies of the central DB on all servers which might add to the system storage costs over time.  Hopefully, I can prune the central DB so it doesn't grow too fast.

On Wednesday, September 19, 2012 3:18:52 AM UTC-4, neila wrote:

Tungsten Replicator would help and you could delay replication by just taking individual replicators offline. The biggest problem as Peter and Jay have already pointed out is conflict resolution. The replicator does not help with that it is still up to your application to handle conflicts as the replicators just take the transactions from server A and apply them to server B.

Neil