3 ms·

How database replication helps me sleep at night

33 points by martian 16y ago

midas 16y ago
Aren't there other ways to do database replication?
- birken 16y ago
  (post author) Yes, there are a lot of third-party replication solutions for Postgres (http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling http://wiki.postgresql.org/wiki/Replication,_Clustering,_and...), which offer various features (some of which offer even more complex and redundent replication options than the built-in replication). However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. And as discussed in the post, I believe that streaming replication is a very nice replication solution, and probably good enough for most applications.
  - jswinghammer 16y ago
    How do you like Postgres? I'm doing a lot of work with MySQL lately and curious about Postgres.
    - birken 16y ago
      I can't say I have a ton of experience with Mysql, but I am a huge fan of Postgres. I think especially for people who just want their database to work, having to worry about which of the storage engines you should be using for MySQL would be a headache. With Postgres, they focused on one storage engine and making it work as broadly as possible, and I think that is the right way to go about it. In addition, administration + configuration is really nice, the community and mailing lists are active and helpful, and the performance has been great for our usage. We also use it heavily for geospacial stuff (using PostGIS), and I think Postgres is ahead of MySQL for that. I'm sure there are places online that do a much better job comparing them, but from my experiences with Postgres I would have no reason not to use it for any relational database needs.
    - pkteison 16y ago
      I know MySQL has drunk the cool-aid with innodb, but 10 years ago their FAQ entry about why ACID was a bad idea convinced me I didn't ever want to rely on them for anything I might normally rely on a database for. I'm cool with intentionally trading off database features for other features that I value more (like availability), but I want it to be an intentional choice, and then it shouldn't still pretend to be a database for me - admit it's an unreliable data store and just use this unreliable fast thing for a cache, not as a database.
  - rosser 16y ago
    However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. Depends on what you need replication to do for you. If you just want a full replica of your db, the built-in stuff is getting really good with 9.x (as far as I've heard; I have no direct experience with it). That said, the PostgreSQL hackers are specifically making most-case tools, and for most people, they're great. If you have more complicated needs, though, you'll need to look elsewhere. The built-in tools are all-or-nothing, and single-master. I've worked in multiple environments where we had needs that went beyond what they'd have offered, had they existed at the time; multi-master, replicating a subset of your database(s), and transforming the data in-flight during replication are real needs that organizations find themselves facing, and based on what the core committee has said about their plans, those aren't going to be addressed with the built-in tools any time soon, if ever.
pkteison 16y ago
Cons lists are massively oversimplified. What about the case reddit had recently, where their masters and slaves got out of sync (presumably because master didn't fully flush a commit to disk, e.g. write buffering was on somewhere)? Hot standby is a hard problem, and has far more cons than just "you can't recover from drop table". When the fortune 500 company I used to work at would try replication with Oracle, it was often responsible for poor write performance, and sometimes we'd end up with two points of failure (a problem with either of 2 dbs could now make a system effectively unavailable) where before there had been only one. Maybe it's gotten better, maybe postgres has a great implementation, maybe our DBAs were just horrible... but five years ago I developed the opinion that the only way to get it right with traditional databases is to teach the application layer to handle failure, and sacrifice some part of ACID - and I haven't seen anything to convince me to change my mind since.
- birken 16y ago
  I agree this is certainly a simplification, but I don't think one grossly so. A lot of the points you mention do not apply to our setup: - Our standby database is guaranteed to be consistent (barring some massive hardware failure), because we disable write caching on both the drives and the RAID controller, and the only data being exchanged is the write-ahead log. If the standby receives corrupt data, it will not apply it and will break off the replication and maintain its consistent (and stale) state. The underlying file system of the master and standby are completely distinct, thus any failure on either machine has no effect on the other. - Streaming replication has no effect on write performance on the master, as the replication is best-effort and asynchronous. Of course this is a redundancy trade-off, but one that is fine for our needs. If you need to commit every transaction to multiple machines, that is a whole different and more complicated problem. - Increasing availability was not the main goal for us. If our master database crashes, our website will break. However, our database state should be intact (either on the master or standby), and that was our goal. Having master-master replication or something along those lines is significantly more complicated and invites a host of problems that involve a lot of trade-offs. We accept that in case of master database hardware failure, a human is going to have to fix it. However, we believe with our current system, we can greatly reduce the time it will take.

33 points by martian 16y ago

midas 16y ago

Aren't there other ways to do database replication?

birken 16y ago
(post author) Yes, there are a lot of third-party replication solutions for Postgres (http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling http://wiki.postgresql.org/wiki/Replication,_Clustering,_and...), which offer various features (some of which offer even more complex and redundent replication options than the built-in replication). However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. And as discussed in the post, I believe that streaming replication is a very nice replication solution, and probably good enough for most applications.
- jswinghammer 16y ago
  How do you like Postgres? I'm doing a lot of work with MySQL lately and curious about Postgres.
  - birken 16y ago
    I can't say I have a ton of experience with Mysql, but I am a huge fan of Postgres. I think especially for people who just want their database to work, having to worry about which of the storage engines you should be using for MySQL would be a headache. With Postgres, they focused on one storage engine and making it work as broadly as possible, and I think that is the right way to go about it. In addition, administration + configuration is really nice, the community and mailing lists are active and helpful, and the performance has been great for our usage. We also use it heavily for geospacial stuff (using PostGIS), and I think Postgres is ahead of MySQL for that. I'm sure there are places online that do a much better job comparing them, but from my experiences with Postgres I would have no reason not to use it for any relational database needs.
  - pkteison 16y ago
    I know MySQL has drunk the cool-aid with innodb, but 10 years ago their FAQ entry about why ACID was a bad idea convinced me I didn't ever want to rely on them for anything I might normally rely on a database for. I'm cool with intentionally trading off database features for other features that I value more (like availability), but I want it to be an intentional choice, and then it shouldn't still pretend to be a database for me - admit it's an unreliable data store and just use this unreliable fast thing for a cache, not as a database.
- rosser 16y ago
  However, since they are all third-party solutions, there are such varying levels of complexity and support I think you would prefer to use the built-in versions of replication. Depends on what you need replication to do for you. If you just want a full replica of your db, the built-in stuff is getting really good with 9.x (as far as I've heard; I have no direct experience with it). That said, the PostgreSQL hackers are specifically making most-case tools, and for most people, they're great. If you have more complicated needs, though, you'll need to look elsewhere. The built-in tools are all-or-nothing, and single-master. I've worked in multiple environments where we had needs that went beyond what they'd have offered, had they existed at the time; multi-master, replicating a subset of your database(s), and transforming the data in-flight during replication are real needs that organizations find themselves facing, and based on what the core committee has said about their plans, those aren't going to be addressed with the built-in tools any time soon, if ever.

pkteison 16y ago

Cons lists are massively oversimplified. What about the case reddit had recently, where their masters and slaves got out of sync (presumably because master didn't fully flush a commit to disk, e.g. write buffering was on somewhere)? Hot standby is a hard problem, and has far more cons than just "you can't recover from drop table". When the fortune 500 company I used to work at would try replication with Oracle, it was often responsible for poor write performance, and sometimes we'd end up with two points of failure (a problem with either of 2 dbs could now make a system effectively unavailable) where before there had been only one. Maybe it's gotten better, maybe postgres has a great implementation, maybe our DBAs were just horrible... but five years ago I developed the opinion that the only way to get it right with traditional databases is to teach the application layer to handle failure, and sacrifice some part of ACID - and I haven't seen anything to convince me to change my mind since.

birken 16y ago
I agree this is certainly a simplification, but I don't think one grossly so. A lot of the points you mention do not apply to our setup: - Our standby database is guaranteed to be consistent (barring some massive hardware failure), because we disable write caching on both the drives and the RAID controller, and the only data being exchanged is the write-ahead log. If the standby receives corrupt data, it will not apply it and will break off the replication and maintain its consistent (and stale) state. The underlying file system of the master and standby are completely distinct, thus any failure on either machine has no effect on the other. - Streaming replication has no effect on write performance on the master, as the replication is best-effort and asynchronous. Of course this is a redundancy trade-off, but one that is fine for our needs. If you need to commit every transaction to multiple machines, that is a whole different and more complicated problem. - Increasing availability was not the main goal for us. If our master database crashes, our website will break. However, our database state should be intact (either on the master or standby), and that was our goal. Having master-master replication or something along those lines is significantly more complicated and invites a host of problems that involve a lot of trade-offs. We accept that in case of master database hardware failure, a human is going to have to fix it. However, we believe with our current system, we can greatly reduce the time it will take.