High Availability Solutions for Databases? 83
An anonymous reader asks: "What would be the best high availability solution for databases? I don't have enough money to afford Oracle RAC or any architecture that require an expensive SAN. What about open source solutions? MySQL cluster seems to be more master/slave and you can lose data when the master dies. What about this Sequoia project that seems good for PostgreSQL and other databases? Has anyone tried it? What HA solution do you use for your database?"
MySQL Cluster != master/slave (Score:5, Informative)
MySQL cluster and replication are not the same (Score:4, Informative)
Neither option is really "beautiful", though Cluster has a lot of promise for the future, especially in 5.1.
DRBD (Score:4, Informative)
http://www.drbd.org/ [drbd.org]
If you are smart, you'll play around with this on a test network or VMWare first. Get it all tweaked out and actually test it by killing a server while in mid-transaction to see if it works for you.
Re:MySQL Cluster != master/slave (Score:5, Informative)
We deployed it ourselves, and it worked ok for a while, but things went very very wrong when we tried updating one of the configuration parameters, causing us to to inexplicably lose quite a bit of data.
Avoid it. At least for another generation or two, or three.
Slony + PGPool are one postgresql option. (Score:2, Informative)
For commercial postgresql options, Afilias (who wrote Slony and uses it to power the
Re:MySQL Cluster != master/slave (Score:4, Informative)
Hopefully it will make into version 5.1.
MySQL Cluster 4.1/5.0 supports:
* transactions
* transparent data partitioning and transparent data distribution
* recovery using logging
* (multi) node failure and automatic non blocking hot sync for crashed/stopped nodes
* hot backup
MySQL Cluster 5.1 will support:
* user defined partitioning
* cluster to cluster async. replication (like "ordinary" mysql replication)
The disk impl. supports
* support putting column in memory or on disk
(currently index columns has to be in memory)
* all HA features mentioned above.
Re:MySQL Cluster != master/slave (Score:5, Informative)
"Shared storage" doesn't mean "not highly available". If you're serious about building a database that cannot go down, you get multiple servers, each with two Ethernet interfaces and two Fibre Channel cards. Get two Fibre Channel switches, and two Ethernet switches. Get two Fibre Channel disk arrays. Hook together as follows.
Both disk arrays have one or more uplinks to both FC switches. Each server has one HBA connected to each switch. Then, use Linux multipathing to provide automatic failover in case either switch dies or either HBA dies, and use Oracle ASM or Linux MD to mirror the data so you're good even if you unplug the shared storage.
Set up the servers to use 802.1q VLAN tagging. (Remember, it's a good idea to keep your inter-node communication separate from client-to-server communication.) Create two Ethernet bridges, using the Linux bridging driver, binding eth0.2/eth1.2 to one and eth0.3/eth1.3 to the other. Take the two switches, set up 802.1q on the ports going to your database servers, and connect them together (preferably using high-speed uplinks, but channel bonding works fine). Enable spanning tree on the switches and on the bridges. Connect both switches to the rest of your network.
Now, if a switch starts on fire, spanning tree on the servers will fail over to the other one automatically. If a network cable gets cut or a network card goes out on one node, that one node will fail over all traffic to the remaining interface, and the inter-switch trunk will make everything keep working. Suddenly, you've got a robust network.
So, we've got network and storage covered. What about the database software? Neither MySQL nor Postgres can use this sort of configuration, but Oracle RAC can. Add a dash of TAF, and suddenly, any component -- network switch, database server, SAN switch, disk array -- can fail in the middle of a SELECT and the application won't notice. That is highly available.
Yes, this solution costs more. Our data -- more accurately, the cost of it not being accessible -- justifies the expense. But don't tell me that shared storage is a weakness.
Take a look at Avokia (Score:2, Informative)
Radiant Data PeerFS (Score:2, Informative)
Radiant Data has a product called PeerFS which is a replicated filesystem (rw/rw, active/active) which allows you to also hold MySQL databases on it. You run 2 seperate MySQL servers pointing to the same data folder, and have it use POSIX locks for writes. The data is physicaly held on each server, and synced across the network.
I am testing it at work ( http://www.concord.org/ [concord.org] ) now for our websites. VERY easy to setup, but it supports MyISAM tables, and NOT InnoDB (or the other way around).
This does not solve IP failover, but there are other solutions out there for that. As a bonus we're holding our php webpages and MySQL databases files on the replicated PeerFS Filesystem.
-Ben
Re:MySQL Cluster != master/slave (Score:3, Informative)
Each node in an OCFS2 cluster updates a special file every two second and maintains a TCP connection to its neighbors. If this process doesn't work (i.e. writes are failing, are backlogged, or for whatever reason don't hit the disks in a timely fashion), the node will fence itself. Likewise, if the node starts on fire, it will naturally stop updating the heartbeat data. Either way, other nodes are aware of who is up and who is down, even if the IP network dies. If a node's access to the storage system dies, OCFS2 won't be able to successfully read back its own heartbeat timestamp, and the node fences itself.
Note that this heartbeat procedure happens on the same block device that the data files are on. There's no way to reach the data files unless a node is in agreement with every other node, thus no way to cause "split-brain".
Say, somehow, a series of failures occur all simultaneously that results in Array 1 being connected only to nodes 1-4 and Array 2 being connected only to nodes 5-8. Each node keeps heartbeating, and after a little bit, Node 5 notices that Node 1 hasn't updated its timestamp, but that Node 1 just said that it did (over Ethernet). OCFS2 notices this immediately, much inter-node communication happens, and nodes 5-8 decide to fence themselves, leaving 1-4 as the only nodes allowed to touch either disk array. Still working as advertised.
Let's examine that failure. First, you'd have to terminate the links between both fibre channel switches. Then, you'd have to break uplink 2 on array 1 and uplink 1 on array 2. Then, you'd have to take out the second HBA on nodes 1-4 and the first HBA on nodes 5-8. Simultaneously. And the system handles this gracefully.
Now, what happens when the network goes out too, you ask? Remember that each node has one connection to both switches. Say we lose a switch -- all the nodes fail over to the other one. Hmm. Say we unplug nodes 1-4 from switch 2 and unplug nodes 5-8 from switch 1 -- both switches are connected together directly, and thanks to VLAN trunking, all the nodes can still reach each other. Darn. Say we also unplug that link. Well, both switches are plugged into the rest of the network, right? Assuming you also set up VLAN trunking there, the switches will be able to reach each other through a third switch. Hmm. What if we unplug those, too?
Congratulations! Split-brain! Nodes 1-4 proceed on Array 1 and Switch 1, and nodes 5-8 proceed on Array 2 and Switch 2. You only had to unplug 22 different cables -- eight Ethernet cables and eight FC cables from the nodes to the switches, two inter-switch links (one Ethernet and one FC), two disk array uplinks (one from each array to each switch), and two Ethernet uplinks (one from each Ethernet switch) to the rest of the network -- simultaneously.
As I said, no single point of failure.
Re:MySQL Cluster != master/slave (Score:2, Informative)
In my current company, our network group tends to keep things to themselves and we'd never have known we didn't have redundant fiber if someone didn't shoot out our cable that ran through a bad neigborhood and it became a topic of conversation (yes, with a gun).
I also worked at another company that had redundant fiber lines that were both taken out by a single backhoe, apparently they were bundled together.