Friday, February 02, 2007

Spoke too soon?

After a bit of thought, I came to the realization I spoke too soon. What came to my head this morning is the question "Why do people want multi-master replication?" This is where the problem with my previous idea comes to play because wouldn't improve insert/update performance whereas real multi-master replication would. At least that's what I'm assuming based on how I understand it working.

So, does this mean that my idea is worthless? No, I don't think so. If you have an application with a high percentage of reads, it may be worthwhile to have each client connect to a replicated server instead of the central server for updates.

Now granted, I don't know the performance characteristics about my previous idea, however I'm going to safely guess that if I have 10 replicated servers and I use federated tables for inserts, I'm can bog down the central server. Not exactly a scenario we want.

Thursday, February 01, 2007

Multi-master replication solved with Federated tables?

As many of you may know, MySQL does not support multi-master replication out of the box. Yes, it is possible to come up with a process to handle it, but they're not always the best or the easiest to implement. Well, today I had a thought: could I simulate multi-master replication using Federated tables?

Here's the idea: we have a central server where all inserts will be applied. We then replicate that server to several other servers. On each of the replicated servers, we put a second database that is strictly for making updates to the data. This second database will consist of only federated tables that are linked to the central server. All inserts, updates, and deletes are applied to the second database while all selects would be performed on the first database, which is the replicated data.

Now, I'm not sure how well this will work, but to me it seems like it would be a fairly elegant solution to the problem. Since all of the inserts will technically be occurring on the central server, we don't have to worry about duplicate keys as much.

I'll have to look into this some more...