I've got a fairly good sense of what MySQL replication can perform. I am wondering the other databases support replication, and just how they rival MySQL yet others?
Some questions I'd have are:
- Is replication built-in, or perhaps an add-on/wordpress plugin?
- So how exactly does the replication work (high-level)? MySQL provides statement-based replication (and row-based replication in five.1). I am thinking about how other databases compare. What will get shipped within the wire? How can changes get put on the replicas?
- Could it be simple to check consistency between master and slaves?
- How easy could it be to obtain a unsuccessful replica in sync using the master?
- Performance? One factor I personally don't like about MySQL replication is the fact that it's single-threaded, and replicas frequently have trouble maintaining, because the master could be running many updates in parallel, however the replicas need to run them serially. What are the gotchas such as this in other databases?
- Every other interesting features...
MySQL's replication is weak inasmuch as one should sacrifice other functionality to obtain full master/master support (because of the restriction on supported backends).
PostgreSQL's replication is weak inasmuch as only master/standby is supported built-in (using log shipping) more effective solutions (for example Slony or Londiste) require add-on functionality. Archive log segments are shipped within the wire, what are same records accustomed to make certain that the stand alone database is within working, consistent condition on unclean startup. This is exactly what I am using presently, and that we have resynchronization (and setup, along with other functionality) fully automated. None of those approaches are fully synchronous. More complete support is going to be built-in by PostgreSQL 8.5. Log shipping doesn't allow databases to leave synchronization, so there's no requirement for ways to test the synchronized status getting the 2 databases back to sync involves setting the backup flag around the master, rsyncing towards the slave (using the database still runnning this really is safe), and unsetting the backup flag (and restarting the slave process) using the archive logs produced throughout the backup process available my shop has this method (as with other administration tasks) automated. Performance is really a nonissue, because the master needs to replay the log segments internally anyhow additionally to doing other work thus, the slaves will be under less load compared to master.
Oracle's RAC (which is not correctly replication, as there's just one storage after sales -- however, you have multiple frontends discussing the burden, and may build redundancy into that shared storage after sales itself, therefore it is worth mention here) is really a multi-master approach much more comprehensive than other solutions, but is very costly. Database contents aren't "shipped within the wire" rather, they are saved towards the shared after sales, which all of the systems involved can access. Because there's just one after sales, the systems cannot emerge from sync.
Continuent provides a third-party solution which does fully synchronous statement-level replication with support for those three of the aforementioned databases however, the in a commercial sense supported version of the product is not particularly cheap (though greatly less costly. Before I given it, Continuent's solution needed manual intervention for getting a cluster back to sync.