a8cjdbc - Database Clustering via JDBC
Monday, November 12, 2007 at 11:05PM Practically any software project nowadays could not survive without a database (DBMS) backend storing all the business data that is vital to you and/or your customers. When projects grow larger, the amount of data usually grows larger exponentially. So you start moving the DBMS to a separate server to gain more speed and capacity. Which is all good and healthy but you do not gain any extra safety for this business data. You might be backing up your database once a day so in case the database server crashes you don't lose EVERYTHING, but how much can you really afford to lose?
Well clearly this depends on what kind of data you are storing. In our case the users of our solutions use our software products to do their everyday (all day) work. They have "everything" they need for their business stored in the database we are providing. So is 24 hours of data loss acceptable? No, not really. One hour? Maybe. But what we really want is a second database running with the EXACT same data. We mostly use PostgreSQL which does not have built in database replication. There is some solution based on triggers to replicate the data from one database to another one. We have learned that setting all this up on an existing database with plenty of tables is rather complicated and changing the database structure afterwards can not be done with simple create/alter statements anymore. And since we ARE running solutions that constantly change and improve, we need to be able to deploy updates including database structure changes quickly and easily.
So what we really wanted was a transparent JDBC layer that does the replication for us. We tested a great solution called "Sequoia", but it is also a rather heavy-weight product with a lot of features that did not really help in the performance department and that we didn't need anyway. What we needed was:
- a JDBC driver so the application does not know anything about the replication
- of course: transactional safety for write operations
- load-balanced reads (we are running 2 database servers, so why waste the ability to do parallel reads from 2 servers and almost multiply the performance by 2?)
- for backups: the ability to detach one server, do the backup on that machine and then reattach the server
- automatic and transparent failover / failsafe
- Fast In-VM-Replication - no serialisation
- Easy integration
Reader Comments (5)
As long as you don't need CLOB, BLOB or sequence support, you're golden. It'll be interesting how they over come these limitations.
- Clobs and Blobs are supported in the next version (beta successfully tested)... (in the next view days available)
- Sequences make more problems, but there is an internal alpha version with sequences (performance test outstanding, etc...)
I like a lot of the articles I read here but this is just a direct copy paste from the linked article. Not even a quotation. We can do better.
You might be interested in lbpool (though it was designed for MySQL)
http://code.tailrank.com/lbpool
It's not a replication solution but it handles MySQL replication faults, balancing, and crashed servers.
We've used it in production for Spinn3r for about 1.5 years now and it's really solid.
New: There is also a trial version available for testing.