4 ms·

Comparing MySQL and Postgres 9.0 Replication

65 points by rgeorge28 16y ago

sph 16y 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 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.
- wiredfool 16y 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.
  - sph 16y 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.
    - wiredfool 16y ago
      As the article points out, the data gets written to the WAL on the slave before the transaction is committed on the master Then the article is wrong, it doesn't. I can reboot or kill the slave and the master will still happily accept transactions. It's also pretty clear from tcpdump that the data only goes on commit, and it's one way from the master->slave.
dstorrs 16y 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?
- wiredfool 16y 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.
- bmurphy 16y 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.

65 points by rgeorge28 16y ago

sph 16y 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 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.

wiredfool 16y 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.
- sph 16y 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.
  - wiredfool 16y ago
    As the article points out, the data gets written to the WAL on the slave before the transaction is committed on the master Then the article is wrong, it doesn't. I can reboot or kill the slave and the master will still happily accept transactions. It's also pretty clear from tcpdump that the data only goes on commit, and it's one way from the master->slave.

dstorrs 16y 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?

wiredfool 16y 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.
bmurphy 16y 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.