5 ms·

Advanced replication strategies in MySQL and Oracle mostly cause hair loss and divorce. Everywhere I have worked that uses these databases eventually convinces

Advanced replication strategies in MySQL and Oracle mostly cause hair loss and divorce. Everywhere I have worked that uses these databases eventually convinces itself that it needs futuristic replication technology and then goes on to suffer from outages and weird corruption. It turns out, distributed consistency is a hard problem. Oracle and MySQL both benefit monetarily from people assuming it will work and then activating it, only to find out a month or two later that it's a complicated problem that requires actual planning and oversight. I think Postgres will be a better product when it incorporates similar functionality, but the implementations in MySQL and Oracle lie somewhere on the border between false advertising and reckless endangerment. I appreciate that Postgres not having it as two-lines of configuration sends a message that you are making a commitment. Nearly everything else in Postgres is set-it-and-forget-it. You really want to have a DBA if you're going to try to make replication work. When you read the docs on having a hot standby and how much of a commitment that is and sense yourself getting tired, you're starting to appreciate what you might be getting yourself into with single-master replication, let alone multi-master.

kaamos 9y ago
> Nearly everything else in Postgres is set-it-and-forget-it. There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.
- fusiongyro 9y ago
  I read their article, and a dozen of the rebuttals. If you want to use MySQL badly enough, you can.
- glogla 9y ago
  Uber switched from MySQL used as relational database, to Postgres used as relational database, to MySQL used as key-value store. Nobody is saying MySQL is a bad key-value store!
  - pmarreck 9y ago
    Is key-value store the most stable scalable architecture? Reddit, I believe, uses the same idea
    - softawre 9y ago
      If you give up ACID (the C) you can scale more (the P), sure. https://en.wikipedia.org/wiki/CAP_theorem https://en.wikipedia.org/wiki/CAP_theorem
    - fusiongyro 9y ago
      To restate your other reply, key-value allows you to sweep consistency problems under the rug. What happens if two transactions try to update the same row? One of the transactions fails. What happens if two clients write different values to the same key in a multi-master key-value store? The last one in wins and the first write is just lost. Many of the NoSQL databases have provisions for these situations so they can handle both writes but bubble the decision up to the application layer. CouchDB, for instance, will tell you you've got multiple versions and has a way for you to tell it how to resolve the conflict. CRDTs let you bake the logic in for handling a conflict. MySQL does not do any of that. If your database consists of "likes" and other low-value data like that, you may not care if you're losing data at some low rate. Reddit is in that situation, and the decision seems to work for them. But I don't think it generalizes to all situations.
- merb 9y ago
  > There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth. not a good idea, somebody who let their database master run out of storage space is probably not a good source.
- user5994461 9y ago
  Uber is a bad reference for good practices.
- jeltz 9y ago
  The Uber blog post got a lot of things wrong and if that is representative of their knowledge of PostgreSQL I would not suggest using them as an authority on PostgreSQL or databases in general.
  - t8ge55geu8ygt 9y ago
    What did they get wrong?
barrkel 9y ago
This has not been my experience of multi-master asynchronous replication in MySQL at work. We use only one replica for actual work and the second master is read-only until failover - and is only write enabled after the primary is disabled - and it works quite well. Getting replication bootstrapped synergises well with having a smooth backup restore procedure, similarly any hiccups in replication can be dealt with using restore. We have other release QA procedures that use restored backups for testing, so our restore procedure is extremely well exercised. Really fast restores using the Percona tools is a considerable bonus of the MySQL world.
- kelnos 9y ago
  > This has not been my experience of multi-master asynchronous replication in MySQL at work. We use only one replica for actual work and the second master is read-only until failover That's not multi-master. That's single master with a hot standby. > any hiccups in replication can be dealt with using restore. Restore isn't an option when your database is so large that it takes most of a day to copy the backup onto the replacement master to restore from it. (Not speaking hypothetically here.) Obviously restoring from a backup is still there as a last resort in this case, but you really don't want to have to go there.
  - pas 9y ago
    Galera builds on that, and it works very well. It has weak points, but if you build a working Galera cluster and test it (test the failovers, test [re]bootstrapping a node), you can then be confident that it'll continue to work as intended for a long time. There are rough edges. Upgrades, recovery from corrupted hot backup, etc. But it's very easy to rebuild the cluster in flight, for example when changing some fundamental cluster property, or after a PEBKAC, (with a not too big read-only window), though it requires plus one node, a regular MySQL/MariaDB server that acts as a donor (where Galera can bootstrap from), but that's not too surprising if you want to at least serve read-only requests.
    - seanp2k2 9y ago
      You also can't use Galera on RDS, which is a deal breaker for some: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MariaDB.html http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_M...
