Geo redundant OSS database

[Daniel Fussell]

On 01/19/2012 10:04 PM, Bryan Sant wrote:
> Pluggers,
>
> 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.
>
> Thanks,
> -Bryan
>
> /*
> 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.

Grazie,
;-Daniel Fussell