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 - Master / Slave is the only option (Score:4, Interesting)
We chose to go with a Master / Slave option which basically gave us failover within 3 seconds. Any more fine grained monitoring and the CPU performance on the slave gets pretty high. Not ideal, but probably the best option that uses MySQL when you don't want to be tied so much to one platform.
Re:MySQL Cluster != master/slave (Score:5, Interesting)
Unfortunately, open source hasn't caught up to the big guys yet in the area of replication.
Re:MySQL Cluster != master/slave (Score:3, Interesting)
Yes, the RAM only tables suck for large DBs. On the other hand, they're REALLY fast and they can be easily scaled up on commodity hardware rather than requiring faster and bigger servers and storage. I'm in medium shop that only requires a few gigs of table space. I'm n+2 on webservers/sql servers and data storage nodes for under $15k. If I wanted to use Redhat on amd64 with gobs of ram/machine, I could probably be n+2 to 64G for under $50k. The only time I will EVER have to take my cluster down is to add more storage space or to replace my switch. That will result in about 10 minutes of downtime. Based on growth, I won't have to do that for another 18 months. If I wanted to spend another $10k, I could avoid having to take down the system in the event of switch failure. I've never had a carrier grade switch failure on any of my networks, so I'm feeling pretty good about availability.
Yes it's expensive to get up into the terabyte range with ram based tables, but there's also no other way to do it without either spending ridiculous amounts of money or being dependent on a single piece of hardware.
Nice "question" (Score:5, Interesting)
Hello, anonymous Sequoia promoter seeking free advertising. (BTW, You might try picking a product name that normal people can spell without thinking about it).
Your solution is not database clustering, and should not be advertised as such. It's more a long the lines of a database connection proxy which supports multiple simultaneous backends and operating on them in parallel, with some added features to make HA-like solutions relatively easy.
The downside of this style of approach, as opposed to an architecture of the likes of Oracle "RAC", is that it doesn't scale up as you add backend nodes (at least not for writes, but in any case for read-only scaling there are simpler solutions for all of the vendors, even the free ones), and it must have limits on how many transactions it can backlog and replay to a temporarily-unreachable/down server before that server has to be re-synced from scratch in order to catch back up (and I have to wonder if there's really any real-world scenario under real transaction load in which the practical net effect wouldn't be a complete resync of a backend server anytime something goes wrong with it, in which case one could throw out any attempt to backlog transactions for a single failed server and just keep things simple - you fall out of sync, you resync).
The open source world really needs a RAC-like solution for PostgreSQL and MySQL (I'm a fan of friendly open-source competition, so while my personal preference is PostgreSQL, I hope both projects stay current and popular for many years to come). Unfortunately there is unlikely to be a generic way to do this, it will probably have to be re-invented for each database project.
I took a brief look around PostgreSQL's guts a while back, and it actually seemed like the architecture they use isn't far off from something RAC-capable to begin with, just nobody's quite buttoned together a few peices here and there to make it happen. Basically on SMP multiple co-operating backends already serve parallel requests and synchronized on a shared memory cache. There's patches out there for the linux kernel to support network-synchronized distributed shared memory. Put two and two together, and what do you get? Something not far off from a first-pass hack at a RAC-like network-distributed database caching system. Most of the other details are easy to solve (start/shutdown, join/leave cluster, tracking of processes across the cluster, etc), or belong in another problem domain (implementing shared storage filesystems (hey, we have GFS, Oracle OCFS, etc available...)). One of the biggest issues would be multiple nodes all having pg "Writer" processes. The first step would probably be to put the writer on one node and failover the writer functionality when that node dies, to be quickly replaced by a scheme whereby multiple writers can work by synchronizing through a distributed lock manager (there are already dlm modules available for linux). Then there's the issue of making the current distributed shared-memory patches do the right thing performance-wise for this kind of usage, and so on. It's not easy, but it's not outside the realm of possibility.
Re:MySQL - Master / Slave is the only option (Score:4, Interesting)
Re:Nice "question" (Score:2, Interesting)
As a SAN has to be shared between all nodes in your cluster, this already limit the availability and scalability of your database to your SAN capabilities (you are shifting the problem to the disk). With a shared nothing architecture you are also replicating the disks and thus distributing the IO workload as well. Note that when you want to synchronize nodes that are not collocated, then a SAN does not work anymore.
You should read some papers about C-JDBC (the original name of the Sequoia technology) and you will find that the technology can scale quite well. We were able to achieve throughput of a million transaction a minute... that should be quite enough for many businesses.
This looks like a religious war between shared-disk and shared-nothing solutions but a SAN and its admin cost is usually not compatible with someone seeking for an open source solution. If you can afford the SAN, why not just using Oracle RAC?
Clustering, Master/Slave, Load- balancing (Score:3, Interesting)
You can acheive this in three basic ways. Each has their own pros and cons. I recommend that you weigh them out and come to a decision you think you can live with.
Clustering - You have a group of servers (physical hardware) each running the same software and working to stay synched up with each other. Now clustering comes in two flavors active/active and active/passive. The active/active clusters share the requests between them. The active/passive clusters wait for a node to fail and then another node in cluster assumes the active role.
Master/Slave - This is similiar to clustering in that you have a group of servers (physical pieces of hardware) each running copies of the software. The master does not service requests directly and operates only as a central repository replicating all the data to the slaves. The slaves synch with the master. If one slave fails, there is normally some means for another slave to take over. Note: While this is old and largely deprecated, it is still quite functional and cost-effective under certain circumstances.
Load-Balancing - There are several physical pieces of hardware who each process transactions all the time. All updates/writes/commits are sent to all the servers, but reads are serviced only by one. Note: Again, this is deprecated, but can still be quite functional in specific circumstances.
My experience has been that typically all that is needed for clustering to work is some shared drive space for the various instances in the cluster to manage their own internal bookkeeping. In all honesty, there are several factors here that would make a big difference in what I would recommend for you. One - how much data are you talking about storing? Two - What are you using it for? If this is driving your web site, a second or two of latency while something fails over won't be noticable to the end user. If you're transmitting medical records to an EMT team, it might be fatal. Three - Why are you looking at HA? What need are you trying to fill?
2 cents,
Queen B.
Links for you to consider -
http://www.linuxlabs.com/clusgres.html [linuxlabs.com]
http://www.openminds.co.uk/high_availability_solu
PS: Yes, I like Postgres. It supports foreign key relationships out the box (ummm...*R*DMBS, anyone?) It also doesn't force me to put in unnecessary indexes to use fk's. I will say that MySQL has managed to address some of my previous complaints about not supporting views, stored procedures or triggers. I haven't had time to test their version 5 for now, so I'll be silent as I have no opinion on their implementation.