4 ms·

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 i

by spudlyo 16y 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.

moe 16y 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 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?
- spudlyo 16y ago
  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.
  - moe 16y ago
    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.
    - spudlyo 16y ago
      You win. Your sweeping generalizations, personal anecdotes, and FUD have won me over. Bravo.
      - 16y ago
morgo 16y 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.

by spudlyo 16y ago

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

spudlyo 16y ago
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.
- moe 16y ago
  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.
  - spudlyo 16y ago
    You win. Your sweeping generalizations, personal anecdotes, and FUD have won me over. Bravo.
    - 16y ago

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