News for hackers. Stuff that matters.
Re:Comparing MySQL and Postgres 9.0 Replication by spudlyo on Thursday November 18, 2010 @04:07PM (#1919254)
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.

Re:Comparing MySQL and Postgres 9.0 Replication by moe on Thursday November 18, 2010 @05:56PM (#1919650)
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?

Re:Comparing MySQL and Postgres 9.0 Replication by spudlyo on Thursday November 18, 2010 @06:30PM (#1919763)
1. This is the first release of baked in replication for PostgreSQL. MySQL has had years to evolve their approach and to fix bugs. That's not to say either system is perfect, but their relative maturity seems obvious.

2. No. It's widely understood because MySQL has a much larger mindshare than PostgreSQL. Statement based replication has its faults, but complexity isn't one of them.

3. It's not a joke. There is more documentation. There is the official documentation, but if you don't like that there are plenty of blog posts, books, and recorded talks on the subject.

Re:Comparing MySQL and Postgres 9.0 Replication by moe on Thursday November 18, 2010 @07:43PM (#1919962)
MySQL has had years to evolve their approach and to fix bugs.

That sounds nice in theory, but completely ignores the reality of the respective code-bases. Features in MySQL have a tendency to rot rather than mature. It could hardly become more obvious than in the comparison between this version 1 of postgres replication against the "evolved" MySQL equivalent.

Postgres may have the smaller feature set, it may be the first version, yet I'm still inclined to trust it a lot more than what MySQL has to offer. Simply because postgres has a track-record of being rock solid and MySQL, well, not so much.

Sure, in theory you can build fancy rings, cascades and even multi-master topologies in MySQL. But what is that worth when even the simplest master/slave mode still randomly and silently desyncs in various, undocumented situations?

Statement based replication has its faults, but complexity isn't one of them.

There's a nice quote from Alan Perlis:

    Fools ignore complexity,
    experts avoid it;
    geniuses remove it.
Some complexity is inherent to replication. The question remains which parts, if any, they removed and which parts they ignored...

It's not a joke. There is more documentation.

Sorry, measuring documentation in terms of quantity sounds like yet another joke to me. As you can tell by my snarky comments I'm responsible for babysitting the odd MySQL cluster in my day-job (and postgres, too, so I have a frame of reference). It is indeed true that for most common problems I'll find the solution not in the official docs but rather on some more or less obscure forum, in broken english, linked from page >3 of the google search results (after 2 pages of outdated and plain wrong information).

So, in all fairness, yes, most common problems can eventually be solved that way. But good luck debugging a not so common ring, multi-master or otherwise non-standard scenario that way. Or encoding issues. Or silent truncation. Or hotbackups that are strangely much smaller than they should be. Or the dreaded 'mysql'-database being screwed after restoring a backup to a newer minor-version...

Anyways, the way postgres handles this seems more sensible to me. In postgres the overwhelming majority of the "common" MySQL-issues simply doesn't exist.

Re:Comparing MySQL and Postgres 9.0 Replication by spudlyo on Thursday November 18, 2010 @08:20PM (#1920030)
You win. Your sweeping generalizations, personal anecdotes, and FUD have won me over. Bravo.
Re:Comparing MySQL and Postgres 9.0 Replication by wanderr on Friday November 19, 2010 @12:42AM (#1920535)
Do you have any experience with MySQL replication? I do, and mine aligns with what moe is describing. Nice in theory isn't worth much if it's crappy in reality.
Re:Comparing MySQL and Postgres 9.0 Replication by spudlyo on Friday November 19, 2010 @01:37AM (#1920659)
Yes. Over the last five years I've supported replication on hundreds of servers, powering sites you've probably used. I've had to clean up after crashes with unsynched binlogs, writes that completed partially on master, corrupted relay logs, failed hubs, and endless inconsistencies for a myriad of reasons.

Guess what, things have gotten incrementally better and continue to improve. Bugs get found and fixed. MySQL replication is mature enough to power huge sites like Twitter, Facebook, and countless others. There has been exactly one PostgreSQL release that supports replication suitable for read scaling.

Re:Comparing MySQL and Postgres 9.0 Replication by wanderr on Friday November 19, 2010 @02:11PM (#1922384)
I measure stability by how well something works, not by how many releases it has had. MySQL replication is getting better, but it does still have major problems. Sure, it's good enough to power huge sites (we use it here as well), but it's frustrating to deal with. Fortunately it's being used by web 2.0 companies and not banks, so generally if a slave is corrupted and returns weird results, customers aren't going to be too upset and in a lot of cases might not even notice, but it's still an annoying problem to deal with. I wonder how many more releases it's going to take for them to get it solid.
Re:Comparing MySQL and Postgres 9.0 Replication by morgo on Thursday November 18, 2010 @08:57PM (#1920108)
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.