High Availability Solutions for Databases?

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?"

Comments

MySQL Cluster != master/slave

[cravey]

While MySQL supports master/slave replication, MySQL Cluster specifically avoids that entire model. It's an entirely synchronous database storage engine. If you want master/slave, use postgres. If you want high availability and can handle the lack of a small number of features, MySQL Cluster is the way to go. The only real downside to the architecture required for CLuster is that all of the data is stored in RAM based tables. transactions are logged to disk every (configurable) time interval. If you're going to try for HA, you might want to RTFM on the available options before you settle on one.

Re:MySQL Cluster != master/slave

[Joff_NZ]

The other thing to note with MySQL Cluster, is that, even in 'stable' releases of MySQL, is horribly unstable, and prone to massive data loss..

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.

Re:MySQL Cluster != master/slave

[jorela]

Actually we have implemented disk storage.
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

[delta407]

Name a DB from a 'big guy' that doesn't require a shared resource. Oracle? Nope. RAC requires a shared storage solution. Lose your single storage device device and you lose access to your db.
...
The only time I will EVER have to take my cluster down is to add more storage space or to replace my switch.

Sorry, what? You're bashing Oracle for having a system with a single point of failure, then add two of your own?

"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.

MySQL cluster and replication are not the same

[Scott]

The submitter of this question seems to have confused the two, Cluster and the older replication. Cluster does not in any way rely on a master/slave setup. Think of Cluster as RAID for databases, where you can lose a node (or more, depending on your configuration) before you lose your db. The current drawbacks of cluster are that it is in-memory and doesn't support certain features, such as fulltext indexing. Replication isn't going to cause you to lose data either if your application is designed to handle a situation where the master server (which you kick your writes to) hits the bricks. Have the app go into a read only mode from your slave.

Neither option is really "beautiful", though Cluster has a lot of promise for the future, especially in 5.1.