I am trying to merge 4 source db's (one for each region) into a global db. We use ID ranges as primary key on the tables in each region and so the merge should happen nicely. I have a fairly high volume of updates/inserts occurring in these regional db's, about 1MB a second.

However -

  1. The blackhole solution does not quite work for more than two master DB's. I have 4 source DB's and chaining them serially like that would slow down the updates/inserts too much and overall make the system too fragile.

  2. I don't think the stop slave/switch master/start slave loop would work either because that would slow things down too much. All source db's update at the same time.

  3. I can't use MariaDB since it's still in Alpha and this is a production environment.

The source DB's have all the same name and exact same table structure. Although when there are upgrades, we will have situations where the table structures are out of sync for a few days during gradual rollout.

Here is what I tried -

  1. One Slave for each region source master

  2. Each Slave has federated tables that point to tables in the Global DB

  3. Triggers on the replicated table in the slave pass on the change to the Global DB via updates/inserts/deletes on the federated table

Problem here - the update/deletes on the Federated tables in the slave, seem to trigger "select *" within the global db for some reason and are s-l-o-w. Yes, I have the proper indexes all around.

Help!! Any other ideas?

There is no solution for fan-in with traditional MySQL replication.

Another option is Tungsten Replicator. Here are a couple of posts on how it supports fan-in replication:

Another option is Galera replication, which is integrated with Percona XtraDB Cluster and MariaDB Galera Cluster. In Galera, all cluster nodes take replicated changes from all other cluster nodes. Which satisfies your goal of fan-in -- sort of -- but it means that all nodes have all the data.

For what it's worth, MariaDB 10 made their General Availability announcement on March 31, 2014.

I don't recommend the Federated storage engine. It should really be deprecated, because it's slow, buggy, and fragile. My feel is that MySQL is considering scrapping it. See http://www.tocker.ca/2013/10/17/how-do-you-use-the-federated-storage-engine.html

  • Thanks a lot! How does one +1 you? – Anil Apr 15 '14 at 20:09
  • @Anil, you need at least 15 rep to upvote. stackoverflow.com/help/privileges/vote-up – Bill Karwin Apr 15 '14 at 20:17
  • How mature/stable is Tungsten and MariaDB? The app I'm working on can't afford reliability issues of any sort. – Anil Apr 15 '14 at 22:19
  • @Anil, All software has bugs. – Bill Karwin Apr 15 '14 at 22:22
  • Ofcourse! And Software does take a while to mature and stabalize. So my question was, how stable/mature was Tungsten and MariaDB. – Anil Apr 16 '14 at 13:12

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.