OpenSolaris - Entries from July 2007

So, the state of open source database replication is pretty sad. MySQL replication just doesn’t cut it in many serious environments because the slaves can’t keep up with the write load on the master. So, PostgreSQL right? Well, not so fast. PostgreSQL replication is handled in one of two ways: Slony or PITR (point-in-time recovery).

Slony provides all the same features as MySQL’s replication (except that it is much harder to setup and maintain), but also boasts the same acute performance issues — a busy master can easily outpace slaves, leaving them in the dust. Query-log-based replication is pretty flawed and while creative people will do whack shit to try to push the envelope this still doesn’t make it a good method.

PITR is much more like Oracle’s replication mechanism. PITR takes the WAL (write-ahead logs) and ships them over to the slave to be reapplied. This leaves you with an identical database (block for block) and a weak machine can easily keep up with a beefy master. In Oracle terminology “WALs” are called “archive logs.”

So, with PITR, all our problems are solved, right? No. When using a PITR-style warm standby configuration the database is in “recovery mode” all the time. This means the database is “sorta up” waiting for the next WAL log to appear so that it can play forward through the transactions and “catch up” to the master: “continuous recovery.” This means the database isn’t available for queries. Now, Oracle works the same way. While Oracle is recovering, you can’t use the database. However, using Oracle you can cancel recover, mount the database read-only, do some queries, unmount and begin recovery again picking right up where you left off. In PostgreSQL you cannot open the database in read-only mode and then later continue recovery as the act of opening the database (even in read-only mode) will deviate from the path of the recovery — can we say design flaw?

While Oracle’s “got game” on PostgreSQL, the concept of stopping recovery so that we can run queries on the slave isn’t ideal. If my queries are substantial my “warm standby” will get colder and colder as it sits neglecting to apply archive logs. So, I want my warm standby and I want to be able to run long, heavy queries against it. And someone’s going to give it to me!

However, I’m impatient. So, I’m going to make it work myself. Using the power of ZFS, I’m going to snap my PITR slave and clone it into a “disposable” “point-in-time” copy. This is really useful for running heavy reports.