Geo redundant OSS database
dfussell at byu.edu
Fri Jan 20 14:53:18 MST 2012
On 01/19/2012 10:04 PM, Bryan Sant wrote:
> Has anyone had any experience working with PostgreSQL or MySQL in a
> geo redundant configuration (replication/clustering)? I see that
> MySQL has MySQL Cluster
> (http://www.mysql.com/products/cluster/)--which is their "Carrier
> Grade" offering. Looks compelling. I know that PostgreSQL (>=
> version 9) has clustering as well, but I'm not sure how feature
> complete it is. I'm interested in any insights you might have.
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
I've been doing a plain-old, 2-server MySQL active/semi-active
multi-master ring for the last 4 years or so, and it's worked pretty
well for me. I'm using per-server odd/even ID generation and a hardware
load-balancer handling which server a connection request goes to. I once
tried active/active balancing without odd/even ID's out of morbid
curiosity and to get experience in case I ever broke replication. Then
I figured out how to manually reconcile the resulting split-brain
cluster and broken replication and tested again using odd/even ID's. I
didn't have any problems with active/active in testing, but I've been
too chicken to do it in production. One of these days I'll find or
write something to audit each table for absolute consistency and have
nagios test it periodically. Then I'll feel much better about running
it in full active/active fashion.
I'm not doing more than about 30 inserts and deletes every 20 seconds or
so and the machines sit next to each other, so I couldn't tell you how
well it would do remotely under a constant update load. I've had a few
times when a switch knocked out the semi-active machine's network
connection. When the network came back up the replication restarted and
the log replayed just fine. It replayed slower than I would have liked,
catching up something like 10 seconds for every 1 second of real-time.
But the majority of my writes are several sensors inserting and deleting
log records in the same MyISAM table at the same time, so I suspect the
MyISAM full-table lock was the limiting factor in my case. I have some
bayes tables that used to handle a 50000 email/day load across 4 mail
servers and replication had not problem keeping up.
I looked a little at MySQL ClusterDB, but I didn't like that it was in
memory only, and seemed to be limited to MyISAM tables. After the Sun
and Oracle mergers I looked at PostgreSQL a little more, but I got the
impression it's replication was still a work in progress, and not really
on the developer's list of desirable features.
More information about the PLUG