Comparing MySQL and Postgres 9.0 Replication

http://www.theserverside.com/feature/Comparing-MySQL-and-Postgres-90-Replication

by rgeorge28 • 16 years ago

65 20 16 years ago

sph2• 16 years ago

This is a wonderful in-depth article and the PostgreSQL team deserves a lot of kudos for the work they put into this. I'm looking forward to using it. The thing I find interesting that MySQL offers is multi-master and circular replication. Cal Henderson noted that Flickr uses multiple masters replicating to slave servers in their setup during DjangoCon 2008 (a great presentation if you haven't seen it: http://www.youtube.com/watch?v=i6Fr65PFqfk).

Granted, multi-master setups aren't needed for most sites and PostgreSQL's new WAL replication and the fact that it makes sure that the WAL is written on the slave before the transaction is committed on the master means that data integrity should be top notch. It's exciting to see PostgreSQL's development progressing so well.

wiredfool5• 16 years ago

  it makes sure that the WAL is written on the slave
  before the transaction is committed on the master 
  means that data integrity should be top notch.

That's not my reading of the documentation. The slaves may delay applying a transaction for some configurable period of time to allow conflicting read-only queries to finish. Slaves can also disconnect and reconnect at will without stopping the master.

sph2• 16 years ago

The slaves may delay applying the transaction, but the data is already on the slave machine. As the article points out, the data gets written to the WAL on the slave before the transaction is committed on the master. The slave might not update the database from the WAL immediately, but the data is on the slave in case the master goes down.

Basically, it means that there is the potential for replication lag and so you have to make sure you query from the master for just-updated data. However, it also means that if the master goes down, any data committed on the master is already on the slave in the WAL, just possibly not applied yet. So, it doesn't isolate you from replication lag, but it is a nice thing when it comes to making sure that the data remains intact even if a complete failure of the master occurs.

dstorrs• 16 years ago

My takeaways from this are:

1] MySQL supports more replication options, whereas PostgreSQL focuses on the ones that 95% of users will care about and does not implement the rest.

2] MySQL's replication has several failure modes that can lead to data loss or non-synchronization. For example, it is statement based by default, so any statement which includes a call to NOW() will return different results on master and slave. PostgreSQL also has failure modes, but the defaults are designed to minimize them -- e.g. it always uses WAL and data file rewrites to ensure that data is properly synced and consistent.

3] Due to points 1 & 2, if you aren't an expert and don't need the extra features offered by MySQL, you are probably better off with PostgreSQL.

4] 3rd party tools exist for both servers which close many of the feature gaps between them.

Could someone who is more knowledgeable than me tell me if this is a fair reading?

wiredfool5• 16 years ago

Pretty reasonable, from someone who has been following postgres for 10 years. I'm not sure I'd say that postgres covers 95% of use cases, as it really only covers single master. That is probably the most common case, but I'd have a hard time putting numbers on it. Extra features cut both ways, some apps are better with postgres, some with MySql.

Postgres was 'late' to the replication party, and only in this last release have they had hot slave machines. (with core functionality, 3rd party has a range of alternatives). Currently, it is single master, multi slave replication. Previously, it was been a warm spare arrangement, up to 1 WAL log behind, for durability, and not for load balancing. There were ways of making it less laggy, but it was not trivial to restore a log, bring the db up, and then be in a position to restore another log.

bmurphy3• 16 years ago

Close. Postgres has traditionally focused on letting the community provide replication solutions, but we've been screaming so long for one that "just works" that they're finally getting around to building it.

I wouldn't say it covers 95% of the use cases. In fact, I find it very limiting, but if all you need is to make sure your database is backed up it's a great step in the right direction.

That being said, the topology limitations suck. If you lose your master server, you are running in a degraded environment until you can rebuild ALL slaves from scratch. This really sucks if you have a very large database that takes a long time to rebuild.

Postgres 9.0 replication is a great first step, but it's only a step. There's still a lot of work to be done.

wiredfool5• 16 years ago

  If you lose your master server, you are running in a
  degraded environment until you can rebuild ALL slaves from
  scratch. This really sucks if you have a very large
  database that takes a long time to rebuild.

I just tested this, and it's not quite true.

Assume you have 1 master, and 2 slaves(1=new master/2=additional slave) each replicating directly from the master. If they're in sync, then if you:

  * shutdown the master
  * shutdown slave1/new master
  * remove slave1's recovery.conf file
  * assign slave1 old master's ip address
  * start slave1 as new master

then slave2 will follow and you'll still have a replicated set. If, instead of doing the shutdown/remove recovery.conf/startup dance, you touch the trigger file, a new timeline is created and the slave2 isn't going to follow that timeline. I haven't found a way to make a slave follow to a new timeline yet.

spudlyo4• 16 years ago

1. I don't pretend to know what 95% of users will care about.

2. MySQL writes the timestamp for NOW() into the binlog so provided your master and slave are in the same timezone, it's replication safe. Functions like UUID() and RAND() will behave as you describe.

3. I disagree. MySQL's replication is more mature, widely understood, and has more documentation than PostgreSQL's.

moe2• 16 years ago

I disagree strongly with "more mature", "widely understood" and "more documentation".

1. Mature?

I have seen MySQL replication blow up in so many awful ways, it's not funny. And yes, I've seen it happen on 5.* deployments. The worst part is that MySQL hardly ever detects a problem, and you hardly ever know why it went wrong - it just silently corrupts. The only way to detect the corruption is through regular checksum runs which can be costly on large databases (there's a reason why the checksum-utility from maatkit has fairly sophisticated options for incremental checksumming).

2. Widely understood?

I challenge you to prove your understanding and explain only a small subset of the functionality: Please enumerate all failure modes that can lead to corruption in statement based replication, and how to avoid them.

3. More documentation?

That has to be a joke. The MySQL documentation is a mess. It is poorly structured, poorly written and full of conflicting and misleading bits. Please show me the MySQL document about replication that comes remotely close to http://www.postgresql.org/docs/9.0/static/hot-standby.html in terms of clarity and exhaustiveness. Notice the long section about "query conflicts" - where is the MySQL equivalent?

morgo• 16 years ago

Almost. RAND() writes the rand seed as meta data to the binary log. It's safe (as is NOW() - like you pointed out). UUID() is not safe though.

But examples like this are so special case - it doesn't show the real danger. A DELETE with a LIMIT clause and no ORDER BY can semantically delete different rows on the slave than on the master.

You never, EVER want to use circular replication (A->B->C->A). Surrounding a crash it's such a pain to re-piece together the data.

Comparing MySQL and Postgres 9.0 Replication

Related Stories