CaptainZapp 9y ago
Interesting side note: A significant number of Oracle shops use Sybase Replication Server to replicate from an Oracle - to an Oracle database. Go figure...
will_be_no_ai 9y ago
RE: Oracle Replication - Have you looked at DbVisit? Compared to the PhD you need for GoldenGate, you can get by with a high school diploma with this stuff. http://www.dbvisit.com/ http://www.dbvisit.com/

kaamos 9y ago

> Nearly everything else in Postgres is set-it-and-forget-it. There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth.

fusiongyro 9y ago
I read their article, and a dozen of the rebuttals. If you want to use MySQL badly enough, you can.
glogla 9y ago
Uber switched from MySQL used as relational database, to Postgres used as relational database, to MySQL used as key-value store. Nobody is saying MySQL is a bad key-value store!
- pmarreck 9y ago
  Is key-value store the most stable scalable architecture? Reddit, I believe, uses the same idea
  - softawre 9y ago
    If you give up ACID (the C) you can scale more (the P), sure. https://en.wikipedia.org/wiki/CAP_theorem https://en.wikipedia.org/wiki/CAP_theorem
  - fusiongyro 9y ago
    To restate your other reply, key-value allows you to sweep consistency problems under the rug. What happens if two transactions try to update the same row? One of the transactions fails. What happens if two clients write different values to the same key in a multi-master key-value store? The last one in wins and the first write is just lost. Many of the NoSQL databases have provisions for these situations so they can handle both writes but bubble the decision up to the application layer. CouchDB, for instance, will tell you you've got multiple versions and has a way for you to tell it how to resolve the conflict. CRDTs let you bake the logic in for handling a conflict. MySQL does not do any of that. If your database consists of "likes" and other low-value data like that, you may not care if you're losing data at some low rate. Reddit is in that situation, and the decision seems to work for them. But I don't think it generalizes to all situations.
merb 9y ago
> There are many people who would disagree with that. Talking to Uber engineers would probably be a good idea to dispel that myth. not a good idea, somebody who let their database master run out of storage space is probably not a good source.
user5994461 9y ago
Uber is a bad reference for good practices.
jeltz 9y ago
The Uber blog post got a lot of things wrong and if that is representative of their knowledge of PostgreSQL I would not suggest using them as an authority on PostgreSQL or databases in general.
- t8ge55geu8ygt 9y ago
  What did they get wrong?

barrkel 9y ago

This has not been my experience of multi-master asynchronous replication in MySQL at work. We use only one replica for actual work and the second master is read-only until failover - and is only write enabled after the primary is disabled - and it works quite well. Getting replication bootstrapped synergises well with having a smooth backup restore procedure, similarly any hiccups in replication can be dealt with using restore. We have other release QA procedures that use restored backups for testing, so our restore procedure is extremely well exercised. Really fast restores using the Percona tools is a considerable bonus of the MySQL world.

kelnos 9y ago
> This has not been my experience of multi-master asynchronous replication in MySQL at work. We use only one replica for actual work and the second master is read-only until failover That's not multi-master. That's single master with a hot standby. > any hiccups in replication can be dealt with using restore. Restore isn't an option when your database is so large that it takes most of a day to copy the backup onto the replacement master to restore from it. (Not speaking hypothetically here.) Obviously restoring from a backup is still there as a last resort in this case, but you really don't want to have to go there.
- pas 9y ago
  Galera builds on that, and it works very well. It has weak points, but if you build a working Galera cluster and test it (test the failovers, test [re]bootstrapping a node), you can then be confident that it'll continue to work as intended for a long time. There are rough edges. Upgrades, recovery from corrupted hot backup, etc. But it's very easy to rebuild the cluster in flight, for example when changing some fundamental cluster property, or after a PEBKAC, (with a not too big read-only window), though it requires plus one node, a regular MySQL/MariaDB server that acts as a donor (where Galera can bootstrap from), but that's not too surprising if you want to at least serve read-only requests.
  - seanp2k2 9y ago
    You also can't use Galera on RDS, which is a deal breaker for some: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_MariaDB.html http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_M...

CaptainZapp 9y ago

Interesting side note: A significant number of Oracle shops use Sybase Replication Server to replicate from an Oracle - to an Oracle database. Go figure...

will_be_no_ai 9y ago

RE: Oracle Replication - Have you looked at DbVisit? Compared to the PhD you need for GoldenGate, you can get by with a high school diploma with this stuff. http://www.dbvisit.com/ http://www.dbvisit.com/