>> This handles part of the problem but a true load balanced master
>> solution is needed. There's no real advantage in spending 5, 10 or
>> $20,000 on a failover master if you can't load balance and the spare
>> will just sit idle.
> Sure there is. If your master blows up, you have a spare waiting to
> take its place. It may not solve your problem, but it is a "real"
> advantage for some folks.
Heh, that's a tough pill for management to swallow. :) We've been
discussing the HA/Failover solutions for our DB's all day. We finally
came to the conclusion we'll have to use some type of failover (non-load
balanced) solution and have the failover either a) reside in a slave
cluster, or b) provide another service to be deemed "non-idle".
>> Master servers should intelligently talk to each other and determine
>> duplicate key problems.
> What if the masters are a few thousand miles apart with 80-120ms
> network latency? You may gain some load-handling capabilities (in
> theory), but you're got a serious bottleneck to deal with.
This is a good point. I'm at a loss for how to handle the issue then.
Move away from using auto increment? We're taking a low/moderate amount
of traffic from a consistent provider that generates 500-700
queries/minute. The odds of duplicate keys based on auto_increment in
load balanced masters is too high for comfort.
-snip-
>> Then toss in some code to sync the downed master with the current
>> running ones.
> Instead of using MySQL's native replication?
No, using MySQL replication. Copying 15GB of data across even the LAN
is too much for me. :)
>> Perhaps you could point replication to the LVS IP instead of a
>> specific machine. When it comes back up, it will find a valid
>> master to connect to via LVS, replicate, and then rejoin the
>> collective... err, cluster. :)
> The trick is to make sure that all the masters have EXACTLY the same
> data in their binary logs (give or take the server-id).
Well, I realized you can't point to a machine back into its own cluster.
The cluster of slaves is still a viable option, but load balanced
masters will have to wait...
Thanks for the input.
-J
