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 /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
