Open Source Database Clusters?

grugruto asks: "A lot of open source solutions are available to scale web sites with clusters but what about databases? I can't afford an Oracle RAC license but can I have something more reliable and fault tolerant than my single Postgres box? I have seen this recent article that looks promising for open source solutions. Do anyone have experiences with clusters of MySQL , Postgres-R, C-JDBC or other solutions? How does it compare to commercial products?"

Comments

transactionality is hard

[solarisguy]

For what it's worth, the commercial solutions are hard to setup, unstable and terribly difficult to maintain, and this is after a small fortune has been invested in making them work. Not to knock the open source solution, but it's hard to beleive that something that is infrequently used and difficult to understand will be truly production quality if you want to use it for money.

Check out Emic Networks

[venom600]

We've been evaluating the Emic application cluster for MySQL and have had pretty good results. It's a new product (so YMMV), but it looks promising.
Emic Networks

Not personally, but

[revividus]

I've been looking into MySQL for a bit, and I saw this article recently, which is directly concerning clustered database servers running MySQL.

Maybe it will be of interest...

The big problem is replication

[MarkusQ]

IMHO, the biggest problem is replication; keeping them all consistent in the face of asyncronous updates. It can also reduce/eliminate the advantages of clustering if you have a significant number of updates compared to the number of quieries.

I guess the best answer depends on how dynamic your data is. If it's static, there are all sorts of easy answers. If all the updates come from a central source, or on a predictable schedule, you're almost as well off. If updates come from the great unwashed but the data can be partitioned in some way (say, geographically) you can still do it. If updates come from all over but queries can be centralized, or if your database is tiny, or if latency isn't a problem, or if you have a machine that prints money, it can still be done.

If you want to do everything for everyone everywhere, right now if not sooner, for under twenty bucks, you're screwed.

So, what are your needs?

-- MarkusQ

Re:-1:Troll

[venom600]

ACID enforcement isn't there
Actually ACID compliance is getting pretty darn good in databases like MySQL. Care to elaborate about what ACID compliance issues you have?

Don't be an OSS idealogue in the business world, you end up unemployed.
Actually, in our flailing economy 'OSS idealogues' as you call them are making a lot of head-way. OSS now has a viable alternative to *just about* any commercial enterprise software out there.

High Availability

[mcdrewski42]

HA is always crapshoot/tradeoff between cost and risk. Throw enough $ at the problem and you'll approach 100% availability.

I know that 'more robust' is a nice thing to want, but you really need to think about what you really need. If it takes 15 minutes to switch over to a backup copy (using some magic RAID disk mirroring maybe?) and 15 minutes to restart the app and let it checkpoint it's way up to a decent operational speed again, is that good enough?

If it takes an hour, how about that?

How much time/heartache or money is it worth for you to have system downtime, and how much are you willing to expend to reduce it by 5, 15, 30 minutes?

So, there's really a continuum of availabilty you have to pick your point in. At the low end, you have no backups and recreate everything from scratch. At the high end you use Vendor X's real clustering solution and 24x7 monitoring, then have zero downtime even in a disaster. Somewhere in the middle is you.

Now I realise this an overtly commercial view of things, but if needs be replace money with effort and season to taste.

eRserver

[linuxwrangler]

I have found PostgreSQL to be nearly bullet-proof. I routinely have connections up for months at a time (that's individual persistent connections - the server is up much longer and the connections usually get dropped when I upgrade the client software). Still, sh*t happens and replication has been a sore point for many databases both open and commercial.

You should investigate eRserver. It was originally a commercial replication product for Postgres but has been open-sourced. I haven't tried it yet but it's on my to-do list.

Re:eRserver

[TheFuzzy]

Well, the .ORG domain runs on PostgreSQL + eRServer, so that's one scalable solution ...

Shared storage?

[crstophr]

You can make a High Availability cluster out of most any software if you have some kind of shared storage.

People have used firewire drives connected to two different computers to accomplish this cheaply. Oracle is giving away a cluster filesystem (so they can sell RAC on linux) there is OpenGFS as well for filesystem usage.

Just write some basic monitoring scripts that will bring up your postgress database on the second server should the first one fail. Just make sure those scripts completely take down the old database on the first server in the case of a partial failure. Having two databases try to open the same data would be a really bad thing.

Here are some links to articles that should help:

Overview

Howto

Cluster Filesystem

These are mainly geared for Oracle/RAC, all you need is the firewire shared storage and cluster filesystem. You're on your own to write the monitoring and failover scripts. Hope this helps. --Chris

Emic, InnoDB Hot Backup

[vinsci]

Two MySQL products I found interesting (neither of which is open source at this time):

• CLUSTERING IN TUNE WITH APACHE AND MYSQL (Free registration might be required. Also see Emic Application Cluster for MySQL
• InnoDB Hot Backup (with point in time backup)

The rest of this comment is quoted verbatim from InnoDB News

MySQL/InnoDB-4.0.1 and Oracle 9i win the database server benchmark of PC Magazine and eWEEK. February 27, 2002 - In the benchmark eWEEK measured the performance of an e-commerce application on leading commercial databases IBM DB2, Oracle, MS SQL Server, Sybase ASE, and MySQL/InnoDB. The application server in the test was BEA WebLogic. The operating system was Windows 2000 Advanced Server running on a 4-way Hewlett-Packard Xeon server with 2 GB RAM and 24 Ultra3 SCSI hard drives.

eWEEK writes: "Of the five databases we tested, only Oracle9i and MySQL were able to run our Nile application as originally written for 8 hours without problems."

The whole story. The throughput chart.

What is slashdot doing?

[rtnz]

What does Slashdot do for this? I recall way back in the day there was some information about what the Slashdot tech looks like, anyone have info regarding their database setup? L

MySQL replication: Flawless (so far)

[allankim]

I've been running a 3-4 node MySQL 3.23.x cluster on Slowlaris 9 since January. It has survived several catastrophic power outages and numerous other insults without a hiccup. Load is fairly light (about 3,000 updates daily and a similar number of queries on each server) so YMMV.

Re:MySQL Replication

[Hamstaus]

Rings? Are you implying *all* servers involved in the replication process could handle writes rather than a master that handles writes and a bunch of slaves that handle all the read access? If this is true, point me to some docs :) That would be too cool.

Here you go.

The part you are probably interested in is this:

You should run your slaves with the --log-bin option and without --log-slave-updates. This way the slave will be ready to become a master as soon as you issue STOP SLAVE; RESET MASTER, and CHANGE MASTER TO on the other slaves.

Note that if you decide to "ring" your server setups, then you are not necessarily helping distribute the load, you are simply creating redundant masters in the case that your primary machine becomes unavailable. Also, you'll have to write your own monitoring scripts. MySQL says they are working on some tools for this... I'm excited to see what they come up with.